How to find which part in where clause excluded data?

user_2256568 0
Viewed 46

I have a query A like this:

SELECT * FROM my_table WHERE foreign_key = 'abc' AND field1 = 'foo' AND field2 = 'bar';

Is there a way to find out which part of the where clause was responsible for the exclusion of data compared to a superset of the data? For example compared to query B:

SELECT * FROM my_table WHERE foreign_key = 'abc';

I know that you could take every part of the where clause in a seperate query and take the difference quantity of query B. Is there a more efficient way to do this or a best practice?

The goal is to mark the excluded data rows with a reason for their exclusion.

mysql
3 Answers

I will answer using the simplified query:

SELECT *
FROM my_table
WHERE field1 = 'foo' AND field2 = 'bar';

We can try using CASE expressions to label the cause of the failure:

SELECT
    id,
    CASE WHEN (field1 <> 'foo' OR field1 IS NULL) AND
              (field2 <> 'bar' OR field2 IS NULL)
         THEN 'field1,field2'
         WHEN (field1 <> 'foo' OR field1 IS NULL)
         THEN 'field1'
         WHEN (field2 <> 'bar' OR field2 IS NULL)
         THEN 'field2'
         ELSE 'pass' END AS reason
FROM my_table;

Note that this query doesn't actually have a WHERE clause; it returns all records, each one labelled either as a pass, or with the fields which would cause the failure.

user_18632290

is to mark the excluded data rows with a reason for their exclusion.

Then presumably you want to see the data which had previously been excluded - meaning you need to remove the predicates from the WHERE clause. If you simply add them to the SELECT clause you will see which are met and which are not - 

SELECT *,
(foreign_key = 'abc') AS foreign_key_rule,
(field1 = 'foo') AS field1_rule,
(field2 = 'bar') AS field2_rule
FROM my_table WHERE 1;
user_2239920

You could do something like this to find out why rows would be excluded:

select
  id,
  case
    when coalesce(foreign_key, '') != 'abc' then 'foreign_key not "abc"'
    when coalesce(field1, '') != 'foo' then 'field1 not "foo"'
    when coalesce(field2, '') != 'bar' then 'field2 not "bar"'
  end as exclusion_reason
from my_table
where not (foreign_key = 'abc' AND field1 = 'foo' AND field2 = 'bar')

Note that the where clause has been negated, by being wrapped in not (...), so only excluded rows are returned.

You could adapt this to mark excluded rows like this:

update my_table set
exclusion_reason = case
    when coalesce(foreign_key, '') != 'abc' then 'foreign_key not "abc"'
    when coalesce(field1, '') != 'foo' then 'field1 not "foo"'
    when coalesce(field2, '') != 'bar' then 'field2 not "bar"'
  end
where where not (foreign_key = 'abc' AND field1 = 'foo' AND field2 = 'bar')
user_2561960
Related