One of my colleagues made a typo in a query today that led to me discovering this issue — I think it’s a bug, though it may just be how the language is defined. But certainly a subquery that cannot run should not act as if it returns TRUE.

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table foo (fid tinyint unsigned not null primary key); create table bar (bid char(1)

not null default ”);

Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> insert into foo (fid) VALUES (1),(2); insert into bar (bid) VALUES (’1′),(’a');

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0
Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0
mysql> explain select * from foo where fid in (select fid from bar)\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: foo

         type: index

possible_keys: NULL

          key: PRIMARY

      key_len: 1

          ref: NULL

         rows: 2

        Extra: Using where; Using index

*************************** 2. row ***************************

           id: 2

  select_type: DEPENDENT SUBQUERY

        table: bar

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 2

        Extra: Using where

2 rows in set (0.00 sec)
mysql> select * from foo where fid in (select fid from bar);

+—–+

| fid |

+—–+

|   1 |

|   2 |

+—–+

2 rows in set (0.00 sec)
mysql> select fid from bar;

ERROR 1054 (42S22): Unknown column ‘fid’ in ‘field list’

mysql>

Part of me thinks there’s an implicit join going on or something, but I’m not sure how that’s working.

So, should select * from foo where fid in (select fid from bar); work? If so, why is that?