#Enable transformations for InnoDB for ease of testing
SET optimizer_switch = 'subquery_to_derived=on';
CREATE TABLE t1(a INT);
CREATE TABLE t2(a INT);
INSERT INTO t1 VALUES(1), (2), (3), (4);
INSERT INTO t2 VALUES(1), (2);
CREATE TABLE t0 AS SELECT *FROM t1;
CREATE TABLE t3(a INT, b INT);
INSERT INTO t3 VALUES(1, 3), (2, 3);
ANALYZE TABLE t1, t2, t0, t3;

--echo #
--echo # example supported query
--echo #

# inner field present in SELECT list (t2.a)
let $query =
SELECT * FROM t1 WHERE(SELECT a FROM t2 WHERE t2.a = t1.a) > 0;

eval $query;
eval EXPLAIN $query;

# inner field not present in SELECT list (t3.a)
let $query =
SELECT * FROM t1 WHERE(SELECT b FROM t3 WHERE t3.a = t1.a) > 0;

eval $query;
eval EXPLAIN $query;

let $query =
SELECT * FROM t1 WHERE(SELECT ABS(a) FROM t2 WHERE t2.a = t1.a) > 0;

eval $query;
eval EXPLAIN $query;
--echo #
--echo # example supported query: more than one correlated field
--echo #
let $query =
SELECT * FROM t2
WHERE(SELECT a FROM t3
      WHERE t3.a = t2.a AND
            t3.b = t2.a) > 0;
eval $query;
eval EXPLAIN $query;

--echo #
--echo # example unsupported query(no non-equalities)
--echo #
let $query =
SELECT * FROM t3
WHERE(SELECT a FROM t2
      WHERE t2.a > t3.a) > 0;
eval $query;
eval EXPLAIN $query;

let $query =
SELECT * FROM t3
WHERE(SELECT a FROM t2
      WHERE t2.a != t3.a) > 0;
eval $query;
eval EXPLAIN $query;

--echo #
--echo # example unsupported query(<=>)
--echo #
INSERT INTO t2 VALUES(NULL),(NULL);
INSERT INTO t3 VALUES(NULL, 3);
let $query =
SELECT * FROM t3
WHERE(SELECT a FROM t2
      WHERE t2.a <=> t3.a);
--error ER_SUBQUERY_NO_1_ROW
eval $query;
eval EXPLAIN $query;
DELETE FROM t2 WHERE a IS NULL;
DELETE FROM t3 WHERE a IS NULL;

--echo #
--echo # example unsupported query (correlation not inside WHERE)
--echo #
let $query =
SELECT a,
       (SELECT SUM(a) + t3.b FROM t2) FROM t3;
eval $query;
eval EXPLAIN $query;

--echo #
--echo # example unsupported query(correlation not inside WHERE)
--echo #
let $query =
SELECT a,
       (SELECT SUM(a) OVER w FROM t2 WINDOW w AS(ORDER BY t3.a) LIMIT 1)
FROM t3;
eval $query;
eval EXPLAIN $query;

--echo #
--echo # example unsupported query(correlation not inside WHERE)
--echo #
let $query =
SELECT a FROM t2
 WHERE(SELECT SUM(b) FROM t3 GROUP BY a, t2.a LIMIT 1) > 0;
eval $query;
eval EXPLAIN $query;

--echo #
--echo # example unsupported query(correlation not inside WHERE)
--echo #
let $query =
SELECT a FROM t2
WHERE (SELECT SUM(b) FROM t3 GROUP BY a HAVING SUM(b) > t2.a LIMIT 1) > 0;
eval $query;
eval EXPLAIN $query;

--echo #
--echo # example unsupported query (aggregate not inside WHERE))
--echo # 
let $query =
SELECT a, 
       (SELECT t2.a FROM t2 ORDER BY t1.a LIMIT 1)
FROM t1;
eval $query;
eval EXPLAIN $query;

--echo #
--echo # example unsupported query (aggregate which aggregates outside subquery)
--echo #
let $query =
SELECT SUM(a),
       a, (SELECT MIN(a) FROM t2 WHERE a = COUNT(*))
FROM t1 GROUP BY a;
eval $query;
eval EXPLAIN $query;

--echo #
--echo # example unsupported query (aggregate which aggregates outside subquery)
--echo #
let $query =
SELECT SUM(a),
       a, (SELECT MIN(a) FROM t2 WHERE a = AVG(t1.a))
FROM t1 GROUP BY a;
eval $query;
eval EXPLAIN $query;

--echo #
--echo # Added cardinality check: aggregate to ensure exactly one row after we
--echo # add GROUP BY
--echo #
INSERT INTO t2 VALUES (2);
let $query=
SELECT * FROM t1 WHERE (SELECT a FROM t2 WHERE t2.a = t1.a) > 0;

--error ER_SUBQUERY_NO_1_ROW
eval $query;
eval EXPLAIN $query;
SET optimizer_switch = 'subquery_to_derived=default';
--error ER_SUBQUERY_NO_1_ROW
eval $query;
eval EXPLAIN $query;

SET optimizer_switch = 'subquery_to_derived=on';

--echo #
--echo # We have an aggregate, no need for adding COUNT(*):
--echo #
let $query=
SELECT * FROM t1 WHERE (SELECT COUNT(a) FROM t2 WHERE t2.a = t1.a) > 0;
eval $query;
eval EXPLAIN $query;

--echo #
--echo # The existing GROUP BY groups on another field that inner in where
--echo # predicate:
--echo #
let $query=
SELECT * FROM t1 WHERE (SELECT COUNT(a) FROM t3 WHERE t3.a = t1.a GROUP BY b) > 0;
eval $query;
eval EXPLAIN $query;

--echo #
--echo # Test case that used to yield wrong result before we corrected
--echo # computations of slice positions to accommodate non-hidden fields
--echo # after hidden ones.
--echo #
create table p(p_pkey int primary key);
create table l(l_pkey int,
               l_quantity int);

insert into p values (10), (20), (30), (40);
insert into l values (10, 100),
                     (10, 10),
                     (20, 200),
                     (10, 1);

SET optimizer_switch = 'subquery_to_derived=on';

let $query=
select * from l, p
where p_pkey = l_pkey and
      l_quantity < (select 0.9 * avg(l_quantity)
                    from l where l_pkey = p_pkey);

eval $query;
eval EXPLAIN $query;

DROP TABLE p, l;


DROP TABLE t0, t1, t2, t3;

--echo #
--echo # Test case arried over from subquery_scalar_to_derived.test which now
--echo # transforms. Caused initial problems due to the HAVING: the reference
--echo # WHERE ... = t4.b is realized as an Item_ref rather than an
--echo # Item_field, now handled.
--echo #
CREATE TABLE t2 (a INT, b INT);
CREATE TABLE t4 (a INT NOT NULL, b INT NOT NULL);
INSERT INTO t2 VALUES (1, 7), (2, 7), (2,10);
INSERT INTO t4 VALUES (4, 8), (3, 8), (5, 9), (12, 7), (1, 7),
                      (10, 9), (9, 6), (7, 6), (3, 9), (1, 10);
ANALYZE TABLE t2, t4;
let $query=
SELECT b, MAX(a) AS ma FROM t4
GROUP BY b HAVING ma < (SELECT MAX(t2.a) FROM t2 WHERE t2.b=t4.b);

eval $query;
eval EXPLAIN $query;

DROP TABLE t2, t4;

--echo #
--echo # Move the cardinality assert into the LEFT JOIN condition, lest we
--echo # lose outer rows due to empty scalar subquery in select list.
--echo #
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE TABLE t2 (a INT PRIMARY KEY, b INT);
CREATE TABLE t3 (c INT);

INSERT INTO t1 (a) VALUES (1), (2);
INSERT INTO t2 (a,b) VALUES (1,2), (2,3);
INSERT INTO t3 (c) VALUES (1), (2);
ANALYZE TABLE t1, t2, t3;

let $query=
SELECT (SELECT t1.a FROM t1, t2 WHERE t1.a = t2.b AND t2.a = t3.c ORDER BY t1.a) FROM t3;
eval $query;
eval EXPLAIN $query;

DROP TABLE t1, t2, t3;

--echo # 
--echo # Detect and skip transform if correlated field is inside a nested subquery
--echo #
CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
CREATE TABLE t2 SELECT DISTINCT * FROM t1;
ANALYZE TABLE t1, t2;

--echo # This one is nominally correlated inside a nested subquery, but it gets
--echo # unrolled before the transformation, so transformation sees only "t1.b",
--echo # not "(SELECT t1.b FROM DUAL)", so the transformation takes place.
let $query=
SELECT t1.a, SUM(t1.b)
FROM t1
WHERE t1.a = (SELECT t2.a
              FROM t2
              WHERE t2.a > (SELECT t1.b FROM DUAL) AND t1.a=t2.a)
GROUP BY t1.a ORDER BY t1.a LIMIT 30;

eval EXPLAIN $query;

--echo # This has grouping inside a nested subquery, soit does not get unrolled,
--echo # and transformation is not done
let $query=
SELECT t1.a, SUM(t1.b)
FROM t1
WHERE t1.a = (SELECT t2.a
              FROM t2
              WHERE t2.a > (SELECT SUM(t1.b) FROM DUAL) AND t1.a=t2.a)
GROUP BY t1.a ORDER BY t1.a LIMIT 30;

eval EXPLAIN $query;

--echo #
--echo # Correlated field is inside an ORDER BY optimized away. We should really
--echo # have been able to transform this.  Possible fix: update subquery's
--echo # dependency information after the optimization so it is no longer marked
--echo # as correlated. FIXME. This used to crash before because we had an
--echo # assert that correlated subquery have at least one correlated field.
--echo #
let $query=
SELECT t1.a, SUM(t1.b)
FROM t1
WHERE t1.a = (SELECT SUM(t2.b)
              FROM t2
              WHERE t2.a > 4 ORDER BY t1.b)
GROUP BY t1.a ORDER BY t1.a LIMIT 30;

eval EXPLAIN $query;

DROP TABLES t1, t2;

--echo #
--echo # Detect and skip transform for LIMIT/OFFSET
--echo #
CREATE TABLE t1 (
  id INTEGER NOT NULL ,
  contract_id INTEGER DEFAULT NULL,
  datestamp DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  KEY contract_id (contract_id),
  KEY idx_datestamp (datestamp)
);

INSERT INTO t1 VALUES
       (1,2,'2006-09-18 09:07:53'), (2,3,'2006-09-18 09:07:53'),
       (3,4,'2006-09-18 09:07:53'), (4,10,'2006-09-18 09:07:53'),
       (5,7,'2006-09-18 09:07:53'), (6,5,'2006-09-18 09:07:53'),
       (7,9,'2006-09-18 09:07:53'), (8,10,'2006-09-18 09:07:53'),
       (9,10,'2006-09-18 09:07:53'), (10,6,'2014-09-18 09:07:53');

CREATE TABLE t2 (id INTEGER NOT NULL, PRIMARY KEY (id));

INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
ANALYZE TABLE t1,t2;

EXPLAIN
SELECT (SELECT datestamp
        FROM t1
        WHERE contract_id = t2.id
        ORDER BY datestamp ASC
        LIMIT 1)
FROM t2;

DROP TABLE t1, t2;

--echo #
--echo # Detect and skip transform for correlated derived table in
--echo # FROM list of subquery
--echo #

CREATE TABLE t1(a INT, b INT DEFAULT 0);
INSERT INTO t1(a) VALUES (1), (2);
CREATE TABLE t2 SELECT * FROM t1;
ANALYZE TABLE t1, t2;

EXPLAIN
SELECT (SELECT dt.a
        FROM   (SELECT 1 AS a, t2.a AS b
                FROM t2
                HAVING t1.a) dt     # <----- outer reference inside derived table.
        WHERE dt.b=t1.a) AS subq    # <----- normal outer reference
FROM t1;

DROP TABLE t1, t2;

--echo #
--echo # Detect and skip transform for is the correlated subquery
--echo # has window functions
--echo #
CREATE TABLE t_a (a INT, b INT);
INSERT INTO t_a VALUES (4, 40), (1, 10), (2, 20), (2, 20), (3, 30);
CREATE TABLE t_b SELECT DISTINCT a FROM t_a;
ANALYZE TABLE t_a, t_b;

EXPLAIN
SELECT (SELECT SUM(t_b.a) OVER ()
        FROM t_b
        WHERE t_b.a = t_a.a) aa,
       b
FROM t_a
GROUP BY aa, b;

DROP TABLE t_a, t_b;


--echo #
--echo # Handle implicitly grouped COUNT with COALESCE
--echo #
CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1);
ANALYZE TABLE t1, t2;

let $query=
SELECT t1.id, ( SELECT COUNT(t.id)
                FROM t2 AS t
                WHERE t.id = t1.id ) AS c FROM t1;
--echo # Transformed. Would give wrong result without COALESCE
eval $query;
eval EXPLAIN $query;

--echo # Not transformed: COUNT is involved in expression in select list
let $query=
SELECT t1.id, ( SELECT COUNT(t.id)+2
                FROM t2 AS t
                WHERE t.id = t1.id ) AS c FROM t1;
eval EXPLAIN $query;

DROP TABLE t1, t2;

--echo #
--echo # Disallow correlated subquery in ON clause
--echo #

CREATE TABLE t1 (a INT, b INT);

EXPLAIN
SELECT COUNT(*)
FROM t1 a JOIN
     t1 outr
     ON a.a = (SELECT COUNT(*) FROM t1 inr WHERE inr.a = outr.a);

DROP TABLE t1;

--echo #
--echo # Bug#32215632: WL#13520: SEGFAULT IN SELECT_LEX::
--echo #               DECORRELATE_DERIVED_SCALAR_SUBQUERY_POST()
--echo #

CREATE TABLE t1 (f1 INTEGER, f2 INTEGER);

INSERT INTO t1 VALUES (1,2);

CREATE TABLE t2 LIKE t1;
INSERT INTO t2 SELECT * FROM t1;

SELECT (SELECT COUNT(t2.f1) FROM (t2) WHERE t2.f2 <> table1.f1
 AND t2.f2 != table1.f1) AS dt FROM (SELECT * FROM t1 ) AS table1;

DROP TABLE t1,t2;

--echo #
--echo # Bug#32216224: WL#13520: ASSERTION FAILURE IN
--echo #               SELECT_LEX_UNIT::ACCUMULATE_USED_TABLES
--echo #

CREATE TABLE t1 (f1 INTEGER, f2 INTEGER);

INSERT INTO t1 VALUES (1,2);

CREATE ALGORITHM=MERGE VIEW view_merge AS
SELECT (SELECT MAX(t1.f1) AS dt_f1 FROM (t1)
        WHERE t1.f2 > table1.f2 OR t1.f2 != 2)
  FROM (SELECT * FROM t1)  AS table1 ;

SELECT * FROM view_merge;

DROP TABLE t1;
DROP VIEW view_merge;

--echo #
--echo # Bug#32225812: WL#13520: ASSERTION FAILURE IN ADD_KEY_FIELD
--echo #               AT SQL/SQL_OPTIMIZER.CC
--echo #

CREATE TABLE t1 (f1 INTEGER, f2 INTEGER, f3 INTEGER,
                 PRIMARY KEY(f1), KEY(f2));

SELECT f1, (SELECT SUM(t2.f2) FROM (t1 as t2)
            WHERE t2.f3 = t1.f3 AND t2.f1 < t1.f1) AS dt
  FROM t1 WHERE f2 =3 GROUP BY f1;

DROP TABLE t1;

--echo #
--echo # Bug#32231084: WL#13520: SEGMENTATION FAULT IN
--echo #               QEP_SHARED_OWNER::IDX AT SQL_OPT_EXEC_SHARED.H
--echo #

CREATE TABLE t1 (f1 INTEGER, f2 VARCHAR(1), f3 VARCHAR(1), PRIMARY KEY(f1));

SELECT (SELECT MIN(t1.f1) FROM t1
        WHERE t1.f3 > t2.f3 OR t1.f3 = t2.f3)
FROM (( SELECT * FROM t1) AS t2 RIGHT JOIN t1 ON 1);

DROP TABLE t1;

--echo # Check if full group by checks work for fields from views

CREATE TABLE t1(a INT);
CREATE TABLE t2(a INT, b INT);
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE VIEW v2 AS SELECT * FROM t2;
INSERT INTO t1 VALUES(1), (2), (3), (4);
INSERT INTO t2 VALUES(1, 3), (2, 3);
SELECT * FROM v1 WHERE(SELECT b FROM v2 WHERE v2.a = v1.a) > 0;
DROP TABLE t1,t2;
DROP VIEW v1,v2;

--echo # Bug#32250083 : WL#13520: SEGFAULT IN QEP_SHARED_OWNER::IDX AT
--echo #	   SQL/SQL_OPT_EXEC_SHARED.H
--echo # Bug#32250359 : WL#13520: ASSERTION QUERY_BLOCK->IS_RECURSIVE()
--echo #                FAILED IN JOIN::MAKE_JOIN_PLAN()

CREATE TABLE t1 (f1 INTEGER, f2 INTEGER, f3 INTEGER,
                 f4 VARCHAR(1), PRIMARY KEY(f1));

CREATE VIEW v1 AS SELECT * FROM t1;

let $query = SELECT SUM(t2.f4),
 (SELECT COUNT(dt1.f2) FROM (t1 AS dt1) WHERE dt1.f3 = v1.f1 )
 FROM ( v1 RIGHT OUTER JOIN( t1 AS t2 STRAIGHT_JOIN t1 AS t3 ON 1) ON 1);

--error 1140
eval $query;

set sql_mode="";
eval explain $query;
eval $query;
set sql_mode=default;

DROP TABLE t1;
DROP VIEW v1;

--echo #
--echo # Bug#32288314: WL#13520: DIFFERENT NUMBER OF ROWS WITH
--echo #               OPTIMIZER_SWITCH SUBQUERY_TO_DERIVED=ON
--echo #

CREATE TABLE t1 (f1 INTEGER , f2 INTEGER);

INSERT INTO t1 VALUES (1,1), (2,2), (3,3);

SELECT /*+ SET_VAR(optimizer_switch='subquery_to_derived=OFF') */ *
FROM t1 WHERE ( SELECT COUNT(dt.f1) FROM t1 AS dt WHERE dt.f2 > t1.f2);

SELECT * FROM t1 WHERE ( SELECT COUNT(dt.f1) FROM t1 AS dt WHERE dt.f2 > t1.f2);

DROP TABLE t1;

--echo #
--echo # Bug#32303319: WL#13520: QUERY RETURNS EMPTY RESULTSET WITH
--echo #	       TRANSFORMATION ENABLED
--echo #

CREATE TABLE t1 (f1 INTEGER, f2 VARCHAR(1) , f3 VARCHAR(1), PRIMARY KEY(f1));

INSERT INTO t1 (f1,f2,f3) values (1,'a','e'),(2,'о','i'),(3,'7','j');

--sorted_result
SELECT /*+ SET_VAR(optimizer_switch='subquery_to_derived=OFF') */
  (SELECT MAX(dt.f1) AS max FROM t1 AS dt
   WHERE dt.f2 = dt1.f2 AND dt.f3 > 'h' ) AS field1
 FROM (t1 AS dt1, t1 AS dt2) GROUP BY field1;

--sorted_result
SELECT
  (SELECT MAX(dt.f1) AS max FROM t1 AS dt
   WHERE dt.f2 = dt1.f2 AND dt.f3 > 'h' ) AS field1
 FROM (t1 AS dt1, t1 AS dt2) GROUP BY field1;

DROP TABLE t1;

--echo #
--echo # Bug#32301860: WL#13520: ASSERTION FAILURE IN JOIN::MAKE_JOIN_PLAN()
--echo # AT SQL/SQL_OPTIMIZER.CC
--echo #

CREATE TABLE t1 (f1 INTEGER, f2 INTEGER, f3 VARCHAR(1), PRIMARY KEY(f1));

CREATE VIEW view_t1 AS SELECT * FROM t1;

let $query= SELECT
 (SELECT SUM(dt2.f2) FROM (t1 AS dt1 STRAIGHT_JOIN t1  AS dt2 ON 1)
 WHERE dt1.f3 = table1.f3) AS field1,
 MAX(table2.f2) AS field4
 FROM (view_t1 AS table1 RIGHT JOIN
 ((t1 AS table2 JOIN t1 AS table3 ON (table3 .f1 = table2.f2))) ON 1)
 WHERE table1 . f3 != 'x' ;

--error 1140
eval $query;

set sql_mode="";
eval explain $query;
eval $query;
set sql_mode=default;

DROP TABLE t1;
DROP view view_t1;

--echo #
--echo # Bug#32365780:MYSQL SREVER CRASH - SEGMENTATION FAULT IN
--echo #	      QUERY_BLOCK::DECORRELATE_DERIVED_SCALAR_SUBQUERY_POST
--echo #

CREATE TABLE t1 ( f1 INTEGER, f2 INTEGER, f3 INTEGER, f4 INTEGER);

let $query =
SELECT f1 FROM t1 AS table1
 WHERE f2 <> (SELECT f1 FROM t1 WHERE table1.f3 = (SELECT f4 FROM t1));

#Query should see transformation happening and also should not crash the
#server
eval explain $query;
eval $query;

DROP TABLE t1;

--echo #
--echo # Bug#32348682: WL#13520: WRONG RESULT WHEN PREDICATE IN A CORRELATED
--echo #                         SUBQUERY HAS EXPRESSIONS
--echo #
CREATE TABLE t1(a INTEGER, b INTEGER);
CREATE TABLE t2(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES(5, 5);
INSERT INTO t2 VALUES(1,4),(2,3);

let $query=SELECT * FROM t1 WHERE(SELECT a FROM t2 WHERE t2.a+t2.b = t1.a) > 0;

# Subquery should not be transformed into derived table
eval explain $query;
--error ER_SUBQUERY_NO_1_ROW
eval $query;

DROP TABLE t1,t2;

--echo #
--echo # Bug#32378012: ASSERTION `GOING.ELEMENTS >= 1' FAILED IN
--echo #	       SQL/SQL_RESOLVER.CC
--echo #

CREATE TABLE t1 (f1 INTEGER NOT NULL, f2 INTEGER);

let $query= SELECT (SELECT MIN(f2) FROM t1 AS t2
 WHERE t2.f2 = ISNULL(dt.f1)) AS field1 FROM t1 AS dt GROUP BY field1;

eval explain $query;
eval $query;

DROP TABLE t1;

SET optimizer_switch = 'subquery_to_derived=default';
# Local Variables:
# mode: sql
# sql-product: mysql
# comment-column: 48
# comment-start: "--echo # "
# fill-column: 80
# End: