in mysql-test/include/subquery_scalar_to_derived.inc [99:1136]
LEFT JOIN (SELECT COUNT(a) AS cnt FROM t2) AS lj1 ON TRUE
LEFT JOIN (SELECT MAX(a) * 4 AS mx FROM t2) AS lj2 ON TRUE
WHERE t1.a > cnt AND t1.a < mx;
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # If we have an implicit grouping we we know that cardinality
--echo # of result set is one, so no need for runtime checking of the
--echo # cardinality of the derived tables
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t3);
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # If not, detect if we see more than one row in subquery
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t3 GROUP BY a);
--error ER_SUBQUERY_NO_1_ROW
eval $query;
--replace_regex $elide_costs
eval explain $query;
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2 UNION SELECT 1);
--error ER_SUBQUERY_NO_1_ROW
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # Should work, {2} \ {1} == {2}
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2 EXCEPT SELECT 1);
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # Should fail, {2} U {3} \ {1} == {2, 3}
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2 UNION SELECT 3 EXCEPT SELECT 1);
--error ER_SUBQUERY_NO_1_ROW
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # Should return no rows, since the intersection is the empty set
--echo # {2} ∩ {1} == {}
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2 INTERSECT SELECT 1);
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # Should return no rows, since the result is the empty set
--echo # {2} \ {2} == {}
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2 EXCEPT SELECT 2);
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # This should give one row: {2} ∩ {2} == {2}
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2 INTERSECT SELECT 2);
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # This should give one row: {2} ∩ {2} == {2}
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2 INTERSECT ALL SELECT 2);
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # This should remove the duplicate: {1, 1} ∩ALL {1} == {1}, so ok
let $query = SELECT * FROM t1
WHERE t1.a > ((SELECT COUNT(a) AS cnt FROM t2 GROUP BY a LIMIT 2) INTERSECT ALL SELECT 1);
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # ditto
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2 GROUP BY a INTERSECT ALL SELECT 1);
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # Empty scalar subquery
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT a from t1 WHERE false);
eval $query;
--replace_regex $elide_costs
eval explain $query;
let $query = SELECT a + (SELECT a from t1 WHERE false) FROM t1;
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # If we limit the cardinality, it should work:
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2 UNION SELECT 1 LIMIT 1);
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # Check that offset/limit doesn't fool us:
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2 UNION SELECT 1 LIMIT 1 OFFSET 1);
eval $query;
--replace_regex $elide_costs
eval explain $query;
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2 UNION SELECT 1 LIMIT 1 OFFSET 0);
eval $query;
--replace_regex $elide_costs
eval explain $query;
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2 UNION SELECT 1 LIMIT 2 OFFSET 0);
--error ER_SUBQUERY_NO_1_ROW
eval $query;
--replace_regex $elide_costs
eval explain $query;
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2 UNION SELECT 1 LIMIT 2 OFFSET 1);
eval $query;
--replace_regex $elide_costs
eval explain $query;
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2 UNION (SELECT 1 LIMIT 1));
--error ER_SUBQUERY_NO_1_ROW
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo Without aggregation
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT a FROM t2 LIMIT 1);
eval $query;
--replace_regex $elide_costs
eval explain $query;
let $query = SELECT * FROM t1
WHERE t1.a > (SELECT a FROM t2);
--error ER_SUBQUERY_NO_1_ROW
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo In the below, the query block under consideration for transformation is
--echo the outer subquery. It
--echo
--echo a) contains a scalar subquery in its select list
--echo b) is implicitly grouped, cf. SUM(a)
--echo
--echo so we trigger an attempt to put the grouping into a separate derived
--echo table. But we also see that it contains an aggregate that has an outer
--echo reference, SUM(t3.a), so we leave it untouched.
--replace_regex $elide_costs
EXPLAIN
SELECT (SELECT SUM(a) + (SELECT SUM(t1.a) FROM t1) + SUM(t3.a) FROM t2) FROM t3;
--echo Don't try transform grouping into a derived table if we have a reference
--echo to the scalar subquery in the HAVING clause
--replace_regex $elide_costs
EXPLAIN
SELECT SUM(a), (SELECT SUM(b) FROM t3) scalar FROM t1 HAVING SUM(a) > scalar;
--echo #
--echo # Check that we disable transform if we set a user variable in the query
--echo # cf. WL#12885 requirement FR#6.
--echo #
--replace_regex $elide_costs
EXPLAIN SELECT t1.a + (@foo:=3) FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2);
--echo #
--echo # Check that RAND precludes transform
--echo #
let $query =
SELECT DISTINCT 3 + (SELECT COUNT(a) + RAND() FROM t1) FROM t1;
--replace_regex $elide_costs
eval explain $query;
eval SELECT COUNT(*) > 1 FROM ($query) AS dt;
--echo #
--echo # 1.1 J O I N C O N D I T I O N containing scalar subquery
--echo #
let query = SELECT t1.a, t2.a
FROM t1
JOIN t2
ON t1.a+t2.a = (SELECT COUNT(*) FROM t1);
--sorted_result
eval $query;
--replace_regex $elide_costs
eval explain $query;
let query = SELECT t1.a, t2.a, t3.a
FROM t1
JOIN t2
ON t1.a+t2.a = (SELECT COUNT(*) FROM t1)
JOIN t3
ON t1.a + (SELECT MIN(a) FROM t1) = t3.b;
--sorted_result
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo OUTER join
let query = SELECT t1.a, t2.a, t3.a
FROM t1
LEFT JOIN ( t2
JOIN t3
ON t2.a = (SELECT COUNT(*) FROM t1) )
ON t1.a + (SELECT MIN(a) FROM t1) = t3.b;
eval $query;
--replace_regex $elide_costs
eval explain $query;
# verify result with ordinary execution path
SET optimizer_switch='subquery_to_derived=default';
eval $query;
SET optimizer_switch='subquery_to_derived=on';
--echo This is a manually transformed version of the above.
let query =
SELECT t1.a, t2.a, t3.a
FROM t1
LEFT JOIN (SELECT MIN(a) FROM t1) derived_1
ON TRUE
LEFT JOIN ( t2
LEFT JOIN (SELECT COUNT(*) FROM t1) AS derived_2
ON TRUE
JOIN t3
ON t2.a = derived_2.`COUNT(*)` )
ON t1.a + derived_1.`MIN(a)` = t3.b;
eval $query;
--replace_regex $elide_costs
eval explain $query;
let query = SELECT t1.a, t2.a, t3.a
FROM t1
STRAIGHT_JOIN ( t2
STRAIGHT_JOIN t3
ON t2.a = (SELECT COUNT(*) FROM t1) )
ON t1.a + (SELECT MIN(a) FROM t1) = t3.b;
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo #
--echo # 2. S E L E C T list expression subqueries
--echo #
let $query = SELECT a + (SELECT -SUM(a) FROM t1) AS cnt FROM t2;
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # Equivalent manually recrafted query: same plan as previous
let $query = SELECT a + derived.cnt
FROM t2
LEFT OUTER JOIN (SELECT -SUM(a) AS cnt FROM t1) AS derived
ON TRUE;
--sorted_result
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo #
--echo # 2.1 S E L E C T list expression subqueries and UNION
--echo #
let $query = SELECT a + (SELECT SUM(a) FROM t1) FROM t1 UNION ALL
SELECT a + (SELECT SUM(a) FROM t1) FROM t1;
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo #
--echo # 3. N E S T E D scalar subqueries
--echo #
let $query = SELECT a + (SELECT SUM(a) + (SELECT COUNT(a) FROM t1) FROM t1) AS cnt FROM t2;
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # Equivalent manually recrafted query: same plan as previous
let $query =
SELECT (t2.a + derived_1_0.sum_plus_cnt) AS cnt
FROM t2
LEFT JOIN (SELECT (derived_2_0.tmp_aggr_1 + derived_2_1.count_a) AS sum_plus_cnt
FROM (SELECT STRAIGHT_JOIN SUM(t1.a) AS tmp_aggr_1 from t1) derived_2_0
LEFT JOIN (SELECT COUNT(t1.a) AS count_a from t1) derived_2_1
ON TRUE) derived_1_0
ON TRUE;
--sorted_result
eval $query;
--replace_regex $elide_costs
eval explain $query;
# This query actually belongs in group 5 below. It has an original
# derived table and crashed at one point, so include here anyway,
# before we drop these base tables.
SELECT a + (SELECT SUM(a) + (SELECT COUNT(a) FROM t1)
FROM (SELECT * from t1) t11) AS cnt FROM t2;
SELECT AVG(a) OVER () AS `avg`,
a + (SELECT SUM(a) + (SELECT COUNT(a) FROM t1)
FROM (SELECT * from t1) t11) AS cnt FROM t2;
DROP TABLE t0, t1, t2, t3;
--echo #
--echo # 4. C O R R E L A T E D query to derived with LATERAL
--echo #
--echo # Note: this feature has been disabled for WL#12885.
--echo # The tests are kept in case we re-enable this transformation
--echo #
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 (a) VALUES (1), (2);
CREATE TABLE t2 SELECT * FROM t1;
ANALYZE TABLE t1, t2;
let $query =
SELECT (WITH RECURSIVE dt AS (SELECT t1.a AS a UNION
SELECT a+1 FROM dt WHERE a<10)
SELECT t1.a * CONCAT(COUNT(*), '.', FLOOR(AVG(dt.a)))
FROM dt) AS subq
FROM t1;
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # Equivalent manually recrafted query: same plan as previous
let $query =
SELECT derived0.cnct AS subq
FROM t1
LEFT JOIN LATERAL (WITH RECURSIVE dt AS (SELECT t1.a AS a UNION
SELECT (dt.a + 1)
FROM dt WHERE dt.a < 10)
SELECT t1.a * CONCAT(COUNT(0), '.', FLOOR(AVG(dt.a))) AS cnct
FROM dt) derived0
ON TRUE;
--sorted_result
eval $query;
--replace_regex $elide_costs
eval explain $query;
DROP TABLE t1, t2;
--echo # Detect correlation outside of transformed query block which
--echo # is not caught by the scalar aggregate function inspection in
--echo # Item_singlerow_subselect::collect_scalar_subqueries. If there
--echo # is such a subquery, we skip transformation of that block.
CREATE TABLE t1(i INT);
CREATE TABLE t2(a INT);
CREATE TABLE t3(x INT);
ANALYZE TABLE t1, t2, t3;
--replace_regex $elide_costs
EXPLAIN
SELECT (
SELECT (SELECT COUNT(*) FROM t2) +
(SELECT AVG(a)
FROM t2
WHERE t2.a = t3.x) AS aggs
FROM t1
) AS bignest
FROM t3;
--echo # Without that subquery, we transform the level 2 query block,
--echo # but the the outermost still contains its subquery.
--replace_regex $elide_costs
EXPLAIN
SELECT (
SELECT (SELECT COUNT(*) FROM t2) AS aggs
FROM t1
) AS bignest
FROM t3;
DROP TABLE t1, t2, t3;
--echo #
--echo # 5. S U B Q U E R Y I N S E L E C T L I S T + G R O U P E D
--echo # O U T E R Q U E R Y
--echo #
CREATE TABLE t1 (a INT NOT NULL, b SMALLINT);
INSERT INTO t1 VALUES (12,12);
ANALYZE TABLE t1;
--echo # subquery is separate in SELECT list
let $query =
SELECT (SELECT COUNT(*)
FROM t1
WHERE a=11725) AS tot,
IFNULL(MAX(b),0)+1 + 5 AS mx
FROM t1
WHERE false;
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # subquery is part of expression with aggregate in SELECT list
let $query =
SELECT (SELECT COUNT(*)
FROM t1
WHERE a=11725) +
IFNULL(MAX(b),0)+1 + 5 AS mx
FROM t1
WHERE false;
eval $query;
--replace_regex $elide_costs
eval explain $query;
INSERT INTO t1 VALUES (13, 12);
--echo # outer query has DISTINCT, verify it is retained
let $query =
SELECT DISTINCT (SELECT COUNT(*)
FROM t1) +
IFNULL(MAX(b),0)+1 + 5 AS mx
FROM t1
WHERE a > 5
GROUP BY a;
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # outer query has LIMIT: verify it is retained
let $query =
SELECT (SELECT COUNT(*)
FROM t1) +
IFNULL(MAX(b),0)+1 + 5 AS mx
FROM t1
GROUP BY a LIMIT 1;
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # The subquery under under consideration for transformation contains a
--echo # aggregate function (MIN(t1_outer.a) and we have no explicit grouping,
--echo # so it would a priori be a candidate putting in a derived table. But
--echo # here, the aggregation function is aggregated in a query block outer to
--echo # the one being transformed. This means we do not need to push it into a
--echo # derived table.
let $query =
SELECT
(SELECT (SELECT COUNT(*)
FROM t1) +
MAX(t1.b) + MIN(t1_outer.a) AS tot
FROM t1) FROM t1 AS t1_outer;
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # Aggregate aggregates in the transformation query block, but is
--echo # contained in a subquery. Currently, this blocks transformation, but can
--echo # be fixed if we let Item::transform visit subqueries.
let $query =
SELECT (SELECT COUNT(*)
FROM t1) +
MAX(b) +
(SELECT MIN(a) + AVG(top.a) FROM t1)
AS tot
FROM t1 top;
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo # correlated version:
--echo # explicit grouping, no need for moving the grouping down into a
--echo # derived table
let $query =
SELECT (SELECT COUNT(*) + `outer`.a
FROM t1) +
IFNULL(MAX(b),0)+1 + 5 AS mx
FROM t1 AS `outer`
GROUP BY a;
eval $query;
--replace_regex $elide_costs
eval explain $query;
--echo Translated first step by hand
SELECT (SELECT COUNT(*) + derived_1.d_1 FROM t1) +
IFNULL(derived_1.`MAX(b)`,0) + 1 + 5 AS mx
FROM (SELECT STRAIGHT_JOIN MAX(outer_t.b) AS `MAX(b)`,
outer_t.a AS d_1
FROM t1 outer_t
GROUP BY outer_t.a) derived_1;
--echo Translated by hand
SELECT (derived_1.`COUNT(*) + outer_t.a` +
IFNULL(derived_0.`MAX(b)`,0)) + 1 + 5 AS mx
FROM (SELECT STRAIGHT_JOIN MAX(outer_t.b) AS `MAX(b)`,
outer_t.a AS d_1
FROM t1 outer_t
GROUP BY outer_t.a) derived_0
LEFT JOIN LATERAL (SELECT (COUNT(0) + derived_0.d_1)
AS `COUNT(*) + outer_t.a`
FROM t1) derived_1
ON(true)
WHERE true;
--echo # correlated version, with outer aggregate, not transformed
let $query =
SELECT (SELECT COUNT(*) + MAX(outer_t.b)
FROM t1) +
IFNULL(MAX(b),0)+1 + 5 AS mx
FROM t1 AS outer_t
GROUP BY a;
eval $query;
--replace_regex $elide_costs
eval explain $query;
DROP TABLE t1;
--echo #
--echo # More complex example
--echo #
CREATE VIEW events_digest AS
SELECT * FROM performance_schema.events_statements_summary_by_digest;
--replace_regex /^[\t.0-9]*$/xxxxx/
SELECT s2.avg_us avg_us,
IFNULL(SUM(s1.cnt)/NULLIF((SELECT COUNT(*) FROM
events_digest), 0), 0) percentile
FROM sys.`x$ps_digest_avg_latency_distribution` AS s1
JOIN sys.`x$ps_digest_avg_latency_distribution` AS s2
ON s1.avg_us <= s2.avg_us
GROUP BY s2.avg_us
HAVING IFNULL(SUM(s1.cnt)/ NULLIF((SELECT COUNT(*) FROM events_digest), 0), 0) > 0.95
ORDER BY percentile LIMIT 1;
# redundantly repeated query here, but "$query = ;" plus "eval $query"
# loses backticks
--replace_regex $elide_costs
EXPLAIN
SELECT s2.avg_us avg_us,
IFNULL(SUM(s1.cnt)/NULLIF((SELECT COUNT(*) FROM
events_digest), 0), 0) percentile
FROM sys.`x$ps_digest_avg_latency_distribution` AS s1
JOIN sys.`x$ps_digest_avg_latency_distribution` AS s2
ON s1.avg_us <= s2.avg_us
GROUP BY s2.avg_us
HAVING IFNULL(SUM(s1.cnt)/ NULLIF((SELECT COUNT(*) FROM events_digest), 0), 0) > 0.95
ORDER BY percentile LIMIT 1;
--echo # Manually translated, equivalent query (except we need to
--echo # include the derived_1_1.`COUNT(*)` in the select list for it
--echo # to be resolvable in the HAVING clause), and we need to
--echo # disable strict group checking: safe, but not recognized as
--echo # such by server..
SET sql_mode='';
--replace_regex $elide_costs
EXPLAIN
SELECT s2.avg_us AS avg_us,
IFNULL((SUM(s1.cnt) / NULLIF(derived_1_0.`COUNT(*)`,0)),0) AS percentile,
derived_1_1.`COUNT(*)`
FROM sys.`x$ps_digest_avg_latency_distribution` s1 JOIN
sys.`x$ps_digest_avg_latency_distribution` s2 LEFT JOIN
(SELECT COUNT(0) AS `COUNT(*)`
FROM performance_schema.events_statements_summary_by_digest) derived_1_0
ON(TRUE) LEFT JOIN
(SELECT COUNT(0) AS `COUNT(*)`
FROM performance_schema.events_statements_summary_by_digest) derived_1_1
ON(TRUE)
WHERE (s1.avg_us <= s2.avg_us)
GROUP BY s2.avg_us
HAVING (IFNULL((SUM(s1.cnt) / NULLIF(derived_1_1.`COUNT(*)`,0)),0) > 0.95)
ORDER BY percentile LIMIT 1;
SET sql_mode=default;
--echo # outer query has window: verify it is retained on outer level
--replace_regex /^[\t.0-9]*$/xxxxx/
SELECT AVG(s2.avg_us) OVER () + 3 AS avgsum,
s2.avg_us avg_us,
s2.avg_us avg_us2,
SUM(s2.avg_us) OVER () + 3 AS avgsum2,
IFNULL(SUM(s1.cnt)/NULLIF((SELECT COUNT(*) FROM
events_digest), 0), 0) percentile
FROM sys.`x$ps_digest_avg_latency_distribution` AS s1
JOIN sys.`x$ps_digest_avg_latency_distribution` AS s2
ON s1.avg_us <= s2.avg_us
GROUP BY s2.avg_us
HAVING IFNULL(SUM(s1.cnt)/ NULLIF((SELECT COUNT(*) FROM events_digest), 0), 0) > 0.95
ORDER BY percentile LIMIT 1;
DROP VIEW events_digest;
--echo # An example with aggregates in ORDER BY and HAVING not seen in SELECT list
CREATE TABLE t1 (
school_name VARCHAR(45) NOT NULL,
country VARCHAR(45) NOT NULL,
funds_requested FLOAT NOT NULL,
schooltype VARCHAR(45) NOT NULL
);
INSERT INTO t1 VALUES ("the school", "USA", 1200, "Human");
ANALYZE TABLE t1;
let $query=
SELECT COUNT(country) AS countrycount,
SUM(funds_requested) AS smcnt,
country,
(SELECT SUM(funds_requested) FROM t1) AS total_funds,
ROW_NUMBER() OVER (ORDER BY STDDEV_POP(funds_requested)) AS rn
FROM t1
GROUP BY country
HAVING AVG(funds_requested) > 0
ORDER BY SUM(ABS(funds_requested));
eval $query;
--replace_regex $elide_costs
eval EXPLAIN $query;
SET sql_mode='';
let $query=
SELECT COUNT(country) AS countrycount,
SUM(funds_requested) AS smcnt,
country,
(SELECT SUM(funds_requested) FROM t1) AS total_funds,
ROW_NUMBER() OVER (ORDER BY STDDEV_POP(funds_requested)) AS rn
FROM t1
HAVING AVG(funds_requested) > 0
ORDER BY SUM(ABS(funds_requested));
eval $query;
--replace_regex $elide_costs
eval EXPLAIN $query;
--echo Manually translated
SELECT derived_1_0.countrycount AS countrycount,
derived_1_0.smcnt AS smcnt,
derived_1_0.d_1 AS country,
derived_1_1.`SUM(funds_requested)` AS total_funds,
row_number() OVER (ORDER BY derived_1_1.`SUM(funds_requested)` ) AS rn
FROM (SELECT COUNT(t1.country) AS countrycount,
SUM(t1.funds_requested) AS smcnt,
AVG(t1.funds_requested) AS tmp_aggr_1,
STD(t1.funds_requested) AS tmp_aggr_2,
t1.country AS d_1
FROM t1
HAVING (AVG(t1.funds_requested) > 0)) derived_1_0
LEFT JOIN
(SELECT SUM(t1.funds_requested) AS `SUM(funds_requested)`
FROM t1) derived_1_1
ON(TRUE);
SET sql_mode=default;
DROP TABLE t1;
--echo # Cause for introducing TABLE_LIST::m_was_grouped2derived:
--echo # EXECUTE would assert if we didn't have it.
CREATE TABLE cc (i INT);
INSERT INTO cc VALUES (1);
ANALYZE TABLE cc;
let $query=
SELECT (SELECT COUNT(i) FROM cc AS cc_alias
WHERE (cc.i IN (SELECT cc_alias.i FROM cc))) AS cnt
FROM cc
GROUP BY i;
eval $query;
--replace_regex $elide_costs
eval EXPLAIN $query;
#Remove commented out code FIXME.
#eval PREPARE stmt FROM "$query";
#EXECUTE stmt;
#
#DROP PREPARE stmt;
DROP TABLE cc;
--echo # Test: detect correlated aggregates deep inside scalar subquery (was
--echo # missed before we let has_correlated_aggregate walk subqueries
CREATE TABLE t (a INT);
INSERT INTO t VALUES (1);
ANALYZE TABLE t;
--echo # should be left untouched, since COUNT(q.i) aggregates in the
--echo # outermost SELECT
--replace_regex $elide_costs
EXPLAIN SELECT (
SELECT GROUP_CONCAT((SELECT COUNT(q.i) FROM t))
FROM t) AS i
FROM (SELECT a AS i FROM t) q;
DROP TABLE t;
--echo
--echo # IN to EXISTS transformation makes us skip subquery_to_derived.
--echo
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL);
CREATE TABLE t3 (e INT NOT NULL);
INSERT INTO t1 VALUES (1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
INSERT INTO t2 VALUES (2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
INSERT INTO t3 VALUES (10), (30), (10), (20);
ANALYZE TABLE t1, t2, t3;
let $query =
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE (SELECT MIN(e) FROM t3 as tc
WHERE tc.e IS NOT NULL) < SOME(SELECT e FROM t3 as tc
WHERE ta.b=tc.e));
--sorted_result
eval $query;
--replace_regex $elide_costs
eval EXPLAIN $query;
--echo #
--echo # Two parallel scalar subqueries + grouping to derived table
--echo #
let $query =
SELECT SUM(t1.a) + (SELECT SUM(t2.c)
FROM t2),
(SELECT COUNT(t3.e) FROM t3)
FROM t1;
eval $query;
--replace_regex $elide_costs
eval EXPLAIN $query;
DROP TABLE t1, t2, t3;
--echo # Bug discovered from running rapid.cp_i_subquery.test What is
--echo # different here is that we have a combination of an implicitly
--echo # grouped query + a scalar subquery in the select list + the top block
--echo # has an IN subquery in the WHERE clause. The WHERE clause gets
--echo # moved into the derived table for the grouping, but this move didn't
--echo # account for the semijoin already determined for the top level, which
--echo also has to go into the new derived table.
CREATE TABLE t1(
pedcompralote INT NOT NULL,
pedcompraseq SMALLINT
);
INSERT INTO t1 VALUES (12,12);
CREATE TABLE t2(
cod INT NOT NULL,
ped INT,
PRIMARY KEY (cod),
KEY ped (ped)
);
INSERT INTO t2 VALUES
(11724,1779), (11725,1779), (11726,1779), (11727,1779),
(11728,1779), (11729,1779), (11730,1779), (11731,1779);
SELECT (SELECT COUNT(*)
FROM t1
WHERE pedcompralote=11725) AS tot,
IFNULL(MAX(pedcompraseq),0)+1 AS newcode
FROM t1
WHERE pedcompralote IN (SELECT cod FROM t2 WHERE ped=1779);
DROP TABLE t1, t2;
--echo # outer query has DEFAULT: verify it is handled correctly
CREATE TABLE t(i INT DEFAULT 5);
INSERT INTO t VALUES (4);
ANALYZE TABLE t;
let $query =
SELECT DEFAULT(i) AS def,
5 + DEFAULT(i) AS def2,
i AS any_v,
(SELECT i FROM t) AS subquery,
SUM(i) AS summ
FROM t;
--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
eval $query;
SET SQL_MODE='';
eval $query;
--replace_regex $elide_costs
eval EXPLAIN $query;
SET SQL_MODE=default;
--echo # Bug#35150438 Unwarranted error message for ANY_VALUE with
--echo # scalar subquery transform
let $query =
SELECT ANY_VALUE(i) AS i1,
(SELECT i FROM t) AS subquery,
SUM(i) AS summ
FROM t;
eval $query;
--replace_regex $elide_costs
eval EXPLAIN $query;
--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
SELECT i + ANY_VALUE(i) AS i1,
(SELECT i FROM t) AS subquery,
SUM(i) AS summ
FROM t;
--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
SELECT ANY_VALUE(i) + i AS i1,
(SELECT i FROM t) AS subquery,
SUM(i) AS summ
FROM t;
let $query =
SELECT ANY_VALUE(ANY_VALUE(i) + i) AS i1,
(SELECT i FROM t) AS subquery,
SUM(i) AS summ
FROM t;
eval $query;
--replace_regex $elide_costs
eval EXPLAIN $query;
--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
SELECT ANY_VALUE(i) AS i1, i as i2,
(SELECT i FROM t) AS subquery,
SUM(i) AS summ
FROM t;
--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
SELECT i as i2, ANY_VALUE(i) AS i1,
(SELECT i FROM t) AS subquery,
SUM(i) AS summ
FROM t;
let $query =
SELECT ANY_VALUE(i) as i2, ANY_VALUE(i) AS i1,
(SELECT i FROM t) AS subquery,
SUM(i) AS summ
FROM t;
eval $query;
--replace_regex $elide_costs
eval EXPLAIN $query;
DROP TABLE t;
--echo #
--echo # 6. S U B Q U E R Y I N H A V I N G C L A U S E
--echo #
CREATE TABLE t1(i int, j int);
CREATE TABLE t2(i int);
INSERT INTO t1 VALUES (1, 10);
INSERT INTO t1 VALUES (1, 20);
INSERT INTO t1 VALUES (1, 30);
INSERT INTO t1 VALUES (2, 11);
INSERT INTO t1 VALUES (2, 20);
INSERT INTO t1 VALUES (2, 30);
INSERT INTO t2 VALUES (25);
ANALYZE TABLE t1, t2;
--echo Implicit grouping, HAVING: no transform
let $query=
SELECT SUM(j) FROM t1
HAVING SUM(j) > (SELECT SUM(t2.i) FROM t2);
eval $query;
--replace_regex $elide_costs
eval EXPLAIN $query;
--echo Only HAVING is ok to transform, no implicit grouping
let $query=
SELECT j FROM t1
HAVING j > (SELECT MIN(t2.i) FROM t2);
eval $query;
--replace_regex $elide_costs
eval EXPLAIN $query;
--echo HAVING and GROUP BY, also ok
let $query=
SELECT i, j FROM t1
GROUP BY i, j
HAVING SUM(j) > (SELECT SUM(t2.i) FROM t2);
eval $query;
--replace_regex $elide_costs
eval EXPLAIN $query;
--echo HAVING, GROUP BY and ROLLUP
let $query=
SELECT i, j FROM t1
GROUP BY i, j WITH ROLLUP
HAVING SUM(j) > (SELECT SUM(t2.i) FROM t2);
eval $query;
--replace_regex $elide_costs
eval EXPLAIN $query;
DROP TABLE t1, t2;
--echo Bigger example (TPC-H Q11) where we get a scalar transformation
--echo in the grouping derived table also, i.e. nested scalar to
--echo derived transformation.
CREATE TABLE supplier (
s_suppkey INT NOT NULL,
s_nationkey BIGINT NOT NULL,
PRIMARY KEY (s_suppkey)
);
CREATE TABLE nation (
n_nationkey INT NOT NULL,
n_name CHAR(25) DEFAULT NULL,
PRIMARY KEY (n_nationkey)
);
CREATE TABLE partsupp (
ps_partkey BIGINT NOT NULL,
ps_suppkey BIGINT NOT NULL,
ps_availqty INT DEFAULT NULL,
ps_supplycost DECIMAL(10,0) DEFAULT NULL,
PRIMARY KEY (ps_partkey, ps_suppkey)
);
INSERT INTO nation VALUES (1, 'germany'),
(2, 'norway'),
(3, 'u.k.');
INSERT INTO supplier VALUES (1, 1);
INSERT INTO partsupp VALUES
(1, 1, 10, 555),
(2, 1, 1, 2222),
(3, 1, 300, 700),
(4, 1, 259, 400),
(5, 1, 20, 400),
(6, 1, 1000, 300),
(7, 1, 30, 700);
ANALYZE TABLE supplier, nation, partsupp;
let $query=
SELECT
ps_partkey,
SUM(ps_supplycost * ps_availqty) AS value
FROM
partsupp,
supplier,
nation
WHERE
ps_suppkey = s_suppkey AND
s_nationkey = n_nationkey AND
n_name = 'germany'
GROUP BY
ps_partkey HAVING
SUM(ps_supplycost * ps_availqty) > (
SELECT
SUM(ps_supplycost * ps_availqty) * 0.1
FROM
partsupp,
supplier,
nation
WHERE
ps_suppkey = s_suppkey AND
s_nationkey = n_nationkey AND
n_name = 'germany'
)
ORDER BY value DESC;
eval $query;
--replace_regex $elide_costs
eval EXPLAIN $query;
DROP TABLE partsupp, nation, supplier;
--echo Bugs discovered during full regression suite runs with
--echo transformation enabled, selectively repeated here, since we
--echo won't be running InnoDB with transformation enabled normally.
--echo Bug 1
CREATE TABLE tbl1 (
login INT NOT NULL,
numb DECIMAL(15,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (login),
KEY numb (numb)
);
CREATE TABLE tbl2 (
login INT NOT NULL,
cmd TINYINT NOT NULL,
nump DECIMAL(15,2) NOT NULL DEFAULT '0.00',
KEY cmd (cmd),
KEY login (login)
);
SET autocommit = 0;
START TRANSACTION;
let $i=500;
while ($i)
{
--eval insert into tbl1 values($i, '$i.$j')
dec $i;
}