mirror of
https://github.com/MariaDB/server.git
synced 2025-10-30 18:36:12 +01:00
40b3525fcc
Workaround patch: Do not remove GROUP BY clause when it has
subquer(ies) in it.
remove_redundant_subquery_clauses() removes redundant GROUP BY clause
from queries in form:
expr IN (SELECT no_aggregates GROUP BY ...)
expr {CMP} {ALL|ANY|SOME} (SELECT no_aggregates GROUP BY ...)
This hits problems when the GROUP BY clause itself has subquer(y/ies).
This patch is just a workaround: it disables removal of GROUP BY clause
if the clause has one or more subqueries in it.
Tests:
- subselect_elimination.test has all known crashing cases.
- subselect4.result, insert_select.result are updated.
Note that in some cases results of SELECT are changed too (not just
EXPLAINs). These are caused by non-deterministic SQL: when running a
query like:
x > ANY( SELECT col1 FROM t1 GROUP BY constant_expression)
without removing the GROUP BY, the executor is free to pick the value
of t1.col1 from any row in the GROUP BY group (denote it $COL1_VAL).
Then, it computes x > ANY(SELECT $COL1_VAL).
When running the same query and removing the GROUP BY:
x > ANY( SELECT col1 FROM t1)
the executor will actually check all rows of t1.
242 lines
5.6 KiB
Text
242 lines
5.6 KiB
Text
--source include/have_innodb.inc
|
|
--source include/have_sequence.inc
|
|
|
|
--echo #
|
|
--echo # MDEV-28621 group by optimization incorrectly removing subquery where
|
|
--echo # subject buried in a function
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (i int) ;
|
|
INSERT INTO t1 VALUES (1),(2),(3);
|
|
|
|
--error ER_SUBQUERY_NO_1_ROW
|
|
SELECT 1 FROM t1
|
|
WHERE i in
|
|
( SELECT a+1
|
|
FROM
|
|
(SELECT (SELECT i FROM (SELECT 1 FROM t1) dt) AS a FROM t1) dt2
|
|
GROUP BY a
|
|
);
|
|
|
|
DROP TABLE t1;
|
|
|
|
create table t1 (a int, b int, c int);
|
|
insert into t1 select seq, seq, seq from seq_1_to_10;
|
|
create table t2 as select * from t1;
|
|
create table t20 as select * from t1;
|
|
create table t21 as select * from t1;
|
|
create table t3 as select * from t1;
|
|
select a, a in
|
|
(
|
|
select
|
|
(
|
|
select max(c) from t20 where t20.a<=t2.a
|
|
) as SUBQ1 from t2 group by SUBQ1+1
|
|
) as COL
|
|
from t1;
|
|
|
|
create view v2 as
|
|
select
|
|
a, b,
|
|
(select max(c) from t20 where t20.a<=t2.a) as SUBQ1,
|
|
(select max(c) from t21 where t21.a<=t2.a) as SUBQ2
|
|
from t2;
|
|
|
|
--echo # test partial elimination
|
|
|
|
explain
|
|
select
|
|
a,
|
|
a in (select a from v2 where a>3 and v2.SUBQ2>=0 group by v2.SUBQ1, v2.SUBQ2)
|
|
from t1;
|
|
|
|
--echo # test buried subselects in group by clause
|
|
|
|
select a, a in
|
|
(
|
|
select
|
|
(
|
|
select max(c) from t20 where t20.a<=t2.a
|
|
)*2 as SUBQ1 from t2 group by SUBQ1+1
|
|
) as COL
|
|
from t1;
|
|
|
|
drop view v2;
|
|
drop table t1, t2, t20, t21, t3;
|
|
|
|
--echo # Testcase from MDEV-32311
|
|
|
|
# some warning duplicated using ps-protocol
|
|
--disable_warnings
|
|
SELECT (
|
|
( WITH x ( x ) AS
|
|
(SELECT ( SELECT 'x' UNION SELECT 'x' ) FROM ( SELECT ( 'x' ) ) x)
|
|
SELECT x FROM x
|
|
WHERE x IN ( ( SELECT 'x' AND x GROUP BY x ) )
|
|
)
|
|
) AS SUBQ;
|
|
--enable_warnings
|
|
|
|
--echo # MDEV-32390:
|
|
|
|
CREATE TABLE t0 ( c43 DECIMAL ( 31 ) DEFAULT ( 45 ) ) ;
|
|
INSERT INTO t0 VALUES ( 13 ) , ( 29 ) ;
|
|
ALTER TABLE t0 ADD COLUMN c24 INT AFTER c43 ;
|
|
INSERT INTO t0 VALUES ( DEFAULT , DEFAULT ) , ( DEFAULT , DEFAULT ) ;
|
|
SELECT t1 . c22 AS c9 FROM ( SELECT ( SELECT + EXISTS ( SELECT -128 AS c29 ) <<
|
|
LOCATE ( t0 . c43 , t0 . c24 <= t0 . c24 NOT BETWEEN 4642475734208631537 AND
|
|
-108 , NULLIF ( 57 , -8 ) SOUNDS LIKE TRIM( TRAILING FROM 6107036197732405580 )
|
|
) - t0 . c43 AS c57 FROM t0 LIMIT 1 ) AS c22 FROM t0 ) AS t1 HAVING TRIM( CASE
|
|
t1 . c22 WHEN -16 THEN RAND ( ) % HEX ( t1 . c22 ) - SUBSTRING_INDEX ( t1 . c22,
|
|
':A9SEZxtjN,fKN*zR' , 'V*vhJb}&c%Op,[T[S,j`F9NDsK;\'8 4;m"
|
|
P,ce}1r"3ID1DN' ) >> NULLIF ( t1 . c22 , -95 ) ELSE -2 END IS TRUE
|
|
FROM t1 . c22 >= EXISTS ( SELECT t2 . c57 AS c59 FROM ( SELECT CASE c24 WHEN
|
|
-103 THEN 85 ELSE 22 END IS TRUE AS c57 FROM t0 ) AS t2 WHERE MOD ( 64 , 46 ) =
|
|
CONVERT ( 73 , BINARY ) % RAND ( ) IS NOT NULL = -65 GROUP BY c57 , c22 , c22
|
|
WINDOW w0 AS ( PARTITION BY t2 . c57 ) ) & PI ( ) ) ;
|
|
|
|
DROP TABLE t0;
|
|
|
|
--echo # MDEV-32309
|
|
|
|
SELECT
|
|
( WITH x ( x ) AS
|
|
(
|
|
WITH x ( x ) AS ( SELECT 1 ) SELECT ( SELECT x ) FROM x
|
|
)
|
|
SELECT x FROM x WHERE x IN ( SELECT NULL GROUP BY x )
|
|
) as col1 ;
|
|
|
|
--echo # MDEV-32391
|
|
|
|
CREATE TABLE t0 ( c15 INT , c33 INT ) engine=innodb;
|
|
INSERT INTO t0 ( c15 ) WITH t1 AS ( SELECT SQRT ( 123 ) NOT
|
|
REGEXP MOD ( 91 , -121 ) = ALL ( SELECT c15 AS c33 FROM t0 ) AS c49 FROM t0 )
|
|
SELECT t1 . c49 IS UNKNOWN AS c59 FROM t1 CROSS JOIN t0 AS t2
|
|
WHERE t1 . c49 = + EXISTS ( SELECT -5839312620871436105 AS c17 GROUP BY c49 )
|
|
BETWEEN -109 AND CHAR_LENGTH ( 2694839150676403988 ) - - LOWER ( -13 ) ;
|
|
DROP TABLE t0;
|
|
|
|
--echo # MDEV-28620
|
|
CREATE TABLE t1 ( a int);
|
|
INSERT INTO t1 VALUES (1),(2);
|
|
|
|
--error ER_SUBQUERY_NO_1_ROW
|
|
SELECT EXISTS
|
|
( SELECT 1 FROM t1 GROUP BY 1 IN (SELECT a FROM t1)
|
|
ORDER BY a + (SELECT 1 FROM t1 WHERE (1,2) NOT IN (SELECT 1,0))
|
|
) as SUBQ;
|
|
DROP TABLE t1;
|
|
|
|
--echo # MDEV-30842 Item_subselect::get_cache_parameters and UBSAN member
|
|
--echo # access within null pointer
|
|
|
|
CREATE TABLE x (x INT) ENGINE=InnoDB;
|
|
INSERT INTO x (x) VALUES (0);
|
|
--error ER_UPDATE_TABLE_USED
|
|
INSERT INTO x (x) VALUES (x IN (SELECT (SELECT x FROM (SELECT x FROM
|
|
(SELECT 0 IN (SELECT x=0 FROM (SELECT x FROM (SELECT (SELECT (SELECT (SELECT
|
|
(SELECT 0 AS x) FROM x AS x) IN (SELECT 0 AS x) AS x) FROM x AS x) IN
|
|
(SELECT x WHERE x=0) AS x FROM x AS x) AS x) AS x GROUP BY x) AS x FROM x) AS x)
|
|
AS x) IN (SELECT 0 AS x) AS x FROM x));
|
|
DROP TABLE x;
|
|
|
|
--echo # MDEV-28622: Item_subselect eliminated flag set but Item still
|
|
--echo # evaluated/used.
|
|
|
|
CREATE TABLE t1 ( a int) ;
|
|
CREATE VIEW v1 (i) AS SELECT EXISTS(SELECT 1) FROM t1;
|
|
|
|
SELECT 1 FROM v1 WHERE i NOT IN (SELECT i = 0 FROM v1 WHERE i = -1 GROUP BY i);
|
|
DROP TABLE t1;
|
|
DROP VIEW v1;
|
|
|
|
CREATE TABLE t(c1 INT);
|
|
|
|
SELECT 0
|
|
WHERE 0 IN
|
|
(
|
|
SELECT 0 FROM
|
|
(
|
|
SELECT 0 IN
|
|
(
|
|
SELECT
|
|
(
|
|
SELECT c1 FROM t
|
|
)
|
|
) AS c
|
|
FROM t
|
|
) AS dt
|
|
WHERE c GROUP BY c
|
|
);
|
|
|
|
DROP TABLE t;
|
|
|
|
create table t1 (a int, b int, c int);
|
|
insert into t1 select seq, seq, seq from seq_1_to_10;
|
|
create table t2 as select * from t1;
|
|
create table t20 as select * from t1;
|
|
create table t3 as select * from t1;
|
|
|
|
create view v2 as
|
|
select
|
|
a, b, (select max(c) from t20 where t20.a<=t2.a) as SUBQ1
|
|
from t2;
|
|
|
|
explain
|
|
select
|
|
a, a in (select a from v2 where a>3 group by v2.SUBQ1)
|
|
from t1;
|
|
|
|
prepare s from '
|
|
explain
|
|
select
|
|
a, a in (select a from v2 where a>3 group by v2.SUBQ1)
|
|
from t1';
|
|
|
|
execute s;
|
|
execute s;
|
|
execute s;
|
|
|
|
prepare s from '
|
|
explain
|
|
select
|
|
a, a in (select a from v2 where a>3 and SUBQ1+1 group by v2.SUBQ1)
|
|
from t1';
|
|
|
|
execute s;
|
|
execute s;
|
|
execute s;
|
|
|
|
delimiter //;
|
|
|
|
create procedure p1()
|
|
begin
|
|
explain
|
|
select
|
|
a, a in (select a from v2 where a>3 group by v2.SUBQ1)
|
|
from t1;
|
|
end//
|
|
|
|
create procedure p2()
|
|
begin
|
|
explain
|
|
select
|
|
a, a in (select a from v2 where a>3 and SUBQ1+1 group by v2.SUBQ1)
|
|
from t1;
|
|
end//
|
|
|
|
delimiter ;//
|
|
|
|
call p1();
|
|
call p1();
|
|
call p2();
|
|
call p2();
|
|
drop procedure p1;
|
|
drop procedure p2;
|
|
|
|
|
|
drop view v2;
|
|
drop table t1,t2,t3,t20;
|
|
|
|
--echo # end of 10.4 tests
|