| Bug #111510 | nth_value window function call assertion error | ||
|---|---|---|---|
| Submitted: | 21 Jun 2023 7:57 | Modified: | 2 May 12:48 |
| Reporter: | Pedro Ferreira | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S6 (Debug Builds) |
| Version: | 8.0.33 | OS: | Ubuntu (22.04) |
| Assigned to: | CPU Architecture: | x86 (x86_64) | |
| Tags: | nth_value | ||
[21 Jun 2023 7:57]
Pedro Ferreira
[21 Jun 2023 7:59]
MySQL Verification Team
Hello Pedro Ferreira, Thank you for the report and feedback. Observed that 8.0.33 debug build is affected. regards, Umesh
[21 Jun 2023 8:00]
MySQL Verification Team
-- release build ./mtr bug111510 --nocheck-testcases Logging: ./mtr bug111510 --nocheck-testcases MySQL Version 8.0.33 Checking supported features Using 'all' suites Collecting tests Checking leftover processes Removing old var directory - WARNING: Using the 'mysql-test/var' symlink Creating var directory '/export/home/tmp/ushastry/mysql-8.0.33/mysql-test/var' Installing system database Using parallel: 1 ============================================================================== TEST NAME RESULT TIME (ms) COMMENT ------------------------------------------------------------------------------ CREATE TABLE t0 (c0 BIGINT); INSERT INTO t0(c0) VALUES (1),(2),(3),(4); SELECT nth_value(t0.c0, 1) OVER (PARTITION BY (SELECT 1) ORDER BY rand() RANGE CURRENT ROW) FROM t0; nth_value(t0.c0, 1) OVER (PARTITION BY (SELECT 1) ORDER BY rand() RANGE CURRENT ROW) 2 2 2 2 [ 50%] main.bug111510 [ pass ] 7 [100%] shutdown_report [ pass ] -- debug build - affected ./mtr bug111510 --nocheck-testcases --debug-server Logging: ./mtr bug111510 --nocheck-testcases --debug-server MySQL Version 8.0.33 Checking supported features - Binaries are debug compiled Using 'all' suites Collecting tests Checking leftover processes Removing old var directory - WARNING: Using the 'mysql-test/var' symlink Creating var directory '/export/home/tmp/ushastry/mysql-8.0.33/mysql-test/var' Installing system database Using parallel: 1 ============================================================================== TEST NAME RESULT TIME (ms) COMMENT ------------------------------------------------------------------------------ CREATE TABLE t0 (c0 BIGINT); INSERT INTO t0(c0) VALUES (1),(2),(3),(4); SELECT nth_value(t0.c0, 1) OVER (PARTITION BY (SELECT 1) ORDER BY rand() RANGE CURRENT ROW) FROM t0; [ 50%] main.bug111510 [ fail ] Test ended at 2023-06-21 09:59:07 CURRENT_TEST: main.bug111510 mysqltest: At line 3: Query 'SELECT nth_value(t0.c0, 1) OVER (PARTITION BY (SELECT 1) ORDER BY rand() RANGE CURRENT ROW) FROM t0' failed. ERROR 2013 (HY000): Lost connection to MySQL server during query -bt #0 0x00007f27eaab7aa1 in pthread_kill () from /lib64/libpthread.so.0 #1 0x0000000003f9296e in my_write_core(int) () #2 0x00000000032365f5 in handle_fatal_signal () #3 <signal handler called> #4 0x00007f27e8cf1387 in raise () from /lib64/libc.so.6 #5 0x00007f27e8cf2a78 in abort () from /lib64/libc.so.6 #6 0x00007f27e8cea1a6 in __assert_fail_base () from /lib64/libc.so.6 #7 0x00007f27e8cea252 in __assert_fail () from /lib64/libc.so.6 #8 0x00000000035df9ce in (anonymous namespace)::process_buffered_windowing_record(THD*, Temp_table_param*, bool, bool*) () #9 0x00000000035dfcfb in BufferingWindowIterator::ReadBufferedRow(bool) () #10 0x00000000035dfda5 in BufferingWindowIterator::Read() () #11 0x0000000003190a7b in Query_expression::ExecuteIteratorQuery(THD*) () #12 0x0000000003190b92 in Query_expression::execute(THD*) () #13 0x000000000311a12a in Sql_cmd_dml::execute_inner(THD*) () #14 0x0000000003123873 in Sql_cmd_dml::execute(THD*) () #15 0x00000000030c4964 in mysql_execute_command(THD*, bool) () #16 0x00000000030c5dc9 in dispatch_sql_command(THD*, Parser_state*) () #17 0x00000000030c752b in dispatch_command(THD*, COM_DATA const*, enum_server_command) () #18 0x00000000030c926e in do_command(THD*) () #19 0x0000000003227bb7 in handle_connection () #20 0x0000000004743af9 in pfs_spawn_thread () #21 0x00007f27eaab2ea5 in start_thread () from /lib64/libpthread.so.0 #22 0x00007f27e8db9b2d in clone () from /lib64/libc.so.6
[7 Jul 2023 10:00]
Pedro Ferreira
Today I got this assertion again at another function: CREATE TABLE t0 (c0 INT, c1 INT UNSIGNED); INSERT INTO t0(c0,c1) VALUES(1,2),(3,371); SELECT cume_dist() OVER (PARTITION BY tx.c1 ORDER BY t0.c1 DESC RANGE BETWEEN CURRENT ROW AND 10 FOLLOWING) FROM (SELECT 1) x(x) RIGHT JOIN t0 tx ON tx.c0 = x.x JOIN t0; sql/iterators/window_iterators.cc:140 assert(!row_added && !found_first);
[4 Apr 15:14]
Dag Wanvik
Posted by developer:
The second repro is actually another bug and can be simplified to this:
CREATE TABLE t1(t0_c1 INT UNSIGNED);
INSERT INTO t1 VALUES (2), (371);
SELECT COUNT(*) OVER (ORDER BY t0_c1 DESC
RANGE BETWEEN CURRENT ROW AND 10 FOLLOWING)
FROM t1;
The column type must be unsigned for the assert to happen.
[2 May 12:48]
Jon Stephens
Documented fix as follows in the MySQL 9.4.0 changelog:
It was possible in a window frame specification using RANGE
units to have an ORDER BY expression containing a RAND()
function. This broke an invariant in the RANGE frame
specification, that the values be monotonically ascending or
descending. We solve this by disallowing a nondeterministic
ORDER BY expression when combined with RANGE units; this is now
rejected with an error.
This also fixes a related issue in which RANGE unit comparison
failed when computing the frame limits for BETWEEN CURRENT ROW
AND after_value FOLLOWING where the row value being compared was
unsigned and could be less than after_value, leading to
underflow and a possible incorrect result. In such cases, we now
reject the operation with an error.
Closed.
