List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:January 5 2012 9:54am
Subject:bzr push into mysql-trunk branch (jorgen.loland:3506 to 3507) Bug#13538387
Bug#13540692 Bug#13541761
View as plain text  
 3507 Jorgen Loland	2012-01-04
      BUG#13540692: WRONG NULL HANDLING WITH RIGHT JOIN + 
                    DISTINCT OR ORDER BY
      BUG#13538387: WRONG RESULT ON SELECT DISTINCT + LEFT JOIN + 
                    LIMIT + MIX OF MYISAM AND INNODB
      BUG#13541761: WRONG RESULTS ON CORRELATED SUBQUERY + 
                    AGGREGATE FUNCTION + MYISAM OR MEMORY
      
      The fix for bugs 13430588/13422961/11760517 did two things:
      
       1) removed setting of record[0] null-bits from
          mark_as_null_row(). This caused bugs 13540692 and 
          13538387. Fixed by setting Copy_field::null_row
          to from_field->table->null_row regardless of whether
          the field can be NULL since not nullable columns 
          must have value NULL if the row is null-complemented.
       2) made JOIN::clear() call mark_as_null_row() on const 
          tables in addition to non-const tables. This caused 
          13541761 because when const tables inside subqueries 
          are temporarily marked as null, we have to save and 
          later restore their null-row info as the fields are 
          never reevaluated. Fixed by calling 
          save_const_null_info() and restore_const_null_info().
     @ mysql-test/r/distinct.result
        Add test for BUG#13540692 and BUG#13538387
     @ mysql-test/r/group_by.result
        Add test for BUG#13541761
     @ mysql-test/t/distinct.test
        Add test for BUG#13540692 and BUG#13538387
     @ mysql-test/t/group_by.test
        Add test for BUG#13541761
     @ sql/field_conv.cc
        Set Copy_field::null_row to from_field->table->null_row
        regardless of whether the field can be NULL since not nullable
        columns must have value NULL if the row is null-complemented.
        Due to this, functions with duplicate code could be removed.
     @ sql/sql_executor.cc
        do_select(): save and restore const table null-info when calling JOIN::clear()

    modified:
      mysql-test/r/distinct.result
      mysql-test/r/group_by.result
      mysql-test/t/distinct.test
      mysql-test/t/group_by.test
      sql/field_conv.cc
      sql/sql_executor.cc
 3506 Jorgen Loland	2012-01-04
      BUG#13350136 - TEMPORARY MYISAM TABLES DO NOT PACK LONG 
                     VARCHAR COLUMNS
      
      MyISAM has three record formats (fixed, packed and compressed) 
      out of which fixed and packed are applicable for temporary 
      tables. The difference is that with fixed length record format,
      VARCHAR trailing bytes are written whereas for packed record 
      format the trailing bytes are not written. The packed record 
      therefore saves space at the cost of some additional memcopies.
      
      The packed record format is currently not in use by temporary
      tables, although old commit messages suggests that this has not
      always been the case, e.g:
      
         revision: sp1r-monty@stripped
         Date: 2004-12-06
         "Internal temporary files can now use fixed length tables 
          if the used VARCHAR columns are short"
      
      This CS enables packed record format for temporary tables.
      
      The CS also includes a fix for a MyISAM bug that is only a
      problem when the packed format and a special unique constraint
      (that checksums wide columns) is used. This unique constraint
      only applies to internal temporary tables when uniqueness is
      required on columns with a total size bigger than normal 
      indexes allow (1000 bytes). The problem:
      
      A column's value in MyISAM can be read from or written to the 
      row buffer using MI_COLUMNDEF::offset ("normal" column 
      definition) or HA_KEYSEG::start (key definition). The former 
      is calculated in mi_open() and is the sum of the length of all
      preceding columns. The latter is calculated in mi_create(). 
      The bug was that in mi_create(), HA_KEYSEG::start was set to 
      the offset of the column for the on-disk format (reclength) 
      whereas it should have been the offset for the in-memory format.
      
      Due to this bug, unique constraint values were written to one
      offset (HA_KEYSEG::start in mi_check_unique()) and read from
      another offset (MI_COLUMNDEF::offset in _mi_pack_record et al),
      resulting in duplicate rows in the result set because equally
      valued rows got seemingly different checksums.
     @ mysql-test/suite/opt_trace/r/temp_table.result
        Trace changes because packed record format is used for tmp table
     @ sql/sql_tmp_table.cc
        Enable packed record format for MyISAM temporary tables
     @ storage/myisam/mi_create.c
        HA_KEYSEG::start is supposed to point to the same offset as
        MI_COLUMNDEF::offset, which is the offset of a column when in
        memory. Instead it pointed to the offset for the on-disk 
        format. Set in mi_create()

    modified:
      mysql-test/suite/opt_trace/r/temp_table.result
      sql/sql_tmp_table.cc
      storage/myisam/mi_create.c
=== modified file 'mysql-test/r/distinct.result'
--- a/mysql-test/r/distinct.result	2011-11-02 12:47:31 +0000
+++ b/mysql-test/r/distinct.result	2012-01-04 13:53:33 +0000
@@ -826,3 +826,62 @@ ORDER BY col_time_key
 LIMIT 3;
 col_int_key
 DROP TABLE t1;
+#
+# BUG#13540692: WRONG NULL HANDLING WITH RIGHT JOIN + 
+#               DISTINCT OR ORDER BY
+#
+CREATE TABLE t1 (
+a INT,
+b INT NOT NULL
+);
+INSERT INTO t1 VALUES (1,2), (3,3);
+
+EXPLAIN SELECT DISTINCT subselect.b
+FROM t1 LEFT JOIN 
+(SELECT it_b.* FROM t1 as it_a LEFT JOIN t1 as it_b ON true) AS subselect 
+ON t1.a = subselect.b
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (Block Nested Loop)
+2	DERIVED	it_a	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	it_b	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+SELECT DISTINCT subselect.b
+FROM t1 LEFT JOIN 
+(SELECT it_b.* FROM t1 as it_a LEFT JOIN t1 as it_b ON true) AS subselect 
+ON t1.a = subselect.b
+;
+b
+3
+NULL
+DROP TABLE t1;
+#
+# BUG#13538387: WRONG RESULT ON SELECT DISTINCT + LEFT JOIN + 
+#               LIMIT + MIX OF MYISAM AND INNODB
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (2),(3);
+CREATE TABLE t2 (b INT);
+CREATE TABLE t3 (
+a INT,
+b INT,
+PRIMARY KEY (b)
+);
+INSERT INTO t3 VALUES (2001,1), (2007,2);
+EXPLAIN SELECT DISTINCT t3.a AS t3_date
+FROM t1
+LEFT JOIN t2 ON false
+LEFT JOIN t3 ON t2.b = t3.b
+LIMIT 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t3	const	PRIMARY	NULL	NULL	NULL	1	Using temporary
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Distinct
+SELECT DISTINCT t3.a AS t3_date
+FROM t1
+LEFT JOIN t2 ON false
+LEFT JOIN t3 ON t2.b = t3.b
+LIMIT 1;
+t3_date
+NULL
+DROP TABLE t1,t2,t3;

=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2011-12-20 12:04:31 +0000
+++ b/mysql-test/r/group_by.result	2012-01-04 13:53:33 +0000
@@ -2241,3 +2241,28 @@ i	j	COUNT(i)
 NULL	NULL	0
 
 DROP TABLE t1,t2;
+#
+# BUG#13541761: WRONG RESULTS ON CORRELATED SUBQUERY + 
+#               AGGREGATE FUNCTION + MYISAM OR MEMORY
+#
+CREATE TABLE t1 (
+a varchar(1)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('a'), ('b');
+CREATE TABLE t2 (
+a varchar(1),
+b int(11)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('a',1);
+
+EXPLAIN SELECT (SELECT MAX(b) FROM t2 WHERE t2.a != t1.a) as MAX 
+FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	
+SELECT (SELECT MAX(b) FROM t2 WHERE t2.a != t1.a) as MAX 
+FROM t1;
+MAX
+NULL
+1
+DROP TABLE t1,t2;

=== modified file 'mysql-test/t/distinct.test'
--- a/mysql-test/t/distinct.test	2011-11-02 12:47:31 +0000
+++ b/mysql-test/t/distinct.test	2012-01-04 13:53:33 +0000
@@ -652,3 +652,56 @@ ORDER BY col_time_key
 LIMIT 3;
 
 DROP TABLE t1;
+
+--echo #
+--echo # BUG#13540692: WRONG NULL HANDLING WITH RIGHT JOIN + 
+--echo #               DISTINCT OR ORDER BY
+--echo #
+
+CREATE TABLE t1 (
+  a INT,
+  b INT NOT NULL
+);
+INSERT INTO t1 VALUES (1,2), (3,3);
+
+let $query=
+SELECT DISTINCT subselect.b
+FROM t1 LEFT JOIN 
+     (SELECT it_b.* FROM t1 as it_a LEFT JOIN t1 as it_b ON true) AS subselect 
+     ON t1.a = subselect.b
+;
+
+--echo
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1;
+
+--echo #
+--echo # BUG#13538387: WRONG RESULT ON SELECT DISTINCT + LEFT JOIN + 
+--echo #               LIMIT + MIX OF MYISAM AND INNODB
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (2),(3);
+
+CREATE TABLE t2 (b INT);
+
+CREATE TABLE t3 (
+  a INT,
+  b INT,
+  PRIMARY KEY (b)
+);
+INSERT INTO t3 VALUES (2001,1), (2007,2);
+
+let $query=
+SELECT DISTINCT t3.a AS t3_date
+FROM t1
+     LEFT JOIN t2 ON false
+     LEFT JOIN t3 ON t2.b = t3.b
+LIMIT 1;
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1,t2,t3;

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2011-12-20 12:04:31 +0000
+++ b/mysql-test/t/group_by.test	2012-01-04 13:53:33 +0000
@@ -1580,3 +1580,32 @@ SELECT i, j, COUNT(i) FROM t1 JOIN t2 WH
 
 --echo
 DROP TABLE t1,t2;
+
+--echo #
+--echo # BUG#13541761: WRONG RESULTS ON CORRELATED SUBQUERY + 
+--echo #               AGGREGATE FUNCTION + MYISAM OR MEMORY
+--echo #
+
+CREATE TABLE t1 (
+  a varchar(1)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES ('a'), ('b');
+
+CREATE TABLE t2 (
+  a varchar(1),
+  b int(11)
+) ENGINE=MyISAM;
+
+INSERT INTO t2 VALUES ('a',1);
+
+let $query= 
+SELECT (SELECT MAX(b) FROM t2 WHERE t2.a != t1.a) as MAX 
+FROM t1;
+
+--echo
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1,t2;
+

=== modified file 'sql/field_conv.cc'
--- a/sql/field_conv.cc	2011-12-15 12:12:14 +0000
+++ b/sql/field_conv.cc	2012-01-04 13:53:33 +0000
@@ -82,24 +82,8 @@ static void do_field_8(Copy_field *copy)
   copy->to_ptr[7]=copy->from_ptr[7];
 }
 
-
 static void do_field_to_null_str(Copy_field *copy)
 {
-  if (*copy->from_null_ptr & copy->from_bit)
-  {
-    memset(copy->to_ptr, 0, copy->from_length);
-    copy->to_null_ptr[0]=1;			// Always bit 1
-  }
-  else
-  {
-    copy->to_null_ptr[0]=0;
-    memcpy(copy->to_ptr,copy->from_ptr,copy->from_length);
-  }
-}
-
-
-static void do_outer_field_to_null_str(Copy_field *copy)
-{
   if (*copy->null_row ||
       (copy->from_null_ptr && (*copy->from_null_ptr & copy->from_bit)))
   {
@@ -211,21 +195,6 @@ static void do_skip(Copy_field *copy __a
 
 static void do_copy_null(Copy_field *copy)
 {
-  if (*copy->from_null_ptr & copy->from_bit)
-  {
-    *copy->to_null_ptr|=copy->to_bit;
-    copy->to_field->reset();
-  }
-  else
-  {
-    *copy->to_null_ptr&= ~copy->to_bit;
-    (copy->do_copy2)(copy);
-  }
-}
-
-
-static void do_outer_field_null(Copy_field *copy)
-{
   if (*copy->null_row ||
       (copy->from_null_ptr && (*copy->from_null_ptr & copy->from_bit)))
   {
@@ -242,7 +211,7 @@ static void do_outer_field_null(Copy_fie
 
 static void do_copy_not_null(Copy_field *copy)
 {
-  if (*copy->from_null_ptr & copy->from_bit)
+  if (*copy->null_row || (*copy->from_null_ptr & copy->from_bit))
   {
     copy->to_field->set_warning(Sql_condition::WARN_LEVEL_WARN,
                                 WARN_DATA_TRUNCATED, 1);
@@ -263,7 +232,7 @@ static void do_copy_maybe_null(Copy_fiel
 
 static void do_copy_timestamp(Copy_field *copy)
 {
-  if (*copy->from_null_ptr & copy->from_bit)
+  if (*copy->null_row || (*copy->from_null_ptr & copy->from_bit))
   {
     /* Same as in set_field_to_null_with_conversions() */
     copy->to_field->set_time();
@@ -275,7 +244,7 @@ static void do_copy_timestamp(Copy_field
 
 static void do_copy_next_number(Copy_field *copy)
 {
-  if (*copy->from_null_ptr & copy->from_bit)
+  if (*copy->null_row || (*copy->from_null_ptr & copy->from_bit))
   {
     /* Same as in set_field_to_null_with_conversions() */
     copy->to_field->table->auto_increment_field_not_null= FALSE;
@@ -574,6 +543,7 @@ void Copy_field::set(uchar *to,Field *fr
   from_ptr=from->ptr;
   to_ptr=to;
   from_length=from->pack_length();
+  null_row= &from->table->null_row;
   if (from->maybe_null())
   {
     from_null_ptr=from->null_ptr;
@@ -581,13 +551,7 @@ void Copy_field::set(uchar *to,Field *fr
     to_ptr[0]=	  1;				// Null as default value
     to_null_ptr=  (uchar*) to_ptr++;
     to_bit=	  1;
-    if (from->table->maybe_null)
-    {
-      null_row=   &from->table->null_row;
-      do_copy=	  do_outer_field_to_null_str;
-    }
-    else
-      do_copy=	  do_field_to_null_str;
+    do_copy=	  do_field_to_null_str;
   }
   else
   {
@@ -630,6 +594,7 @@ void Copy_field::set(Field *to,Field *fr
 
   // set up null handling
   from_null_ptr=to_null_ptr=0;
+  null_row= &from->table->null_row;
   if (from->maybe_null())
   {
     from_null_ptr=	from->null_ptr;
@@ -638,13 +603,7 @@ void Copy_field::set(Field *to,Field *fr
     {
       to_null_ptr=	to->null_ptr;
       to_bit=		to->null_bit;
-      if (from_null_ptr)
-	do_copy=	do_copy_null;
-      else
-      {
-	null_row=	&from->table->null_row;
-	do_copy=	do_outer_field_null;
-      }
+      do_copy=	do_copy_null;
     }
     else
     {

=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc	2011-12-20 12:04:31 +0000
+++ b/sql/sql_executor.cc	2012-01-04 13:53:33 +0000
@@ -1545,21 +1545,33 @@ do_select(JOIN *join,List<Item> *fields,
     }
     else if (join->send_row_on_empty_set())
     {
-      if (!join->having || join->having->val_int())
-      {
-        // Mark tables as containing only NULL values
-        join->clear();
+      table_map save_nullinfo= 0;
+      /*
+        If this is a subquery, we need to save and later restore
+        the const table NULL info before clearing the tables
+        because the following executions of the subquery do not
+        reevaluate constant fields. @see save_const_null_info
+        and restore_const_null_info
+      */
+      if (join->select_lex->master_unit()->item && join->const_tables)
+        save_const_null_info(join, &save_nullinfo);
 
-        // Calculate aggregate functions for no rows
-        List<Item> *columns_list= (procedure ? &join->procedure_fields_list :
-                                   fields);
-        List_iterator_fast<Item> it(*columns_list);
-        Item *item;
-        while ((item= it++))
-          item->no_rows_in_result();
+      // Mark tables as containing only NULL values
+      join->clear();
 
+      // Calculate aggregate functions for no rows
+      List<Item> *columns_list= (procedure ? &join->procedure_fields_list :
+                                 fields);
+      List_iterator_fast<Item> it(*columns_list);
+      Item *item;
+      while ((item= it++))
+        item->no_rows_in_result();
+
+      if (!join->having || join->having->val_int())
         rc= join->result->send_data(*columns_list);
-      }
+
+      if (save_nullinfo)
+        restore_const_null_info(join, save_nullinfo);
     }
     /*
       An error can happen when evaluating the conds 

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (jorgen.loland:3506 to 3507) Bug#13538387Bug#13540692 Bug#13541761Jorgen Loland9 Jan