Like you surmised, that is only part of the query. The full query is a
generated by Tableau and it¹s a lot longer. Tableau was only saying there
was an error executing the query but not what¹s wrong with it. It does
give you the option to copy the query though so I did and ran it straight
in the drill embedded console and saw that error. Here¹s the query:
SELECT `Custom_SQL_Query`.`APN` AS `APN`,
`Custom_SQL_Query`.`CDN Enterprise` AS `CDN_Enterprise`,
(CASE WHEN (`Custom_SQL_Query`.`Media Stalling Percentage` < 0) THEN
'Not Reported' ELSE CAST(`Custom_SQL_Query`.`Media Stalling Percentage` AS
VARCHAR(21)) END) AS `Calculation_BCCHEECAFEDDDJDJHBF`,
(CASE WHEN (`Custom_SQL_Query`.`Media Start Delay Ms` < 0) THEN 'Not
Reported' ELSE CAST(`Custom_SQL_Query`.`Media Start Delay Ms` AS
VARCHAR(21)) END) AS `Calculation_BCCHEECAFEDDEFCBDEI`,
CAST((CASE WHEN 1000 = 0 THEN null ELSE CAST(`Custom_SQL_Query`.`Clip
Duration Ms` AS FLOAT) / 1000 END) AS INTEGER) AS
`Calculation_BHABHCHDIIHBAGEJIGC`,
(CASE WHEN 1024 = 0 THEN null ELSE (CASE WHEN 1024 = 0 THEN null ELSE
CAST(`Custom_SQL_Query`.`Clip Size` AS FLOAT) / 1024 END) / 1024 END) AS
`Calculation_BHABHCHDIIHBAJJDJCH`,
CAST((CASE WHEN 1000 = 0 THEN null ELSE
CAST(`Custom_SQL_Query`.`Streamed Duration Ms` AS FLOAT) / 1000 END) AS
INTEGER) AS `Calculation_BHABHCHDIIHBBIHAEHC`,
(CASE WHEN (`Custom_SQL_Query`.`Session Qoe Score` < 1.5) THEN 'Bad'
WHEN (`Custom_SQL_Query`.`Session Qoe Score` < 2.5) THEN 'Poor' WHEN
(`Custom_SQL_Query`.`Session Qoe Score` < 3.5) THEN 'Fair' WHEN
(`Custom_SQL_Query`.`Session Qoe Score` < 4.5) THEN 'Good' ELSE
'Excellent' END) AS `Calculation_GAGJFBFDDJAHDEJJBDG`,
`Custom_SQL_Query`.`Cell Identity` AS `Cell_Identity`,
`Custom_SQL_Query`.`End Time` AS `End_Time`,
`Custom_SQL_Query`.`Location Area Code` AS `Location_Area_Code`,
`Custom_SQL_Query`.`Media Access Delay Ms` AS `Media_Access_Delay_Ms`,
`Custom_SQL_Query`.`Media Container Type` AS `Media_Container_Type`,
`Custom_SQL_Query`.`Media Session Type` AS `Media_Session_Type`,
`Custom_SQL_Query`.`Media Site` AS `Media_Site`,
`Custom_SQL_Query`.`Media Use` AS `Media_Use`,
`Custom_SQL_Query`.`Radio Access Technology` AS
`Radio_Access_Technology`,
`Custom_SQL_Query`.`Server Address` AS `Server_Address`,
`Custom_SQL_Query`.`Server Host Name` AS `Server_Host_Name`,
`Custom_SQL_Query`.`Start Time` AS `Start_Time`,
`Custom_SQL_Query`.`URL` AS `URL`,
SUM(`Custom_SQL_Query`.`Num Payload Bytes`) AS
`sum_Num_Payload_Bytes_ok`,
SUM(`Custom_SQL_Query`.`Num Retransmit Bytes`) AS
`sum_Num_Retransmit_Bytes_ok`
FROM (
select cast(columns[0] as VARCHAR(50)) as `Session ID`,
cast(columns[1] as VARCHAR(20)) as `Protocol Name`,
cast(columns[2] as TIMESTAMP) as `Ingest Time`,
cast(columns[3] as TIMESTAMP) as `Start Time`,
cast(columns[4] as TIMESTAMP) as `End Time`,
cast(columns[5] as VARCHAR(100)) as `APN`,
cast(columns[6] as VARCHAR(200)) as `URL`,
cast(columns[7] as VARCHAR(30)) as `Media Session Type`,
cast(columns[8] as VARCHAR(30)) as `Media Use`,
cast(columns[9] as BIGINT) as `Clip Size`,
cast(columns[10] as BIGINT) as `Clip Duration Ms`,
cast(columns[11] as BIGINT) as `Streamed Duration Ms`,
cast(columns[12] as VARCHAR(30)) as `Policy Action`,
cast(columns[13] as VARCHAR(30)) as `Media Site`,
cast(columns[14] as VARCHAR(30)) as `Probe Name`,
cast(columns[15] as VARCHAR(30)) as `Tier Plan`,
cast(columns[16] as VARCHAR(30)) as `Device Category`,
cast(columns[17] as VARCHAR(20)) as `Device Brand`,
cast(columns[18] as VARCHAR(30)) as `Device Model`,
cast(columns[19] as VARCHAR(20)) as `Device Operating System`,
cast(columns[20] as VARCHAR(30)) as `Device Operating System Version`,
cast(columns[21] as VARCHAR(10)) as `Location Type`,
cast(columns[22] as VARCHAR(50)) as `Location`,
cast(columns[23] as DOUBLE) as `Longitude`,
cast(columns[24] as DOUBLE) as `Latitude`,
cast(columns[25] as INTEGER) as `Mobile Country Code`,
cast(columns[26] as INTEGER) as `Mobile Network Code`,
cast(columns[27] as INTEGER) as `Location Area Code`,
cast(columns[28] as INTEGER) as `Routing Area Code`,
cast(columns[29] as INTEGER) as `Service Area Code`,
cast(columns[30] as INTEGER) as `Tracking Area Code`,
cast(columns[31] as INTEGER) as `Cell Identity`,
cast(columns[32] as INTEGER) as `Eutran Cell Identity`,
cast(columns[33] as FLOAT) as `Session Qoe Score`,
cast(columns[34] as FLOAT) as `Delivery Qoe Score`,
cast(columns[35] as FLOAT) as `Presentation Qoe Score`,
cast(columns[36] as INTEGER) as `Ad Probability`,
cast(columns[37] as FLOAT) as `Network Quality Score`,
cast(columns[38] as BIGINT) as `Media Access Delay Ms`,
cast(columns[39] as BIGINT) as `Media Reproduction Delay Ms`,
cast(columns[40] as BIGINT) as `Media Start Delay Ms`,
cast(columns[41] as INTEGER) as `Media Stalling Percentage`,
cast(columns[42] as VARCHAR(10)) as `Transport Protocol`,
cast(columns[43] as VARCHAR(30)) as `Streaming Protocol Category`,
cast(columns[44] as VARCHAR(30)) as `Streaming Protocol`,
cast(columns[45] as VARCHAR(30)) as `Media Container Type`,
cast(columns[46] as VARCHAR(20)) as `Media Container Encryption`,
cast(columns[47] as VARCHAR(20)) as `Application Protocol Encryption`,
cast(columns[48] as VARCHAR(20)) as `Video Codec`,
cast(columns[49] as VARCHAR(20)) as `Video Codec profile`,
cast(columns[50] as INTEGER) as `Video Width`,
cast(columns[51] as INTEGER) as `Video Height`,
cast(columns[52] as INTEGER) as `Video Frame Rate`,
cast(columns[53] as VARCHAR(20)) as `Video Encryption`,
cast(columns[54] as VARCHAR(20)) as `Audio Codec`,
cast(columns[55] as VARCHAR(20)) as `Audio Codec Profile`,
cast(columns[56] as INTEGER) as `Audio Channel Count`,
cast(columns[57] as INTEGER) as `Audio Sampling Rate`,
cast(columns[58] as VARCHAR(20)) as `Audio Encryption`,
cast(columns[59] as BIGINT) as `Num Bytes`,
cast(columns[60] as INTEGER) as `Num Packets`,
cast(columns[61] as BIGINT) as `Num Payload Bytes`,
cast(columns[62] as INTEGER) as `Num Retransmit Bytes`,
cast(columns[63] as INTEGER) as `Num Retransmit Packets`,
cast(columns[64] as VARCHAR(20)) as `Subscriber Id`,
cast(columns[65] as VARCHAR(30)) as `Equipment Id`,
cast(columns[66] as VARCHAR(20)) as `Phone Number`,
cast(columns[67] as VARCHAR(20)) as `Subscriber Address`,
cast(columns[68] as VARCHAR(20)) as `Server Address`,
cast(columns[69] as VARCHAR(50)) as `Server Host Name`,
cast(columns[70] as VARCHAR(20)) as `CDN Enterprise`,
cast(columns[71] as VARCHAR(10)) as `Radio Access Technology`
from dfs.video_analytics.`lors/*`
) `Custom_SQL_Query`
WHERE (`Custom_SQL_Query`.`Phone Number` = '02089433377')
GROUP BY `Custom_SQL_Query`.`APN`,
`Custom_SQL_Query`.`CDN Enterprise`,
(CASE WHEN (`Custom_SQL_Query`.`Media Stalling Percentage` < 0) THEN
'Not Reported' ELSE CAST(`Custom_SQL_Query`.`Media Stalling Percentage` AS
VARCHAR(21)) END),
(CASE WHEN (`Custom_SQL_Query`.`Media Start Delay Ms` < 0) THEN 'Not
Reported' ELSE CAST(`Custom_SQL_Query`.`Media Start Delay Ms` AS
VARCHAR(21)) END),
CAST((CASE WHEN 1000 = 0 THEN null ELSE CAST(`Custom_SQL_Query`.`Clip
Duration Ms` AS FLOAT) / 1000 END) AS INTEGER),
(CASE WHEN 1024 = 0 THEN null ELSE (CASE WHEN 1024 = 0 THEN null ELSE
CAST(`Custom_SQL_Query`.`Clip Size` AS FLOAT) / 1024 END) / 1024 END),
CAST((CASE WHEN 1000 = 0 THEN null ELSE
CAST(`Custom_SQL_Query`.`Streamed Duration Ms` AS FLOAT) / 1000 END) AS
INTEGER),
(CASE WHEN (`Custom_SQL_Query`.`Session Qoe Score` < 1.5) THEN 'Bad'
WHEN (`Custom_SQL_Query`.`Session Qoe Score` < 2.5) THEN 'Poor' WHEN
(`Custom_SQL_Query`.`Session Qoe Score` < 3.5) THEN 'Fair' WHEN
(`Custom_SQL_Query`.`Session Qoe Score` < 4.5) THEN 'Good' ELSE
'Excellent' END),
`Custom_SQL_Query`.`Cell Identity`,
`Custom_SQL_Query`.`End Time`,
`Custom_SQL_Query`.`Location Area Code`,
`Custom_SQL_Query`.`Media Access Delay Ms`,
`Custom_SQL_Query`.`Media Container Type`,
`Custom_SQL_Query`.`Media Session Type`,
`Custom_SQL_Query`.`Media Site`,
`Custom_SQL_Query`.`Media Use`,
`Custom_SQL_Query`.`Radio Access Technology`,
`Custom_SQL_Query`.`Server Address`,
`Custom_SQL_Query`.`Server Host Name`,
`Custom_SQL_Query`.`Start Time`,
`Custom_SQL_Query`.`URL`;
Regards,
Ali Kashmar
EMC| Open Innovation Lab
O: 613.321.1334 | M: 613.219.9361
On 2016-03-08, 8:59 PM, "Jacques Nadeau" <[email protected]> wrote:
>I'm not sure what is happening here. In general, I can write a query with
>a
>similar clause without issue. Can you confirm what the complete query was
>that was being run when this error occurred?
>
>This worked fine for me:
>
>select CASE WHEN 1000 = 0 THEN NULL ELSE CAST(xxx.`Clip Duration Ms` AS
>FLOAT) / 1000 END from cp.`employee.json` xxx;
>
>Note that your example is missing the select and from clauses which is why
>I am asking for the complete query.
>
>thanks,
>Jacques
>
>
>
>
>--
>Jacques Nadeau
>CTO and Co-Founder, Dremio
>
>On Tue, Mar 8, 2016 at 1:34 PM, Kashmar, Ali <[email protected]> wrote:
>
>> Hi,
>>
>> I¹m encountering this error while using Tableau with Drill:
>>
>> (org.apache.drill.exec.work.foreman.ForemanException) Unexpected
>>exception
>> during fragment initialization: Internal error: while converting CASE
>>WHEN
>> 1000 = 0 THEN NULL ELSE CAST(`Custom_SQL_Query`.`Clip Duration Ms` AS
>> FLOAT) / 1000 END
>>
>> Caused By (java.lang.UnsupportedOperationException) class
>> org.apache.calcite.sql.SqlLiteral: NULL
>> org.apache.calcite.util.Util.needToImplement():920
>>
>>
>>org.apache.calcite.sql.validate.SqlValidatorImpl.getValidatedNodeType():1
>>420
>>
>> org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertLiteral():90
>> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit():4153
>> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit():3598
>> org.apache.calcite.sql.SqlLiteral.accept():404
>>
>>
>>org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression
>>():4057
>> org.apache.calcite.sql2rel.StandardConvertletTable.convertCase():303
>> sun.reflect.GeneratedMethodAccessor162.invoke():-1
>> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
>> java.lang.reflect.Method.invoke():606
>>
>> I¹m trying to understand what it means exactly. Is NULL not supported?
>>Is
>> there a property I can set to enable it? I didn¹t find anything to help
>>me
>> in the docs.
>>
>> Maybe worth mentioning that I didn¹t actually see the error in Tableau.
>>I
>> enabled verbose errors in drill console and actually copied the query
>>and
>> ran it in the console.
>>
>> Thanks,
>>
>> Ali Kashmar
>> EMC| Open Innovation Lab
>> O: 613.321.1334 | M: 613.219.9361
>>
