List:Commits« Previous MessageNext Message »
From:Vinay Fisrekar Date:May 10 2011 9:54am
Subject:bzr commit into mysql-5.1 branch (vinay.fisrekar:3685) Bug#11766521
View as plain text  
#At file:///home/vinayf/repo/branches/mysql-5.1/ based on revid:karen.langford@stripped

 3685 Vinay Fisrekar	2011-05-10
      BUG#11766521 - Adding bug scenario for data types in main suite
      
      SELECT is not returning result set for "equal" (=) and "NULL safe equal
      operator"  (<=>) on BIT data type. Extending this scenario for all data types.

    added:
      mysql-test/r/implicit_char_to_num_conversion.result
      mysql-test/t/implicit_char_to_num_conversion.test
=== added file 'mysql-test/r/implicit_char_to_num_conversion.result'
--- a/mysql-test/r/implicit_char_to_num_conversion.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/implicit_char_to_num_conversion.result	2011-05-10 09:54:17 +0000
@@ -0,0 +1,491 @@
+DROP TABLE IF EXISTS t5;
+CREATE TABLE t5(c1  BIT(1) NOT NULL PRIMARY KEY, c2  BIT(1)) ENGINE = <default_engine>;
+INSERT IGNORE INTO t5 VALUES (95, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122,
+118), (0, 177),(75, 42), (108, 67), (79, 349), (59, 188), (69, 206), (49, 345), (118,
+380),(111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36),(116,
+390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499),(30, 83), (5,
+178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403),(44, 307), (68, 454), (57,
+135);
+INSERT IGNORE INTO t5 VALUES(96,null);
+Warnings:
+Warning	1264	Out of range value for column 'c1' at row 1
+SELECT HEX(c1),HEX(c2) FROM t5 ORDER BY c1;
+HEX(c1)	HEX(c2)
+0	1
+1	1
+SELECT hex(c1),hex(c2) FROM t5 WHERE c1 = b'1' ORDER BY c1;
+hex(c1)	hex(c2)
+1	1
+SELECT hex(c1),hex(c2) FROM t5 WHERE c1 <=> b'1' ORDER BY c1;
+hex(c1)	hex(c2)
+1	1
+SELECT hex(c1),hex(c2) FROM t5 WHERE c1 != b'1' ORDER BY c1;
+hex(c1)	hex(c2)
+0	1
+SELECT hex(c1),hex(c2) FROM t5 WHERE c1 >= '1' ORDER BY c1;
+hex(c1)	hex(c2)
+1	1
+SELECT hex(c1),hex(c2) FROM t5 WHERE c1 <= '1' ORDER BY c1;
+hex(c1)	hex(c2)
+0	1
+1	1
+DROP TABLE t5;
+CREATE TABLE t5(c1 FLOAT(5,2) NOT NULL PRIMARY KEY, c2 FLOAT(5,2) UNSIGNED ) ENGINE = <default_engine>;
+INSERT IGNORE INTO t5 VALUES (95.95, 46.46), (10.10,11.11),(1,5),(7.7,-8.8),(-9223372036854775809.92,-9223372036854775808.92);
+Warnings:
+Warning	1264	Out of range value for column 'c2' at row 4
+Warning	1264	Out of range value for column 'c1' at row 5
+Warning	1264	Out of range value for column 'c2' at row 5
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+SELECT c1,c2 FROM t5 ORDER BY c1;
+c1	c2
+-999.99	0.00
+0.00	NULL
+1.00	5.00
+7.70	0.00
+10.10	11.11
+95.95	46.46
+96.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 >= '95' ORDER BY c1;
+c1	c2
+95.95	46.46
+96.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+c1	c2
+-999.99	0.00
+0.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+c1	c2
+-999.99	0.00
+1.00	5.00
+7.70	0.00
+10.10	11.11
+95.95	46.46
+96.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+c1	c2
+-999.99	0.00
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+c1	c2
+-999.99	0.00
+DROP TABLE t5;
+CREATE TABLE t5(c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT UNSIGNED ) ENGINE = <default_engine>;
+INSERT IGNORE INTO t5 VALUES (95, 46), (10,11),(1,5),(7,-8),(-129,-129);
+Warnings:
+Warning	1264	Out of range value for column 'c2' at row 4
+Warning	1264	Out of range value for column 'c1' at row 5
+Warning	1264	Out of range value for column 'c2' at row 5
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+SELECT c1,c2 FROM t5 ORDER BY c1;
+c1	c2
+-128	0
+0	NULL
+1	5
+7	0
+10	11
+95	46
+96	NULL
+SELECT c1,c2 FROM t5 WHERE c1 = '10' ORDER BY c1;
+c1	c2
+10	11
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11' ORDER BY c1;
+c1	c2
+10	11
+SELECT c1,c2 FROM t5 WHERE c1 >= '95' ORDER BY c1;
+c1	c2
+95	46
+96	NULL
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+c1	c2
+-128	0
+0	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+c1	c2
+-128	0
+1	5
+7	0
+10	11
+95	46
+96	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+c1	c2
+-128	0
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+c1	c2
+-128	0
+DROP TABLE t5;
+CREATE TABLE t5(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT UNSIGNED ) ENGINE = <default_engine>;
+INSERT IGNORE INTO t5 VALUES (95, 46), (10,11),(1,5),(7,-8),(-32769,-32768);
+Warnings:
+Warning	1264	Out of range value for column 'c2' at row 4
+Warning	1264	Out of range value for column 'c1' at row 5
+Warning	1264	Out of range value for column 'c2' at row 5
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+SELECT c1,c2 FROM t5 ORDER BY c1;
+c1	c2
+-32768	0
+0	NULL
+1	5
+7	0
+10	11
+95	46
+96	NULL
+SELECT c1,c2 FROM t5 WHERE c1 = '10' ORDER BY c1;
+c1	c2
+10	11
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11' ORDER BY c1;
+c1	c2
+10	11
+SELECT c1,c2 FROM t5 WHERE c1 >= '95' ORDER BY c1;
+c1	c2
+95	46
+96	NULL
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+c1	c2
+-32768	0
+0	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+c1	c2
+-32768	0
+1	5
+7	0
+10	11
+95	46
+96	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+c1	c2
+-32768	0
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+c1	c2
+-32768	0
+DROP TABLE t5;
+CREATE TABLE t5(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT UNSIGNED ) ENGINE = <default_engine>;
+INSERT IGNORE INTO t5 VALUES (95, 46), (10,11),(1,5),(7,-8),(-8388609,-8388608);
+Warnings:
+Warning	1264	Out of range value for column 'c2' at row 4
+Warning	1264	Out of range value for column 'c1' at row 5
+Warning	1264	Out of range value for column 'c2' at row 5
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+SELECT c1,c2 FROM t5 ORDER BY c1;
+c1	c2
+-8388608	0
+0	NULL
+1	5
+7	0
+10	11
+95	46
+96	NULL
+SELECT c1,c2 FROM t5 WHERE c1 = '10' ORDER BY c1;
+c1	c2
+10	11
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11' ORDER BY c1;
+c1	c2
+10	11
+SELECT c1,c2 FROM t5 WHERE c1 >= '95' ORDER BY c1;
+c1	c2
+95	46
+96	NULL
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+c1	c2
+-8388608	0
+0	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+c1	c2
+-8388608	0
+1	5
+7	0
+10	11
+95	46
+96	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+c1	c2
+-8388608	0
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+c1	c2
+-8388608	0
+DROP TABLE t5;
+CREATE TABLE t5(c1 INT NOT NULL PRIMARY KEY, c2 INT UNSIGNED ) ENGINE = <default_engine>;
+INSERT IGNORE INTO t5 VALUES (95, 46), (10,11),(1,5),(7,-8),(-2147483649,-2147483648);
+Warnings:
+Warning	1264	Out of range value for column 'c2' at row 4
+Warning	1264	Out of range value for column 'c1' at row 5
+Warning	1264	Out of range value for column 'c2' at row 5
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+SELECT c1,c2 FROM t5 ORDER BY c1;
+c1	c2
+-2147483648	0
+0	NULL
+1	5
+7	0
+10	11
+95	46
+96	NULL
+SELECT c1,c2 FROM t5 WHERE c1 = '10' ORDER BY c1;
+c1	c2
+10	11
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11' ORDER BY c1;
+c1	c2
+10	11
+SELECT c1,c2 FROM t5 WHERE c1 >= '95' ORDER BY c1;
+c1	c2
+95	46
+96	NULL
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+c1	c2
+-2147483648	0
+0	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+c1	c2
+-2147483648	0
+1	5
+7	0
+10	11
+95	46
+96	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+c1	c2
+-2147483648	0
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+c1	c2
+-2147483648	0
+DROP TABLE t5;
+CREATE TABLE t5(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT UNSIGNED ) ENGINE = <default_engine>;
+INSERT IGNORE INTO t5 VALUES (95, 46), (10,11),(1,5),(7,-8),(-9223372036854775809,-9223372036854775808);
+Warnings:
+Warning	1264	Out of range value for column 'c2' at row 4
+Warning	1264	Out of range value for column 'c1' at row 5
+Warning	1264	Out of range value for column 'c2' at row 5
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+SELECT c1,c2 FROM t5 ORDER BY c1;
+c1	c2
+-9223372036854775808	0
+0	NULL
+1	5
+7	0
+10	11
+95	46
+96	NULL
+SELECT c1,c2 FROM t5 WHERE c1 = '10' ORDER BY c1;
+c1	c2
+10	11
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11' ORDER BY c1;
+c1	c2
+10	11
+SELECT c1,c2 FROM t5 WHERE c1 >= '95' ORDER BY c1;
+c1	c2
+95	46
+96	NULL
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+c1	c2
+-9223372036854775808	0
+0	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+c1	c2
+-9223372036854775808	0
+1	5
+7	0
+10	11
+95	46
+96	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+c1	c2
+-9223372036854775808	0
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+c1	c2
+-9223372036854775808	0
+DROP TABLE t5;
+CREATE TABLE t5(c1 DOUBLE(5,2) NOT NULL PRIMARY KEY, c2 DOUBLE(5,2) UNSIGNED ) ENGINE = <default_engine>;
+INSERT IGNORE INTO t5 VALUES (95.9595, 46.46), (10.1010,11.1111),(1,5),(7.7,-8.8),(-9223372036854775809.92,-9223372036854775808.92);
+Warnings:
+Warning	1264	Out of range value for column 'c2' at row 4
+Warning	1264	Out of range value for column 'c1' at row 5
+Warning	1264	Out of range value for column 'c2' at row 5
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+SELECT c1,c2 FROM t5 ORDER BY c1;
+c1	c2
+-999.99	0.00
+0.00	NULL
+1.00	5.00
+7.70	0.00
+10.10	11.11
+95.96	46.46
+96.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 = '10.10' ORDER BY c1;
+c1	c2
+10.10	11.11
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11.11' ORDER BY c1;
+c1	c2
+10.10	11.11
+SELECT c1,c2 FROM t5 WHERE c1 >= '95.95' ORDER BY c1;
+c1	c2
+95.96	46.46
+96.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+c1	c2
+-999.99	0.00
+0.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+c1	c2
+-999.99	0.00
+1.00	5.00
+7.70	0.00
+10.10	11.11
+95.96	46.46
+96.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+c1	c2
+-999.99	0.00
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+c1	c2
+-999.99	0.00
+DROP TABLE t5;
+CREATE TABLE t5(c1 NUMERIC(5,2) NOT NULL PRIMARY KEY, c2 NUMERIC(5,2) UNSIGNED ) ENGINE = <default_engine>;
+INSERT IGNORE INTO t5 VALUES (95.9595, 46.46), (10.1010,11.1111),(1,5),(7.7,-8.8),(-9223372036854775809.92,-9223372036854775808.92);
+Warnings:
+Note	1265	Data truncated for column 'c1' at row 1
+Note	1265	Data truncated for column 'c1' at row 2
+Note	1265	Data truncated for column 'c2' at row 2
+Warning	1264	Out of range value for column 'c2' at row 4
+Warning	1264	Out of range value for column 'c1' at row 5
+Warning	1264	Out of range value for column 'c2' at row 5
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+SELECT c1,c2 FROM t5 ORDER BY c1;
+c1	c2
+-999.99	0.00
+0.00	NULL
+1.00	5.00
+7.70	0.00
+10.10	11.11
+95.96	46.46
+96.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 = '10.10' ORDER BY c1;
+c1	c2
+10.10	11.11
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11.11' ORDER BY c1;
+c1	c2
+10.10	11.11
+SELECT c1,c2 FROM t5 WHERE c1 >= '95.95' ORDER BY c1;
+c1	c2
+95.96	46.46
+96.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+c1	c2
+-999.99	0.00
+0.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+c1	c2
+-999.99	0.00
+1.00	5.00
+7.70	0.00
+10.10	11.11
+95.96	46.46
+96.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+c1	c2
+-999.99	0.00
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+c1	c2
+-999.99	0.00
+DROP TABLE t5;
+CREATE TABLE t5(c1 DECIMAL(5,2) NOT NULL PRIMARY KEY, c2 DECIMAL(5,2) UNSIGNED ) ENGINE = <default_engine>;
+INSERT IGNORE INTO t5 VALUES (95.9595, 46.46), (10.1010,11.1111),(1,5),(7.7,-8.8),(-9223372036854775809.92,-9223372036854775808.92);
+Warnings:
+Note	1265	Data truncated for column 'c1' at row 1
+Note	1265	Data truncated for column 'c1' at row 2
+Note	1265	Data truncated for column 'c2' at row 2
+Warning	1264	Out of range value for column 'c2' at row 4
+Warning	1264	Out of range value for column 'c1' at row 5
+Warning	1264	Out of range value for column 'c2' at row 5
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+INSERT IGNORE INTO t5 VALUES(null,null);
+Warnings:
+Warning	1048	Column 'c1' cannot be null
+SELECT c1,c2 FROM t5 ORDER BY c1;
+c1	c2
+-999.99	0.00
+0.00	NULL
+1.00	5.00
+7.70	0.00
+10.10	11.11
+95.96	46.46
+96.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 = '10.10' ORDER BY c1;
+c1	c2
+10.10	11.11
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11.11' ORDER BY c1;
+c1	c2
+10.10	11.11
+SELECT c1,c2 FROM t5 WHERE c1 >= '95.95' ORDER BY c1;
+c1	c2
+95.96	46.46
+96.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+c1	c2
+-999.99	0.00
+0.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+c1	c2
+-999.99	0.00
+1.00	5.00
+7.70	0.00
+10.10	11.11
+95.96	46.46
+96.00	NULL
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+c1	c2
+-999.99	0.00
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+c1	c2
+-999.99	0.00
+DROP TABLE t5;

=== added file 'mysql-test/t/implicit_char_to_num_conversion.test'
--- a/mysql-test/t/implicit_char_to_num_conversion.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/implicit_char_to_num_conversion.test	2011-05-10 09:54:17 +0000
@@ -0,0 +1,208 @@
+# This test aims at using string/char literal in comparison operators without explicit type-cast
+# This is a bug test for bug#11766521 (59650) - Incorrect result is returned if string/char literal 
+# is used with comparision operator and bit data type column
+# Test is extended to include numeric data type comparison with string/char literal
+
+--disable_warnings
+DROP TABLE IF EXISTS t5;
+--enable_warnings
+
+let $default_engine = `select @@global.storage_engine`;
+
+# bug#11766521 - BIT Datatype comparison in where clause return incorrect result for '=' , '<=>' operators
+--replace_result $default_engine <default_engine>
+eval CREATE TABLE t5(c1  BIT(1) NOT NULL PRIMARY KEY, c2  BIT(1)) ENGINE = $default_engine;
+--disable_warnings
+INSERT IGNORE INTO t5 VALUES (95, 46), (31, 438), (61, 152), (78, 123), (88, 411), (122,
+118), (0, 177),(75, 42), (108, 67), (79, 349), (59, 188), (69, 206), (49, 345), (118,
+380),(111, 368), (94, 468), (56, 379), (77, 133), (29, 399), (9, 363), (23, 36),(116,
+390), (119, 368), (87, 351), (123, 411), (24, 398), (34, 202), (28, 499),(30, 83), (5,
+178), (60, 343), (4, 245), (104, 280), (106, 446), (127, 403),(44, 307), (68, 454), (57,
+135);
+--enable_warnings
+INSERT IGNORE INTO t5 VALUES(96,null);
+SELECT HEX(c1),HEX(c2) FROM t5 ORDER BY c1;
+# Enable Following two select after bug#59650 fix
+#SELECT hex(c1),hex(c2) FROM t5 WHERE c1 = '1' ORDER BY c1;
+#SELECT hex(c1),hex(c2) FROM t5 WHERE c1 <=> '1' ORDER BY c1;
+SELECT hex(c1),hex(c2) FROM t5 WHERE c1 = b'1' ORDER BY c1;
+SELECT hex(c1),hex(c2) FROM t5 WHERE c1 <=> b'1' ORDER BY c1;
+SELECT hex(c1),hex(c2) FROM t5 WHERE c1 != b'1' ORDER BY c1;
+SELECT hex(c1),hex(c2) FROM t5 WHERE c1 >= '1' ORDER BY c1;
+SELECT hex(c1),hex(c2) FROM t5 WHERE c1 <= '1' ORDER BY c1;
+DROP TABLE t5;
+
+
+
+# FLOAT Data-type
+--replace_result $default_engine <default_engine>
+eval CREATE TABLE t5(c1 FLOAT(5,2) NOT NULL PRIMARY KEY, c2 FLOAT(5,2) UNSIGNED ) ENGINE = $default_engine;
+INSERT IGNORE INTO t5 VALUES (95.95, 46.46), (10.10,11.11),(1,5),(7.7,-8.8),(-9223372036854775809.92,-9223372036854775808.92);
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+SELECT c1,c2 FROM t5 ORDER BY c1;
+# Compare with string literal
+# following two queries does not return result may be due to bug#11766521. Enable Following two select after bug#59650 fix.
+#SELECT c1,c2 FROM t5 WHERE c1 = '10.10' ORDER BY c1;
+#SELECT c1,c2 FROM t5 WHERE c2 <=> '11.11' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 >= '95' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+DROP TABLE t5;
+
+
+# TINYINT Datatype
+--replace_result $default_engine <default_engine>
+eval CREATE TABLE t5(c1 TINYINT NOT NULL PRIMARY KEY, c2 TINYINT UNSIGNED ) ENGINE = $default_engine;
+INSERT IGNORE INTO t5 VALUES (95, 46), (10,11),(1,5),(7,-8),(-129,-129);
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+SELECT c1,c2 FROM t5 ORDER BY c1;
+# Compare with string literal
+SELECT c1,c2 FROM t5 WHERE c1 = '10' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 >= '95' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+DROP TABLE t5;
+
+
+# SMALLINT Datatype
+--replace_result $default_engine <default_engine>
+eval CREATE TABLE t5(c1 SMALLINT NOT NULL PRIMARY KEY, c2 SMALLINT UNSIGNED ) ENGINE = $default_engine;
+INSERT IGNORE INTO t5 VALUES (95, 46), (10,11),(1,5),(7,-8),(-32769,-32768);
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+SELECT c1,c2 FROM t5 ORDER BY c1;
+# Compare with string literal
+SELECT c1,c2 FROM t5 WHERE c1 = '10' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 >= '95' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+DROP TABLE t5;
+
+
+# MEDIUMINT Datatype
+--replace_result $default_engine <default_engine>
+eval CREATE TABLE t5(c1 MEDIUMINT NOT NULL PRIMARY KEY, c2 MEDIUMINT UNSIGNED ) ENGINE = $default_engine;
+INSERT IGNORE INTO t5 VALUES (95, 46), (10,11),(1,5),(7,-8),(-8388609,-8388608);
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+SELECT c1,c2 FROM t5 ORDER BY c1;
+# Compare with string literal
+SELECT c1,c2 FROM t5 WHERE c1 = '10' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 >= '95' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+DROP TABLE t5;
+
+
+# INT Datatype
+--replace_result $default_engine <default_engine>
+eval CREATE TABLE t5(c1 INT NOT NULL PRIMARY KEY, c2 INT UNSIGNED ) ENGINE = $default_engine;
+INSERT IGNORE INTO t5 VALUES (95, 46), (10,11),(1,5),(7,-8),(-2147483649,-2147483648);
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+SELECT c1,c2 FROM t5 ORDER BY c1;
+# Compare with string literal
+SELECT c1,c2 FROM t5 WHERE c1 = '10' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 >= '95' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+DROP TABLE t5;
+
+
+# BIGINT Data-type
+--replace_result $default_engine <default_engine>
+eval CREATE TABLE t5(c1 BIGINT NOT NULL PRIMARY KEY, c2 BIGINT UNSIGNED ) ENGINE = $default_engine;
+INSERT IGNORE INTO t5 VALUES (95, 46), (10,11),(1,5),(7,-8),(-9223372036854775809,-9223372036854775808);
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+SELECT c1,c2 FROM t5 ORDER BY c1;
+# Compare with string literal
+SELECT c1,c2 FROM t5 WHERE c1 = '10' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 >= '95' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+DROP TABLE t5;
+
+
+
+
+# DOUBLE Datatype
+--replace_result $default_engine <default_engine>
+eval CREATE TABLE t5(c1 DOUBLE(5,2) NOT NULL PRIMARY KEY, c2 DOUBLE(5,2) UNSIGNED ) ENGINE = $default_engine;
+INSERT IGNORE INTO t5 VALUES (95.9595, 46.46), (10.1010,11.1111),(1,5),(7.7,-8.8),(-9223372036854775809.92,-9223372036854775808.92);
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+SELECT c1,c2 FROM t5 ORDER BY c1;
+# Compare with string literal
+SELECT c1,c2 FROM t5 WHERE c1 = '10.10' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11.11' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 >= '95.95' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+DROP TABLE t5;
+
+
+# NUMERIC Datatype
+--replace_result $default_engine <default_engine>
+eval CREATE TABLE t5(c1 NUMERIC(5,2) NOT NULL PRIMARY KEY, c2 NUMERIC(5,2) UNSIGNED ) ENGINE = $default_engine;
+INSERT IGNORE INTO t5 VALUES (95.9595, 46.46), (10.1010,11.1111),(1,5),(7.7,-8.8),(-9223372036854775809.92,-9223372036854775808.92);
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+SELECT c1,c2 FROM t5 ORDER BY c1;
+# Compare with string literal
+SELECT c1,c2 FROM t5 WHERE c1 = '10.10' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11.11' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 >= '95.95' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+DROP TABLE t5;
+
+
+# DECIMAL Datatype
+--replace_result $default_engine <default_engine>
+eval CREATE TABLE t5(c1 DECIMAL(5,2) NOT NULL PRIMARY KEY, c2 DECIMAL(5,2) UNSIGNED ) ENGINE = $default_engine;
+INSERT IGNORE INTO t5 VALUES (95.9595, 46.46), (10.1010,11.1111),(1,5),(7.7,-8.8),(-9223372036854775809.92,-9223372036854775808.92);
+INSERT IGNORE INTO t5 VALUES(96,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+INSERT IGNORE INTO t5 VALUES(null,null);
+SELECT c1,c2 FROM t5 ORDER BY c1;
+# Compare with string literal
+SELECT c1,c2 FROM t5 WHERE c1 = '10.10' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c2 <=> '11.11' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 >= '95.95' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 <= '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '0' ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = 0 ORDER BY c1;
+SELECT c1,c2 FROM t5 WHERE c1 != '1' and c1 <=0 and c2 = '0' ORDER BY c1;
+DROP TABLE t5;


Attachment: [text/bzr-bundle] bzr/vinay.fisrekar@oracle.com-20110510095417-glm6wpdlq3ghcfv7.bundle
Thread
bzr commit into mysql-5.1 branch (vinay.fisrekar:3685) Bug#11766521Vinay Fisrekar10 May