Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2.14, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
Description
The following test case demonstrates the problem:
create table t1 (a int, b int, c int, d int);
|
create table t2 (e int, f int, g int);
|
insert into t1 values
|
(1,1,18,1), (2,1,25,1), (1,3,40,1), (2,1,15,4),
|
(4,2,24,4), (3,2,23,1), (1,2,40,2), (3,4,17,2),
|
(5,5,65,1), (2,3,70,3), (1,4,35,3), (2,3,25,3);
|
insert into t2 values
|
(1,2,38), (2,3,15), (1,3,40), (1,4,35),
|
(2,2,70), (3,4,23), (5,5,12), (5,4,17),
|
(3,3,17), (4,2,24), (2,5,25), (5,1,65);
|
|
|
select * from t1 where (a>1) and
|
(a) in
|
(
|
select sum(e) over (partition by f)
|
from t2
|
where e<5
|
)
|
;
|
The output for the above query is:
MariaDB [test]> select * from t1 where (a>1) and
|
-> (a) in
|
-> (
|
-> select sum(e) over (partition by f)
|
-> from t2
|
-> where e<5
|
-> )
|
-> ;
|
Empty set (0.00 sec)
|
This not correct as we have:
ariaDB [test]> select sum(e) over (partition by f)
|
-> from t2
|
-> where e<5
|
-> ;
|
+------------------------------+
|
| sum(e) over (partition by f) |
|
+------------------------------+
|
| 7 |
|
| 6 |
|
| 6 |
|
| 4 |
|
| 7 |
|
| 4 |
|
| 6 |
|
| 7 |
|
| 2 |
|
+------------------------------+
|
|
|
MariaDB [test]> select a from t1 where (a>1);
|
+------+
|
| a |
|
+------+
|
| 2 |
|
| 2 |
|
| 4 |
|
| 3 |
|
| 3 |
|
| 5 |
|
| 2 |
|
| 2 |
|
+------+
|
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
EXPLAIN EXTENDED for this query shows that in-to-exists transformation is applied to the
IN subquery predicate:
MariaDB [test]> explain extended
|
-> select * from t1 where (a>1) and
|
-> (a) in
|
-> (
|
-> select sum(e) over (partition by f)
|
-> from t2
|
-> where e<5
|
-> )
|
-> ;
|
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | Using where |
|
| 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | Using where; Using temporary |
|
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
|
2 rows in set, 1 warning (0.00 sec)
|
|
|
MariaDB [test]> show warnings;
|
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where `test`.`t1`.`a` > 1 and <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select sum(`test`.`t2`.`e`) over ( partition by `test`.`t2`.`f`) from `test`.`t2` where `test`.`t2`.`e` < 5))) |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
The transformation is applied because the left part of the IN predicate is of the INTEGER type while the right part is of the DECIMAL part.
However using in-to-exists transformation for IN subqueries with window functions is not valid.
mysql-8.0.11 cannot handle this query:
mysql> select * from t1 where (a>1) and
|
-> (a) in
|
-> (
|
-> select sum(e) over (partition by f)
|
-> from t2
|
-> where e<5
|
-> )
|
-> ;
|
ERROR 1235 (42000): This version of MySQL doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions'
|
Before using in-to-exists transformation here we have to wrap the subquery into a derived table.
Such wrapping will work for non-correlated IN subqueries.
In general case with a correlated IN subquery it will lead to a correlated derived table that still can be materialized for different values of external references in the same way as derived tables built in the split optimization that is used for non-mergeable derived tables.