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 List-Archive: http://lists.mysql.com/commits/142295 X-Bug: 13538387,13540692,13541761 Message-Id: <20120105095445.08B4A374@atum21.no.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 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 *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 *columns_list= (procedure ? &join->procedure_fields_list : - fields); - List_iterator_fast 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 *columns_list= (procedure ? &join->procedure_fields_list : + fields); + List_iterator_fast 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).