MySQL Bugs: #115481: Test main.window

Bug #115481	Test main.window_functions failing
Submitted:	2 Jul 4:29	Modified:	3 Jul 6:27
Reporter:	Laurynas Biveinis (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.38, 8.4.1, 9.0.0	OS:	MacOS (14.5)
Assigned to:		CPU Architecture:	ARM

Description:
[ 50%] main.window_functions                     [ fail ]
        Test ended at 2024-07-02 07:27:51

CURRENT_TEST: main.window_functions
--- /Users/laurynas/vilniusdb/mysql-8.0.38/mysql-test/r/window_functions.result	2024-07-01 21:20:34
+++ /Users/laurynas/vilniusdb/mysql-8.0.38/_build-debug/mysql-test/var/log/window_functions.reject	2024-07-02 07:27:51
@@ -3718,30 +3718,30 @@
 22:30:00	NULL
 SELECT p, t, FIRST_VALUE(t) OVER w FROM t_time2 WINDOW w AS (PARTITION by p );
 p	t	FIRST_VALUE(t) OVER w
-1	00:30:00	12:30:00
-1	00:31:00	12:30:00
-1	01:30:00	12:30:00
-1	04:30:00	12:30:00
-1	06:30:00	12:30:00
-1	12:30:00	12:30:00
-1	13:30:00	12:30:00
-1	15:30:00	12:30:00
-1	18:30:00	12:30:00
-1	20:30:00	12:30:00
-1	21:30:00	12:30:00
-1	22:30:00	12:30:00
-2	00:30:00	12:30:00
-2	00:31:00	12:30:00
-2	01:30:00	12:30:00
-2	04:30:00	12:30:00
-2	06:30:00	12:30:00
-2	12:30:00	12:30:00
-2	13:30:00	12:30:00
-2	15:30:00	12:30:00
-2	18:30:00	12:30:00
-2	20:30:00	12:30:00
-2	21:30:00	12:30:00
-2	22:30:00	12:30:00
+1	00:30:00	00:31:00
+1	00:31:00	00:31:00
+1	01:30:00	00:31:00
+1	04:30:00	00:31:00
+1	06:30:00	00:31:00
+1	12:30:00	00:31:00
+1	13:30:00	00:31:00
+1	15:30:00	00:31:00
+1	18:30:00	00:31:00
+1	20:30:00	00:31:00
+1	21:30:00	00:31:00
+1	22:30:00	00:31:00
+2	00:30:00	22:30:00
+2	00:31:00	22:30:00
+2	01:30:00	22:30:00
+2	04:30:00	22:30:00
+2	06:30:00	22:30:00
+2	12:30:00	22:30:00
+2	13:30:00	22:30:00
+2	15:30:00	22:30:00
+2	18:30:00	22:30:00
+2	20:30:00	22:30:00
+2	21:30:00	22:30:00
+2	22:30:00	22:30:00
 SELECT p, t, FIRST_VALUE(t) OVER w FROM t_time2 WINDOW w AS (PARTITION by p ORDER BY t);
 p	t	FIRST_VALUE(t) OVER w
 1	00:30:00	00:30:00

mysqltest: Result content mismatch

How to repeat:
Server built with -DCMAKE_EXPORT_COMPILE_COMMANDS=ON -DCMAKE_BUILD_TYPE=Debug -DWITH_DEBUG=ON -DMYSQL_MAINTAINER_MODE=ON -DWITH_SYSTEM_LIBS=ON -DWITH_NDBCLUSTER_STORAGE_ENGINE=OFF -DFORCE_COLORED_OUTPUT=ON -DDOWNLOAD_BOOST=ON -DWITH_BOOST=~/vilniusdb/mysql-boost/

Hello Laurynas,

Thank you for the report and feedback!

regards,
Umesh

Posted by developer:
 
Duplicate of Bug#36746818.
External bug #115373: https://bugs.mysql.com/bug.php?id=115373

Same on 8.4.1 (as I cannot comment on the duplicate bug)

Same on 9.0.0

The problem with this query is that is non-deterministic; there is a partition specification, but no ordering. The partitioning performs a non-stable sort on the result set. So, unless a ordering is imposed by an ORDER BY clause in the window specification, the row order within the partition is non-derministic. So for a window function for which row order counts, e.g. FIRST_VALUE, the result is non-deterministic.

The solution is to add sufficient ordering, or remove the test query. In this case the latter was chosen since we have a similar query *with* ordering right after it in the test.