[Commits] Rev 3153: Bug lp:781508: Take relevant test cases from MySQL 5.6 feature preview trees in file:///home/tsk/mprog/src/5.3/

timour at askmonty.org timour at askmonty.org
Fri Aug 12 11:39:34 EEST 2011


At file:///home/tsk/mprog/src/5.3/

------------------------------------------------------------
revno: 3153
revision-id: timour at askmonty.org-20110812083929-1j21fmpso3iala1d
parent: sanja at askmonty.org-20110812082350-kdhvaffgpxkq00oi
fixes bug(s): https://launchpad.net/bugs/781508
committer: timour at askmonty.org
branch nick: 5.3
timestamp: Fri 2011-08-12 11:39:29 +0300
message:
  Bug lp:781508: Take relevant test cases from MySQL 5.6 feature preview trees
    
  Identified all test cases in the MySQL file subquery.inc that are
  not present in MariaDB. This patch adds the test cases that are:
  - not present in MySQL 5.5, and
  - already fixed in MariaDB 5.3
  
  The patch adds test cases for the following mysql-trunk bugs:
  - Bug#12763207 - not a bug, mysql-trunk, added test case
  - BUG#50257    - not a bug, mysql-trunk, added test case
  - Bug 11765699 - not a bug, mysql-trunk, added test case
  - BUG#12616253 - not a bug, mysql-trunk, added test case
  
  The comparison was based on the following version of
    mysql-trunk:
    
    revno: 3350 [merge]
    committer: Marko Mäkelä <marko.makela at oracle.com>
    branch nick: mysql-trunk
    timestamp: Mon 2011-08-08 12:42:09 +0300
    message:
      Merge mysql-5.5 to mysql-trunk.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2011-08-09 07:28:57 +0000
+++ b/mysql-test/r/subselect.result	2011-08-12 08:39:29 +0000
@@ -5148,4 +5148,121 @@ NULL
 5
 DROP TABLE t1, t2, t3;
 End of 5.3 tests
+End of 5.5 tests.
+#
+# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
+#
+CREATE TABLE t1(a1 int);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2(a1 int);
+INSERT INTO t2 VALUES (3);
+SELECT @@session.sql_mode INTO @old_sql_mode;
+SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
+1
+1
+1
+SET SESSION sql_mode=@old_sql_mode;
+DROP TABLE t1, t2;
+#
+# BUG#50257: Missing info in REF column of the EXPLAIN 
+#            lines for subselects
+#
+CREATE TABLE t1 (a INT, b INT, INDEX (a));
+INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20);
+
+EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    2       
+2       DERIVED t1      ref     a       a       5       const   1       
+
+EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    4       
+2       SUBQUERY        t1      ref     a       a       5       const   1       Using index
+
+DROP TABLE t1;
+#
+# Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
+#                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0), (1);
+CREATE TABLE t2(
+b TEXT, 
+c INT, 
+PRIMARY KEY (b(1))
+);
+INSERT INTO t2 VALUES ('a', 2), ('b', 3);
+SELECT 1 FROM t1 WHERE a = 
+(SELECT 1 FROM t2 WHERE b = 
+(SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+ORDER BY b
+);
+1
+SELECT 1 FROM t1 WHERE a = 
+(SELECT 1 FROM t2 WHERE b = 
+(SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+GROUP BY b
+);
+1
+DROP TABLE t1, t2;
+#
+# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
+#
+CREATE TABLE t1 (f1 varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+CREATE TABLE t2 (f1_key varchar(1), KEY (f1_key));
+INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'),
+('d'),('y'),('t'),('d'),('s');
+SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT f1_key 
+FROM t2 
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+f1      f1_key
+v       j
+s       j
+v       v
+s       v
+v       c
+s       c
+v       m
+s       m
+v       d
+s       d
+v       d
+s       d
+v       y
+s       y
+v       t
+s       t
+v       d
+s       d
+v       s
+s       s
+explain SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT f1_key 
+FROM t2 
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY table1  ALL     NULL    NULL    NULL    NULL    2       
+1       PRIMARY table2  index   NULL    f1_key  4       NULL    10      Using where; Using index; Using join buffer (flat, BNL join)
+2       DEPENDENT SUBQUERY      t2      range   f1_key  f1_key  4       NULL    6       Range checked for each record (index map: 0x1); Using temporary
+DROP TABLE t1,t2;
 set optimizer_switch=@subselect_tmp;

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2011-07-20 18:48:41 +0000
+++ b/mysql-test/t/subselect.test	2011-08-12 08:39:29 +0000
@@ -4420,4 +4420,100 @@ SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHE
 DROP TABLE t1, t2, t3;
 
 --echo End of 5.3 tests
+
+--echo End of 5.5 tests.
+
+--echo #
+--echo # Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
+--echo #
+
+CREATE TABLE t1(a1 int);
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2(a1 int);
+INSERT INTO t2 VALUES (3);
+
+SELECT @@session.sql_mode INTO @old_sql_mode;
+SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+
+## All these are subject to the transformation
+## '1 < some (...)' => '1 < max(...)'
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
+
+SET SESSION sql_mode=@old_sql_mode;
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # BUG#50257: Missing info in REF column of the EXPLAIN 
+--echo #            lines for subselects
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, INDEX (a));
+INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20);
+
+--echo
+EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
+--echo
+EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
+
+--echo
+DROP TABLE t1;
+
+--echo #
+--echo # Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
+--echo #                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
+--echo #
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0), (1); 
+
+CREATE TABLE t2(
+  b TEXT, 
+  c INT, 
+  PRIMARY KEY (b(1))
+);
+INSERT INTO t2 VALUES ('a', 2), ('b', 3);
+
+SELECT 1 FROM t1 WHERE a = 
+ (SELECT 1 FROM t2 WHERE b = 
+  (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+   ORDER BY b
+ );
+
+SELECT 1 FROM t1 WHERE a = 
+ (SELECT 1 FROM t2 WHERE b = 
+  (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+   GROUP BY b
+ );
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
+--echo #
+
+CREATE TABLE t1 (f1 varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+
+CREATE TABLE t2 (f1_key varchar(1), KEY (f1_key));
+INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'),
+('d'),('y'),('t'),('d'),('s');
+
+let $query=SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT f1_key 
+FROM t2 
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+
+eval $query;
+eval explain $query;
+
+DROP TABLE t1,t2;
+
 set optimizer_switch=@subselect_tmp;



More information about the commits mailing list