Description:
These symptoms probably covers several different causes.
I'm using bbffe0dd6dc37bc6314f096303c9bf4580375c22 .
If you patch like this:
diff --git a/mysql-test/t/window_functions.test b/mysql-test/t/window_functions.test
index 192aab8..7eb901a 100644
--- a/mysql-test/t/window_functions.test
+++ b/mysql-test/t/window_functions.test
@@ -180,12 +180,13 @@ EXECUTE p; # The second execute used to fail
DROP PREPARE p;
--echo Tests with ROLLUP
-SELECT k, MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t
+SELECT MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t
GROUP BY (k) WITH ROLLUP;
+
--error ER_WRONG_USAGE
SELECT k, MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t
GROUP BY (k) WITH ROLLUP ORDER BY wf DESC;
-SELECT k, MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t
+SELECT MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t
GROUP BY k,j WITH ROLLUP;
#This gives correct results, but test is not stable because we cannot give
#order by at the end
@@ -1684,7 +1685,7 @@ SELECT AVG('e') OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
#Bug-25369943
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1),(2);
-SELECT i FROM t1 WHERE i IN ( SELECT SUM(i) OVER (ROWS CURRENT ROW) FROM t1);
+SELECT i FROM t1 WHERE i IN ( SELECT 0+SUM(i) OVER (ROWS CURRENT ROW) FROM t1);
#Bug-25365972
--error ER_NOT_SUPPORTED_YET
SELECT FIRST_VALUE(i) IGNORE NULLS OVER () FROM t1;
The results become wrong:
CURRENT_TEST: main.window_functions
--- /home/mysql_src/git/dbg/mysql-test/r/window_functions.result 2017-01-18 12:42:21.405911923 +0300
+++ /home/mysql_src/git/dbg/build_debug/mysql-test/var/log/window_functions.reject 2017-01-19 16:15:22.119113054 +0300
@@ -358,37 +358,37 @@
NULL 10.5000 10.5000 1
DROP PREPARE p;
Tests with ROLLUP
-SELECT k, MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t
+SELECT MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t
GROUP BY (k) WITH ROLLUP;
-k MIN(i) SUM(j) wf
-1 1 11 1
-2 1 11 3
-3 1 11 6
-4 1 11 10
-NULL 1 44 10
+MIN(i) SUM(j) wf
+1 11 1
+1 11 3
+1 11 6
+1 11 10
+1 44 14 <<<<<<<<<<<<< 10 became 14
SELECT k, MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t
GROUP BY (k) WITH ROLLUP ORDER BY wf DESC;
ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY
-SELECT k, MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t
+SELECT MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t
GROUP BY k,j WITH ROLLUP;
-k MIN(i) SUM(j) wf
-1 1 1 1
-1 1 2 2
-1 1 8 3
-1 1 11 4
-2 1 1 6
-2 1 2 8
-2 1 8 10
-2 1 11 12
-3 1 1 15
-3 1 2 18
-3 1 8 21
-3 1 11 24
-4 1 1 28
-4 1 2 32
-4 1 8 36
-4 1 11 40
-NULL 1 44 40
+MIN(i) SUM(j) wf
+1 1 1
+1 2 2
+1 8 3
+1 11 4
+1 1 6
+1 2 8
+1 8 10
+1 11 12
+1 1 15
+1 2 18
+1 8 21
+1 11 24
+1 1 28
+1 2 32
+1 8 36
+1 11 40
+1 44 44 <<<<<<<< 40 became 44
SELECT sex, AVG(id), ROW_NUMBER() OVER w FROM t1
GROUP BY sex ASC WITH ROLLUP WINDOW w AS ();
sex AVG(id) ROW_NUMBER() OVER w
@@ -4964,10 +4964,8 @@
Warning 1292 Truncated incorrect DOUBLE value: 'e'
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1),(2);
-SELECT i FROM t1 WHERE i IN ( SELECT SUM(i) OVER (ROWS CURRENT ROW) FROM t1);
+SELECT i FROM t1 WHERE i IN ( SELECT 0+SUM(i) OVER (ROWS CURRENT ROW) FROM t1);
i
-1 <<<<<<<<<< rows are gone
-2
SELECT FIRST_VALUE(i) IGNORE NULLS OVER () FROM t1;
ERROR 42000: This version of MySQL doesn't yet support 'IGNORE NULLS'
DROP TABLE t1;
Another one: patch like this:
diff --git a/mysql-test/t/lead_lag.test b/mysql-test/t/lead_lag.test
index 3d8cb55..dd59bab 100644
--- a/mysql-test/t/lead_lag.test
+++ b/mysql-test/t/lead_lag.test
@@ -146,7 +146,7 @@ INSERT INTO t VALUES (null, '[6]', ST_POINTFROMTEXT('POINT(6 6)'), 6, '6', 6.0,
('2', '[2]' , ST_POINTFROMTEXT('POINT(2 2)'), 2, '2', 2.0, null, 20.0/3),
('3', '[3]' , ST_POINTFROMTEXT('POINT(3 3)'), 3, '3', 3.0, 10.0/3, null);
-SELECT LEAD(c1, 100, j1) OVER () lcj, IFNULL(c1, j1) ifn_cj FROM t;
+SELECT LEAD(j1, 100, j1) OVER () lcj, IFNULL(c1, j1) ifn_cj FROM t;
SELECT LEAD(c1, 100, g1) OVER () lcg, IFNULL(c1, g1) ifn_cg FROM t;
SELECT LEAD(c1, 100, i1) OVER () lci, IFNULL(c1, i1) ifn_ci FROM t;
SELECT LEAD(c1, 100, b1) OVER () lcb, IFNULL(c1, b1) ifn_cb FROM t;
and observe:
CURRENT_TEST: main.lead_lag
--- /home/mysql_src/git/dbg/mysql-test/r/lead_lag.result 2017-01-17 12:19:03.723883976 +0300
+++ /home/mysql_src/git/dbg/build_debug/mysql-test/var/log/lead_lag.reject 2017-01-20 11:11:36.343378026 +0300
@@ -353,15 +353,15 @@
Note 1265 Data truncated for column 'e2' at row 6
Note 1265 Data truncated for column 'e2' at row 7
Note 1265 Data truncated for column 'e1' at row 8
-SELECT LEAD(c1, 100, j1) OVER () lcj, IFNULL(c1, j1) ifn_cj FROM t;
+SELECT LEAD(j1, 100, j1) OVER () lcj, IFNULL(c1, j1) ifn_cj FROM t;
lcj ifn_cj
-[6] [6]
-NULL 7
-[8] 8
-[9] 9
-[0] 0
-[1] 1
-[2] 2
+[6] 3 <<<<<<<<<< Value of IFNULL changed
+NULL 3 <<<<<<<<<<
+[8] 3 <<<<<<<<<<
+[9] 3
+[0] 3
+[1] 3
+[2] 3
[3] 3
SELECT LEAD(c1, 100, g1) OVER () lcg, IFNULL(c1, g1) ifn_cg FROM t;
lcg ifn_cg
How to repeat:
see above.
Description: These symptoms probably covers several different causes. I'm using bbffe0dd6dc37bc6314f096303c9bf4580375c22 . If you patch like this: diff --git a/mysql-test/t/window_functions.test b/mysql-test/t/window_functions.test index 192aab8..7eb901a 100644 --- a/mysql-test/t/window_functions.test +++ b/mysql-test/t/window_functions.test @@ -180,12 +180,13 @@ EXECUTE p; # The second execute used to fail DROP PREPARE p; --echo Tests with ROLLUP -SELECT k, MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t +SELECT MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t GROUP BY (k) WITH ROLLUP; + --error ER_WRONG_USAGE SELECT k, MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t GROUP BY (k) WITH ROLLUP ORDER BY wf DESC; -SELECT k, MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t +SELECT MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t GROUP BY k,j WITH ROLLUP; #This gives correct results, but test is not stable because we cannot give #order by at the end @@ -1684,7 +1685,7 @@ SELECT AVG('e') OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW); #Bug-25369943 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (1),(2); -SELECT i FROM t1 WHERE i IN ( SELECT SUM(i) OVER (ROWS CURRENT ROW) FROM t1); +SELECT i FROM t1 WHERE i IN ( SELECT 0+SUM(i) OVER (ROWS CURRENT ROW) FROM t1); #Bug-25365972 --error ER_NOT_SUPPORTED_YET SELECT FIRST_VALUE(i) IGNORE NULLS OVER () FROM t1; The results become wrong: CURRENT_TEST: main.window_functions --- /home/mysql_src/git/dbg/mysql-test/r/window_functions.result 2017-01-18 12:42:21.405911923 +0300 +++ /home/mysql_src/git/dbg/build_debug/mysql-test/var/log/window_functions.reject 2017-01-19 16:15:22.119113054 +0300 @@ -358,37 +358,37 @@ NULL 10.5000 10.5000 1 DROP PREPARE p; Tests with ROLLUP -SELECT k, MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t +SELECT MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t GROUP BY (k) WITH ROLLUP; -k MIN(i) SUM(j) wf -1 1 11 1 -2 1 11 3 -3 1 11 6 -4 1 11 10 -NULL 1 44 10 +MIN(i) SUM(j) wf +1 11 1 +1 11 3 +1 11 6 +1 11 10 +1 44 14 <<<<<<<<<<<<< 10 became 14 SELECT k, MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t GROUP BY (k) WITH ROLLUP ORDER BY wf DESC; ERROR HY000: Incorrect usage of CUBE/ROLLUP and ORDER BY -SELECT k, MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t +SELECT MIN(i), SUM(j), SUM(k) OVER (ROWS UNBOUNDED PRECEDING) wf FROM t GROUP BY k,j WITH ROLLUP; -k MIN(i) SUM(j) wf -1 1 1 1 -1 1 2 2 -1 1 8 3 -1 1 11 4 -2 1 1 6 -2 1 2 8 -2 1 8 10 -2 1 11 12 -3 1 1 15 -3 1 2 18 -3 1 8 21 -3 1 11 24 -4 1 1 28 -4 1 2 32 -4 1 8 36 -4 1 11 40 -NULL 1 44 40 +MIN(i) SUM(j) wf +1 1 1 +1 2 2 +1 8 3 +1 11 4 +1 1 6 +1 2 8 +1 8 10 +1 11 12 +1 1 15 +1 2 18 +1 8 21 +1 11 24 +1 1 28 +1 2 32 +1 8 36 +1 11 40 +1 44 44 <<<<<<<< 40 became 44 SELECT sex, AVG(id), ROW_NUMBER() OVER w FROM t1 GROUP BY sex ASC WITH ROLLUP WINDOW w AS (); sex AVG(id) ROW_NUMBER() OVER w @@ -4964,10 +4964,8 @@ Warning 1292 Truncated incorrect DOUBLE value: 'e' CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (1),(2); -SELECT i FROM t1 WHERE i IN ( SELECT SUM(i) OVER (ROWS CURRENT ROW) FROM t1); +SELECT i FROM t1 WHERE i IN ( SELECT 0+SUM(i) OVER (ROWS CURRENT ROW) FROM t1); i -1 <<<<<<<<<< rows are gone -2 SELECT FIRST_VALUE(i) IGNORE NULLS OVER () FROM t1; ERROR 42000: This version of MySQL doesn't yet support 'IGNORE NULLS' DROP TABLE t1; Another one: patch like this: diff --git a/mysql-test/t/lead_lag.test b/mysql-test/t/lead_lag.test index 3d8cb55..dd59bab 100644 --- a/mysql-test/t/lead_lag.test +++ b/mysql-test/t/lead_lag.test @@ -146,7 +146,7 @@ INSERT INTO t VALUES (null, '[6]', ST_POINTFROMTEXT('POINT(6 6)'), 6, '6', 6.0, ('2', '[2]' , ST_POINTFROMTEXT('POINT(2 2)'), 2, '2', 2.0, null, 20.0/3), ('3', '[3]' , ST_POINTFROMTEXT('POINT(3 3)'), 3, '3', 3.0, 10.0/3, null); -SELECT LEAD(c1, 100, j1) OVER () lcj, IFNULL(c1, j1) ifn_cj FROM t; +SELECT LEAD(j1, 100, j1) OVER () lcj, IFNULL(c1, j1) ifn_cj FROM t; SELECT LEAD(c1, 100, g1) OVER () lcg, IFNULL(c1, g1) ifn_cg FROM t; SELECT LEAD(c1, 100, i1) OVER () lci, IFNULL(c1, i1) ifn_ci FROM t; SELECT LEAD(c1, 100, b1) OVER () lcb, IFNULL(c1, b1) ifn_cb FROM t; and observe: CURRENT_TEST: main.lead_lag --- /home/mysql_src/git/dbg/mysql-test/r/lead_lag.result 2017-01-17 12:19:03.723883976 +0300 +++ /home/mysql_src/git/dbg/build_debug/mysql-test/var/log/lead_lag.reject 2017-01-20 11:11:36.343378026 +0300 @@ -353,15 +353,15 @@ Note 1265 Data truncated for column 'e2' at row 6 Note 1265 Data truncated for column 'e2' at row 7 Note 1265 Data truncated for column 'e1' at row 8 -SELECT LEAD(c1, 100, j1) OVER () lcj, IFNULL(c1, j1) ifn_cj FROM t; +SELECT LEAD(j1, 100, j1) OVER () lcj, IFNULL(c1, j1) ifn_cj FROM t; lcj ifn_cj -[6] [6] -NULL 7 -[8] 8 -[9] 9 -[0] 0 -[1] 1 -[2] 2 +[6] 3 <<<<<<<<<< Value of IFNULL changed +NULL 3 <<<<<<<<<< +[8] 3 <<<<<<<<<< +[9] 3 +[0] 3 +[1] 3 +[2] 3 [3] 3 SELECT LEAD(c1, 100, g1) OVER () lcg, IFNULL(c1, g1) ifn_cg FROM t; lcg ifn_cg How to repeat: see above.