MySQL Bugs: #111562: nth_value window function assertion error

Bug #111562	nth_value window function assertion error
Submitted:	26 Jun 2023 11:15	Modified:	14 Nov 2023 22:40
Reporter:	Pedro Ferreira	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S6 (Debug Builds)
Version:	8.0.33	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	x86 (x86_64)
Tags:	nth_value

Description:
Run the following queries:

CREATE TABLE t0 (c1 TINYINT UNSIGNED, c5 BIT);
INSERT INTO `t0` VALUES (255,NULL),(255,NULL),(0,NULL),(0,NULL),(0,NULL),(0,NULL),(0,NULL),(12,_binary '\0'),(206,_binary '\0'),(135,_binary '\0'),(193,_binary '\0'),(244,_binary '\0');
SELECT nth_value(t0.c1, 97) OVER (ORDER BY t0.c1 RANGE BETWEEN 99 FOLLOWING AND 51 FOLLOWING) FROM t0;

The SELECT will trigger an assertion error at sql/iterators/window_iterators.cc:329
assert(w->frame_buffer()->s->db_type()->db_type == DB_TYPE_INNODB || cnt <= 1 || (w->last_row_output() == 0 && w->frame()->m_from->m_border_type == WBT_VALUE_FOLLOWING) || for_nth_value);

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the queries above.

Hello Pedro Ferreira,

Thank you for the report and feedback.
Observed that 8.0.33 debug build is affected.

regards,
Umesh

Posted by developer:
 
Fixed as of the upcoming MySQL 8.3.0 release, and here's the proposed changelog entry from the documentation team:

In debug builds, using the NTH_ VALUE() window function with an empty
logical range produced an assertion error.

Thank you for the bug report.

This is flagged as fixed in 8.3.0, but also as affecting 8.0.33.

Will this be fixed in a future 8.0 release (I tested on 8.0.36 debug build and it crashes).