[MDEV-3633] LP:923246 - Loosescan reports different result than other semijoin methods Created: 2012-01-29  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Igor Babaev Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug923246.xml    

 Description   

Bug #49845 from bugs.mysql.com still can be reproduced in mariadb-5.3:

MariaDB [test]> set optimizer_switch='materialization=off';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
-----------------------------------------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

-----------------------------------------------------------------------------------------------------------------+

1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; LooseScan
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1)
1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)

-----------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [test]> set optimizer_switch='mrr=on';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
-----------------------------------------------------------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

-----------------------------------------------------------------------------------------------------------------------------------+

1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Rowid-ordered scan; LooseScan
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1)
1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join)

-----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [test]> select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
------

a

------

0
0
1
1
2
2
3
3
4
4
5
5
6
6
7
7
8
8
9
9
10
10
11
11
12
12
13
13
14
14
15
15
16
16
17
17
18
18
19
19

------
40 rows in set (0.01 sec)

MariaDB [test]> set optimizer_switch='loosescan=off';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
------

a

------

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

------
20 rows in set (0.00 sec)

 Comments   
Comment by Sergei Petrunia [ 2012-01-30 ]

Re: Loosescan reports different result than other semijoin methods
The cause of the bug seems to be the same as with the MySQL bug: with mrr=on, the optimizer picks the query plan with "Using index condition; Using where; Rowid-ordered scan; LooseScan". This is incorrect, because LooseScan requires records to be produced in key order, while "Rowid-ordered scan" doesnt not guarantee any ordering.

Comment by Rasmus Johansson (Inactive) [ 2012-02-20 ]

Launchpad bug id: 923246

Generated at Thu Feb 08 06:50:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.