You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Rahul Raj <ra...@option3.io> on 2019/02/27 06:44:39 UTC
Query Compilation error with 80+ CASE statements
Hi,
I am getting compilation error on Drill 1.15 when query contains a large
number of case statements. I have included the query below. Query works
fine when few case statements are removed.
org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
CompileException: File
'org.apache.drill.exec.compile.DrillJavaFileObject[ProjectorGen8635.java]',
Line 872, Column 9: ProjectorGen8635.java:872: error: cannot assign a value
to final variable out out = input; ^
(compiler.err.cant.assign.val.to.final.var) Fragment 0:0 Please, refer to
logs for more information. [Error Id: a0d3f054-7c60-4915-9629-55e5dacd8606
on jiffydemo:31010]
Query is :
SELECT
CAST(`A1` AS INT) `A1`
, CAST(`A2` AS INT) `A2`
, `A3`
, `A4`
, `A5`
, (CASE WHEN (`A6` = '') THEN null ELSE `A6` END) `A6`
, `A7`
, `A8`
, `A9`
, `A10`
, CAST(A11 AS INT) `A11`
, (CASE WHEN (`A12` = '') THEN null ELSE `A12` END) `A12`
, CAST(`checkNull`(`A13`) AS INT) `A13`
, CAST(`checkNull`(`A14`) AS INT) `A14`
, (CASE WHEN (`A15` = '') THEN null ELSE `A15` END) `A15`
, CAST(`checkNull`(`A16`) AS INT) `A16`
, CAST(`checkNull`(`A17`) AS INT) `A17`
, CAST(`checkNull`(`A18`) AS INT) `A18`
, (CASE WHEN (`A19` = '') THEN null ELSE `A19` END) `A19`
, `A20`
, `A21`
, `A22`
, (CASE WHEN (`_1` = '') THEN null ELSE `_1` END) `_1`
, (CASE WHEN (`_2` = '') THEN null ELSE `_2` END) `_2`
, (CASE WHEN (`_3` = '') THEN null ELSE `_3` END) `_3`
, (CASE WHEN (`_4` = '') THEN null ELSE `_4` END) `_4`
, (CASE WHEN (`_5` = '') THEN null ELSE `_5` END) `_5`
, (CASE WHEN (`_6` = '') THEN null ELSE `_6` END) `_6`
, (CASE WHEN (`_7` = '') THEN null ELSE `_7` END) `_7`
, (CASE WHEN (`_8` = '') THEN null ELSE `_8` END) `_8`
, (CASE WHEN (`_9` = '') THEN null ELSE `_9` END) `_9`
, (CASE WHEN (`_10` = '') THEN null ELSE `_10` END) `_10`
, (CASE WHEN (`_11` = '') THEN null ELSE `_11` END) `_11`
, (CASE WHEN (`_12` = '') THEN null ELSE `_12` END) `_12`
, (CASE WHEN (`_13` = '') THEN null ELSE `_13` END) `_13`
, (CASE WHEN (`_14` = '') THEN null ELSE `_14` END) `_14`
, (CASE WHEN (`_15` = '') THEN null ELSE `_15` END) `_15`
, (CASE WHEN (`_16` = '') THEN null ELSE `_16` END) `_16`
, (CASE WHEN (`_17` = '') THEN null ELSE `_17` END) `_17`
, (CASE WHEN (`_18` = '') THEN null ELSE `_18` END) `_18`
, (CASE WHEN (`_19` = '') THEN null ELSE `_19` END) `_19`
, (CASE WHEN (`_20` = '') THEN null ELSE `_20` END) `_20`
, (CASE WHEN (`_21` = '') THEN null ELSE `_21` END) `_21`
, (CASE WHEN (`_22` = '') THEN null ELSE `_22` END) `_22`
, (CASE WHEN (`_23` = '') THEN null ELSE `_23` END) `_23`
, (CASE WHEN (`_24` = '') THEN null ELSE `_24` END) `_24`
, (CASE WHEN (`_25` = '') THEN null ELSE `_25` END) `_25`
, (CASE WHEN (`_26` = '') THEN null ELSE `_26` END) `_26`
, (CASE WHEN (`_27` = '') THEN null ELSE `_27` END) `_27`
, (CASE WHEN (`_28` = '') THEN null ELSE `_28` END) `_28`
, (CASE WHEN (`_29` = '') THEN null ELSE `_29` END) `_29`
, (CASE WHEN (`_30` = '') THEN null ELSE `_30` END) `_30`
, (CASE WHEN (`_31` = '') THEN null ELSE `_31` END) `_31`
, (CASE WHEN (`_32` = '') THEN null ELSE `_32` END) `_32`
, (CASE WHEN (`_33` = '') THEN null ELSE `_33` END) `_33`
, (CASE WHEN (`_34` = '') THEN null ELSE `_34` END) `_34`
, (CASE WHEN (`_35` = '') THEN null ELSE `_35` END) `_35`
, (CASE WHEN (`_36` = '') THEN null ELSE `_36` END) `_36`
, (CASE WHEN (`_37` = '') THEN null ELSE `_37` END) `_37`
, (CASE WHEN (`_38` = '') THEN null ELSE `_38` END) `_38`
, (CASE WHEN (`_39` = '') THEN null ELSE `_39` END) `_39`
, (CASE WHEN (`_40` = '') THEN null ELSE `_40` END) `_40`
, (CASE WHEN (`_41` = '') THEN null ELSE `_41` END) `_41`
, (CASE WHEN (`_42` = '') THEN null ELSE `_42` END) `_42`
, (CASE WHEN (`_43` = '') THEN null ELSE `_43` END) `_43`
, (CASE WHEN (`_44` = '') THEN null ELSE `_44` END) `_44`
, (CASE WHEN (`_45` = '') THEN null ELSE `_45` END) `_45`
, (CASE WHEN (`_46` = '') THEN null ELSE `_46` END) `_46`
, (CASE WHEN (`_47` = '') THEN null ELSE `_47` END) `_47`
, (CASE WHEN (`_48` = '') THEN null ELSE `_48` END) `_48`
, (CASE WHEN (`_49` = '') THEN null ELSE `_49` END) `_49`
, (CASE WHEN (`_50` = '') THEN null ELSE `_50` END) `_50`
, (CASE WHEN (`_51` = '') THEN null ELSE `_51` END) `_51`
, (CASE WHEN (`_52` = '') THEN null ELSE `_52` END) `_52`
, (CASE WHEN (`_53` = '') THEN null ELSE `_53` END) `_53`
, (CASE WHEN (`_54` = '') THEN null ELSE `_54` END) `_54`
, (CASE WHEN (`_55` = '') THEN null ELSE `_55` END) `_55`
, (CASE WHEN (`_56` = '') THEN null ELSE `_56` END) `_56`
, (CASE WHEN (`_57` = '') THEN null ELSE `_57` END) `_57`
, (CASE WHEN (`_58` = '') THEN null ELSE `_58` END) `_58`
, (CASE WHEN (`_59` = '') THEN null ELSE `_59` END) `_59`
, (CASE WHEN (`_60` = '') THEN null ELSE `_60` END) `_60`
, (CASE WHEN (`_61` = '') THEN null ELSE `_61` END) `_61`
, (CASE WHEN (`_62` = '') THEN null ELSE `_62` END) `_62`
, (CASE WHEN (`_63` = '') THEN null ELSE `_63` END) `_63`
, (CASE WHEN (`_64` = '') THEN null ELSE `_64` END) `_64`
, (CASE WHEN (`_65` = '') THEN null ELSE `_65` END) `_65`
, (CASE WHEN (`_66` = '') THEN null ELSE `_66` END) `_66`
, (CASE WHEN (`_67` = '') THEN null ELSE `_67` END) `_67`
, (CASE WHEN (`_68` = '') THEN null ELSE `_68` END) `_68`
, (CASE WHEN (`_69` = '') THEN null ELSE `_69` END) `_69`
, (CASE WHEN (`_70` = '') THEN null ELSE `_70` END) `_70`
, (CASE WHEN (`_71` = '') THEN null ELSE `_71` END) `_71`
, (CASE WHEN (`_72` = '') THEN null ELSE `_72` END) `_72`
, (CASE WHEN (`_73` = '') THEN null ELSE `_73` END) `_73`
, (CASE WHEN (`_74` = '') THEN null ELSE `_74` END) `_74`
FROM
( select '0' `A1`, '1' `A2`, '2' `A3`, '3' `A4`, '4' `A5`, '5' `A6`, '6'
`A7`, '7' `A8`, '8' `A9`, '9' `A10`, '10' `A11`, '11' `A12`, '12' `A13`,
'13' `A14`, '14' `A15`, '15' `A16`, '16' `A17`, '17' `A18`, '18' `A19`,
'19' `A20`, '20' `A21`, '21' `A22`, '23' `_1`, '24' `_2`, '25' `_3`, '26'
`_4`, '27' `_5`, '28' `_6`, '29' `_7`, '30' `_8`, '31' `_9`, '32' `_10`,
'33' `_11`, '34' `_12`, '35' `_13`, '36' `_14`, '37' `_15`, '38' `_16`,
'39' `_17`, '40' `_18`, '41' `_19`, '42' `_20`, '43' `_21`, '44' `_22`,
'45' `_23`, '46' `_24`, '47' `_25`, '48' `_26`, '49' `_27`, '50' `_28`,
'51' `_29`, '52' `_30`, '53' `_31`, '54' `_32`, '55' `_33`, '56' `_34`,
'57' `_35`, '58' `_36`, '59' `_37`, '60' `_38`, '61' `_39`, '62' `_40`,
'63' `_41`, '64' `_42`, '65' `_43`, '66' `_44`, '67' `_45`, '68' `_46`,
'69' `_47`, '70' `_48`, '71' `_49`, '72' `_50`, '73' `_51`, '74' `_52`,
'75' `_53`, '76' `_54`, '77' `_55`, '78' `_56`, '79' `_57`, '80' `_58`,
'81' `_59`, '82' `_60`, '83' `_61`, '84' `_62`, '85' `_63`, '86' `_64`,
'87' `_65`, '88' `_66`, '89' `_67`, '90' `_68`, '91' `_69`, '92' `_70`,
'93' `_71`, '94' `_72`, '95' `_73`, '96' `_74`, '97' `_75`, '98' `_76`,
'99' `_77`, '100' `_78`, '101' `_79`, '102' `_80`, '103' `_81`, '104'
`_82`, '105' `_83`, '106' `_84`, '107' `_85`, '108' `_86`, '109' `_87`,
'110' `_88`, '111' `_89`, '112' `_90`, '113' `_91`, '114' `_92`, '115'
`_93`, '116' `_94`, '117' `_95`, '118' `_96`, '119' `_97`, '120' `_98`,
'121' `_99`, '122' `_100`, '123' `_101`, '124' `_102`, '125' `_103`, '126'
`_104`, '127' `_105`, '128' `_106`, '129' `_107`, '130' `_108`, '131'
`_109`, '132' `_110`, '133' `_111`, '134' `_112`, '135' `_113`, '136'
`_114`, '137' `_115`, '138' `_116`, '139' `_117`, '140' `_118`, '141'
`_119`, '142' `_120`, '143' `_121`, '144' `_122`, '145' `_123`, '146'
`_124`, '147' `_125`, '148' `_126`, '149' `_127`, '150' `_128`, '151'
`_129`, '152' `_130`, '153' `_131`, '154' `_132`, '155' `_133`, '156'
`_134`, '157' `_135`, '158' `_136`, '159' `_137`, '160' `_138`, '161'
`_139`, '162' `_140`, '163' `_141`, '164' `_142`, '165' `_143`, '166'
`_144`, '167' `_145`, '168' `_146`, '169' `_147`, '170' `_148`, '171'
`_149`, '172' `_150`, '173' `_151`, '174' `_152`, '175' `_153`, '176'
`_154`, '177' `_155`, '178' `_156`, '179' `_157`, '180' `_158`, '181'
`_159`, '182' `_160`, '183' `_161`, '184' `_162`, '185' `_163`, '186'
`_164`, '187' `_165`, '188' `_166`, '189' `_167`, '190' `_168`, '191'
`_169`, '192' `_170`, '193' `_171`, '194' `_172`, '195' `_173`, '196'
`_174`, '197' `_175`, '198' `_176`, '199' `_177`, '200' `_178`, '201'
`_179`, '202' `_180`, '203' `_181`, '204' `_182`, '205' `_183`, '206'
`_184`, '207' `_185` from (values(1)) )
Regards,
Rahul
--
_*** This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom it is
addressed. If you are not the named addressee then you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately and delete this e-mail from your system.***_
Re: Query Compilation error with 80+ CASE statements
Posted by Rahul Raj <ra...@option3.io>.
Hi Girish,
The plans look almost the same. I have attached them here.
The generated class with error details at the end of the file:
https://raw.githubusercontent.com/rajrahul/files/master/ProjectorGen8680.java
Plan when query fails:
https://raw.githubusercontent.com/rajrahul/files/master/error-plan.txt
Plan without error:
https://raw.githubusercontent.com/rajrahul/files/master/withouterror-plan.txt
@Arjun, the property does not help .
Regards,
Rahul
On Thu, Feb 28, 2019 at 10:47 AM Arjun kr <ar...@outlook.com> wrote:
> Rahul,
>
> You can try setting system option 'exec.java.compiler.exp_in_method_size'
> to lesser value from the default of 50 if you haven't tried already and see
> if it succeeds.
>
>
> alter session set `exec.java.compiler.exp_in_method_size` = <number>;
>
> Thanks,
>
> Arjun
>
> ________________________________
> From: Abhishek Girish <ag...@apache.org>
> Sent: Thursday, February 28, 2019 5:55 AM
> To: user
> Subject: Re: Query Compilation error with 80+ CASE statements
>
> Rahul,
>
> Can you please share plans for both queries (one with fewer which succeeds
> and one which fails). Also the verbose error.
>
> On Tue, Feb 26, 2019 at 11:33 PM Rahul Raj <ra...@option3.io> wrote:
>
> > Some more update to the mail above:
> >
> > The query above has a UDF 'checkNull' used . The UDF code is placed
> inside
> > the compiled query code, causing it to fail where there are more case
> > statements. The below snippet is from the UDF.
> >
> > {
> > if (input.end - input.start == 0) {
> > throw new RuntimeException("IllegalArgumentException : null
> values
> > in non nullable fields");
> > } else
> > {
> > out = input;
> > }
> > }
> >
> > Any thoughts on this? Are there any naming conventions while developing a
> > UDF?
> >
> > Regards,
> > Rahul
> >
> >
> >
> > On Wed, Feb 27, 2019 at 12:14 PM Rahul Raj <ra...@option3.io> wrote:
> >
> > > Hi,
> > >
> > > I am getting compilation error on Drill 1.15 when query contains a
> large
> > > number of case statements. I have included the query below. Query works
> > > fine when few case statements are removed.
> > >
> > > org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
> > > CompileException: File
> > >
> >
> 'org.apache.drill.exec.compile.DrillJavaFileObject[ProjectorGen8635.java]',
> > > Line 872, Column 9: ProjectorGen8635.java:872: error: cannot assign a
> > value
> > > to final variable out out = input; ^
> > > (compiler.err.cant.assign.val.to.final.var) Fragment 0:0 Please, refer
> to
> > > logs for more information. [Error Id:
> > a0d3f054-7c60-4915-9629-55e5dacd8606
> > > on jiffydemo:31010]
> > >
> > > Query is :
> > >
> > > SELECT
> > > CAST(`A1` AS INT) `A1`
> > > , CAST(`A2` AS INT) `A2`
> > > , `A3`
> > > , `A4`
> > > , `A5`
> > > , (CASE WHEN (`A6` = '') THEN null ELSE `A6` END) `A6`
> > > , `A7`
> > > , `A8`
> > > , `A9`
> > > , `A10`
> > > , CAST(A11 AS INT) `A11`
> > > , (CASE WHEN (`A12` = '') THEN null ELSE `A12` END) `A12`
> > > , CAST(`checkNull`(`A13`) AS INT) `A13`
> > > , CAST(`checkNull`(`A14`) AS INT) `A14`
> > > , (CASE WHEN (`A15` = '') THEN null ELSE `A15` END) `A15`
> > > , CAST(`checkNull`(`A16`) AS INT) `A16`
> > > , CAST(`checkNull`(`A17`) AS INT) `A17`
> > > , CAST(`checkNull`(`A18`) AS INT) `A18`
> > > , (CASE WHEN (`A19` = '') THEN null ELSE `A19` END) `A19`
> > > , `A20`
> > > , `A21`
> > > , `A22`
> > > , (CASE WHEN (`_1` = '') THEN null ELSE `_1` END) `_1`
> > > , (CASE WHEN (`_2` = '') THEN null ELSE `_2` END) `_2`
> > > , (CASE WHEN (`_3` = '') THEN null ELSE `_3` END) `_3`
> > > , (CASE WHEN (`_4` = '') THEN null ELSE `_4` END) `_4`
> > > , (CASE WHEN (`_5` = '') THEN null ELSE `_5` END) `_5`
> > > , (CASE WHEN (`_6` = '') THEN null ELSE `_6` END) `_6`
> > > , (CASE WHEN (`_7` = '') THEN null ELSE `_7` END) `_7`
> > > , (CASE WHEN (`_8` = '') THEN null ELSE `_8` END) `_8`
> > > , (CASE WHEN (`_9` = '') THEN null ELSE `_9` END) `_9`
> > > , (CASE WHEN (`_10` = '') THEN null ELSE `_10` END) `_10`
> > > , (CASE WHEN (`_11` = '') THEN null ELSE `_11` END) `_11`
> > > , (CASE WHEN (`_12` = '') THEN null ELSE `_12` END) `_12`
> > > , (CASE WHEN (`_13` = '') THEN null ELSE `_13` END) `_13`
> > > , (CASE WHEN (`_14` = '') THEN null ELSE `_14` END) `_14`
> > > , (CASE WHEN (`_15` = '') THEN null ELSE `_15` END) `_15`
> > > , (CASE WHEN (`_16` = '') THEN null ELSE `_16` END) `_16`
> > > , (CASE WHEN (`_17` = '') THEN null ELSE `_17` END) `_17`
> > > , (CASE WHEN (`_18` = '') THEN null ELSE `_18` END) `_18`
> > > , (CASE WHEN (`_19` = '') THEN null ELSE `_19` END) `_19`
> > > , (CASE WHEN (`_20` = '') THEN null ELSE `_20` END) `_20`
> > > , (CASE WHEN (`_21` = '') THEN null ELSE `_21` END) `_21`
> > > , (CASE WHEN (`_22` = '') THEN null ELSE `_22` END) `_22`
> > > , (CASE WHEN (`_23` = '') THEN null ELSE `_23` END) `_23`
> > > , (CASE WHEN (`_24` = '') THEN null ELSE `_24` END) `_24`
> > > , (CASE WHEN (`_25` = '') THEN null ELSE `_25` END) `_25`
> > > , (CASE WHEN (`_26` = '') THEN null ELSE `_26` END) `_26`
> > > , (CASE WHEN (`_27` = '') THEN null ELSE `_27` END) `_27`
> > > , (CASE WHEN (`_28` = '') THEN null ELSE `_28` END) `_28`
> > > , (CASE WHEN (`_29` = '') THEN null ELSE `_29` END) `_29`
> > > , (CASE WHEN (`_30` = '') THEN null ELSE `_30` END) `_30`
> > > , (CASE WHEN (`_31` = '') THEN null ELSE `_31` END) `_31`
> > > , (CASE WHEN (`_32` = '') THEN null ELSE `_32` END) `_32`
> > > , (CASE WHEN (`_33` = '') THEN null ELSE `_33` END) `_33`
> > > , (CASE WHEN (`_34` = '') THEN null ELSE `_34` END) `_34`
> > > , (CASE WHEN (`_35` = '') THEN null ELSE `_35` END) `_35`
> > > , (CASE WHEN (`_36` = '') THEN null ELSE `_36` END) `_36`
> > > , (CASE WHEN (`_37` = '') THEN null ELSE `_37` END) `_37`
> > > , (CASE WHEN (`_38` = '') THEN null ELSE `_38` END) `_38`
> > > , (CASE WHEN (`_39` = '') THEN null ELSE `_39` END) `_39`
> > > , (CASE WHEN (`_40` = '') THEN null ELSE `_40` END) `_40`
> > > , (CASE WHEN (`_41` = '') THEN null ELSE `_41` END) `_41`
> > > , (CASE WHEN (`_42` = '') THEN null ELSE `_42` END) `_42`
> > > , (CASE WHEN (`_43` = '') THEN null ELSE `_43` END) `_43`
> > > , (CASE WHEN (`_44` = '') THEN null ELSE `_44` END) `_44`
> > > , (CASE WHEN (`_45` = '') THEN null ELSE `_45` END) `_45`
> > > , (CASE WHEN (`_46` = '') THEN null ELSE `_46` END) `_46`
> > > , (CASE WHEN (`_47` = '') THEN null ELSE `_47` END) `_47`
> > > , (CASE WHEN (`_48` = '') THEN null ELSE `_48` END) `_48`
> > > , (CASE WHEN (`_49` = '') THEN null ELSE `_49` END) `_49`
> > > , (CASE WHEN (`_50` = '') THEN null ELSE `_50` END) `_50`
> > > , (CASE WHEN (`_51` = '') THEN null ELSE `_51` END) `_51`
> > > , (CASE WHEN (`_52` = '') THEN null ELSE `_52` END) `_52`
> > > , (CASE WHEN (`_53` = '') THEN null ELSE `_53` END) `_53`
> > > , (CASE WHEN (`_54` = '') THEN null ELSE `_54` END) `_54`
> > > , (CASE WHEN (`_55` = '') THEN null ELSE `_55` END) `_55`
> > > , (CASE WHEN (`_56` = '') THEN null ELSE `_56` END) `_56`
> > > , (CASE WHEN (`_57` = '') THEN null ELSE `_57` END) `_57`
> > > , (CASE WHEN (`_58` = '') THEN null ELSE `_58` END) `_58`
> > > , (CASE WHEN (`_59` = '') THEN null ELSE `_59` END) `_59`
> > > , (CASE WHEN (`_60` = '') THEN null ELSE `_60` END) `_60`
> > > , (CASE WHEN (`_61` = '') THEN null ELSE `_61` END) `_61`
> > > , (CASE WHEN (`_62` = '') THEN null ELSE `_62` END) `_62`
> > > , (CASE WHEN (`_63` = '') THEN null ELSE `_63` END) `_63`
> > > , (CASE WHEN (`_64` = '') THEN null ELSE `_64` END) `_64`
> > > , (CASE WHEN (`_65` = '') THEN null ELSE `_65` END) `_65`
> > > , (CASE WHEN (`_66` = '') THEN null ELSE `_66` END) `_66`
> > > , (CASE WHEN (`_67` = '') THEN null ELSE `_67` END) `_67`
> > > , (CASE WHEN (`_68` = '') THEN null ELSE `_68` END) `_68`
> > > , (CASE WHEN (`_69` = '') THEN null ELSE `_69` END) `_69`
> > > , (CASE WHEN (`_70` = '') THEN null ELSE `_70` END) `_70`
> > > , (CASE WHEN (`_71` = '') THEN null ELSE `_71` END) `_71`
> > > , (CASE WHEN (`_72` = '') THEN null ELSE `_72` END) `_72`
> > > , (CASE WHEN (`_73` = '') THEN null ELSE `_73` END) `_73`
> > > , (CASE WHEN (`_74` = '') THEN null ELSE `_74` END) `_74`
> > >
> > >
> > > FROM
> > > ( select '0' `A1`, '1' `A2`, '2' `A3`, '3' `A4`, '4' `A5`, '5' `A6`,
> '6'
> > > `A7`, '7' `A8`, '8' `A9`, '9' `A10`, '10' `A11`, '11' `A12`, '12'
> `A13`,
> > > '13' `A14`, '14' `A15`, '15' `A16`, '16' `A17`, '17' `A18`, '18' `A19`,
> > > '19' `A20`, '20' `A21`, '21' `A22`, '23' `_1`, '24' `_2`, '25' `_3`,
> '26'
> > > `_4`, '27' `_5`, '28' `_6`, '29' `_7`, '30' `_8`, '31' `_9`, '32'
> `_10`,
> > > '33' `_11`, '34' `_12`, '35' `_13`, '36' `_14`, '37' `_15`, '38' `_16`,
> > > '39' `_17`, '40' `_18`, '41' `_19`, '42' `_20`, '43' `_21`, '44' `_22`,
> > > '45' `_23`, '46' `_24`, '47' `_25`, '48' `_26`, '49' `_27`, '50' `_28`,
> > > '51' `_29`, '52' `_30`, '53' `_31`, '54' `_32`, '55' `_33`, '56' `_34`,
> > > '57' `_35`, '58' `_36`, '59' `_37`, '60' `_38`, '61' `_39`, '62' `_40`,
> > > '63' `_41`, '64' `_42`, '65' `_43`, '66' `_44`, '67' `_45`, '68' `_46`,
> > > '69' `_47`, '70' `_48`, '71' `_49`, '72' `_50`, '73' `_51`, '74' `_52`,
> > > '75' `_53`, '76' `_54`, '77' `_55`, '78' `_56`, '79' `_57`, '80' `_58`,
> > > '81' `_59`, '82' `_60`, '83' `_61`, '84' `_62`, '85' `_63`, '86' `_64`,
> > > '87' `_65`, '88' `_66`, '89' `_67`, '90' `_68`, '91' `_69`, '92' `_70`,
> > > '93' `_71`, '94' `_72`, '95' `_73`, '96' `_74`, '97' `_75`, '98' `_76`,
> > > '99' `_77`, '100' `_78`, '101' `_79`, '102' `_80`, '103' `_81`, '104'
> > > `_82`, '105' `_83`, '106' `_84`, '107' `_85`, '108' `_86`, '109' `_87`,
> > > '110' `_88`, '111' `_89`, '112' `_90`, '113' `_91`, '114' `_92`, '115'
> > > `_93`, '116' `_94`, '117' `_95`, '118' `_96`, '119' `_97`, '120' `_98`,
> > > '121' `_99`, '122' `_100`, '123' `_101`, '124' `_102`, '125' `_103`,
> > '126'
> > > `_104`, '127' `_105`, '128' `_106`, '129' `_107`, '130' `_108`, '131'
> > > `_109`, '132' `_110`, '133' `_111`, '134' `_112`, '135' `_113`, '136'
> > > `_114`, '137' `_115`, '138' `_116`, '139' `_117`, '140' `_118`, '141'
> > > `_119`, '142' `_120`, '143' `_121`, '144' `_122`, '145' `_123`, '146'
> > > `_124`, '147' `_125`, '148' `_126`, '149' `_127`, '150' `_128`, '151'
> > > `_129`, '152' `_130`, '153' `_131`, '154' `_132`, '155' `_133`, '156'
> > > `_134`, '157' `_135`, '158' `_136`, '159' `_137`, '160' `_138`, '161'
> > > `_139`, '162' `_140`, '163' `_141`, '164' `_142`, '165' `_143`, '166'
> > > `_144`, '167' `_145`, '168' `_146`, '169' `_147`, '170' `_148`, '171'
> > > `_149`, '172' `_150`, '173' `_151`, '174' `_152`, '175' `_153`, '176'
> > > `_154`, '177' `_155`, '178' `_156`, '179' `_157`, '180' `_158`, '181'
> > > `_159`, '182' `_160`, '183' `_161`, '184' `_162`, '185' `_163`, '186'
> > > `_164`, '187' `_165`, '188' `_166`, '189' `_167`, '190' `_168`, '191'
> > > `_169`, '192' `_170`, '193' `_171`, '194' `_172`, '195' `_173`, '196'
> > > `_174`, '197' `_175`, '198' `_176`, '199' `_177`, '200' `_178`, '201'
> > > `_179`, '202' `_180`, '203' `_181`, '204' `_182`, '205' `_183`, '206'
> > > `_184`, '207' `_185` from (values(1)) )
> > >
> > > Regards,
> > > Rahul
> > >
> >
> > --
> > _*** This email and any files transmitted with it are confidential and
> > intended solely for the use of the individual or entity to whom it is
> > addressed. If you are not the named addressee then you should not
> > disseminate, distribute or copy this e-mail. Please notify the sender
> > immediately and delete this e-mail from your system.***_
> >
>
--
_*** This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom it is
addressed. If you are not the named addressee then you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately and delete this e-mail from your system.***_
Re: Query Compilation error with 80+ CASE statements
Posted by Arjun kr <ar...@outlook.com>.
Rahul,
You can try setting system option 'exec.java.compiler.exp_in_method_size' to lesser value from the default of 50 if you haven't tried already and see if it succeeds.
alter session set `exec.java.compiler.exp_in_method_size` = <number>;
Thanks,
Arjun
________________________________
From: Abhishek Girish <ag...@apache.org>
Sent: Thursday, February 28, 2019 5:55 AM
To: user
Subject: Re: Query Compilation error with 80+ CASE statements
Rahul,
Can you please share plans for both queries (one with fewer which succeeds
and one which fails). Also the verbose error.
On Tue, Feb 26, 2019 at 11:33 PM Rahul Raj <ra...@option3.io> wrote:
> Some more update to the mail above:
>
> The query above has a UDF 'checkNull' used . The UDF code is placed inside
> the compiled query code, causing it to fail where there are more case
> statements. The below snippet is from the UDF.
>
> {
> if (input.end - input.start == 0) {
> throw new RuntimeException("IllegalArgumentException : null values
> in non nullable fields");
> } else
> {
> out = input;
> }
> }
>
> Any thoughts on this? Are there any naming conventions while developing a
> UDF?
>
> Regards,
> Rahul
>
>
>
> On Wed, Feb 27, 2019 at 12:14 PM Rahul Raj <ra...@option3.io> wrote:
>
> > Hi,
> >
> > I am getting compilation error on Drill 1.15 when query contains a large
> > number of case statements. I have included the query below. Query works
> > fine when few case statements are removed.
> >
> > org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
> > CompileException: File
> >
> 'org.apache.drill.exec.compile.DrillJavaFileObject[ProjectorGen8635.java]',
> > Line 872, Column 9: ProjectorGen8635.java:872: error: cannot assign a
> value
> > to final variable out out = input; ^
> > (compiler.err.cant.assign.val.to.final.var) Fragment 0:0 Please, refer to
> > logs for more information. [Error Id:
> a0d3f054-7c60-4915-9629-55e5dacd8606
> > on jiffydemo:31010]
> >
> > Query is :
> >
> > SELECT
> > CAST(`A1` AS INT) `A1`
> > , CAST(`A2` AS INT) `A2`
> > , `A3`
> > , `A4`
> > , `A5`
> > , (CASE WHEN (`A6` = '') THEN null ELSE `A6` END) `A6`
> > , `A7`
> > , `A8`
> > , `A9`
> > , `A10`
> > , CAST(A11 AS INT) `A11`
> > , (CASE WHEN (`A12` = '') THEN null ELSE `A12` END) `A12`
> > , CAST(`checkNull`(`A13`) AS INT) `A13`
> > , CAST(`checkNull`(`A14`) AS INT) `A14`
> > , (CASE WHEN (`A15` = '') THEN null ELSE `A15` END) `A15`
> > , CAST(`checkNull`(`A16`) AS INT) `A16`
> > , CAST(`checkNull`(`A17`) AS INT) `A17`
> > , CAST(`checkNull`(`A18`) AS INT) `A18`
> > , (CASE WHEN (`A19` = '') THEN null ELSE `A19` END) `A19`
> > , `A20`
> > , `A21`
> > , `A22`
> > , (CASE WHEN (`_1` = '') THEN null ELSE `_1` END) `_1`
> > , (CASE WHEN (`_2` = '') THEN null ELSE `_2` END) `_2`
> > , (CASE WHEN (`_3` = '') THEN null ELSE `_3` END) `_3`
> > , (CASE WHEN (`_4` = '') THEN null ELSE `_4` END) `_4`
> > , (CASE WHEN (`_5` = '') THEN null ELSE `_5` END) `_5`
> > , (CASE WHEN (`_6` = '') THEN null ELSE `_6` END) `_6`
> > , (CASE WHEN (`_7` = '') THEN null ELSE `_7` END) `_7`
> > , (CASE WHEN (`_8` = '') THEN null ELSE `_8` END) `_8`
> > , (CASE WHEN (`_9` = '') THEN null ELSE `_9` END) `_9`
> > , (CASE WHEN (`_10` = '') THEN null ELSE `_10` END) `_10`
> > , (CASE WHEN (`_11` = '') THEN null ELSE `_11` END) `_11`
> > , (CASE WHEN (`_12` = '') THEN null ELSE `_12` END) `_12`
> > , (CASE WHEN (`_13` = '') THEN null ELSE `_13` END) `_13`
> > , (CASE WHEN (`_14` = '') THEN null ELSE `_14` END) `_14`
> > , (CASE WHEN (`_15` = '') THEN null ELSE `_15` END) `_15`
> > , (CASE WHEN (`_16` = '') THEN null ELSE `_16` END) `_16`
> > , (CASE WHEN (`_17` = '') THEN null ELSE `_17` END) `_17`
> > , (CASE WHEN (`_18` = '') THEN null ELSE `_18` END) `_18`
> > , (CASE WHEN (`_19` = '') THEN null ELSE `_19` END) `_19`
> > , (CASE WHEN (`_20` = '') THEN null ELSE `_20` END) `_20`
> > , (CASE WHEN (`_21` = '') THEN null ELSE `_21` END) `_21`
> > , (CASE WHEN (`_22` = '') THEN null ELSE `_22` END) `_22`
> > , (CASE WHEN (`_23` = '') THEN null ELSE `_23` END) `_23`
> > , (CASE WHEN (`_24` = '') THEN null ELSE `_24` END) `_24`
> > , (CASE WHEN (`_25` = '') THEN null ELSE `_25` END) `_25`
> > , (CASE WHEN (`_26` = '') THEN null ELSE `_26` END) `_26`
> > , (CASE WHEN (`_27` = '') THEN null ELSE `_27` END) `_27`
> > , (CASE WHEN (`_28` = '') THEN null ELSE `_28` END) `_28`
> > , (CASE WHEN (`_29` = '') THEN null ELSE `_29` END) `_29`
> > , (CASE WHEN (`_30` = '') THEN null ELSE `_30` END) `_30`
> > , (CASE WHEN (`_31` = '') THEN null ELSE `_31` END) `_31`
> > , (CASE WHEN (`_32` = '') THEN null ELSE `_32` END) `_32`
> > , (CASE WHEN (`_33` = '') THEN null ELSE `_33` END) `_33`
> > , (CASE WHEN (`_34` = '') THEN null ELSE `_34` END) `_34`
> > , (CASE WHEN (`_35` = '') THEN null ELSE `_35` END) `_35`
> > , (CASE WHEN (`_36` = '') THEN null ELSE `_36` END) `_36`
> > , (CASE WHEN (`_37` = '') THEN null ELSE `_37` END) `_37`
> > , (CASE WHEN (`_38` = '') THEN null ELSE `_38` END) `_38`
> > , (CASE WHEN (`_39` = '') THEN null ELSE `_39` END) `_39`
> > , (CASE WHEN (`_40` = '') THEN null ELSE `_40` END) `_40`
> > , (CASE WHEN (`_41` = '') THEN null ELSE `_41` END) `_41`
> > , (CASE WHEN (`_42` = '') THEN null ELSE `_42` END) `_42`
> > , (CASE WHEN (`_43` = '') THEN null ELSE `_43` END) `_43`
> > , (CASE WHEN (`_44` = '') THEN null ELSE `_44` END) `_44`
> > , (CASE WHEN (`_45` = '') THEN null ELSE `_45` END) `_45`
> > , (CASE WHEN (`_46` = '') THEN null ELSE `_46` END) `_46`
> > , (CASE WHEN (`_47` = '') THEN null ELSE `_47` END) `_47`
> > , (CASE WHEN (`_48` = '') THEN null ELSE `_48` END) `_48`
> > , (CASE WHEN (`_49` = '') THEN null ELSE `_49` END) `_49`
> > , (CASE WHEN (`_50` = '') THEN null ELSE `_50` END) `_50`
> > , (CASE WHEN (`_51` = '') THEN null ELSE `_51` END) `_51`
> > , (CASE WHEN (`_52` = '') THEN null ELSE `_52` END) `_52`
> > , (CASE WHEN (`_53` = '') THEN null ELSE `_53` END) `_53`
> > , (CASE WHEN (`_54` = '') THEN null ELSE `_54` END) `_54`
> > , (CASE WHEN (`_55` = '') THEN null ELSE `_55` END) `_55`
> > , (CASE WHEN (`_56` = '') THEN null ELSE `_56` END) `_56`
> > , (CASE WHEN (`_57` = '') THEN null ELSE `_57` END) `_57`
> > , (CASE WHEN (`_58` = '') THEN null ELSE `_58` END) `_58`
> > , (CASE WHEN (`_59` = '') THEN null ELSE `_59` END) `_59`
> > , (CASE WHEN (`_60` = '') THEN null ELSE `_60` END) `_60`
> > , (CASE WHEN (`_61` = '') THEN null ELSE `_61` END) `_61`
> > , (CASE WHEN (`_62` = '') THEN null ELSE `_62` END) `_62`
> > , (CASE WHEN (`_63` = '') THEN null ELSE `_63` END) `_63`
> > , (CASE WHEN (`_64` = '') THEN null ELSE `_64` END) `_64`
> > , (CASE WHEN (`_65` = '') THEN null ELSE `_65` END) `_65`
> > , (CASE WHEN (`_66` = '') THEN null ELSE `_66` END) `_66`
> > , (CASE WHEN (`_67` = '') THEN null ELSE `_67` END) `_67`
> > , (CASE WHEN (`_68` = '') THEN null ELSE `_68` END) `_68`
> > , (CASE WHEN (`_69` = '') THEN null ELSE `_69` END) `_69`
> > , (CASE WHEN (`_70` = '') THEN null ELSE `_70` END) `_70`
> > , (CASE WHEN (`_71` = '') THEN null ELSE `_71` END) `_71`
> > , (CASE WHEN (`_72` = '') THEN null ELSE `_72` END) `_72`
> > , (CASE WHEN (`_73` = '') THEN null ELSE `_73` END) `_73`
> > , (CASE WHEN (`_74` = '') THEN null ELSE `_74` END) `_74`
> >
> >
> > FROM
> > ( select '0' `A1`, '1' `A2`, '2' `A3`, '3' `A4`, '4' `A5`, '5' `A6`, '6'
> > `A7`, '7' `A8`, '8' `A9`, '9' `A10`, '10' `A11`, '11' `A12`, '12' `A13`,
> > '13' `A14`, '14' `A15`, '15' `A16`, '16' `A17`, '17' `A18`, '18' `A19`,
> > '19' `A20`, '20' `A21`, '21' `A22`, '23' `_1`, '24' `_2`, '25' `_3`, '26'
> > `_4`, '27' `_5`, '28' `_6`, '29' `_7`, '30' `_8`, '31' `_9`, '32' `_10`,
> > '33' `_11`, '34' `_12`, '35' `_13`, '36' `_14`, '37' `_15`, '38' `_16`,
> > '39' `_17`, '40' `_18`, '41' `_19`, '42' `_20`, '43' `_21`, '44' `_22`,
> > '45' `_23`, '46' `_24`, '47' `_25`, '48' `_26`, '49' `_27`, '50' `_28`,
> > '51' `_29`, '52' `_30`, '53' `_31`, '54' `_32`, '55' `_33`, '56' `_34`,
> > '57' `_35`, '58' `_36`, '59' `_37`, '60' `_38`, '61' `_39`, '62' `_40`,
> > '63' `_41`, '64' `_42`, '65' `_43`, '66' `_44`, '67' `_45`, '68' `_46`,
> > '69' `_47`, '70' `_48`, '71' `_49`, '72' `_50`, '73' `_51`, '74' `_52`,
> > '75' `_53`, '76' `_54`, '77' `_55`, '78' `_56`, '79' `_57`, '80' `_58`,
> > '81' `_59`, '82' `_60`, '83' `_61`, '84' `_62`, '85' `_63`, '86' `_64`,
> > '87' `_65`, '88' `_66`, '89' `_67`, '90' `_68`, '91' `_69`, '92' `_70`,
> > '93' `_71`, '94' `_72`, '95' `_73`, '96' `_74`, '97' `_75`, '98' `_76`,
> > '99' `_77`, '100' `_78`, '101' `_79`, '102' `_80`, '103' `_81`, '104'
> > `_82`, '105' `_83`, '106' `_84`, '107' `_85`, '108' `_86`, '109' `_87`,
> > '110' `_88`, '111' `_89`, '112' `_90`, '113' `_91`, '114' `_92`, '115'
> > `_93`, '116' `_94`, '117' `_95`, '118' `_96`, '119' `_97`, '120' `_98`,
> > '121' `_99`, '122' `_100`, '123' `_101`, '124' `_102`, '125' `_103`,
> '126'
> > `_104`, '127' `_105`, '128' `_106`, '129' `_107`, '130' `_108`, '131'
> > `_109`, '132' `_110`, '133' `_111`, '134' `_112`, '135' `_113`, '136'
> > `_114`, '137' `_115`, '138' `_116`, '139' `_117`, '140' `_118`, '141'
> > `_119`, '142' `_120`, '143' `_121`, '144' `_122`, '145' `_123`, '146'
> > `_124`, '147' `_125`, '148' `_126`, '149' `_127`, '150' `_128`, '151'
> > `_129`, '152' `_130`, '153' `_131`, '154' `_132`, '155' `_133`, '156'
> > `_134`, '157' `_135`, '158' `_136`, '159' `_137`, '160' `_138`, '161'
> > `_139`, '162' `_140`, '163' `_141`, '164' `_142`, '165' `_143`, '166'
> > `_144`, '167' `_145`, '168' `_146`, '169' `_147`, '170' `_148`, '171'
> > `_149`, '172' `_150`, '173' `_151`, '174' `_152`, '175' `_153`, '176'
> > `_154`, '177' `_155`, '178' `_156`, '179' `_157`, '180' `_158`, '181'
> > `_159`, '182' `_160`, '183' `_161`, '184' `_162`, '185' `_163`, '186'
> > `_164`, '187' `_165`, '188' `_166`, '189' `_167`, '190' `_168`, '191'
> > `_169`, '192' `_170`, '193' `_171`, '194' `_172`, '195' `_173`, '196'
> > `_174`, '197' `_175`, '198' `_176`, '199' `_177`, '200' `_178`, '201'
> > `_179`, '202' `_180`, '203' `_181`, '204' `_182`, '205' `_183`, '206'
> > `_184`, '207' `_185` from (values(1)) )
> >
> > Regards,
> > Rahul
> >
>
> --
> _*** This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom it is
> addressed. If you are not the named addressee then you should not
> disseminate, distribute or copy this e-mail. Please notify the sender
> immediately and delete this e-mail from your system.***_
>
Re: Query Compilation error with 80+ CASE statements
Posted by Abhishek Girish <ag...@apache.org>.
Rahul,
Can you please share plans for both queries (one with fewer which succeeds
and one which fails). Also the verbose error.
On Tue, Feb 26, 2019 at 11:33 PM Rahul Raj <ra...@option3.io> wrote:
> Some more update to the mail above:
>
> The query above has a UDF 'checkNull' used . The UDF code is placed inside
> the compiled query code, causing it to fail where there are more case
> statements. The below snippet is from the UDF.
>
> {
> if (input.end - input.start == 0) {
> throw new RuntimeException("IllegalArgumentException : null values
> in non nullable fields");
> } else
> {
> out = input;
> }
> }
>
> Any thoughts on this? Are there any naming conventions while developing a
> UDF?
>
> Regards,
> Rahul
>
>
>
> On Wed, Feb 27, 2019 at 12:14 PM Rahul Raj <ra...@option3.io> wrote:
>
> > Hi,
> >
> > I am getting compilation error on Drill 1.15 when query contains a large
> > number of case statements. I have included the query below. Query works
> > fine when few case statements are removed.
> >
> > org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
> > CompileException: File
> >
> 'org.apache.drill.exec.compile.DrillJavaFileObject[ProjectorGen8635.java]',
> > Line 872, Column 9: ProjectorGen8635.java:872: error: cannot assign a
> value
> > to final variable out out = input; ^
> > (compiler.err.cant.assign.val.to.final.var) Fragment 0:0 Please, refer to
> > logs for more information. [Error Id:
> a0d3f054-7c60-4915-9629-55e5dacd8606
> > on jiffydemo:31010]
> >
> > Query is :
> >
> > SELECT
> > CAST(`A1` AS INT) `A1`
> > , CAST(`A2` AS INT) `A2`
> > , `A3`
> > , `A4`
> > , `A5`
> > , (CASE WHEN (`A6` = '') THEN null ELSE `A6` END) `A6`
> > , `A7`
> > , `A8`
> > , `A9`
> > , `A10`
> > , CAST(A11 AS INT) `A11`
> > , (CASE WHEN (`A12` = '') THEN null ELSE `A12` END) `A12`
> > , CAST(`checkNull`(`A13`) AS INT) `A13`
> > , CAST(`checkNull`(`A14`) AS INT) `A14`
> > , (CASE WHEN (`A15` = '') THEN null ELSE `A15` END) `A15`
> > , CAST(`checkNull`(`A16`) AS INT) `A16`
> > , CAST(`checkNull`(`A17`) AS INT) `A17`
> > , CAST(`checkNull`(`A18`) AS INT) `A18`
> > , (CASE WHEN (`A19` = '') THEN null ELSE `A19` END) `A19`
> > , `A20`
> > , `A21`
> > , `A22`
> > , (CASE WHEN (`_1` = '') THEN null ELSE `_1` END) `_1`
> > , (CASE WHEN (`_2` = '') THEN null ELSE `_2` END) `_2`
> > , (CASE WHEN (`_3` = '') THEN null ELSE `_3` END) `_3`
> > , (CASE WHEN (`_4` = '') THEN null ELSE `_4` END) `_4`
> > , (CASE WHEN (`_5` = '') THEN null ELSE `_5` END) `_5`
> > , (CASE WHEN (`_6` = '') THEN null ELSE `_6` END) `_6`
> > , (CASE WHEN (`_7` = '') THEN null ELSE `_7` END) `_7`
> > , (CASE WHEN (`_8` = '') THEN null ELSE `_8` END) `_8`
> > , (CASE WHEN (`_9` = '') THEN null ELSE `_9` END) `_9`
> > , (CASE WHEN (`_10` = '') THEN null ELSE `_10` END) `_10`
> > , (CASE WHEN (`_11` = '') THEN null ELSE `_11` END) `_11`
> > , (CASE WHEN (`_12` = '') THEN null ELSE `_12` END) `_12`
> > , (CASE WHEN (`_13` = '') THEN null ELSE `_13` END) `_13`
> > , (CASE WHEN (`_14` = '') THEN null ELSE `_14` END) `_14`
> > , (CASE WHEN (`_15` = '') THEN null ELSE `_15` END) `_15`
> > , (CASE WHEN (`_16` = '') THEN null ELSE `_16` END) `_16`
> > , (CASE WHEN (`_17` = '') THEN null ELSE `_17` END) `_17`
> > , (CASE WHEN (`_18` = '') THEN null ELSE `_18` END) `_18`
> > , (CASE WHEN (`_19` = '') THEN null ELSE `_19` END) `_19`
> > , (CASE WHEN (`_20` = '') THEN null ELSE `_20` END) `_20`
> > , (CASE WHEN (`_21` = '') THEN null ELSE `_21` END) `_21`
> > , (CASE WHEN (`_22` = '') THEN null ELSE `_22` END) `_22`
> > , (CASE WHEN (`_23` = '') THEN null ELSE `_23` END) `_23`
> > , (CASE WHEN (`_24` = '') THEN null ELSE `_24` END) `_24`
> > , (CASE WHEN (`_25` = '') THEN null ELSE `_25` END) `_25`
> > , (CASE WHEN (`_26` = '') THEN null ELSE `_26` END) `_26`
> > , (CASE WHEN (`_27` = '') THEN null ELSE `_27` END) `_27`
> > , (CASE WHEN (`_28` = '') THEN null ELSE `_28` END) `_28`
> > , (CASE WHEN (`_29` = '') THEN null ELSE `_29` END) `_29`
> > , (CASE WHEN (`_30` = '') THEN null ELSE `_30` END) `_30`
> > , (CASE WHEN (`_31` = '') THEN null ELSE `_31` END) `_31`
> > , (CASE WHEN (`_32` = '') THEN null ELSE `_32` END) `_32`
> > , (CASE WHEN (`_33` = '') THEN null ELSE `_33` END) `_33`
> > , (CASE WHEN (`_34` = '') THEN null ELSE `_34` END) `_34`
> > , (CASE WHEN (`_35` = '') THEN null ELSE `_35` END) `_35`
> > , (CASE WHEN (`_36` = '') THEN null ELSE `_36` END) `_36`
> > , (CASE WHEN (`_37` = '') THEN null ELSE `_37` END) `_37`
> > , (CASE WHEN (`_38` = '') THEN null ELSE `_38` END) `_38`
> > , (CASE WHEN (`_39` = '') THEN null ELSE `_39` END) `_39`
> > , (CASE WHEN (`_40` = '') THEN null ELSE `_40` END) `_40`
> > , (CASE WHEN (`_41` = '') THEN null ELSE `_41` END) `_41`
> > , (CASE WHEN (`_42` = '') THEN null ELSE `_42` END) `_42`
> > , (CASE WHEN (`_43` = '') THEN null ELSE `_43` END) `_43`
> > , (CASE WHEN (`_44` = '') THEN null ELSE `_44` END) `_44`
> > , (CASE WHEN (`_45` = '') THEN null ELSE `_45` END) `_45`
> > , (CASE WHEN (`_46` = '') THEN null ELSE `_46` END) `_46`
> > , (CASE WHEN (`_47` = '') THEN null ELSE `_47` END) `_47`
> > , (CASE WHEN (`_48` = '') THEN null ELSE `_48` END) `_48`
> > , (CASE WHEN (`_49` = '') THEN null ELSE `_49` END) `_49`
> > , (CASE WHEN (`_50` = '') THEN null ELSE `_50` END) `_50`
> > , (CASE WHEN (`_51` = '') THEN null ELSE `_51` END) `_51`
> > , (CASE WHEN (`_52` = '') THEN null ELSE `_52` END) `_52`
> > , (CASE WHEN (`_53` = '') THEN null ELSE `_53` END) `_53`
> > , (CASE WHEN (`_54` = '') THEN null ELSE `_54` END) `_54`
> > , (CASE WHEN (`_55` = '') THEN null ELSE `_55` END) `_55`
> > , (CASE WHEN (`_56` = '') THEN null ELSE `_56` END) `_56`
> > , (CASE WHEN (`_57` = '') THEN null ELSE `_57` END) `_57`
> > , (CASE WHEN (`_58` = '') THEN null ELSE `_58` END) `_58`
> > , (CASE WHEN (`_59` = '') THEN null ELSE `_59` END) `_59`
> > , (CASE WHEN (`_60` = '') THEN null ELSE `_60` END) `_60`
> > , (CASE WHEN (`_61` = '') THEN null ELSE `_61` END) `_61`
> > , (CASE WHEN (`_62` = '') THEN null ELSE `_62` END) `_62`
> > , (CASE WHEN (`_63` = '') THEN null ELSE `_63` END) `_63`
> > , (CASE WHEN (`_64` = '') THEN null ELSE `_64` END) `_64`
> > , (CASE WHEN (`_65` = '') THEN null ELSE `_65` END) `_65`
> > , (CASE WHEN (`_66` = '') THEN null ELSE `_66` END) `_66`
> > , (CASE WHEN (`_67` = '') THEN null ELSE `_67` END) `_67`
> > , (CASE WHEN (`_68` = '') THEN null ELSE `_68` END) `_68`
> > , (CASE WHEN (`_69` = '') THEN null ELSE `_69` END) `_69`
> > , (CASE WHEN (`_70` = '') THEN null ELSE `_70` END) `_70`
> > , (CASE WHEN (`_71` = '') THEN null ELSE `_71` END) `_71`
> > , (CASE WHEN (`_72` = '') THEN null ELSE `_72` END) `_72`
> > , (CASE WHEN (`_73` = '') THEN null ELSE `_73` END) `_73`
> > , (CASE WHEN (`_74` = '') THEN null ELSE `_74` END) `_74`
> >
> >
> > FROM
> > ( select '0' `A1`, '1' `A2`, '2' `A3`, '3' `A4`, '4' `A5`, '5' `A6`, '6'
> > `A7`, '7' `A8`, '8' `A9`, '9' `A10`, '10' `A11`, '11' `A12`, '12' `A13`,
> > '13' `A14`, '14' `A15`, '15' `A16`, '16' `A17`, '17' `A18`, '18' `A19`,
> > '19' `A20`, '20' `A21`, '21' `A22`, '23' `_1`, '24' `_2`, '25' `_3`, '26'
> > `_4`, '27' `_5`, '28' `_6`, '29' `_7`, '30' `_8`, '31' `_9`, '32' `_10`,
> > '33' `_11`, '34' `_12`, '35' `_13`, '36' `_14`, '37' `_15`, '38' `_16`,
> > '39' `_17`, '40' `_18`, '41' `_19`, '42' `_20`, '43' `_21`, '44' `_22`,
> > '45' `_23`, '46' `_24`, '47' `_25`, '48' `_26`, '49' `_27`, '50' `_28`,
> > '51' `_29`, '52' `_30`, '53' `_31`, '54' `_32`, '55' `_33`, '56' `_34`,
> > '57' `_35`, '58' `_36`, '59' `_37`, '60' `_38`, '61' `_39`, '62' `_40`,
> > '63' `_41`, '64' `_42`, '65' `_43`, '66' `_44`, '67' `_45`, '68' `_46`,
> > '69' `_47`, '70' `_48`, '71' `_49`, '72' `_50`, '73' `_51`, '74' `_52`,
> > '75' `_53`, '76' `_54`, '77' `_55`, '78' `_56`, '79' `_57`, '80' `_58`,
> > '81' `_59`, '82' `_60`, '83' `_61`, '84' `_62`, '85' `_63`, '86' `_64`,
> > '87' `_65`, '88' `_66`, '89' `_67`, '90' `_68`, '91' `_69`, '92' `_70`,
> > '93' `_71`, '94' `_72`, '95' `_73`, '96' `_74`, '97' `_75`, '98' `_76`,
> > '99' `_77`, '100' `_78`, '101' `_79`, '102' `_80`, '103' `_81`, '104'
> > `_82`, '105' `_83`, '106' `_84`, '107' `_85`, '108' `_86`, '109' `_87`,
> > '110' `_88`, '111' `_89`, '112' `_90`, '113' `_91`, '114' `_92`, '115'
> > `_93`, '116' `_94`, '117' `_95`, '118' `_96`, '119' `_97`, '120' `_98`,
> > '121' `_99`, '122' `_100`, '123' `_101`, '124' `_102`, '125' `_103`,
> '126'
> > `_104`, '127' `_105`, '128' `_106`, '129' `_107`, '130' `_108`, '131'
> > `_109`, '132' `_110`, '133' `_111`, '134' `_112`, '135' `_113`, '136'
> > `_114`, '137' `_115`, '138' `_116`, '139' `_117`, '140' `_118`, '141'
> > `_119`, '142' `_120`, '143' `_121`, '144' `_122`, '145' `_123`, '146'
> > `_124`, '147' `_125`, '148' `_126`, '149' `_127`, '150' `_128`, '151'
> > `_129`, '152' `_130`, '153' `_131`, '154' `_132`, '155' `_133`, '156'
> > `_134`, '157' `_135`, '158' `_136`, '159' `_137`, '160' `_138`, '161'
> > `_139`, '162' `_140`, '163' `_141`, '164' `_142`, '165' `_143`, '166'
> > `_144`, '167' `_145`, '168' `_146`, '169' `_147`, '170' `_148`, '171'
> > `_149`, '172' `_150`, '173' `_151`, '174' `_152`, '175' `_153`, '176'
> > `_154`, '177' `_155`, '178' `_156`, '179' `_157`, '180' `_158`, '181'
> > `_159`, '182' `_160`, '183' `_161`, '184' `_162`, '185' `_163`, '186'
> > `_164`, '187' `_165`, '188' `_166`, '189' `_167`, '190' `_168`, '191'
> > `_169`, '192' `_170`, '193' `_171`, '194' `_172`, '195' `_173`, '196'
> > `_174`, '197' `_175`, '198' `_176`, '199' `_177`, '200' `_178`, '201'
> > `_179`, '202' `_180`, '203' `_181`, '204' `_182`, '205' `_183`, '206'
> > `_184`, '207' `_185` from (values(1)) )
> >
> > Regards,
> > Rahul
> >
>
> --
> _*** This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom it is
> addressed. If you are not the named addressee then you should not
> disseminate, distribute or copy this e-mail. Please notify the sender
> immediately and delete this e-mail from your system.***_
>
Re: Query Compilation error with 80+ CASE statements
Posted by Rahul Raj <ra...@option3.io>.
Some more update to the mail above:
The query above has a UDF 'checkNull' used . The UDF code is placed inside
the compiled query code, causing it to fail where there are more case
statements. The below snippet is from the UDF.
{
if (input.end - input.start == 0) {
throw new RuntimeException("IllegalArgumentException : null values
in non nullable fields");
} else
{
out = input;
}
}
Any thoughts on this? Are there any naming conventions while developing a
UDF?
Regards,
Rahul
On Wed, Feb 27, 2019 at 12:14 PM Rahul Raj <ra...@option3.io> wrote:
> Hi,
>
> I am getting compilation error on Drill 1.15 when query contains a large
> number of case statements. I have included the query below. Query works
> fine when few case statements are removed.
>
> org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
> CompileException: File
> 'org.apache.drill.exec.compile.DrillJavaFileObject[ProjectorGen8635.java]',
> Line 872, Column 9: ProjectorGen8635.java:872: error: cannot assign a value
> to final variable out out = input; ^
> (compiler.err.cant.assign.val.to.final.var) Fragment 0:0 Please, refer to
> logs for more information. [Error Id: a0d3f054-7c60-4915-9629-55e5dacd8606
> on jiffydemo:31010]
>
> Query is :
>
> SELECT
> CAST(`A1` AS INT) `A1`
> , CAST(`A2` AS INT) `A2`
> , `A3`
> , `A4`
> , `A5`
> , (CASE WHEN (`A6` = '') THEN null ELSE `A6` END) `A6`
> , `A7`
> , `A8`
> , `A9`
> , `A10`
> , CAST(A11 AS INT) `A11`
> , (CASE WHEN (`A12` = '') THEN null ELSE `A12` END) `A12`
> , CAST(`checkNull`(`A13`) AS INT) `A13`
> , CAST(`checkNull`(`A14`) AS INT) `A14`
> , (CASE WHEN (`A15` = '') THEN null ELSE `A15` END) `A15`
> , CAST(`checkNull`(`A16`) AS INT) `A16`
> , CAST(`checkNull`(`A17`) AS INT) `A17`
> , CAST(`checkNull`(`A18`) AS INT) `A18`
> , (CASE WHEN (`A19` = '') THEN null ELSE `A19` END) `A19`
> , `A20`
> , `A21`
> , `A22`
> , (CASE WHEN (`_1` = '') THEN null ELSE `_1` END) `_1`
> , (CASE WHEN (`_2` = '') THEN null ELSE `_2` END) `_2`
> , (CASE WHEN (`_3` = '') THEN null ELSE `_3` END) `_3`
> , (CASE WHEN (`_4` = '') THEN null ELSE `_4` END) `_4`
> , (CASE WHEN (`_5` = '') THEN null ELSE `_5` END) `_5`
> , (CASE WHEN (`_6` = '') THEN null ELSE `_6` END) `_6`
> , (CASE WHEN (`_7` = '') THEN null ELSE `_7` END) `_7`
> , (CASE WHEN (`_8` = '') THEN null ELSE `_8` END) `_8`
> , (CASE WHEN (`_9` = '') THEN null ELSE `_9` END) `_9`
> , (CASE WHEN (`_10` = '') THEN null ELSE `_10` END) `_10`
> , (CASE WHEN (`_11` = '') THEN null ELSE `_11` END) `_11`
> , (CASE WHEN (`_12` = '') THEN null ELSE `_12` END) `_12`
> , (CASE WHEN (`_13` = '') THEN null ELSE `_13` END) `_13`
> , (CASE WHEN (`_14` = '') THEN null ELSE `_14` END) `_14`
> , (CASE WHEN (`_15` = '') THEN null ELSE `_15` END) `_15`
> , (CASE WHEN (`_16` = '') THEN null ELSE `_16` END) `_16`
> , (CASE WHEN (`_17` = '') THEN null ELSE `_17` END) `_17`
> , (CASE WHEN (`_18` = '') THEN null ELSE `_18` END) `_18`
> , (CASE WHEN (`_19` = '') THEN null ELSE `_19` END) `_19`
> , (CASE WHEN (`_20` = '') THEN null ELSE `_20` END) `_20`
> , (CASE WHEN (`_21` = '') THEN null ELSE `_21` END) `_21`
> , (CASE WHEN (`_22` = '') THEN null ELSE `_22` END) `_22`
> , (CASE WHEN (`_23` = '') THEN null ELSE `_23` END) `_23`
> , (CASE WHEN (`_24` = '') THEN null ELSE `_24` END) `_24`
> , (CASE WHEN (`_25` = '') THEN null ELSE `_25` END) `_25`
> , (CASE WHEN (`_26` = '') THEN null ELSE `_26` END) `_26`
> , (CASE WHEN (`_27` = '') THEN null ELSE `_27` END) `_27`
> , (CASE WHEN (`_28` = '') THEN null ELSE `_28` END) `_28`
> , (CASE WHEN (`_29` = '') THEN null ELSE `_29` END) `_29`
> , (CASE WHEN (`_30` = '') THEN null ELSE `_30` END) `_30`
> , (CASE WHEN (`_31` = '') THEN null ELSE `_31` END) `_31`
> , (CASE WHEN (`_32` = '') THEN null ELSE `_32` END) `_32`
> , (CASE WHEN (`_33` = '') THEN null ELSE `_33` END) `_33`
> , (CASE WHEN (`_34` = '') THEN null ELSE `_34` END) `_34`
> , (CASE WHEN (`_35` = '') THEN null ELSE `_35` END) `_35`
> , (CASE WHEN (`_36` = '') THEN null ELSE `_36` END) `_36`
> , (CASE WHEN (`_37` = '') THEN null ELSE `_37` END) `_37`
> , (CASE WHEN (`_38` = '') THEN null ELSE `_38` END) `_38`
> , (CASE WHEN (`_39` = '') THEN null ELSE `_39` END) `_39`
> , (CASE WHEN (`_40` = '') THEN null ELSE `_40` END) `_40`
> , (CASE WHEN (`_41` = '') THEN null ELSE `_41` END) `_41`
> , (CASE WHEN (`_42` = '') THEN null ELSE `_42` END) `_42`
> , (CASE WHEN (`_43` = '') THEN null ELSE `_43` END) `_43`
> , (CASE WHEN (`_44` = '') THEN null ELSE `_44` END) `_44`
> , (CASE WHEN (`_45` = '') THEN null ELSE `_45` END) `_45`
> , (CASE WHEN (`_46` = '') THEN null ELSE `_46` END) `_46`
> , (CASE WHEN (`_47` = '') THEN null ELSE `_47` END) `_47`
> , (CASE WHEN (`_48` = '') THEN null ELSE `_48` END) `_48`
> , (CASE WHEN (`_49` = '') THEN null ELSE `_49` END) `_49`
> , (CASE WHEN (`_50` = '') THEN null ELSE `_50` END) `_50`
> , (CASE WHEN (`_51` = '') THEN null ELSE `_51` END) `_51`
> , (CASE WHEN (`_52` = '') THEN null ELSE `_52` END) `_52`
> , (CASE WHEN (`_53` = '') THEN null ELSE `_53` END) `_53`
> , (CASE WHEN (`_54` = '') THEN null ELSE `_54` END) `_54`
> , (CASE WHEN (`_55` = '') THEN null ELSE `_55` END) `_55`
> , (CASE WHEN (`_56` = '') THEN null ELSE `_56` END) `_56`
> , (CASE WHEN (`_57` = '') THEN null ELSE `_57` END) `_57`
> , (CASE WHEN (`_58` = '') THEN null ELSE `_58` END) `_58`
> , (CASE WHEN (`_59` = '') THEN null ELSE `_59` END) `_59`
> , (CASE WHEN (`_60` = '') THEN null ELSE `_60` END) `_60`
> , (CASE WHEN (`_61` = '') THEN null ELSE `_61` END) `_61`
> , (CASE WHEN (`_62` = '') THEN null ELSE `_62` END) `_62`
> , (CASE WHEN (`_63` = '') THEN null ELSE `_63` END) `_63`
> , (CASE WHEN (`_64` = '') THEN null ELSE `_64` END) `_64`
> , (CASE WHEN (`_65` = '') THEN null ELSE `_65` END) `_65`
> , (CASE WHEN (`_66` = '') THEN null ELSE `_66` END) `_66`
> , (CASE WHEN (`_67` = '') THEN null ELSE `_67` END) `_67`
> , (CASE WHEN (`_68` = '') THEN null ELSE `_68` END) `_68`
> , (CASE WHEN (`_69` = '') THEN null ELSE `_69` END) `_69`
> , (CASE WHEN (`_70` = '') THEN null ELSE `_70` END) `_70`
> , (CASE WHEN (`_71` = '') THEN null ELSE `_71` END) `_71`
> , (CASE WHEN (`_72` = '') THEN null ELSE `_72` END) `_72`
> , (CASE WHEN (`_73` = '') THEN null ELSE `_73` END) `_73`
> , (CASE WHEN (`_74` = '') THEN null ELSE `_74` END) `_74`
>
>
> FROM
> ( select '0' `A1`, '1' `A2`, '2' `A3`, '3' `A4`, '4' `A5`, '5' `A6`, '6'
> `A7`, '7' `A8`, '8' `A9`, '9' `A10`, '10' `A11`, '11' `A12`, '12' `A13`,
> '13' `A14`, '14' `A15`, '15' `A16`, '16' `A17`, '17' `A18`, '18' `A19`,
> '19' `A20`, '20' `A21`, '21' `A22`, '23' `_1`, '24' `_2`, '25' `_3`, '26'
> `_4`, '27' `_5`, '28' `_6`, '29' `_7`, '30' `_8`, '31' `_9`, '32' `_10`,
> '33' `_11`, '34' `_12`, '35' `_13`, '36' `_14`, '37' `_15`, '38' `_16`,
> '39' `_17`, '40' `_18`, '41' `_19`, '42' `_20`, '43' `_21`, '44' `_22`,
> '45' `_23`, '46' `_24`, '47' `_25`, '48' `_26`, '49' `_27`, '50' `_28`,
> '51' `_29`, '52' `_30`, '53' `_31`, '54' `_32`, '55' `_33`, '56' `_34`,
> '57' `_35`, '58' `_36`, '59' `_37`, '60' `_38`, '61' `_39`, '62' `_40`,
> '63' `_41`, '64' `_42`, '65' `_43`, '66' `_44`, '67' `_45`, '68' `_46`,
> '69' `_47`, '70' `_48`, '71' `_49`, '72' `_50`, '73' `_51`, '74' `_52`,
> '75' `_53`, '76' `_54`, '77' `_55`, '78' `_56`, '79' `_57`, '80' `_58`,
> '81' `_59`, '82' `_60`, '83' `_61`, '84' `_62`, '85' `_63`, '86' `_64`,
> '87' `_65`, '88' `_66`, '89' `_67`, '90' `_68`, '91' `_69`, '92' `_70`,
> '93' `_71`, '94' `_72`, '95' `_73`, '96' `_74`, '97' `_75`, '98' `_76`,
> '99' `_77`, '100' `_78`, '101' `_79`, '102' `_80`, '103' `_81`, '104'
> `_82`, '105' `_83`, '106' `_84`, '107' `_85`, '108' `_86`, '109' `_87`,
> '110' `_88`, '111' `_89`, '112' `_90`, '113' `_91`, '114' `_92`, '115'
> `_93`, '116' `_94`, '117' `_95`, '118' `_96`, '119' `_97`, '120' `_98`,
> '121' `_99`, '122' `_100`, '123' `_101`, '124' `_102`, '125' `_103`, '126'
> `_104`, '127' `_105`, '128' `_106`, '129' `_107`, '130' `_108`, '131'
> `_109`, '132' `_110`, '133' `_111`, '134' `_112`, '135' `_113`, '136'
> `_114`, '137' `_115`, '138' `_116`, '139' `_117`, '140' `_118`, '141'
> `_119`, '142' `_120`, '143' `_121`, '144' `_122`, '145' `_123`, '146'
> `_124`, '147' `_125`, '148' `_126`, '149' `_127`, '150' `_128`, '151'
> `_129`, '152' `_130`, '153' `_131`, '154' `_132`, '155' `_133`, '156'
> `_134`, '157' `_135`, '158' `_136`, '159' `_137`, '160' `_138`, '161'
> `_139`, '162' `_140`, '163' `_141`, '164' `_142`, '165' `_143`, '166'
> `_144`, '167' `_145`, '168' `_146`, '169' `_147`, '170' `_148`, '171'
> `_149`, '172' `_150`, '173' `_151`, '174' `_152`, '175' `_153`, '176'
> `_154`, '177' `_155`, '178' `_156`, '179' `_157`, '180' `_158`, '181'
> `_159`, '182' `_160`, '183' `_161`, '184' `_162`, '185' `_163`, '186'
> `_164`, '187' `_165`, '188' `_166`, '189' `_167`, '190' `_168`, '191'
> `_169`, '192' `_170`, '193' `_171`, '194' `_172`, '195' `_173`, '196'
> `_174`, '197' `_175`, '198' `_176`, '199' `_177`, '200' `_178`, '201'
> `_179`, '202' `_180`, '203' `_181`, '204' `_182`, '205' `_183`, '206'
> `_184`, '207' `_185` from (values(1)) )
>
> Regards,
> Rahul
>
--
_*** This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom it is
addressed. If you are not the named addressee then you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately and delete this e-mail from your system.***_