diff options
Diffstat (limited to 'mysql-test/t/select.test')
| -rw-r--r-- | mysql-test/t/select.test | 255 |
1 files changed, 223 insertions, 32 deletions
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 8ecadfc9dfb..f61db538fb4 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2286,25 +2286,6 @@ EXPLAIN SELECT * FROM t1 FORCE INDEX (a); DROP TABLE t1; # -# Bug #18759 "Incorrect string to numeric conversion" -# -# This test is here so that the behavior will not be changed to 4.1 -# and not to 5.0 either. In 4.1 and 5.0 sending an integer as a string -# will be converted internally to real (double) value and it is not -# as accurate as bigint (longlong) for integers. Thus the results may -# vary. In 5.1 internally it is decimal, which is a string type and -# will be more accurate. Due to rather big changes needed to fix this -# in 4.1 or 5.0 it is not desired to do it in the stable versions. -# -# This test is here only to make sure that behavior is not changed in -# 4.1 and 5.0 -# -CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL); -INSERT INTO t1 VALUES (10); -SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1; -DROP TABLE t1; - -# # Bug #21019: First result of SELECT COUNT(*) different than consecutive runs # CREATE TABLE t1 (a int, b int); @@ -2319,6 +2300,7 @@ SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NUL EXPLAIN SELECT STRAIGHT_JOIN SQL_NO_CACHE COUNT(*) FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; SELECT STRAIGHT_JOIN SQL_NO_CACHE * FROM t2, t1 WHERE t1.b = t2.b OR t2.b IS NULL; DROP TABLE IF EXISTS t1,t2; + # # Bug #20954 "avg(keyval) retuns 0.38 but max(keyval) returns an empty set" # @@ -2350,6 +2332,25 @@ DROP TABLE t1,t2; --enable_ps_protocol # +# Bug #18759 "Incorrect string to numeric conversion" +# +# This test is here so that the behavior will not be changed to 4.1 +# and not to 5.0 either. In 4.1 and 5.0 sending an integer as a string +# will be converted internally to real (double) value and it is not +# as accurate as bigint (longlong) for integers. Thus the results may +# vary. In 5.1 internally it is decimal, which is a string type and +# will be more accurate. Due to rather big changes needed to fix this +# in 4.1 or 5.0 it is not desired to do it in the stable versions. +# +# This test is here only to make sure that behavior is not changed in +# 4.1 and 5.0 +# +CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL); +INSERT INTO t1 VALUES (10); +SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1; +DROP TABLE t1; + +# # Bug #22533: storing large hex strings # @@ -3124,18 +3125,7 @@ SELECT t3.a FROM t1,t2,t3 t3.c IN ('bb','ee'); DROP TABLE t1,t2,t3; - -# -# WL3527: Extend IGNORE INDEX so places where index is ignored can -# be specified -# -CREATE TABLE t1 (a INT, b INT, KEY (a)); INSERT INTO t1 VALUES (1,1),(2,2); -EXPLAIN SELECT 1 FROM t1 WHERE a = 1; -EXPLAIN SELECT 1 FROM t1 IGNORE INDEX FOR JOIN (a) WHERE a = 1; -EXPLAIN SELECT 1 FROM t1 USE INDEX FOR JOIN (a) WHERE a = 1; -EXPLAIN SELECT 1 FROM t1 FORCE INDEX FOR JOIN (a) WHERE a = 1; -DROP TABLE t1; - + # # Bug#25172: Not checked buffer size leads to a server crash # @@ -3262,7 +3252,6 @@ SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; DROP TABLE t1,t2; -# # Bug #22026: Warning when using IF statement and large unsigned bigint # @@ -3886,4 +3875,206 @@ SELECT * FROM t1 ORDER BY c, a LIMIT 5; SET @@sort_buffer_size= @old_sort_buffer_size; DROP TABLE t1; + --echo End of 5.0 tests + +# +# Bug #30639: limit offset,rowcount wraps when rowcount >= 2^32 in windows +# +create table t1(a INT, KEY (a)); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5); +SELECT a FROM t1 ORDER BY a LIMIT 2; +SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296; +SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297; +DROP TABLE t1; + +# +# Bug #37936: ASSERT_COLUMN_MARKED_FOR_WRITE in Field_datetime::store , +# Field_varstring::store +# + +CREATE TABLE A (date_key date); + +CREATE TABLE C ( + pk int, + int_nokey int, + int_key int, + date_key date NOT NULL, + date_nokey date, + varchar_key varchar(1) +); + +INSERT INTO C VALUES +(1,1,1,'0000-00-00',NULL,NULL), +(1,1,1,'0000-00-00',NULL,NULL); + +SELECT 1 FROM C WHERE pk > ANY (SELECT 1 FROM C); + +SELECT COUNT(DISTINCT 1) FROM C + WHERE date_key = (SELECT 1 FROM A WHERE C.date_key IS NULL) GROUP BY pk; +SELECT date_nokey FROM C + WHERE int_key IN (SELECT 1 FROM A) + HAVING date_nokey = '10:41:7' + ORDER BY date_key; + +DROP TABLE A,C; + +# +# Bug #42957: no results from +# select where .. (col=col and col=col) or ... (false expression) +# +CREATE TABLE t1 (a INT NOT NULL, b INT); +INSERT INTO t1 VALUES (1, 1); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; +SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2; +DROP TABLE t1; + +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND c=c) OR b > 20; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20; +DROP TABLE t1; + + +--echo # +--echo # Bug#45266: Uninitialized variable lead to an empty result. +--echo # +--disable_warnings +drop table if exists A,AA,B,BB; +CREATE TABLE `A` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `date_key` date NOT NULL, + `date_nokey` date NOT NULL, + `datetime_key` datetime NOT NULL, + `int_nokey` int(11) NOT NULL, + `time_key` time NOT NULL, + `time_nokey` time NOT NULL, + PRIMARY KEY (`pk`), + KEY `date_key` (`date_key`), + KEY `time_key` (`time_key`), + KEY `datetime_key` (`datetime_key`) +); + +CREATE TABLE `AA` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `int_nokey` int(11) NOT NULL, + `time_key` time NOT NULL, + KEY `time_key` (`time_key`), + PRIMARY KEY (`pk`) +); + +CREATE TABLE `B` ( + `date_nokey` date NOT NULL, + `date_key` date NOT NULL, + `time_key` time NOT NULL, + `datetime_nokey` datetime NOT NULL, + `varchar_key` varchar(1) NOT NULL, + KEY `date_key` (`date_key`), + KEY `time_key` (`time_key`), + KEY `varchar_key` (`varchar_key`) +); + +INSERT INTO `B` VALUES ('2003-07-28','2003-07-28','15:13:38','0000-00-00 00:00:00','f'),('0000-00-00','0000-00-00','00:05:48','2004-07-02 14:34:13','x'); + +CREATE TABLE `BB` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `int_nokey` int(11) NOT NULL, + `date_key` date NOT NULL, + `varchar_nokey` varchar(1) NOT NULL, + `date_nokey` date NOT NULL, + PRIMARY KEY (`pk`), + KEY `date_key` (`date_key`) +); + +INSERT INTO `BB` VALUES (10,8,'0000-00-00','i','0000-00-00'),(11,0,'2005-08-18','','2005-08-18'); +# Test #1 +SELECT table1 . `pk` AS field1 + FROM + (BB AS table1 INNER JOIN + (AA AS table2 STRAIGHT_JOIN A AS table3 + ON ( table3 . `date_key` = table2 . `pk` )) + ON ( table3 . `datetime_key` = table2 . `int_nokey` )) + WHERE ( table3 . `date_key` <= 4 AND table2 . `pk` = table1 . `varchar_nokey`) + GROUP BY field1 ; + +SELECT table3 .`date_key` field1 + FROM + B table1 LEFT JOIN B table3 JOIN + (BB table6 JOIN A table7 ON table6 .`varchar_nokey`) + ON table6 .`int_nokey` ON table6 .`date_key` + WHERE NOT ( table1 .`varchar_key` AND table7 .`pk`) GROUP BY field1; + +# Test #2 +SELECT table4 . `time_nokey` AS field1 FROM + (AA AS table1 CROSS JOIN + (AA AS table2 STRAIGHT_JOIN + (B AS table3 STRAIGHT_JOIN A AS table4 + ON ( table4 . `date_key` = table3 . `time_key` )) + ON ( table4 . `pk` = table3 . `date_nokey` )) + ON ( table4 . `time_key` = table3 . `datetime_nokey` )) + WHERE ( table4 . `time_key` < table1 . `time_key` AND + table1 . `int_nokey` != 'f') + GROUP BY field1 ORDER BY field1 , field1; + +SELECT table1 .`time_key` field2 FROM B table1 LEFT JOIN BB JOIN A table5 ON table5 .`date_nokey` ON table5 .`int_nokey` GROUP BY field2; +--enable_warnings + +drop table A,AA,B,BB; +--echo #end of test for bug#45266 + +--echo # +--echo # BUG#48052: Valgrind warning - uninitialized value in init_read_record() +--echo # + +# Needed in 6.0 codebase +#--echo # Disable Index condition pushdown +#--replace_column 1 # +#SELECT @old_icp:=@@engine_condition_pushdown; +#SET SESSION engine_condition_pushdown = 'OFF'; + +CREATE TABLE t1 ( + pk int(11) NOT NULL, + i int(11) DEFAULT NULL, + v varchar(1) DEFAULT NULL, + PRIMARY KEY (pk) +); + +INSERT INTO t1 VALUES (2,7,'m'); +INSERT INTO t1 VALUES (3,9,'m'); + +SELECT v +FROM t1 +WHERE NOT pk > 0 +HAVING v <= 't' +ORDER BY pk; + +# Needed in 6.0 codebase +#--echo # Restore old value for Index condition pushdown +#SET SESSION engine_condition_pushdown=@old_icp; + +DROP TABLE t1; + +--echo # +--echo # Bug#49489 Uninitialized cache led to a wrong result. +--echo # +CREATE TABLE t1(c1 DOUBLE(5,4)); +INSERT INTO t1 VALUES (9.1234); +SELECT * FROM t1 WHERE c1 < 9.12345; +DROP TABLE t1; +--echo # End of test for bug#49489. + + +--echo # +--echo # Bug #49517: Inconsistent behavior while using +--echo # NULLable BIGINT and INT columns in comparison +--echo # +CREATE TABLE t1(a BIGINT UNSIGNED NOT NULL, b BIGINT NULL, c INT NULL); +INSERT INTO t1 VALUES(105, NULL, NULL); +SELECT * FROM t1 WHERE b < 102; +SELECT * FROM t1 WHERE c < 102; +SELECT * FROM t1 WHERE 102 < b; +SELECT * FROM t1 WHERE 102 < c; +DROP TABLE t1; + + +--echo End of 5.1 tests |