Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.3(EOL), 10.4(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
Description
CREATE TABLE t1 (i int); |
INSERT INTO t1 VALUES (1),(2); |
|
CREATE TABLE t2 (c int); |
INSERT INTO t2 VALUES (3),(2),(3); |
|
SELECT * FROM t1 WHERE i IN (SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 ); |
SET optimizer_switch='materialization=off'; |
SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 ); |
MariaDB [test]> SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
|
+------+
|
| i |
|
+------+
|
| 1 |
|
| 2 |
|
+------+
|
2 rows in set (0,000 sec)
|
|
MariaDB [test]> explain extended SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
|
+------+--------------+-------------+--------+---------------+--------------+---------+-----------+------+----------+-----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------+-------------+--------+---------------+--------------+---------+-----------+------+----------+-----------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
|
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 8 | test.t1.i | 1 | 100.00 | Using where |
|
| 2 | MATERIALIZED | t2 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary |
|
+------+--------------+-------------+--------+---------------+--------------+---------+-----------+------+----------+-----------------+
|
3 rows in set, 1 warning (0,000 sec)
|
|
Note (Code 1003): /* select#1 */ select `test`.`t1`.`i` AS `i` from <materialize> (/* select#2 */ select count(0) over ( partition by `test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where `test`.`t1`.`i` = `<subquery2>`.`COUNT(*) OVER (PARTITION BY c)`
|
|
MariaDB [test]> set optimizer_switch='materialization=off';
|
Query OK, 0 rows affected (0,000 sec)
|
|
MariaDB [test]> SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
|
Empty set (0,000 sec)
|
|
MariaDB [test]> explain extended SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
|
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-----------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
|
| 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary |
|
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-----------------+
|
2 rows in set, 1 warning (0,001 sec)
|
|
Note (Code 1003): /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` where <expr_cache><`test`.`t1`.`i`>(<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select count(0) over ( partition by `test`.`t2`.`c`) from `test`.`t2`)))
|