Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
1.2
-
None
-
None
-
2018-17, 2018-18, 2018-19, 2018-20
Description
The handler builds an incorrect execution plan and the mentioned type of queries returns incorrect results. Here are a number of examples with their counterparts taken when vtable is enabled.
select sum(i) from cs2 where cs2.i not in ( select i from cs3 );
|
+--------+
|
| sum(i) |
|
+--------+
|
| 45 |
|
+--------+
|
1 row in set (0.216 sec)
|
|
|
MariaDB [test]> set infinidb_vtable_mode=0;
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [test]> select sum(i) from cs2 where cs2.i not in ( select i from cs3 );
|
+--------+
|
| sum(i) |
|
+--------+
|
| 66 |
|
+--------+
|
1 row in set (0.028 sec)
|
MariaDB [test]> set infinidb_vtable_mode=0;
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [test]> select sum(i) from cs2 where cs2.i in ( select i from cs3 );
|
ERROR 1815 (HY000): Internal error: IDB-1000: 'cs2' and 'cs3' are not joined.
|
|
|
MariaDB [test]> set infinidb_vtable_mode=1;
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [test]> select sum(i) from cs2 where cs2.i in ( select i from cs3 );
|
+--------+
|
| sum(i) |
|
+--------+
|
| 21 |
|
+--------+
|
1 row in set (0.023 sec)
|
MariaDB [test]> set infinidb_vtable_mode=1;
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [test]> select sum(i) from cs2 where cs2.i not in ( select sum(i) from cs3 );
|
+--------+
|
| sum(i) |
|
+--------+
|
| 66 |
|
+--------+
|
1 row in set (0.057 sec)
|
|
|
MariaDB [test]> set infinidb_vtable_mode=0;
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [test]> select sum(i) from cs2 where cs2.i not in ( select sum(i) from cs3 );
|
+--------+
|
| sum(i) |
|
+--------+
|
| NULL |
|
+--------+
|
1 row in set (0.026 sec)
|
