diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/subselect4.result | 57 | ||||
| -rw-r--r-- | mysql-test/t/subselect4.test | 36 |
2 files changed, 92 insertions, 1 deletions
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index a4fd1123227..262170851af 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2632,7 +2632,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 2 SUBQUERY A ALL NULL NULL NULL NULL 2 3 UNION B ALL NULL NULL NULL NULL 2 -NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL Using filesort SELECT t1.a FROM t1 WHERE EXISTS (SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1); a 1 @@ -2643,6 +2643,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 2 Using where 3 DEPENDENT UNION B ALL NULL NULL NULL NULL 2 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t2 B ORDER BY 1); a 1 @@ -2656,4 +2657,58 @@ INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id); ERROR 42S22: Unknown column 's.id' in 'group statement' DROP TABLE t1; +# +# MDEV-23160: SIGSEGV in Explain_node::print_explain_for_children on UNION SELECT +# or on EXPLAIN EXTENDED +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +EXPLAIN SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 3 Using where +3 DEPENDENT UNION B ALL NULL NULL NULL NULL 3 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a)); +a +1 +2 +3 +EXPLAIN SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT a)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 3 Using where +3 DEPENDENT UNION B ALL NULL NULL NULL NULL 3 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT a)); +a +1 +2 +3 +PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a))'; +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 3 Using where +3 DEPENDENT UNION B ALL NULL NULL NULL NULL 3 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 3 Using where +3 DEPENDENT UNION B ALL NULL NULL NULL NULL 3 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +DEALLOCATE PREPARE stmt; +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT id)); +ERROR 42S22: Unknown column 'id' in 'field list' +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT id)); +ERROR 42S22: Unknown column 'id' in 'field list' +DROP TABLE t1; # end of 10.1 tests diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 03929517126..8bd43661dbc 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -2173,4 +2173,40 @@ INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id); DROP TABLE t1; +--echo # +--echo # MDEV-23160: SIGSEGV in Explain_node::print_explain_for_children on UNION SELECT +--echo # or on EXPLAIN EXTENDED +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +EXPLAIN SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a)); + +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a)); + +EXPLAIN SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT a)); + +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT a)); + +PREPARE stmt FROM 'EXPLAIN SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a))'; + +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +--error ER_BAD_FIELD_ERROR +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT id)); + +--error ER_BAD_FIELD_ERROR +SELECT * FROM t1 +WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT id)); + +DROP TABLE t1; + --echo # end of 10.1 tests |