[MDEV-30408] Spider test using semijoin=off returns wrong result (zero rows) - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4
Fix Version/s: 10.5.26, 10.6.19, 10.11.9, 11.1.6, 11.2.5, 11.4.3
Component/s: Optimizer, Storage Engine - Spider
Labels:
- spider-gbh

Description

I only tested this on 10.11. The bug could also be in earlier versions.

Applying this diff:

diff --git a/storage/spider/mysql-test/spider/bg/t/basic_sql.test b/storage/spider/mysql-test/spider/bg/t/basic_sql.test

index 9cf4ce99dc3..6a35fe8dd70 100644

--- a/storage/spider/mysql-test/spider/bg/t/basic_sql.test

+++ b/storage/spider/mysql-test/spider/bg/t/basic_sql.test

@@ -156,6 +156,7 @@ if ($USE_CHILD_GROUP2)

 --connection master_1

+set @@optimizer_switch="semijoin=off";

 SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a;

 --echo

Causes the following result change:
mtr spider/bg.basic_sql

+++ /home/my/maria-10.11/storage/spider/mysql-test/spider/bg/r/basic_sql.reject2023-01-13 20:10:47.205034143 +0200

@@ -49,6 +49,7 @@

 ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1

 SELECT a, b, c FROM tb_l

 connection master_1;

+set @@optimizer_switch="semijoin=off";

 SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a;

 a      b       date_format(c, '%Y-%m-%d %H:%i:%s')

 1      a       2008-08-01 10:21:39

@@ -279,11 +280,6 @@

 SELECT a.a, a.b, date_format(a.c, '%Y-%m-%d %H:%i:%s') FROM tb_l a WHERE

 EXISTS (SELECT * FROM ta_l b WHERE b.b = a.b) ORDER BY a.a;

 a      b       date_format(a.c, '%Y-%m-%d %H:%i:%s')

-1      f       2008-07-01 10:21:39

-2      g       2000-02-01 00:00:00

-3      j       2007-05-04 20:03:11

-4      i       2003-10-30 05:01:03

-5      h       2001-10-31 23:59:59

select using pushdown

Attachments

Activity

Ascending order - Click to sort in descending order

Michael Widenius created issue - 2023-01-13 18:11

Yuchen Pei made changes - 2023-01-15 22:26

Field	Original Value	New Value
Assignee		Yuchen Pei [ JIRAUSER52627 ]

Sergei Golubchik made changes - 2023-02-13 20:19

Fix Version/s

10.11 [ 27614 ]

Sergei Golubchik made changes - 2023-02-13 20:19

Description

I only tested this on 10.11. The bug could also be in earlier versions.

Applying this diff:

diff --git a/storage/spider/mysql-test/spider/bg/t/basic_sql.test b/storage/spider/mysql-test/spider/bg/t/basic_sql.test
index 9cf4ce99dc3..6a35fe8dd70 100644
--- a/storage/spider/mysql-test/spider/bg/t/basic_sql.test
+++ b/storage/spider/mysql-test/spider/bg/t/basic_sql.test
@@ -156,6 +156,7 @@ if ($USE_CHILD_GROUP2)
}
}
--connection master_1
+set @@optimizer_switch="semijoin=off";
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a;

--echo

Causes the following result change:
mtr spider/bg.basic_sql

+++ /home/my/maria-10.11/storage/spider/mysql-test/spider/bg/r/basic_sql.reject2023-01-13 20:10:47.205034143 +0200
@@ -49,6 +49,7 @@
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1
SELECT a, b, c FROM tb_l
connection master_1;
+set @@optimizer_switch="semijoin=off";
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a;
a b date_format(c, '%Y-%m-%d %H:%i:%s')
1 a 2008-08-01 10:21:39
@@ -279,11 +280,6 @@
SELECT a.a, a.b, date_format(a.c, '%Y-%m-%d %H:%i:%s') FROM tb_l a WHERE
EXISTS (SELECT * FROM ta_l b WHERE b.b = a.b) ORDER BY a.a;
a b date_format(a.c, '%Y-%m-%d %H:%i:%s')
-1 f 2008-07-01 10:21:39
-2 g 2000-02-01 00:00:00
-3 j 2007-05-04 20:03:11
-4 i 2003-10-30 05:01:03
-5 h 2001-10-31 23:59:59

select using pushdown

I only tested this on 10.11. The bug could also be in earlier versions.

Applying this diff:
{code:diff}
diff --git a/storage/spider/mysql-test/spider/bg/t/basic_sql.test b/storage/spider/mysql-test/spider/bg/t/basic_sql.test
index 9cf4ce99dc3..6a35fe8dd70 100644
--- a/storage/spider/mysql-test/spider/bg/t/basic_sql.test
+++ b/storage/spider/mysql-test/spider/bg/t/basic_sql.test
@@ -156,6 +156,7 @@ if ($USE_CHILD_GROUP2)
}
}
--connection master_1
+set @@optimizer_switch="semijoin=off";
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a;

--echo
{code}
Causes the following result change:
mtr spider/bg.basic_sql
{code:diff}
+++ /home/my/maria-10.11/storage/spider/mysql-test/spider/bg/r/basic_sql.reject2023-01-13 20:10:47.205034143 +0200
@@ -49,6 +49,7 @@
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1
SELECT a, b, c FROM tb_l
connection master_1;
+set @@optimizer_switch="semijoin=off";
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a;
a b date_format(c, '%Y-%m-%d %H:%i:%s')
1 a 2008-08-01 10:21:39
@@ -279,11 +280,6 @@
SELECT a.a, a.b, date_format(a.c, '%Y-%m-%d %H:%i:%s') FROM tb_l a WHERE
EXISTS (SELECT * FROM ta_l b WHERE b.b = a.b) ORDER BY a.a;
a b date_format(a.c, '%Y-%m-%d %H:%i:%s')
-1 f 2008-07-01 10:21:39
-2 g 2000-02-01 00:00:00
-3 j 2007-05-04 20:03:11
-4 i 2003-10-30 05:01:03
-5 h 2001-10-31 23:59:59
{code}
select using pushdown

Roel Van de Paar made changes - 2023-12-04 23:45

Status

Open [ 1 ]

Confirmed [ 10101 ]

Roel Van de Paar made changes - 2023-12-04 23:45

Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		11.0 [ 28320 ]
Fix Version/s		11.1 [ 28549 ]
Fix Version/s		11.2 [ 28603 ]
Fix Version/s		11.3 [ 28565 ]
Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]
Affects Version/s		11.0 [ 28320 ]
Affects Version/s		11.1 [ 28549 ]
Affects Version/s		11.2 [ 28603 ]
Affects Version/s		11.3 [ 28565 ]
Affects Version/s		11.4 [ 29301 ]

Yuchen Pei made changes - 2023-12-05 07:08

Labels

spider-gbh

Yuchen Pei added a comment - 2023-12-05 07:13

Somehow with the semijoin=off optimizer switch, it uses the group by
handler (gbh) for the EXISTS subquery. Because such subqueries set
explicit_limit to true (see
Item_exists_subselect::fix_length_and_dec()), the gbh uses the default
limit value 0, which causes the subquery to return empty results.

Either we make gbh use the correct limit 1 in this case, or ban gbh
from being used. The latter requires checking why the optimizer switch
causes gbh to be used for the subquery.

Yuchen Pei added a comment - 2023-12-05 07:13 Somehow with the semijoin=off optimizer switch, it uses the group by handler (gbh) for the EXISTS subquery. Because such subqueries set explicit_limit to true (see Item_exists_subselect::fix_length_and_dec()), the gbh uses the default limit value 0, which causes the subquery to return empty results. Either we make gbh use the correct limit 1 in this case, or ban gbh from being used. The latter requires checking why the optimizer switch causes gbh to be used for the subquery.

Julien Fritsch made changes - 2024-05-03 14:32

Fix Version/s	11.0 [ 28320 ]
Fix Version/s	11.3 [ 28565 ]

Yuchen Pei added a comment - 2024-07-12 08:53 - edited

Hi psergei, ptal this one-liner thanks:

upstream/bb-10.5-mdev-30408 upstream/bb-10.5-ycp 3e93edb6758cd93ef99319173946a6ef7369c741

MDEV-30408 Reset explicit_limit in exists2in

Item_exists_subselect::fix_length_and_dec() sets explicit_limit to 1.

In the exists2in transformation it resets select_limit to NULL. For

consistency we should reset explicity_limit too.

This fixes a bug where spider table returns wrong results for queries

that gets through exists2in transformation when semijoin is off.

Yuchen Pei added a comment - 2024-07-12 08:53 - edited Hi psergei , ptal this one-liner thanks: upstream/bb-10.5-mdev-30408 upstream/bb-10.5-ycp 3e93edb6758cd93ef99319173946a6ef7369c741 MDEV-30408 Reset explicit_limit in exists2in Item_exists_subselect::fix_length_and_dec() sets explicit_limit to 1. In the exists2in transformation it resets select_limit to NULL. For consistency we should reset explicity_limit too. This fixes a bug where spider table returns wrong results for queries that gets through exists2in transformation when semijoin is off.

Yuchen Pei made changes - 2024-07-12 08:53

Assignee	Yuchen Pei [ JIRAUSER52627 ]	Sergei Petrunia [ psergey ]
Status	Confirmed [ 10101 ]	In Review [ 10002 ]

Yuchen Pei made changes - 2024-07-12 08:53

Priority

Major [ 3 ]

Critical [ 2 ]

Yuchen Pei made changes - 2024-07-12 08:53

Fix Version/s

10.4 [ 22408 ]

Sergei Petrunia added a comment - 2024-07-16 09:17

ycp, looks good to me.
A cosmetic comment is that I would write "= false" instead of "= 0" which is what we used to write in the old code.

Sergei Petrunia added a comment - 2024-07-16 09:17 ycp , looks good to me. A cosmetic comment is that I would write "= false" instead of "= 0" which is what we used to write in the old code.

Sergei Petrunia made changes - 2024-07-16 09:17

Assignee	Sergei Petrunia [ psergey ]	Yuchen Pei [ JIRAUSER52627 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Yuchen Pei added a comment - 2024-07-17 01:55

Thanks for the review - updated and pushed 03a350378ad627d509a31a788896067b02f80e53 to 10.5

Yuchen Pei added a comment - 2024-07-17 01:55 Thanks for the review - updated and pushed 03a350378ad627d509a31a788896067b02f80e53 to 10.5

Yuchen Pei made changes - 2024-07-17 01:55

Fix Version/s		10.5.26 [ 29832 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.11 [ 27614 ]
Fix Version/s	11.1 [ 28549 ]
Fix Version/s	11.2 [ 28603 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

JiraAutomate made changes - 2024-07-17 01:55

Fix Version/s		10.6.19 [ 29833 ]
Fix Version/s		10.11.9 [ 29834 ]
Fix Version/s		11.1.6 [ 29835 ]
Fix Version/s		11.2.5 [ 29836 ]
Fix Version/s		11.4.3 [ 29837 ]

People

Assignee:: Yuchen Pei

Reporter:: Michael Widenius

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2023-01-13 18:11

Updated:: 2024-07-25 09:14

Resolved:: 2024-07-17 01:55

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration