[MDEV-25844] With multiple subqueries outer query block fails to apply constant conditionalization Created: 2021-06-02  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Remy Fox Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None

Attachments: PNG File image-2021-06-02-13-17-24-514.png     PNG File image-2021-06-02-13-18-07-017.png     PNG File image-2021-06-02-13-18-40-591.png    

 Description   

Create a table with some rows:

CREATE TABLE table_rows (id INT UNSIGNED NOT NULL PRIMARY KEY);
 
INSERT INTO table_rows (id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

Here are three queries that are practically equivalent:

1.

SELECT *
 
FROM table_rows
 
WHERE table_rows.id = 10

2.

SELECT *
 
FROM table_rows AS outer_table_rows
 
WHERE
 
	EXISTS(
 
		SELECT * 
		FROM table_rows AS inner_table_rows
 
		WHERE
 
			outer_table_rows.id = inner_table_rows.id
 
			AND
 
			inner_table_rows.id = 10
 
	)

3.

SELECT *
 
FROM table_rows AS outer_outer_table_rows
 
WHERE
 
	EXISTS(
 
		SELECT * 
		FROM table_rows AS outer_table_rows
 
		WHERE
 
			outer_outer_table_rows.id = outer_table_rows.id
 
			AND
 
			EXISTS(
 
				SELECT * 
				FROM table_rows AS inner_table_rows
 
				WHERE
 
					outer_table_rows.id = inner_table_rows.id
 
					AND
 
					inner_table_rows.id = 10
 
			)
 
	)

Here the third query does not use the constant for the index search. I have found that this can have really bad impact on performance. That will not show for this particular test case, but when a few extra joins are involved in the query blocks, then it could quickly make the query take an infinite time.

Although in this test case the performance issue could be solved by just using the first select, that might not always be an option.
Generated at Thu Feb 08 09:40:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.