[MDEV-27869] Conversion of Big IN Predicates Into Subqueries returns wrong result Created: 2022-02-17  Updated: 2022-04-08  Resolved: 2022-04-08

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Prepared Statements
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Mario Fritschen Assignee: Unassigned
Resolution: Duplicate Votes: 1
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-27937 Assertion failure when executing prep... Closed
Problem/Incident
is caused by MDEV-23182 Server crashes in Item::fix_fields_if... Closed

 Description   

As we updated to MariaDB Version 10.6.7 we encounter a problems with `WHERE IN (?)` statements starting at 1000 parameters return zero rows (default value for in_predicate_conversion_threshold).

After some research it seems to be caused by https://github.com/MariaDB/server/commit/a65d01a4cf04c744f9355bec186430540ce6ba08

My suspect here that MDEV-23182 triggers another bug in `Item_func_in::in_predicate_to_in_subs_transformer`:
https://github.com/MariaDB/server/blob/a65d01a4cf04c744f9355bec186430540ce6ba08/sql/sql_tvc.cc#L962

As the function description of `create_value_list_for_tvc` implies it tries to convert but return true if not successful.
So `in_predicate_to_in_subs_transformer` should not treat that as an error, instead return `this` because conversion failed and let the underlaying function execute the non converted query.

 Comments   
Comment by Elena Stepanova [ 2022-03-07 ]

Thanks for the report. The issue has been confirmed in the scope of MDEV-27937. While MDEV-27937 was created later and hence technically MDEV-27869 cannot be a duplicate of it, MDEV-27937 had a ready-to-run test case, that's why it was picked up earlier. Let's keep tracking the problem there.

MarioF, I can either close this report now, or we can keep it open until MDEV-27937 gets a fix which can be tried on your side – which do you prefer?

Meanwhile, as you probably figured from your research already, you can use higher versions of in_predicate_conversion_threshold as a workaround.

Comment by Mario Fritschen [ 2022-03-08 ]

You can close this ticket i will watch MDEV-27937

Generated at Thu Feb 08 09:56:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.