List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:March 6 2012 10:45am
Subject:bzr push into mysql-trunk branch (roy.lyseng:3716 to 3717) Bug#13735980
View as plain text  
 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#13735980Roy Lyseng6 Mar