2737
2730
DROP TABLE t1xt2;
2733
# Bug #26728: derived table with concatanation of literals in select list
2736
CREATE TABLE t1 (a int);
2737
INSERT INTO t1 VALUES (3), (1), (2);
2739
SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
2740
SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
2745
# Bug #27257: COUNT(*) aggregated in outer query
2748
CREATE TABLE t1 (a int, b int);
2749
CREATE TABLE t2 (m int, n int);
2750
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
2751
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
2754
(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
2758
(SELECT MIN(m) FROM t2 WHERE m = count(*))
2763
HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
2768
# Bug #27229: GROUP_CONCAT in subselect with COUNT() as an argument
2771
CREATE TABLE t1 (a int, b int);
2772
CREATE TABLE t2 (m int, n int);
2773
INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
2774
INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
2776
SELECT COUNT(*) c, a,
2777
(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
2780
SELECT COUNT(*) c, a,
2781
(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
2787
# Bug#27321: Wrong subquery result in a grouping select
2789
CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
2790
INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),
2791
(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),
2792
(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');
2795
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test
2798
(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test
2801
(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test
2802
FROM t1 WHERE t1.d=0 GROUP BY a;
2805
(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
2806
LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
2810
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
2812
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test
2813
FROM t1 as tt GROUP BY tt.a;
2816
(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)
2818
FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test
2819
FROM t1 as tt GROUP BY tt.a;
2824
# Bug #27348: SET FUNCTION used in a subquery from WHERE condition
2827
CREATE TABLE t1 (a int, b int);
2828
INSERT INTO t1 VALUES (2,22),(1,11),(2,22);
2830
SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a;
2831
SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a;
2834
WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;
2836
SET @@sql_mode='ansi';
2838
SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a;
2840
SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a;
2844
WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;
2846
SET @@sql_mode=default;
2849
# Bug#20835 (literal string with =any values)
2851
CREATE TABLE t1 (s1 char(1));
2852
INSERT INTO t1 VALUES ('a');
2853
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);