MDEV-16207: Sig 11 on RHEL7 on Azurebb-5.5-MDEV-16207

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.
author: Oleksandr Byelkin <sanja@mariadb.com> 2018-10-12 20:47:22 +0200
committer: Oleksandr Byelkin <sanja@mariadb.com> 2018-10-12 20:47:22 +0200
commit: 8009d85cc2e9245f6e8be06397914433e4b20007 (patch)
tree: b897a8b121f1d01cf18b3cb68bebca538d9e7157
parent: acf8fc1ff8a7b2d49e25279670b04b8eb096ce0c (diff)
download: mariadb-git-bb-5.5-MDEV-16207.tar.gz
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);
 }
author	Oleksandr Byelkin <sanja@mariadb.com>	2018-10-12 20:47:22 +0200
committer	Oleksandr Byelkin <sanja@mariadb.com>	2018-10-12 20:47:22 +0200
commit	8009d85cc2e9245f6e8be06397914433e4b20007 (patch)
tree	b897a8b121f1d01cf18b3cb68bebca538d9e7157
parent	acf8fc1ff8a7b2d49e25279670b04b8eb096ce0c (diff)
download	mariadb-git-bb-5.5-MDEV-16207.tar.gz