Bug 3005 - When IN is presented with a single element, optimise to =
Summary: When IN is presented with a single element, optimise to =
Status: NEW
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: -- development
Hardware: Other Linux
: Normal enhancement
Assignee: SQL devs
URL:
Keywords: NONEEDTOTEST
Depends on:
Blocks:
 
Reported: 2012-02-14 13:49 CET by Stefan de Konink
Modified: 2016-04-11 11:45 CEST (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Stefan de Konink 2012-02-14 13:49:01 CET
User-Agent:       Mozilla/5.0 (X11; Linux ppc; rv:5.0) Gecko/20100101 Firefox/5.0
Build Identifier: 

In bigger datasets it seems to have a noticeable difference if using IN or using =, for an operation with one item in the IN list. The IN operation could be optimised if the list contains one element.

sql>explain select * from (select 1 as y) as x where y = 1;
+---------------------------------------------------+
| mal                                               |
+===================================================+
| function user.s35_6{autoCommit=true}():void;      |
|     X_3 := sql.single(1:bte);                     |
|     X_4 := algebra.uselect(X_3,1:bte);            |
|     X_5 := algebra.markT(X_4,0@0:oid);            |
|     X_6 := bat.reverse(X_5);                      |
|     X_7 := algebra.leftjoin(X_6,X_3);             |
|     X_8 := sql.resultSet(1,1,X_7);                |
|     sql.rsColumn(X_8,".x","y","tinyint",8,0,X_7); |
|     X_15 := io.stdout();                          |
|     sql.exportResult(X_15,X_8);                   |
| end s35_6;                                        |
+---------------------------------------------------+

sql>explain select * from (select 1 as y) as x where y in (1);
+------------------------------------------------------+
| mal                                                  |
+======================================================+
| function user.s36_6{autoCommit=true}():void;         |
|     X_3 := sql.single(1:bte);                        |
|     X_9 := bat.reverse(X_3);                         |
|     X_4:bat[:oid,:bte]  := bat.new(nil:oid,nil:bte); |
|     X_7 := bat.append(X_4,1:bte,true);               |
|     X_8 := bat.reverse(X_7);                         |
|     X_10 := algebra.semijoin(X_9,X_8);               |
|     X_11 := bat.reverse(X_10);                       |
|     X_12 := algebra.markT(X_11,0@0:oid);             |
|     X_13 := algebra.project(X_12);                   |
|     X_14 := algebra.markT(X_13,0@0:oid);             |
|     X_15 := bat.reverse(X_14);                       |
|     X_16 := algebra.leftjoin(X_15,X_3);              |
|     X_17 := sql.resultSet(1,1,X_16);                 |
|     sql.rsColumn(X_17,".x","y","tinyint",8,0,X_16);  |
|     X_24 := io.stdout();                             |
|     sql.exportResult(X_24,X_17);                     |
| end s36_6;                                           |
+------------------------------------------------------+

Using IN seems to trigger also a completely different plan for more extensive queries. The trivial example again;

sql>explain select * from (select 1 as y) as x where y in (select 1);
+--------------------------------------------------------+
| mal                                                    |
+========================================================+
| function user.s38_6{autoCommit=true}():void;           |
|     X_2 := sql.single(1:bte);                          |
|     X_3 := bat.reverse(X_2);                           |
|     X_4 := algebra.join(X_2,X_3);                      |
|     X_5 := algebra.markT(X_4,0@0:oid);                 |
|     X_6 := algebra.semijoin(X_2,X_5);                  |
|     X_7 := algebra.markT(X_6,0@0:oid);                 |
|     X_8 := bat.reverse(X_7);                           |
|     X_9 := algebra.leftjoin(X_8,X_2);                  |
|     X_10 := sql.resultSet(1,1,X_9);                    |
|     sql.rsColumn(X_10,".x","y":str,"tinyint",8,0,X_9); |
|     X_18 := io.stdout();                               |
|     sql.exportResult(X_18,X_10);                       |
| end s38_6;                                             |
+--------------------------------------------------------+

sql>explain select * from (select 1 as y) as x where y = (select 1);
+---------------------------------------------------+
| mal                                               |
+===================================================+
| function user.s39_6{autoCommit=true}():void;      |
|     X_3 := sql.single(1:bte);                     |
|     X_4 := algebra.uselect(X_3,1:bte);            |
|     X_5 := algebra.markT(X_4,0@0:oid);            |
|     X_6 := bat.reverse(X_5);                      |
|     X_7 := algebra.leftjoin(X_6,X_3);             |
|     X_8 := sql.resultSet(1,1,X_7);                |
|     sql.rsColumn(X_8,".x","y","tinyint",8,0,X_7); |
|     X_15 := io.stdout();                          |
|     sql.exportResult(X_15,X_8);                   |
| end s39_6;                                        |
+---------------------------------------------------+

Reproducible: Always
Comment 1 Fabian Groffen 2012-07-21 19:02:30 CEST
I guess the problem is more likely to be an issue when the argument of IN is a dynamic set.
Comment 2 Ying Zhang cwiconfidential 2012-11-27 15:45:16 CET
No need to add test for feature request