wrong result and panic when joining dual table

[jingyugao]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

select null in (select 1 );
select a in (select 0) xx from (select null as a) x;

2. What did you expect to see?

mysql [localhost:5731] {msandbox} (test) > select null in (select 1 );
+---------------------+
| null in (select 1 ) |
+---------------------+
|                NULL |
+---------------------+
1 row in set (0.00 sec)

mysql [localhost:5731] {msandbox} (test) > select a in (select 0) xx from (select null as a) x;
+------+
| xx   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

3. What did you see instead?

mysql> select null in (select 1 );
+---------------------+
| null in (select 1 ) |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

mysql> select a in (select 0) xx from (select null as a) x;
+------+
| xx   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   5.7.25-TiDB-v4.0.0-alpha-201-g702854f02

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @XuHuaiyu

[jingyugao]

select a in (select 0)xx from (select null as a)x

This sql will cause panic

[shenli]

@jingyugao Nice catch! Would you like to fix it?

[jingyugao]

@jingyugao Nice catch! Would you like to fix it?

I will have a try.

[fzhedu]

The running internals in MySQL is listed below:

mysql> explain select null in ( 1 )\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select (NULL = 1) AS `null in ( 1 )`
1 row in set (0.00 sec)

mysql> explain select null in (select 1 )\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1249
Message: Select 2 was reduced during optimization
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select (NULL = 1) AS `null in (select 1 )`
2 rows in set (0.00 sec)

We can conclude that select 1 is converted to 1.
TiDB runs select null in (1) correctly, so it is necessary to rewrite select 1 to 1.

[ghost]

I believe this is a duplicate of #3773

[shuke987]

duplicate with #3773