diff options
Diffstat (limited to 'mysql-test/t/func_group.test')
| -rw-r--r-- | mysql-test/t/func_group.test | 34 |
1 files changed, 34 insertions, 0 deletions
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 654bb8bb75d..2293ac71454 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -827,4 +827,38 @@ SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50; DROP TABLE t1; +# +# Bug #27573: MIN() on an indexed column which is always NULL sets _other_ +# results to NULL +# +CREATE TABLE t1 ( a INT, b INT, KEY(a) ); +INSERT INTO t1 VALUES (NULL, 1), (NULL, 2); +EXPLAIN SELECT MIN(a), MIN(b) FROM t1; +SELECT MIN(a), MIN(b) FROM t1; + +CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) ); +INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 ); +EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1; +SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1; + +CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b)); +INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2), (2, NULL, 2), (3, NULL, 3); +EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2; +SELECT MIN(a), MIN(b) FROM t3 where a = 2; + +CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b)); +INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2), (2, NULL, 2), (3, 1, 3); +EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2; +SELECT MIN(a), MIN(b) FROM t4 where a = 2; +SELECT MIN(b), min(c) FROM t4 where a = 2; + +CREATE TABLE t5( a INT, b INT, KEY( a, b) ); +INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 ); +EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1; +SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1; +SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1; + +DROP TABLE t1, t2, t3, t4, t5; + +### --echo End of 5.0 tests |