revision-id: 8009d85cc2e9245f6e8be06397914433e4b20007 (mariadb-5.5.61-30-g8009d85cc2e) parent(s): acf8fc1ff8a7b2d49e25279670b04b8eb096ce0c author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-10-12 20:47:22 +0200 message: MDEV-16207: Sig 11 on RHEL7 on Azure The problem was that Item_subselect::const_item was not checked correctly and can differ from used_tables result. Which lead to the situation when Item_direct_view_ref pointing on subselect was constant but the subquery return FALSE on const_item() request (which was an error). Now both methods report the same. --- mysql-test/r/subselect.result | 31 +++++++++++++++++++++++-------- mysql-test/r/subselect_no_mat.result | 31 +++++++++++++++++++++++-------- mysql-test/r/subselect_no_opts.result | 31 +++++++++++++++++++++++-------- mysql-test/r/subselect_no_scache.result | 31 +++++++++++++++++++++++-------- mysql-test/r/subselect_no_semijoin.result | 31 +++++++++++++++++++++++-------- mysql-test/t/subselect.test | 21 +++++++++++++++++++++ sql/item.cc | 10 +++++++++- sql/item_subselect.cc | 5 +++-- sql/sql_select.cc | 4 +++- 9 files changed, 151 insertions(+), 44 deletions(-) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index cdedc02f825..934c145e995 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1156,19 +1156,19 @@ CREATE TABLE t1 (a int(1)); EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select (select rand() from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1` EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select (select encrypt('test') from `test`.`t1`) AS `(SELECT ENCRYPT('test') FROM t1)` from `test`.`t1` EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select (select benchmark(1,1) from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1` drop table t1; @@ -4059,8 +4059,6 @@ SELECT COUNT(*), a FROM t1 GROUP BY a HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1; COUNT(*) a -2 2 -3 3 DROP TABLE t1,t2; CREATE TABLE t1 (a int, b int); CREATE TABLE t2 (m int, n int); @@ -4582,7 +4580,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; MAX(b) (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) -NULL NULL +NULL 0 DROP TABLE t1, st1, st2; # # Bug #48709: Assertion failed in sql_select.cc:11782: @@ -6652,7 +6650,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 NULL +0 7 EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -6660,7 +6658,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 0 +0 1 EXPLAIN SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -7187,4 +7185,21 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away drop table t1,t2; +# +# MDEV-16207: Sig 11 on RHEL7 on Azure +# +CREATE TABLE t2 (ser1 char(20) NOT NULL, ids1 int NOT NULL, nm1 char(32) NOT NULL); +INSERT INTO t2 VALUES ('char1',1,'char2'),('char2',2,'char2'); +CREATE TABLE t3 (id2 int ); +INSERT INTO t3 VALUES (1),(2); +CREATE TABLE t5 (nm1 varchar(255), ls1 varchar(64), state enum('Active','Inactive','Event')); +SELECT * FROM +(SELECT nm1, +(SELECT max(t5.nm1) FROM t5 WHERE t5.ls1 = ser1 AND STATE = 'Active') AS types +FROM t3 JOIN t2 ON ids1 = id2) AS t1 +ORDER BY nm1 ; +nm1 types +char2 NULL +char2 NULL +drop table t2,t3,t5; End of 5.5 tests diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index a7291297e7c..cdb13367c45 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -1163,19 +1163,19 @@ CREATE TABLE t1 (a int(1)); EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select (select rand() from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1` EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select (select encrypt('test') from `test`.`t1`) AS `(SELECT ENCRYPT('test') FROM t1)` from `test`.`t1` EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select (select benchmark(1,1) from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1` drop table t1; @@ -4063,8 +4063,6 @@ SELECT COUNT(*), a FROM t1 GROUP BY a HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1; COUNT(*) a -2 2 -3 3 DROP TABLE t1,t2; CREATE TABLE t1 (a int, b int); CREATE TABLE t2 (m int, n int); @@ -4584,7 +4582,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; MAX(b) (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) -NULL NULL +NULL 0 DROP TABLE t1, st1, st2; # # Bug #48709: Assertion failed in sql_select.cc:11782: @@ -6651,7 +6649,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 NULL +0 7 EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -6659,7 +6657,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 0 +0 1 EXPLAIN SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -7184,6 +7182,23 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away drop table t1,t2; +# +# MDEV-16207: Sig 11 on RHEL7 on Azure +# +CREATE TABLE t2 (ser1 char(20) NOT NULL, ids1 int NOT NULL, nm1 char(32) NOT NULL); +INSERT INTO t2 VALUES ('char1',1,'char2'),('char2',2,'char2'); +CREATE TABLE t3 (id2 int ); +INSERT INTO t3 VALUES (1),(2); +CREATE TABLE t5 (nm1 varchar(255), ls1 varchar(64), state enum('Active','Inactive','Event')); +SELECT * FROM +(SELECT nm1, +(SELECT max(t5.nm1) FROM t5 WHERE t5.ls1 = ser1 AND STATE = 'Active') AS types +FROM t3 JOIN t2 ON ids1 = id2) AS t1 +ORDER BY nm1 ; +nm1 types +char2 NULL +char2 NULL +drop table t2,t3,t5; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index c41fa1be47b..0cfb34e890c 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -1159,19 +1159,19 @@ CREATE TABLE t1 (a int(1)); EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select (select rand() from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1` EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select (select encrypt('test') from `test`.`t1`) AS `(SELECT ENCRYPT('test') FROM t1)` from `test`.`t1` EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select (select benchmark(1,1) from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1` drop table t1; @@ -4059,8 +4059,6 @@ SELECT COUNT(*), a FROM t1 GROUP BY a HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1; COUNT(*) a -2 2 -3 3 DROP TABLE t1,t2; CREATE TABLE t1 (a int, b int); CREATE TABLE t2 (m int, n int); @@ -4580,7 +4578,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; MAX(b) (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) -NULL NULL +NULL 0 DROP TABLE t1, st1, st2; # # Bug #48709: Assertion failed in sql_select.cc:11782: @@ -6647,7 +6645,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 NULL +0 7 EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -6655,7 +6653,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 0 +0 1 EXPLAIN SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -7182,5 +7180,22 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away drop table t1,t2; +# +# MDEV-16207: Sig 11 on RHEL7 on Azure +# +CREATE TABLE t2 (ser1 char(20) NOT NULL, ids1 int NOT NULL, nm1 char(32) NOT NULL); +INSERT INTO t2 VALUES ('char1',1,'char2'),('char2',2,'char2'); +CREATE TABLE t3 (id2 int ); +INSERT INTO t3 VALUES (1),(2); +CREATE TABLE t5 (nm1 varchar(255), ls1 varchar(64), state enum('Active','Inactive','Event')); +SELECT * FROM +(SELECT nm1, +(SELECT max(t5.nm1) FROM t5 WHERE t5.ls1 = ser1 AND STATE = 'Active') AS types +FROM t3 JOIN t2 ON ids1 = id2) AS t1 +ORDER BY nm1 ; +nm1 types +char2 NULL +char2 NULL +drop table t2,t3,t5; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 1c181357050..0e4bdcca9dd 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -1162,19 +1162,19 @@ CREATE TABLE t1 (a int(1)); EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select (select rand() from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1` EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select (select encrypt('test') from `test`.`t1`) AS `(SELECT ENCRYPT('test') FROM t1)` from `test`.`t1` EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select (select benchmark(1,1) from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1` drop table t1; @@ -4065,8 +4065,6 @@ SELECT COUNT(*), a FROM t1 GROUP BY a HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1; COUNT(*) a -2 2 -3 3 DROP TABLE t1,t2; CREATE TABLE t1 (a int, b int); CREATE TABLE t2 (m int, n int); @@ -4588,7 +4586,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; MAX(b) (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) -NULL NULL +NULL 0 DROP TABLE t1, st1, st2; # # Bug #48709: Assertion failed in sql_select.cc:11782: @@ -6658,7 +6656,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 NULL +0 7 EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -6666,7 +6664,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 0 +0 1 EXPLAIN SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -7193,6 +7191,23 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away drop table t1,t2; +# +# MDEV-16207: Sig 11 on RHEL7 on Azure +# +CREATE TABLE t2 (ser1 char(20) NOT NULL, ids1 int NOT NULL, nm1 char(32) NOT NULL); +INSERT INTO t2 VALUES ('char1',1,'char2'),('char2',2,'char2'); +CREATE TABLE t3 (id2 int ); +INSERT INTO t3 VALUES (1),(2); +CREATE TABLE t5 (nm1 varchar(255), ls1 varchar(64), state enum('Active','Inactive','Event')); +SELECT * FROM +(SELECT nm1, +(SELECT max(t5.nm1) FROM t5 WHERE t5.ls1 = ser1 AND STATE = 'Active') AS types +FROM t3 JOIN t2 ON ids1 = id2) AS t1 +ORDER BY nm1 ; +nm1 types +char2 NULL +char2 NULL +drop table t2,t3,t5; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 89c671252ff..fae84f58c13 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -1159,19 +1159,19 @@ CREATE TABLE t1 (a int(1)); EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select (select rand() from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1` EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select (select encrypt('test') from `test`.`t1`) AS `(SELECT ENCRYPT('test') FROM t1)` from `test`.`t1` EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found -2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found +2 UNCACHEABLE SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 select (select benchmark(1,1) from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1` drop table t1; @@ -4059,8 +4059,6 @@ SELECT COUNT(*), a FROM t1 GROUP BY a HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1; COUNT(*) a -2 2 -3 3 DROP TABLE t1,t2; CREATE TABLE t1 (a int, b int); CREATE TABLE t2 (m int, n int); @@ -4580,7 +4578,7 @@ SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; MAX(b) (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) -NULL NULL +NULL 0 DROP TABLE t1, st1, st2; # # Bug #48709: Assertion failed in sql_select.cc:11782: @@ -6647,7 +6645,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), (SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 NULL +0 7 EXPLAIN SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -6655,7 +6653,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(f1), exists(SELECT f1 FROM t1 WHERE f2 > 0 limit 1) AS f4 FROM t2, t1 WHERE 'v'= f3; COUNT(f1) f4 -0 0 +0 1 EXPLAIN SELECT COUNT(f1), f2 > ALL (SELECT f1 FROM t1 WHERE f2 > 0) AS f4 FROM t2, t1 WHERE 'v'= f3; id select_type table type possible_keys key key_len ref rows Extra @@ -7182,6 +7180,23 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away drop table t1,t2; +# +# MDEV-16207: Sig 11 on RHEL7 on Azure +# +CREATE TABLE t2 (ser1 char(20) NOT NULL, ids1 int NOT NULL, nm1 char(32) NOT NULL); +INSERT INTO t2 VALUES ('char1',1,'char2'),('char2',2,'char2'); +CREATE TABLE t3 (id2 int ); +INSERT INTO t3 VALUES (1),(2); +CREATE TABLE t5 (nm1 varchar(255), ls1 varchar(64), state enum('Active','Inactive','Event')); +SELECT * FROM +(SELECT nm1, +(SELECT max(t5.nm1) FROM t5 WHERE t5.ls1 = ser1 AND STATE = 'Active') AS types +FROM t3 JOIN t2 ON ids1 = id2) AS t1 +ORDER BY nm1 ; +nm1 types +char2 NULL +char2 NULL +drop table t2,t3,t5; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 4e35032a789..9f475ae3bf0 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6075,4 +6075,25 @@ explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; drop table t1,t2; +--echo # +--echo # MDEV-16207: Sig 11 on RHEL7 on Azure +--echo # + +CREATE TABLE t2 (ser1 char(20) NOT NULL, ids1 int NOT NULL, nm1 char(32) NOT NULL); +INSERT INTO t2 VALUES ('char1',1,'char2'),('char2',2,'char2'); + +CREATE TABLE t3 (id2 int ); +INSERT INTO t3 VALUES (1),(2); + +CREATE TABLE t5 (nm1 varchar(255), ls1 varchar(64), state enum('Active','Inactive','Event')); + + +SELECT * FROM +(SELECT nm1, +(SELECT max(t5.nm1) FROM t5 WHERE t5.ls1 = ser1 AND STATE = 'Active') AS types +FROM t3 JOIN t2 ON ids1 = id2) AS t1 +ORDER BY nm1 ; + +drop table t2,t3,t5; + --echo End of 5.5 tests diff --git a/sql/item.cc b/sql/item.cc index d2e3b847f5c..1f1aeacf96e 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -7419,7 +7419,15 @@ void Item_ref::make_field(Send_field *field) Item *Item_ref::get_tmp_table_item(THD *thd) { if (!result_field) - return (*ref)->get_tmp_table_item(thd); + { + if (!with_sum_func && !const_item()) + return (*ref)->get_tmp_table_item(thd); + else + { + DBUG_ASSERT((*ref)->const_item() || (*ref)->with_sum_func); + return copy_or_same(thd); + } + } Item_field *item= new Item_field(result_field); if (item) diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 57dcbd4f540..1938db86743 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -854,7 +854,8 @@ void Item_subselect::fix_length_and_dec() table_map Item_subselect::used_tables() const { - return (table_map) ((engine->uncacheable() & ~UNCACHEABLE_EXPLAIN)? + return (table_map) ((!forced_const && !const_item_cache && + (engine->uncacheable() & ~UNCACHEABLE_EXPLAIN))? used_tables_cache : 0L); } @@ -864,7 +865,7 @@ bool Item_subselect::const_item() const DBUG_ASSERT(thd); return (thd->lex->context_analysis_only ? FALSE : - forced_const || const_item_cache); + used_tables() == 0); } Item *Item_subselect::get_tmp_table_item(THD *thd_arg) diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 35937f0536f..9b0c1415662 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -14705,6 +14705,8 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table, bool maybe_null= item->maybe_null; Field *new_field; LINT_INIT(new_field); + DBUG_ENTER("create_tmp_field_from_item"); + DBUG_PRINT("enter", ("Item: %p modify: %d", item, modify_item)); /* To preserve type or DATE/TIME and GEOMETRY fields, @@ -14772,7 +14774,7 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table, item->set_result_field(new_field); if (item->type() == Item::NULL_ITEM) new_field->is_created_from_null_item= TRUE; - return new_field; + DBUG_RETURN(new_field); }