[
https://issues.apache.org/jira/browse/TRAFODION-1220?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Subbiah updated TRAFODION-1220:
--------------------------------------
Fix Version/s: (was: 2.2.0)
> LP Bug: 1450515 - Compilation time for NOT IN predicate *much* longer than
> for IN predicate
> -------------------------------------------------------------------------------------------
>
> Key: TRAFODION-1220
> URL: https://issues.apache.org/jira/browse/TRAFODION-1220
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: Apache Trafodion
> Assignee: Qifan Chen
> Priority: Major
>
> If you have a simple SELECT query with an IN predicate with a list of say 930
> items in the list, the query will compile (on one of the sqws*** machines) in
> approximately 13.5 seconds. If you change the 'IN' to 'NOT IN', the
> compilation time goes to over 50 seconds.
> Furthermore, once the changes get made to allow IN lists and NOT IN lists
> that are say 3000 items long, if you then try the same SELECT query with a
> IN list of 3000 items (unique items), the compilation time is about 18-20
> seconds. However, if you then change the query to specify NOT IN, the
> compilation time goes to 15-20 *minutes*. NOTE: Currently, IN lists are
> limited to about 2230 unique items and NOT IN lists are limited to about 930
> unique items (beyond those lengths you get stack overflow.) Once a fix to
> LP 1323826 gets checked into the source base, you should be able to have IN
> lists or NOT IN lists with as many as 3000 unique items.
> Example SELECT query (with 930 unique items) that exhibits this behavior is
> as follows:
> --create table mytable97 (partnum decimal(4,0), partname char(18));
> prepare s1 from select * from mytable97 where partname in (
> 'A10','B10','C10','D10','E10','F10','G10','H10','I10','J10'
> ,'K10','L10','M10','N10','O10','P10','Q10','R10','S10','T10'
> ,'AA10','AB10','AC10','AD10','AE10','AF10','AG10','AH10','AI10','AJ10'
> ,'BA10','BB10','BC10','BD10','BE10','BF10','BG10','BH10','BI10','BJ10'
> ,'CA10','CB10','CC10','CD10','CE10','CF10','CG10','CH10','CI10','CJ10'
> ,'DA10','DB10','DC10','DD10','DE10','DF10','DG10','DH10','DI10','DJ10'
> ,'EA10','EB10','EC10','ED10','EE10','EF10','EG10','EH10','EI10','EJ10'
> ,'FA10','FB10','FC10','FD10','FE10','FF10','FG10','FH10','FI10','FJ10'
> ,'GA10','GB10','GC10','GD10','GE10','GF10','GG10','GH10','GI10','GJ10'
> ,'HA10','HB10','HC10','HD10','HE10','HF10','HG10','HH10','HI10','HJ10'
> ,'A11','B11','C11','D11','E11','F11','G11','H11','I11','J11'
> ,'K11','L11','M11','N11','O11','P11','Q11','R11','S11','T11'
> ,'AA11','AB11','AC11','AD11','AE11','AF11','AG11','AH11','AI11','AJ11'
> ,'BA11','BB11','BC11','BD11','BE11','BF11','BG11','BH11','BI11','BJ11'
> ,'CA11','CB11','CC11','CD11','CE11','CF11','CG11','CH11','CI11','CJ11'
> ,'DA11','DB11','DC11','DD11','DE11','DF11','DG11','DH11','DI11','DJ11'
> ,'EA11','EB11','EC11','ED11','EE11','EF11','EG11','EH11','EI11','EJ11'
> ,'FA11','FB11','FC11','FD11','FE11','FF11','FG11','FH11','FI11','FJ11'
> ,'GA11','GB11','GC11','GD11','GE11','GF11','GG11','GH11','GI11','GJ11'
> ,'HA11','HB11','HC11','HD11','HE11','HF11','HG11','HH11','HI11','HJ11'
> ,'A12','B12','C12','D12','E12','F12','G12','H12','I12','J12'
> ,'K12','L12','M12','N12','O12','P12','Q12','R12','S12','T12'
> ,'AA12','AB12','AC12','AD12','AE12','AF12','AG12','AH12','AI12','AJ12'
> ,'BA12','BB12','BC12','BD12','BE12','BF12','BG12','BH12','BI12','BJ12'
> ,'CA12','CB12','CC12','CD12','CE12','CF12','CG12','CH12','CI12','CJ12'
> ,'DA12','DB12','DC12','DD12','DE12','DF12','DG12','DH12','DI12','DJ12'
> ,'EA12','EB12','EC12','ED12','EE12','EF12','EG12','EH12','EI12','EJ12'
> ,'FA12','FB12','FC12','FD12','FE12','FF12','FG12','FH12','FI12','FJ12'
> ,'GA12','GB12','GC12','GD12','GE12','GF12','GG12','GH12','GI12','GJ12'
> ,'HA12','HB12','HC12','HD12','HE12','HF12','HG12','HH12','HI12','HJ12'
> ,'A13','B13','C13','D13','E13','F13','G13','H13','I13','J13'
> ,'K13','L13','M13','N13','O13','P13','Q13','R13','S13','T13'
> ,'AA13','AB13','AC13','AD13','AE13','AF13','AG13','AH13','AI13','AJ13'
> ,'BA13','BB13','BC13','BD13','BE13','BF13','BG13','BH13','BI13','BJ13'
> ,'CA13','CB13','CC13','CD13','CE13','CF13','CG13','CH13','CI13','CJ13'
> ,'DA13','DB13','DC13','DD13','DE13','DF13','DG13','DH13','DI13','DJ13'
> ,'EA13','EB13','EC13','ED13','EE13','EF13','EG13','EH13','EI13','EJ13'
> ,'FA13','FB13','FC13','FD13','FE13','FF13','FG13','FH13','FI13','FJ13'
> ,'GA13','GB13','GC13','GD13','GE13','GF13','GG13','GH13','GI13','GJ13'
> ,'HA13','HB13','HC13','HD13','HE13','HF13','HG13','HH13','HI13','HJ13'
> ,'A14','B14','C14','D14','E14','F14','G14','H14','I14','J14'
> ,'K14','L14','M14','N14','O14','P14','Q14','R14','S14','T14'
> ,'AA14','AB14','AC14','AD14','AE14','AF14','AG14','AH14','AI14','AJ14'
> ,'BA14','BB14','BC14','BD14','BE14','BF14','BG14','BH14','BI14','BJ14'
> ,'CA14','CB14','CC14','CD14','CE14','CF14','CG14','CH14','CI14','CJ14'
> ,'DA14','DB14','DC14','DD14','DE14','DF14','DG14','DH14','DI14','DJ14'
> ,'EA14','EB14','EC14','ED14','EE14','EF14','EG14','EH14','EI14','EJ14'
> ,'FA14','FB14','FC14','FD14','FE14','FF14','FG14','FH14','FI14','FJ14'
> ,'GA14','GB14','GC14','GD14','GE14','GF14','GG14','GH14','GI14','GJ14'
> ,'HA14','HB14','HC14','HD14','HE14','HF14','HG14','HH14','HI14','HJ14'
> ,'A15','B15','C15','D15','E15','F15','G15','H15','I15','J15'
> ,'K15','L15','M15','N15','O15','P15','Q15','R15','S15','T15'
> ,'AA15','AB15','AC15','AD15','AE15','AF15','AG15','AH15','AI15','AJ15'
> ,'BA15','BB15','BC15','BD15','BE15','BF15','BG15','BH15','BI15','BJ15'
> ,'CA15','CB15','CC15','CD15','CE15','CF15','CG15','CH15','CI15','CJ15'
> ,'DA15','DB15','DC15','DD15','DE15','DF15','DG15','DH15','DI15','DJ15'
> ,'EA15','EB15','EC15','ED15','EE15','EF15','EG15','EH15','EI15','EJ15'
> ,'FA15','FB15','FC15','FD15','FE15','FF15','FG15','FH15','FI15','FJ15'
> ,'GA15','GB15','GC15','GD15','GE15','GF15','GG15','GH15','GI15','GJ15'
> ,'HA15','HB15','HC15','HD15','HE15','HF15','HG15','HH15','HI15','HJ15'
> ,'A16','B16','C16','D16','E16','F16','G16','H16','I16','J16'
> ,'K16','L16','M16','N16','O16','P16','Q16','R16','S16','T16'
> ,'AA16','AB16','AC16','AD16','AE16','AF16','AG16','AH16','AI16','AJ16'
> ,'BA16','BB16','BC16','BD16','BE16','BF16','BG16','BH16','BI16','BJ16'
> ,'CA16','CB16','CC16','CD16','CE16','CF16','CG16','CH16','CI16','CJ16'
> ,'DA16','DB16','DC16','DD16','DE16','DF16','DG16','DH16','DI16','DJ16'
> ,'EA16','EB16','EC16','ED16','EE16','EF16','EG16','EH16','EI16','EJ16'
> ,'FA16','FB16','FC16','FD16','FE16','FF16','FG16','FH16','FI16','FJ16'
> ,'GA16','GB16','GC16','GD16','GE16','GF16','GG16','GH16','GI16','GJ16'
> ,'HA16','HB16','HC16','HD16','HE16','HF16','HG16','HH16','HI16','HJ16'
> ,'A17','B17','C17','D17','E17','F17','G17','H17','I17','J17'
> ,'K17','L17','M17','N17','O17','P17','Q17','R17','S17','T17'
> ,'AA17','AB17','AC17','AD17','AE17','AF17','AG17','AH17','AI17','AJ17'
> ,'BA17','BB17','BC17','BD17','BE17','BF17','BG17','BH17','BI17','BJ17'
> ,'CA17','CB17','CC17','CD17','CE17','CF17','CG17','CH17','CI17','CJ17'
> ,'DA17','DB17','DC17','DD17','DE17','DF17','DG17','DH17','DI17','DJ17'
> ,'EA17','EB17','EC17','ED17','EE17','EF17','EG17','EH17','EI17','EJ17'
> ,'FA17','FB17','FC17','FD17','FE17','FF17','FG17','FH17','FI17','FJ17'
> ,'GA17','GB17','GC17','GD17','GE17','GF17','GG17','GH17','GI17','GJ17'
> ,'HA17','HB17','HC17','HD17','HE17','HF17','HG17','HH17','HI17','HJ17'
> ,'A18','B18','C18','D18','E18','F18','G18','H18','I18','J18'
> ,'K18','L18','M18','N18','O18','P18','Q18','R18','S18','T18'
> ,'AA18','AB18','AC18','AD18','AE18','AF18','AG18','AH18','AI18','AJ18'
> ,'BA18','BB18','BC18','BD18','BE18','BF18','BG18','BH18','BI18','BJ18'
> ,'CA18','CB18','CC18','CD18','CE18','CF18','CG18','CH18','CI18','CJ18'
> ,'DA18','DB18','DC18','DD18','DE18','DF18','DG18','DH18','DI18','DJ18'
> ,'EA18','EB18','EC18','ED18','EE18','EF18','EG18','EH18','EI18','EJ18'
> ,'FA18','FB18','FC18','FD18','FE18','FF18','FG18','FH18','FI18','FJ18'
> ,'GA18','GB18','GC18','GD18','GE18','GF18','GG18','GH18','GI18','GJ18'
> ,'HA18','HB18','HC18','HD18','HE18','HF18','HG18','HH18','HI18','HJ18'
> ,'A19','B19','C19','D19','E19','F19','G19','H19','I19','J19'
> ,'K19','L19','M19','N19','O19','P19','Q19','R19','S19','T19'
> ,'AA19','AB19','AC19','AD19','AE19','AF19','AG19','AH19','AI19','AJ19'
> ) ;
> Assigned to LaunchPad User James Capps
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
