path: root/mysql-test/main/subselect_elimination.test

--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