I have found out that in a where-in query if the nested query fails, the parent query returns all the records if the where condition field matches with the selected field in the inner query. Example: SELECT * FROM table_a WHERE field_only_in_parent_table IN ( SELECT field_only_in_parent_table FROM table_b WHERE name = 'John Doe')
If you run only the nested query it fails because the field does not exist for table_b If you run all the query, it returns all the records of table_a
Regards,
Flavio
This is not a bug. Returning all records of the parent table is the correct result, due to scope resolution.
The column field_only_in_parent_table does not have a table prefix so it is first checked whether it's a column of the immediate tables (table_b here). Since the column is not there, the next level tables are checked (table_a here). It is a there so the query is executed as:
SELECT * FROM table_a WHERE field_only_in_parent_table IN ( SELECT table_a.field_only_in_parent_table FROM table_b WHERE name = 'John Doe')
To be accurate the rows of table a that are returned are the ones wit non-null values in field_only_in_parent_table and only if table b has at least one row. If table b has 0 rows, then the query returns no rows.