diff options
Diffstat (limited to 'mysql-test/t/select.test')
| -rw-r--r-- | mysql-test/t/select.test | 45 |
1 files changed, 45 insertions, 0 deletions
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 592e366f835..197d89d02d5 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2304,6 +2304,51 @@ 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); +INSERT INTO t1 VALUES (1,1), (2,1), (4,10); + +CREATE TABLE t2 (a int PRIMARY KEY, b int, KEY b (b)); +INSERT INTO t2 VALUES (1,NULL), (2,10); +ALTER TABLE t1 ENABLE KEYS; + +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; +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" +# +--disable_ps_protocol +CREATE TABLE t1 (key1 float default NULL, UNIQUE KEY key1 (key1)); +CREATE TABLE t2 (key2 float default NULL, UNIQUE KEY key2 (key2)); +INSERT INTO t1 VALUES (0.3762),(0.3845),(0.6158),(0.7941); +INSERT INTO t2 VALUES (1.3762),(1.3845),(1.6158),(1.7941); + +explain select max(key1) from t1 where key1 <= 0.6158; +explain select max(key2) from t2 where key2 <= 1.6158; +explain select min(key1) from t1 where key1 >= 0.3762; +explain select min(key2) from t2 where key2 >= 1.3762; +explain select max(key1), min(key2) from t1, t2 +where key1 <= 0.6158 and key2 >= 1.3762; +explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; +explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; + +select max(key1) from t1 where key1 <= 0.6158; +select max(key2) from t2 where key2 <= 1.6158; +select min(key1) from t1 where key1 >= 0.3762; +select min(key2) from t2 where key2 >= 1.3762; +select max(key1), min(key2) from t1, t2 +where key1 <= 0.6158 and key2 >= 1.3762; +select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; +select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; + +DROP TABLE t1,t2; +--enable_ps_protocol + # End of 4.1 tests # |