[MDEV-29477] recursive query silently discards result after an arbitrary (?) threshold Created: 2022-09-06  Updated: 2022-09-09  Resolved: 2022-09-08

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.6.8
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Paul Pogonyshev Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

mysql Ver 15.1 Distrib 10.6.8-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper


 Description   

To reproduce:

drop sequence if exists xxx;
 
create sequence xxx;
 
select count(*) from (with recursive t (x) as (select 1 x union all select x + 1 from t where x < 10000) select next value for xxx from t);

This returns 1001, while 10000 is expected. When replacing 10000 in the 'where' clause with a number not larger than 1001, the result matches expectation.

Usecase: inner query is used to fetch N next numbers from a sequence in one statement. For "smaller" numbers it works without problems.

Displayed behavior is very bad. I suspect there might be some reason and the threshold is configurable somewhere, but the application using the database has no way to know it. Returning too few rows without any errors or even warnings results in a hard-to-understand bug in our application, with practically no way to debug it.

 Comments   
Comment by Sergei Golubchik [ 2022-09-08 ]

https://mariadb.com/kb/en/server-system-variables/#max_recursive_iterations

Comment by Paul Pogonyshev [ 2022-09-09 ]

> used to prevent infinite loops in recursive CTEs

That's a noble goal, presumingly it guards against bugs. However, in this case it's not a bug and I expect it to give correct result even if I specify 1000000. By silently returning something that I don't expect, MariaDB effectively triggers a bug downstream. E.g. Oracle Database also has an arbitrary limit, but it fails hard (with an exception) if it is hit. This is infinitely better than returning a wrong result, because I immediately know that something is wrong instead of letting it fail later in an unpredictable way.

Comment by Sergei Golubchik [ 2022-09-09 ]
  • it's intentional and documented behavior, not a bug.
  • the result is not wrong, it's correct, because the definition of "correctness" includes the limit set by @@max_recursive_iterations. The result would've been incorrect, if it included 100 000 rows, with @@max_recursive_iterations being 10 000.
  • you're free to set @@max_recursive_iterations to any value you want, to 100 000 or even to 1 000 000.
Comment by Paul Pogonyshev [ 2022-09-09 ]

Yeah, just add `max_select_rows`, set it 10000 and document. Then it will not be a bug when you silently return 10000 rows from a `select *` on a million-row table. And not wrong.

Comment by Sergei Golubchik [ 2022-09-09 ]

https://mariadb.com/kb/en/server-system-variables/#sql_select_limit

Comment by Paul Pogonyshev [ 2022-09-09 ]

Perfect. Just strange that is not 1000.

Generated at Thu Feb 08 10:08:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.