Mariadb bug - where condition with "in select" containing null-CodePudding

I found a strange bug (I guess) in mariadb

Suppose you have a table table1 with col1 and other table2 with col1 and you want to list all row in table1 whose col1 values exist in table2.

We could code this as:

select *
from table1
where col1 in (
  select col1 from table2
)

The result contains the expected rows if all data in col1 in table2 are not null.

However, if any values from table2 are null then it returns no rows.

This is unexpected to me and scary as I've used this clause many times.

CodePudding user response：

This is how in is defined to work in SQL: if any of the values in the list used by in are null, none match.

See "Law of the excluded fourth" section of https://en.wikipedia.org/wiki/Null_(SQL) for more info.

All SQL databases behave this way.

CodePudding user response：

I normally use exists, so I haven't struck this problem

select * from table1 t1 
where exists (select t2.col1 from table2 t2 
              where t2.col1 = t1.col1)

Not Tested