summaryrefslogtreecommitdiff
path: root/mysql-test/t/subselect4.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t/subselect4.test')
-rw-r--r--mysql-test/t/subselect4.test279
1 files changed, 117 insertions, 162 deletions
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 5e1f3db2f4a..d939c60ddd1 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -1,7 +1,8 @@
# General purpose bug fix tests go here : subselect.test too large
--disable_warnings
-drop table if exists t1,t2,t3,t4,t5,t6;
+drop table if exists t0,t1,t2,t3,t4,t5,t6;
+drop view if exists v1, v2;
--enable_warnings
set @subselect4_tmp= @@optimizer_switch;
@@ -202,6 +203,9 @@ CREATE TABLE t2c (pk INT NOT NULL, i INT NOT NULL);
INSERT INTO t2c VALUES (0,0), (1,1), (2,2), (3,3);
create index it2c on t2c (i,pk);
+CREATE TABLE t2d (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i));
+INSERT INTO t2d VALUES (0,0), (1,1), (2,2), (3,3);
+
EXPLAIN
SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk);
SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk);
@@ -222,10 +226,32 @@ SELECT * FROM t1 WHERE NULL IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk)
SELECT t1.pk, NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk) FROM t1;
EXPLAIN
+SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk);
+SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk);
+SELECT * FROM t1 WHERE NULL IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk) IS UNKNOWN;
+SELECT t1.pk, NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk) FROM t1;
+
+EXPLAIN
SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk);
SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk);
+SELECT (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk) from t1;
-drop table t1, t2a, t2b, t2c;
+EXPLAIN
+SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2b.i, t2b.pk FROM t2b WHERE t2b.pk = t1.pk);
+SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2b.i, t2b.pk FROM t2b WHERE t2b.pk = t1.pk);
+SELECT (NULL, 1) NOT IN (SELECT t2b.i, t2b.pk FROM t2b WHERE t2b.pk = t1.pk) from t1;
+
+EXPLAIN
+SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk);
+SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk);
+SELECT (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk) from t1;
+
+EXPLAIN
+SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2d.i, t2d.pk FROM t2d WHERE t2d.pk = t1.pk);
+SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2d.i, t2d.pk FROM t2d WHERE t2d.pk = t1.pk);
+SELECT (NULL, 1) NOT IN (SELECT t2d.i, t2d.pk FROM t2d WHERE t2d.pk = t1.pk) from t1;
+
+drop table t1, t2a, t2b, t2c, t2d;
--echo #
--echo # End of 5.1 tests.
@@ -469,173 +495,37 @@ SET SESSION engine_condition_pushdown=@old_icp;
DROP TABLE t1,t2;
--echo #
---echo # BUG#45863 "Assertion failed: (fixed == 0), function fix_fields(),
---echo # file item.cc, line 4448"
+--echo # End of 5.3 tests.
--echo #
---disable_warnings
-DROP TABLE IF EXISTS C, BB;
---enable_warnings
-
-CREATE TABLE C (
- varchar_nokey varchar(1) NOT NULL
-);
-INSERT INTO C VALUES
- ('k'),('a'),(''),('u'),('e'),('v'),('i'),
- ('t'),('u'),('f'),('u'),('m'),('j'),('f'),
- ('v'),('j'),('g'),('e'),('h'),('z');
-CREATE TABLE BB (
- varchar_nokey varchar(1) NOT NULL
-);
-INSERT INTO BB VALUES ('i'),('t');
--- error ER_OPERAND_COLUMNS
-SELECT varchar_nokey FROM C
-WHERE (varchar_nokey, OUTR) IN (SELECT varchar_nokey
- FROM BB);
--- error ER_BAD_FIELD_ERROR
-SELECT varchar_nokey FROM C
-WHERE (varchar_nokey, OUTR) IN (SELECT varchar_nokey, varchar_nokey
- FROM BB);
-DROP TABLE C,BB;
--echo #
---echo # During work with BUG#45863 I had problems with a query that was
---echo # optimized differently in regular and prepared mode.
---echo # Because there was a bug in one of the selected strategies, I became
---echo # aware of the problem. Adding an EXPLAIN query to catch this.
-
---disable_warnings
-DROP TABLE IF EXISTS t1, t2, t3;
---enable_warnings
-
-CREATE TABLE t1
- (EMPNUM CHAR(3) NOT NULL,
- EMPNAME CHAR(20),
- GRADE DECIMAL(4),
- CITY CHAR(15));
-
-CREATE TABLE t2
- (PNUM CHAR(3) NOT NULL,
- PNAME CHAR(20),
- PTYPE CHAR(6),
- BUDGET DECIMAL(9),
- CITY CHAR(15));
-
-CREATE TABLE t3
- (EMPNUM CHAR(3) NOT NULL,
- PNUM CHAR(3) NOT NULL,
- HOURS DECIMAL(5));
-
-INSERT INTO t1 VALUES ('E1','Alice',12,'Deale');
-INSERT INTO t1 VALUES ('E2','Betty',10,'Vienna');
-INSERT INTO t1 VALUES ('E3','Carmen',13,'Vienna');
-INSERT INTO t1 VALUES ('E4','Don',12,'Deale');
-INSERT INTO t1 VALUES ('E5','Ed',13,'Akron');
-
-INSERT INTO t2 VALUES ('P1','MXSS','Design',10000,'Deale');
-INSERT INTO t2 VALUES ('P2','CALM','Code',30000,'Vienna');
-INSERT INTO t2 VALUES ('P3','SDP','Test',30000,'Tampa');
-INSERT INTO t2 VALUES ('P4','SDP','Design',20000,'Deale');
-INSERT INTO t2 VALUES ('P5','IRM','Test',10000,'Vienna');
-INSERT INTO t2 VALUES ('P6','PAYR','Design',50000,'Deale');
-
-INSERT INTO t3 VALUES ('E1','P1',40);
-INSERT INTO t3 VALUES ('E1','P2',20);
-INSERT INTO t3 VALUES ('E1','P3',80);
-INSERT INTO t3 VALUES ('E1','P4',20);
-INSERT INTO t3 VALUES ('E1','P5',12);
-INSERT INTO t3 VALUES ('E1','P6',12);
-INSERT INTO t3 VALUES ('E2','P1',40);
-INSERT INTO t3 VALUES ('E2','P2',80);
-INSERT INTO t3 VALUES ('E3','P2',20);
-INSERT INTO t3 VALUES ('E4','P2',20);
-INSERT INTO t3 VALUES ('E4','P4',40);
-INSERT INTO t3 VALUES ('E4','P5',80);
-
-SET @old_optimizer_switch = @@session.optimizer_switch;
-SET @old_join_cache_level = @@session.join_cache_level;
-SET SESSION optimizer_switch = 'firstmatch=on,loosescan=on,materialization=on,in_to_exists=off,semijoin=on';
-SET SESSION join_cache_level = 1;
-
-CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM);
-
-EXPLAIN SELECT EMPNAME
-FROM t1
-WHERE EMPNUM IN
- (SELECT EMPNUM
- FROM t3
- WHERE PNUM IN
- (SELECT PNUM
- FROM t2
- WHERE PTYPE = 'Design'));
-
-PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
-FROM t1
-WHERE EMPNUM IN
- (SELECT EMPNUM
- FROM t3
- WHERE PNUM IN
- (SELECT PNUM
- FROM t2
- WHERE PTYPE = 'Design'))";
-EXECUTE stmt;
-EXECUTE stmt;
-DEALLOCATE PREPARE stmt;
-
-DROP INDEX t1_IDX ON t1;
-CREATE INDEX t1_IDX ON t1(EMPNUM);
-
-EXPLAIN SELECT EMPNAME
-FROM t1
-WHERE EMPNUM IN
- (SELECT EMPNUM
- FROM t3
- WHERE PNUM IN
- (SELECT PNUM
- FROM t2
- WHERE PTYPE = 'Design'));
-
-PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
-FROM t1
-WHERE EMPNUM IN
- (SELECT EMPNUM
- FROM t3
- WHERE PNUM IN
- (SELECT PNUM
- FROM t2
- WHERE PTYPE = 'Design'))";
-EXECUTE stmt;
-EXECUTE stmt;
-DEALLOCATE PREPARE stmt;
-
-DROP INDEX t1_IDX ON t1;
+--echo # Bug#53236 Segfault in DTCollation::set(DTCollation&)
+--echo #
-EXPLAIN SELECT EMPNAME
-FROM t1
-WHERE EMPNUM IN
- (SELECT EMPNUM
- FROM t3
- WHERE PNUM IN
- (SELECT PNUM
- FROM t2
- WHERE PTYPE = 'Design'));
+CREATE TABLE t1 (
+ pk INTEGER AUTO_INCREMENT,
+ col_varchar VARCHAR(1),
+ PRIMARY KEY (pk)
+)
+;
-PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
-FROM t1
-WHERE EMPNUM IN
- (SELECT EMPNUM
- FROM t3
- WHERE PNUM IN
- (SELECT PNUM
- FROM t2
- WHERE PTYPE = 'Design'))";
-EXECUTE stmt;
-EXECUTE stmt;
-DEALLOCATE PREPARE stmt;
+INSERT INTO t1 (col_varchar)
+VALUES
+('w'),
+('m')
+;
-SET SESSION optimizer_switch = @old_optimizer_switch;
-SET SESSION join_cache_level = @old_join_cache_level;
+SELECT table1.pk
+FROM ( t1 AS table1 JOIN t1 AS table2 ON (table1.col_varchar =
+ table2.col_varchar) )
+WHERE ( 1, 2 ) IN ( SELECT SUBQUERY1_t1.pk AS SUBQUERY1_field1,
+ SUBQUERY1_t1.pk AS SUBQUERY1_field2
+ FROM ( t1 AS SUBQUERY1_t1 JOIN t1 AS SUBQUERY1_t2
+ ON (SUBQUERY1_t2.col_varchar =
+ SUBQUERY1_t1.col_varchar) ) )
+;
-DROP TABLE t1, t2, t3;
+drop table t1;
--echo #
--echo # BUG#716293: "Range checked for each record" is not used if condition refers to outside of subquery
@@ -686,6 +576,27 @@ CREATE TABLE t2 (f11 varchar(1)) ;
INSERT INTO t2 VALUES ('f'),('d');
SET @old_optimizer_switch = @@session.optimizer_switch;
+SET SESSION optimizer_switch = 'materialization=on,in_to_exists=off';
+
+EXPLAIN
+SELECT * FROM t1
+WHERE f3 = (
+ SELECT t1.f3 FROM t1
+ WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 ));
+SELECT * FROM t1
+WHERE f3 = (
+ SELECT t1.f3 FROM t1
+ WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 ));
+
+EXPLAIN
+SELECT * FROM t1
+WHERE f3 = (
+ SELECT f3 FROM t1
+ WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 ));
+SELECT * FROM t1
+WHERE f3 = (
+ SELECT f3 FROM t1
+ WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 ));
SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off';
EXPLAIN
@@ -1135,6 +1046,14 @@ SELECT * FROM t1 WHERE
FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4
GROUP BY SQ1_t1.f4));
+--error ER_SUBQUERY_NO_1_ROW
+SELECT * FROM t1 WHERE
+(SELECT f2 FROM t2
+ WHERE f4 <= ALL
+ (SELECT max(SQ1_t1.f4)
+ FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4
+ GROUP BY SQ1_t1.f4));
+
drop table t1, t2, t3;
--echo #
@@ -1883,6 +1802,42 @@ SELECT * FROM t1 WHERE a1 IN (
SELECT a3 FROM t3 WHERE b2 = b1 AND b2 <= b1 ORDER BY b3
)
);
+drop table t1, t2, t3;
+
+--echo #
+--echo # MDEV-3899 Valgrind warnings (blocks are definitely lost) in filesort on IN subquery with SUM and DISTINCT
+--echo #
+
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(9);
+
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8);
+
+SELECT * FROM t1
+WHERE (1, 1) IN (SELECT a, SUM(DISTINCT a) FROM t1, t2 GROUP BY a);
+
+drop table t1, t2;
+
+--echo #
+--echo # MDEV-3902 Assertion `record_length == m_record_length' failed at Filesort_buffer::alloc_sort_buffer
+--echo #
+
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (pk INT PRIMARY KEY, b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,1),(2,7);
+
+CREATE TABLE t3 (c INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (8);
+
+SELECT * FROM t1
+WHERE (1, 5) IN (SELECT b, SUM(DISTINCT b) FROM t2, t3 GROUP BY b);
+
+SELECT * FROM t2 AS alias1, t2 AS alias2
+WHERE EXISTS ( SELECT 1 ) AND (alias2.pk = alias1.b )
+ORDER BY alias1.b;
drop table t1, t2, t3;
View Lorry Controller Status