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#13541761 | Jorgen Loland | 9 Jan |
