Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2008-02-15 15:47:32+02:00, gkodinov@stripped +4 -0
Bug #31887: DML Select statement not returning same results
when executed in version 5
Zero fill is a field attribute only. So we can't always
propagate constants for zerofill fields : the values and
expression results don't have that flag.
Fixed by converting the const value to a string and
using that in const propagation when the context allows it.
Disable const propagation for fields with ZEROFILL flag in
all the other cases.
mysql-test/r/compare.result@stripped, 2008-02-15 15:47:31+02:00, gkodinov@stripped +38 -0
Bug #31887: test case
mysql-test/t/compare.test@stripped, 2008-02-15 15:47:31+02:00, gkodinov@stripped +31 -0
Bug #31887: test case
sql/item.cc@stripped, 2008-02-15 15:47:31+02:00, gkodinov@stripped +31 -0
Bug #31887: If the context allows conversion
of an int constant to a zero-filled string constant
put the string constant instead of the int constant
when doing const propagation
sql/mysql_priv.h@stripped, 2008-02-15 15:47:32+02:00, gkodinov@stripped +1 -0
Bug #31887: a macro to get all the Field_num
descendant fields.
diff -Nrup a/mysql-test/r/compare.result b/mysql-test/r/compare.result
--- a/mysql-test/r/compare.result 2006-11-28 15:43:57 +02:00
+++ b/mysql-test/r/compare.result 2008-02-15 15:47:31 +02:00
@@ -53,3 +53,41 @@ a b
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: ''
drop table if exists t1;
+CREATE TABLE t1 (b int(2) zerofill, c int(2) zerofill);
+INSERT INTO t1 (b,c) VALUES (1,2), (1,1), (2,2);
+SELECT CONCAT(b,c), CONCAT(b,c) = '0101' FROM t1;
+CONCAT(b,c) CONCAT(b,c) = '0101'
+0102 0
+0101 1
+0202 0
+EXPLAIN EXTENDED SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`b` = 1) and (concat(_binary'01',`test`.`t1`.`c`) = _latin1'0101'))
+SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101';
+b c
+01 01
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (1),(2);
+SELECT a,
+(SELECT COUNT(*) FROM t1
+WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x
+FROM t2 ORDER BY a;
+a x
+1 1
+2 0
+EXPLAIN EXTENDED
+SELECT a,
+(SELECT COUNT(*) FROM t1
+WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x
+FROM t2 ORDER BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where
+Warnings:
+Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
+Note 1003 select `test`.`t2`.`a` AS `a`,(select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (concat(`test`.`t1`.`b`,`test`.`t1`.`c`) = concat(_latin1'0',`test`.`t2`.`a`,_latin1'01')))) AS `x` from `test`.`t2` order by `test`.`t2`.`a`
+DROP TABLE t1,t2;
+End of 5.0 tests
diff -Nrup a/mysql-test/t/compare.test b/mysql-test/t/compare.test
--- a/mysql-test/t/compare.test 2006-08-15 10:13:11 +03:00
+++ b/mysql-test/t/compare.test 2008-02-15 15:47:31 +02:00
@@ -46,3 +46,34 @@ insert into t1 values (0x01,0x01);
select * from t1 where a=b;
select * from t1 where a=b and b=0x01;
drop table if exists t1;
+
+#
+# Bug #31887: DML Select statement not returning same results when executed
+# in version 5
+#
+
+CREATE TABLE t1 (b int(2) zerofill, c int(2) zerofill);
+INSERT INTO t1 (b,c) VALUES (1,2), (1,1), (2,2);
+
+SELECT CONCAT(b,c), CONCAT(b,c) = '0101' FROM t1;
+
+EXPLAIN EXTENDED SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101';
+SELECT b,c FROM t1 WHERE b = 1 AND CONCAT(b,c) = '0101';
+
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (1),(2);
+
+SELECT a,
+ (SELECT COUNT(*) FROM t1
+ WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x
+FROM t2 ORDER BY a;
+
+EXPLAIN EXTENDED
+SELECT a,
+ (SELECT COUNT(*) FROM t1
+ WHERE b = t2.a AND CONCAT(b,c) = CONCAT('0',t2.a,'01')) x
+FROM t2 ORDER BY a;
+
+DROP TABLE t1,t2;
+
+--echo End of 5.0 tests
diff -Nrup a/sql/item.cc b/sql/item.cc
--- a/sql/item.cc 2007-11-20 19:18:20 +02:00
+++ b/sql/item.cc 2008-02-15 15:47:31 +02:00
@@ -4072,6 +4072,30 @@ bool Item_field::subst_argument_checker(
}
+/**
+ Convert a numeric value to a zero-filled string
+
+ @param[in,out] item the item to operate on
+ @param field The field that this value is equated to
+
+ This function converts a numeric value to a string. In this conversion
+ the zero-fill flag of the field is taken into account.
+ This is required so the resulting string value can be used instead of
+ the field reference when propagating equalities.
+*/
+
+static void convert_zerofill_number_to_string(Item **item, Field_num *field)
+{
+ char buff[MAX_FIELD_WIDTH],*pos;
+ String tmp(buff,sizeof(buff), field->charset()), *res;
+
+ res= (*item)->val_str(&tmp);
+ field->prepend_zeros(res);
+ pos= (char *) sql_strmake (res->ptr(), res->length());
+ *item= new Item_string(pos, res->length(), field->charset());
+}
+
+
/*
Set a pointer to the multiple equality the field reference belongs to
(if any)
@@ -4120,6 +4144,13 @@ Item *Item_field::equal_fields_propagato
if (!item ||
(cmp_context != (Item_result)-1 && item->cmp_context != cmp_context))
item= this;
+ else if (field && (field->flags & ZEROFILL_FLAG) && IS_NUM(field->type()))
+ {
+ if (item && cmp_context != INT_RESULT)
+ convert_zerofill_number_to_string(&item, (Field_num *)field);
+ else
+ item= this;
+ }
return item;
}
diff -Nrup a/sql/mysql_priv.h b/sql/mysql_priv.h
--- a/sql/mysql_priv.h 2007-10-29 09:25:37 +02:00
+++ b/sql/mysql_priv.h 2008-02-15 15:47:32 +02:00
@@ -73,6 +73,7 @@ extern const char *primary_key_name;
#include "mysql_com.h"
#include <violite.h>
#include "unireg.h"
+#define IS_NUM(t) ((t) <= FIELD_TYPE_INT24 || (t) == FIELD_TYPE_YEAR || (t) == FIELD_TYPE_NEWDECIMAL)
void init_sql_alloc(MEM_ROOT *root, uint block_size, uint pre_alloc_size);
gptr sql_alloc(unsigned size);
| Thread |
|---|
| • bk commit into 5.0 tree (gkodinov:1.2588) BUG#31887 | kgeorge | 15 Feb |
