What happens when you use REGEXP on columns that contain NULL values?

0-2 years experience

1. 1You have a table `users` with a nullable column `email`. You need to select rows where `email` matches the pattern '^admin@' using REGEXP. What rows will be returned if some `email` values are NULL?
2. 2If you run `SELECT * FROM orders WHERE notes REGEXP 'error'` and `notes` can be NULL, what does MySQL return for those rows?
3. 3Write a simple query that filters a nullable `description` column with REGEXP and ensures rows with NULL are excluded.

2-5 years experience

1. 1During a feature rollout you added a REGEXP filter on a nullable `description` column, and you notice some expected rows are missing. Walk me through how NULL handling in REGEXP could cause this and how you'd fix it.
2. 2Your QA reports that a search API returns fewer results when the underlying MySQL column contains NULLs. Explain why and propose a change to the query.
3. 3You need to count how many rows were excluded because the target column was NULL while also applying a REGEXP filter. How would you write that query?

5-8 years experience

1. 1Design a data pipeline that extracts rows based on REGEXP patterns from a large table where the target column is nullable. Discuss how you would handle NULLs efficiently and any indexing considerations.
2. 2Your team is experiencing performance degradation on a query that uses REGEXP on a nullable column with millions of rows. Explain the impact of NULL handling on the optimizer and propose optimizations.
3. 3We are migrating from MySQL to a new analytics store that doesn't treat REGEXP on NULLs the same way. How would you ensure semantic parity during migration?

8+ years experience

1. 1At an organization level you need to define a standard for handling nullable text fields in regex‑based searches across many services. What guidelines would you set and how would you enforce them to avoid bugs?
2. 2Consider a multi‑tenant SaaS platform where each tenant can define custom regex filters on nullable columns. How would you design the system to safely evaluate these filters, handling NULLs, performance, and security?
3. 3Your company is refactoring a legacy reporting system that heavily relies on REGEXP over nullable columns. Describe a migration strategy that preserves behavior while improving maintainability and testability.