List:Commits« Previous MessageNext Message »
From:Vinay Fisrekar Date:May 10 2011 4:25pm
Subject:bzr commit into mysql-5.1 branch (vinay.fisrekar:3687) Bug#11766521
View as plain text  
#At file:///home/vinayf/repo/branches/mysql-5.1/ based on revid:serge.kozlov@stripped

 3687 Vinay Fisrekar	2011-05-10
      BUG#11766521 - Adding bug scenario for data types in main suite
      
      Impementing Test Review Comment.
      
      Bug test scenario:      
      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 16:25:45 +0000
@@ -0,0 +1,455 @@
+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, 8), (61, -152), (78, 123), (0,135);
+INSERT IGNORE INTO t5 VALUES(96,NULL);
+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
+SELECT HEX(c1),HEX(c2) FROM t5 WHERE c1 < '1' ORDER BY c1;
+HEX(c1)	HEX(c2)
+0	1
+SELECT HEX(c1),HEX(c2) FROM t5 WHERE c1 > '0' ORDER BY c1;
+HEX(c1)	HEX(c2)
+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);
+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;
+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
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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);
+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;
+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
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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);
+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;
+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
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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);
+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;
+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
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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);
+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;
+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
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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);
+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;
+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
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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);
+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;
+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
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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);
+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;
+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
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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);
+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;
+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
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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 16:25:45 +0000
@@ -0,0 +1,246 @@
+########### implicit_char_to_num_conversion.test #######################
+#                                                                      #
+# This test aims at using string/char literal in comparison operators  #
+# without explicit type-cast. This is a bug test for Bug#11766521      #
+# - 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        #
+#                                                                      #
+#                                                                      #
+# Creation:                                                            #
+# 2011-05-10 vfisrekar Implement this test as part of Bug#11766521     #
+#                                                                      #
+########################################################################
+
+--disable_warnings
+DROP TABLE IF EXISTS t5;
+--enable_warnings
+
+let $default_engine = `select @@SESSION.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, 8), (61, -152), (78, 123), (0,135);
+INSERT IGNORE INTO t5 VALUES(96,NULL);
+--enable_warnings
+SELECT HEX(c1),HEX(c2) FROM t5 ORDER BY c1;
+# Enable Following two select after Bug#11766521 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;
+SELECT HEX(c1),HEX(c2) FROM t5 WHERE c1 < '1' ORDER BY c1;
+SELECT HEX(c1),HEX(c2) FROM t5 WHERE c1 > '0' 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;
+--disable_warnings
+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);
+--enable_warnings
+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 
+# them after Bug#11766521 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;
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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;
+--disable_warnings
+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);
+--enable_warnings
+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;
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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;
+--disable_warnings
+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;
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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;
+--disable_warnings
+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);
+--enable_warnings
+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;
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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;
+--disable_warnings
+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);
+--enable_warnings
+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;
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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;
+--disable_warnings
+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);
+--enable_warnings
+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;
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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;
+--disable_warnings
+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);
+--enable_warnings
+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;
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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;
+--disable_warnings
+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);
+--enable_warnings
+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;
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' 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;
+--disable_warnings
+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);
+--enable_warnings
+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;
+SELECT c1,c2 FROM t5 WHERE c1 < 0 AND c2 > '-1' ORDER BY c1;
+DROP TABLE t5;


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