| Bug #115481 | Test main.window_functions failing | ||
|---|---|---|---|
| Submitted: | 2 Jul 2024 4:29 | Modified: | 17 Oct 2024 13:19 |
| Reporter: | Laurynas Biveinis (OCA) | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| 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 | |
[2 Jul 2024 8:11]
MySQL Verification Team
Hello Laurynas, Thank you for the report and feedback! regards, Umesh
[2 Jul 2024 13:31]
Dag Wanvik
Posted by developer: Duplicate of Bug#36746818. External bug #115373: https://bugs.mysql.com/bug.php?id=115373
[2 Jul 2024 13:43]
Laurynas Biveinis
Same on 8.4.1 (as I cannot comment on the duplicate bug)
[3 Jul 2024 6:27]
Laurynas Biveinis
Same on 9.0.0
[3 Jul 2024 12:18]
Dag Wanvik
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.
[17 Oct 2024 13:19]
Laurynas Biveinis
No longer reproducing under 8.0.40 / 8.4.3 / 9.1.0
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/