Bug #115482 Test main.window_jsonaggs failing

• Submitted: 2 Jul 4:32
• Modified: 3 Jul 6:27
• Reporter: Laurynas Biveinis (OCA)
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.38, 8.4.1, 9.0.0
• OS: MacOS (14.5)
• CPU Architecture: ARM

[2 Jul 4:32] Laurynas Biveinis

Description:
[ 50%] main.window_jsonaggs                      [ fail ]
        Test ended at 2024-07-02 07:31:37

CURRENT_TEST: main.window_jsonaggs
--- /Users/laurynas/vilniusdb/mysql-8.0.38/mysql-test/r/window_jsonaggs.result	2024-07-01 21:20:34
+++ /Users/laurynas/vilniusdb/mysql-8.0.38/_build-debug/mysql-test/var/log/window_jsonaggs.reject	2024-07-02 07:31:37
@@ -163,19 +163,19 @@
 OVER w jarray_f FROM t1 GROUP BY i,f WITH ROLLUP HAVING GROUPING(f)=1
 WINDOW w AS (ORDER BY f);
 i	f	jarray_i	jarray_f
-1	NULL	[null, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
-10	NULL	[null, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
-11	NULL	[null, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
-13	NULL	[null, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
-15	NULL	[null, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
-2	NULL	[null, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
-3	NULL	[null, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
-4	NULL	[null, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
-5	NULL	[null, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
-8	NULL	[null, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
-9	NULL	[null, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
-NULL	NULL	[null, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
-NULL	NULL	[null, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
+1	NULL	[8, 15, null, 13, 1, 11, 2, 10, 3, 9, 4, 5, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
+10	NULL	[8, 15, null, 13, 1, 11, 2, 10, 3, 9, 4, 5, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
+11	NULL	[8, 15, null, 13, 1, 11, 2, 10, 3, 9, 4, 5, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
+13	NULL	[8, 15, null, 13, 1, 11, 2, 10, 3, 9, 4, 5, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
+15	NULL	[8, 15, null, 13, 1, 11, 2, 10, 3, 9, 4, 5, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
+2	NULL	[8, 15, null, 13, 1, 11, 2, 10, 3, 9, 4, 5, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
+3	NULL	[8, 15, null, 13, 1, 11, 2, 10, 3, 9, 4, 5, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
+4	NULL	[8, 15, null, 13, 1, 11, 2, 10, 3, 9, 4, 5, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
+5	NULL	[8, 15, null, 13, 1, 11, 2, 10, 3, 9, 4, 5, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
+8	NULL	[8, 15, null, 13, 1, 11, 2, 10, 3, 9, 4, 5, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
+9	NULL	[8, 15, null, 13, 1, 11, 2, 10, 3, 9, 4, 5, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
+NULL	NULL	[8, 15, null, 13, 1, 11, 2, 10, 3, 9, 4, 5, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
+NULL	NULL	[8, 15, null, 13, 1, 11, 2, 10, 3, 9, 4, 5, null]	[null, null, null, null, null, null, null, null, null, null, null, null, null]
 # With other window functions
 SELECT i, f, RANK() OVER w rank1, NTH_VALUE(i,3)
 OVER w nth, LEAD(f, 2) OVER w lead1, LAG(f,2) OVER w lag1, JSON_ARRAYAGG(i)

mysqltest: Result content mismatch

The result from queries just before the failure was:
('a', 4), ('a', 5), ('b', 6);
SELECT JSON_OBJECTAGG(`key`, i) OVER w AS agg
FROM t WINDOW w AS (ORDER BY `key` ROWS CURRENT ROW);
agg
{"a": 4}
{"a": 5}
{"b": 6}
{"e": 1}
{"e": 2}
{"f": 3}
SELECT JSON_OBJECTAGG(`key`, i) OVER w AS agg
FROM t WINDOW w AS (ORDER BY i ROWS CURRENT ROW);
agg
{"e": 1}
{"e": 2}
{"f": 3}
{"a": 4}
{"a": 5}
{"b": 6}
DROP TABLE t;
safe_process[33686]: Child process: 33687, exit: 1

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/

[2 Jul 8:11] MySQL Verification Team

Hello Laurynas,

Thank you for the report and feedback!

regards,
Umesh

[2 Jul 13:43] Laurynas Biveinis

Same on 8.4.1

[2 Jul 14:25] Dag Wanvik

Posted by developer:
 
Similar issue to Bug#36746818, wil be fixed by patch for that issue.

[3 Jul 6:27] Laurynas Biveinis

Same on 9.0.0

[3 Jul 11:47] Dag Wanvik

Posted by developer:
 
Fixed by the patch for 
Internal: Bug#36746818 Sorting (PARTITION BY, ORDER BY) no longer stable for window functions: MacOS
External: https://bugs.mysql.com/bug.php?id=115373