diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2019-12-27 13:05:06 +0200 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2019-12-27 15:14:48 +0200 |
commit | 5ab70e7f68ba9659dbdd8c71759cfe99ee90ebf2 (patch) | |
tree | cde569f712505f981e7a43f01e82c600e282c88c /mysql-test/main/win.test | |
parent | ee9a19fb054085fcea006a25ec957e0d5cb01ce8 (diff) | |
parent | 16bce0f6fe6bcad0091dc45a97a8ac7b33fe9d44 (diff) | |
download | mariadb-git-5ab70e7f68ba9659dbdd8c71759cfe99ee90ebf2.tar.gz |
Merge 10.2 into 10.3
Diffstat (limited to 'mysql-test/main/win.test')
-rw-r--r-- | mysql-test/main/win.test | 75 |
1 files changed, 75 insertions, 0 deletions
diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test index a9a67bef575..38da2bde064 100644 --- a/mysql-test/main/win.test +++ b/mysql-test/main/win.test @@ -2352,6 +2352,81 @@ SELECT (SELECT MIN('foo') OVER() FROM t1 LIMIT 1) as x; drop table t1; --echo # +--echo # MDEV-16579: Wrong result of query using DISTINCT COUNT(*) OVER (*) +--echo # + +CREATE TABLE t1 (i int) ; +INSERT INTO t1 VALUES (1),(0),(1),(2),(0),(1),(2),(1),(2); + +SELECT DISTINCT COUNT(*) OVER (), MOD(MIN(i),2) FROM t1 GROUP BY i ; +drop table t1; + +--echo # +--echo # MDEV-21318: Wrong results with window functions and implicit grouping +--echo # + +CREATE TABLE t1 (a INT); + +--echo # +--echo # With empty const table +--echo # The expected result here is 1, NULL +--echo # + +explain +SELECT row_number() over(), sum(1) FROM t1; +SELECT row_number() over(), sum(1) FROM t1; + +insert into t1 values (2); + +--echo # +--echo # Const table has 1 row, but still impossible where +--echo # The expected result here is 1, NULL +--echo # + +EXPLAIN SELECT row_number() over(), sum(1) FROM t1 where a=1; +SELECT row_number() over(), sum(1) FROM t1 where a=1; + +--echo # +--echo # Impossible HAVING +--echo # Empty result is expected +--echo # + +EXPLAIN SELECT row_number() over(), sum(1) FROM t1 where a=1 having 1=0; +SELECT row_number() over(), sum(1) FROM t1 where a=1 having 1=0; + +--echo # +--echo # const table has 1 row, no impossible where +--echo # The expected result here is 1, 2 +--echo # + +EXPLAIN SELECT row_number() over(), sum(a) FROM t1 where a=2; +SELECT row_number() over(), sum(a) FROM t1 where a=2; +drop table t1; + +--echo # +--echo # Impossible Where +--echo # + +create table t1(a int); +insert into t1 values (1); + +--echo # +--echo # Expected result is NULL, 0, NULL +--echo # +EXPLAIN SELECT MAX(a) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE; +SELECT MAX(a) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE; + +--echo # +--echo # Expected result is 1, 0, NULL +--echo # + +EXPLAIN +SELECT MAX(1) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE; +SELECT MAX(1) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE; + +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # |