3717 Roy Lyseng 2012-03-06
Bug#13735980: Difference in number of rows when using subqueries
Test case for a duplicate bug report.
mysql-test/include/subquery.inc
Added test case for bug#13735980.
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_bka.result
mysql-test/r/subquery_all_bka_nixbnl.result
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_nomat_nosj_bka.result
mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
mysql-test/r/subquery_none.result
mysql-test/r/subquery_none_bka.result
mysql-test/r/subquery_none_bka_nixbnl.result
Added test case results for bug#13735980.
modified:
mysql-test/include/subquery.inc
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_bka.result
mysql-test/r/subquery_all_bka_nixbnl.result
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_nomat_nosj_bka.result
mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
mysql-test/r/subquery_none.result
mysql-test/r/subquery_none_bka.result
mysql-test/r/subquery_none_bka_nixbnl.result
3716 Marko Makela 2012-03-06
Fix some complation errors on Solaris 10 x86 with GCC 3.4.3.
Always include my_global.h (or univ.i) before system headers.
Avoid casting -1 to unsigned. Use ~0 instead.
modified:
extra/innochecksum.cc
storage/innobase/buf/buf0dump.cc
storage/innobase/handler/ha_innodb.cc
storage/innobase/ut/ut0crc32.cc
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc 2012-02-08 15:25:17 +0000
+++ b/mysql-test/include/subquery.inc 2012-03-06 10:44:14 +0000
@@ -5822,3 +5822,48 @@ flush status;
eval $query;
show status like "handler_read%";
drop table t1,t2;
+
+--echo #
+--echo # Bug#13735980 Difference in number of rows when using subqueries
+--echo #
+
+CREATE TABLE m (c1 VARCHAR(1), c2 INTEGER, c3 INTEGER);
+INSERT INTO m VALUES ('',6,8), ('',75,NULL);
+
+CREATE TABLE o (c1 VARCHAR(1));
+INSERT INTO o VALUES ('S'), ('S'), ('S');
+
+CREATE VIEW v1 AS
+SELECT m.c1 AS c1,m.c2 AS c2,m.c3 AS c3 FROM m;
+
+CREATE VIEW v2 AS
+SELECT o.c1 AS c1 FROM o;
+
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN 2 AND 6 OR
+ (t1.c3 IN(4) AND t1.c3 <> 2) OR t1.c2 >= 8;
+
+CREATE TABLE integers (i1 INTEGER);
+INSERT IGNORE INTO integers VALUES (2),(4),(6),(8);
+
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+ (SELECT i1 FROM integers WHERE i1 = 6) OR
+ t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+ t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+ t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+
+# Outcome is unaffected when replacing views with tables:
+
+SELECT t1.c3
+FROM m AS t1 JOIN o AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+ (SELECT i1 FROM integers WHERE i1 = 6) OR
+ t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+ t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+ t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+
+DROP VIEW v1, v2;
+DROP TABLE m, o, integers;
=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/subquery_all.result 2012-03-06 10:44:14 +0000
@@ -7107,4 +7107,49 @@ Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 3
drop table t1,t2;
+#
+# Bug#13735980 Difference in number of rows when using subqueries
+#
+CREATE TABLE m (c1 VARCHAR(1), c2 INTEGER, c3 INTEGER);
+INSERT INTO m VALUES ('',6,8), ('',75,NULL);
+CREATE TABLE o (c1 VARCHAR(1));
+INSERT INTO o VALUES ('S'), ('S'), ('S');
+CREATE VIEW v1 AS
+SELECT m.c1 AS c1,m.c2 AS c2,m.c3 AS c3 FROM m;
+CREATE VIEW v2 AS
+SELECT o.c1 AS c1 FROM o;
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN 2 AND 6 OR
+(t1.c3 IN(4) AND t1.c3 <> 2) OR t1.c2 >= 8;
+c3
+NULL
+NULL
+NULL
+CREATE TABLE integers (i1 INTEGER);
+INSERT IGNORE INTO integers VALUES (2),(4),(6),(8);
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+SELECT t1.c3
+FROM m AS t1 JOIN o AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+DROP VIEW v1, v2;
+DROP TABLE m, o, integers;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_all_bka.result'
--- a/mysql-test/r/subquery_all_bka.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/subquery_all_bka.result 2012-03-06 10:44:14 +0000
@@ -7108,5 +7108,50 @@ Handler_read_prev 0
Handler_read_rnd 2
Handler_read_rnd_next 3
drop table t1,t2;
+#
+# Bug#13735980 Difference in number of rows when using subqueries
+#
+CREATE TABLE m (c1 VARCHAR(1), c2 INTEGER, c3 INTEGER);
+INSERT INTO m VALUES ('',6,8), ('',75,NULL);
+CREATE TABLE o (c1 VARCHAR(1));
+INSERT INTO o VALUES ('S'), ('S'), ('S');
+CREATE VIEW v1 AS
+SELECT m.c1 AS c1,m.c2 AS c2,m.c3 AS c3 FROM m;
+CREATE VIEW v2 AS
+SELECT o.c1 AS c1 FROM o;
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN 2 AND 6 OR
+(t1.c3 IN(4) AND t1.c3 <> 2) OR t1.c2 >= 8;
+c3
+NULL
+NULL
+NULL
+CREATE TABLE integers (i1 INTEGER);
+INSERT IGNORE INTO integers VALUES (2),(4),(6),(8);
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+SELECT t1.c3
+FROM m AS t1 JOIN o AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+DROP VIEW v1, v2;
+DROP TABLE m, o, integers;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_all_bka_nixbnl.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/subquery_all_bka_nixbnl.result 2012-03-06 10:44:14 +0000
@@ -7108,5 +7108,50 @@ Handler_read_prev 0
Handler_read_rnd 2
Handler_read_rnd_next 3
drop table t1,t2;
+#
+# Bug#13735980 Difference in number of rows when using subqueries
+#
+CREATE TABLE m (c1 VARCHAR(1), c2 INTEGER, c3 INTEGER);
+INSERT INTO m VALUES ('',6,8), ('',75,NULL);
+CREATE TABLE o (c1 VARCHAR(1));
+INSERT INTO o VALUES ('S'), ('S'), ('S');
+CREATE VIEW v1 AS
+SELECT m.c1 AS c1,m.c2 AS c2,m.c3 AS c3 FROM m;
+CREATE VIEW v2 AS
+SELECT o.c1 AS c1 FROM o;
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN 2 AND 6 OR
+(t1.c3 IN(4) AND t1.c3 <> 2) OR t1.c2 >= 8;
+c3
+NULL
+NULL
+NULL
+CREATE TABLE integers (i1 INTEGER);
+INSERT IGNORE INTO integers VALUES (2),(4),(6),(8);
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+SELECT t1.c3
+FROM m AS t1 JOIN o AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+DROP VIEW v1, v2;
+DROP TABLE m, o, integers;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result 2012-03-06 10:44:14 +0000
@@ -7107,4 +7107,49 @@ Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 3
drop table t1,t2;
+#
+# Bug#13735980 Difference in number of rows when using subqueries
+#
+CREATE TABLE m (c1 VARCHAR(1), c2 INTEGER, c3 INTEGER);
+INSERT INTO m VALUES ('',6,8), ('',75,NULL);
+CREATE TABLE o (c1 VARCHAR(1));
+INSERT INTO o VALUES ('S'), ('S'), ('S');
+CREATE VIEW v1 AS
+SELECT m.c1 AS c1,m.c2 AS c2,m.c3 AS c3 FROM m;
+CREATE VIEW v2 AS
+SELECT o.c1 AS c1 FROM o;
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN 2 AND 6 OR
+(t1.c3 IN(4) AND t1.c3 <> 2) OR t1.c2 >= 8;
+c3
+NULL
+NULL
+NULL
+CREATE TABLE integers (i1 INTEGER);
+INSERT IGNORE INTO integers VALUES (2),(4),(6),(8);
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+SELECT t1.c3
+FROM m AS t1 JOIN o AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+DROP VIEW v1, v2;
+DROP TABLE m, o, integers;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_nomat_nosj_bka.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka.result 2012-03-06 10:44:14 +0000
@@ -7108,5 +7108,50 @@ Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 3
drop table t1,t2;
+#
+# Bug#13735980 Difference in number of rows when using subqueries
+#
+CREATE TABLE m (c1 VARCHAR(1), c2 INTEGER, c3 INTEGER);
+INSERT INTO m VALUES ('',6,8), ('',75,NULL);
+CREATE TABLE o (c1 VARCHAR(1));
+INSERT INTO o VALUES ('S'), ('S'), ('S');
+CREATE VIEW v1 AS
+SELECT m.c1 AS c1,m.c2 AS c2,m.c3 AS c3 FROM m;
+CREATE VIEW v2 AS
+SELECT o.c1 AS c1 FROM o;
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN 2 AND 6 OR
+(t1.c3 IN(4) AND t1.c3 <> 2) OR t1.c2 >= 8;
+c3
+NULL
+NULL
+NULL
+CREATE TABLE integers (i1 INTEGER);
+INSERT IGNORE INTO integers VALUES (2),(4),(6),(8);
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+SELECT t1.c3
+FROM m AS t1 JOIN o AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+DROP VIEW v1, v2;
+DROP TABLE m, o, integers;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result'
--- a/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result 2012-03-06 10:44:14 +0000
@@ -7108,5 +7108,50 @@ Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 3
drop table t1,t2;
+#
+# Bug#13735980 Difference in number of rows when using subqueries
+#
+CREATE TABLE m (c1 VARCHAR(1), c2 INTEGER, c3 INTEGER);
+INSERT INTO m VALUES ('',6,8), ('',75,NULL);
+CREATE TABLE o (c1 VARCHAR(1));
+INSERT INTO o VALUES ('S'), ('S'), ('S');
+CREATE VIEW v1 AS
+SELECT m.c1 AS c1,m.c2 AS c2,m.c3 AS c3 FROM m;
+CREATE VIEW v2 AS
+SELECT o.c1 AS c1 FROM o;
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN 2 AND 6 OR
+(t1.c3 IN(4) AND t1.c3 <> 2) OR t1.c2 >= 8;
+c3
+NULL
+NULL
+NULL
+CREATE TABLE integers (i1 INTEGER);
+INSERT IGNORE INTO integers VALUES (2),(4),(6),(8);
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+SELECT t1.c3
+FROM m AS t1 JOIN o AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+DROP VIEW v1, v2;
+DROP TABLE m, o, integers;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/subquery_none.result 2012-03-06 10:44:14 +0000
@@ -7106,4 +7106,49 @@ Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 3
drop table t1,t2;
+#
+# Bug#13735980 Difference in number of rows when using subqueries
+#
+CREATE TABLE m (c1 VARCHAR(1), c2 INTEGER, c3 INTEGER);
+INSERT INTO m VALUES ('',6,8), ('',75,NULL);
+CREATE TABLE o (c1 VARCHAR(1));
+INSERT INTO o VALUES ('S'), ('S'), ('S');
+CREATE VIEW v1 AS
+SELECT m.c1 AS c1,m.c2 AS c2,m.c3 AS c3 FROM m;
+CREATE VIEW v2 AS
+SELECT o.c1 AS c1 FROM o;
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN 2 AND 6 OR
+(t1.c3 IN(4) AND t1.c3 <> 2) OR t1.c2 >= 8;
+c3
+NULL
+NULL
+NULL
+CREATE TABLE integers (i1 INTEGER);
+INSERT IGNORE INTO integers VALUES (2),(4),(6),(8);
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+SELECT t1.c3
+FROM m AS t1 JOIN o AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+DROP VIEW v1, v2;
+DROP TABLE m, o, integers;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_none_bka.result'
--- a/mysql-test/r/subquery_none_bka.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/subquery_none_bka.result 2012-03-06 10:44:14 +0000
@@ -7107,5 +7107,50 @@ Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 3
drop table t1,t2;
+#
+# Bug#13735980 Difference in number of rows when using subqueries
+#
+CREATE TABLE m (c1 VARCHAR(1), c2 INTEGER, c3 INTEGER);
+INSERT INTO m VALUES ('',6,8), ('',75,NULL);
+CREATE TABLE o (c1 VARCHAR(1));
+INSERT INTO o VALUES ('S'), ('S'), ('S');
+CREATE VIEW v1 AS
+SELECT m.c1 AS c1,m.c2 AS c2,m.c3 AS c3 FROM m;
+CREATE VIEW v2 AS
+SELECT o.c1 AS c1 FROM o;
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN 2 AND 6 OR
+(t1.c3 IN(4) AND t1.c3 <> 2) OR t1.c2 >= 8;
+c3
+NULL
+NULL
+NULL
+CREATE TABLE integers (i1 INTEGER);
+INSERT IGNORE INTO integers VALUES (2),(4),(6),(8);
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+SELECT t1.c3
+FROM m AS t1 JOIN o AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+DROP VIEW v1, v2;
+DROP TABLE m, o, integers;
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_none_bka_nixbnl.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/subquery_none_bka_nixbnl.result 2012-03-06 10:44:14 +0000
@@ -7107,5 +7107,50 @@ Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 3
drop table t1,t2;
+#
+# Bug#13735980 Difference in number of rows when using subqueries
+#
+CREATE TABLE m (c1 VARCHAR(1), c2 INTEGER, c3 INTEGER);
+INSERT INTO m VALUES ('',6,8), ('',75,NULL);
+CREATE TABLE o (c1 VARCHAR(1));
+INSERT INTO o VALUES ('S'), ('S'), ('S');
+CREATE VIEW v1 AS
+SELECT m.c1 AS c1,m.c2 AS c2,m.c3 AS c3 FROM m;
+CREATE VIEW v2 AS
+SELECT o.c1 AS c1 FROM o;
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN 2 AND 6 OR
+(t1.c3 IN(4) AND t1.c3 <> 2) OR t1.c2 >= 8;
+c3
+NULL
+NULL
+NULL
+CREATE TABLE integers (i1 INTEGER);
+INSERT IGNORE INTO integers VALUES (2),(4),(6),(8);
+SELECT t1.c3
+FROM v1 AS t1 JOIN v2 AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+SELECT t1.c3
+FROM m AS t1 JOIN o AS t2 ON t1.c1 < t2.c1
+WHERE t1.c3 BETWEEN (SELECT i1 FROM integers WHERE i1 = 2) AND
+(SELECT i1 FROM integers WHERE i1 = 6) OR
+t1.c3 IN((SELECT i1 FROM integers WHERE i1 = 4)) AND
+t1.c3 <>(SELECT i1 FROM integers WHERE i1 = 2) OR
+t1.c2 >=(SELECT i1 FROM integers WHERE i1 = 8);
+c3
+NULL
+NULL
+NULL
+DROP VIEW v1, v2;
+DROP TABLE m, o, integers;
set optimizer_switch=default;
set optimizer_switch=default;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3716 to 3717) Bug#13735980 | Roy Lyseng | 6 Mar |
