[GitHub] [druid] sascha-coenen commented on issue #9412: Regression: SQL query with multiple similar CASE clauses cannot be translated to correct native json format

[GitBox <gi...@apache.org>]

sascha-coenen commented on issue #9412: Regression: SQL query with multiple similar CASE clauses cannot be translated to correct native json format
URL: https://github.com/apache/druid/issues/9412#issuecomment-591700246
 
 
   thanks for the feedback.
   wrt to the CASE cityName evaluating to NULL:
   The origiinal query that failed for us looks like this:
   
   ```
   SELECT 
   	TIME_FORMAT(__time, 'yyyy-MM') AS "period",
   	CASE REGEXP_EXTRACT(integrationVersion, 'sdkandroid 2[0-9]\..*') WHEN NULL THEN FALSE ELSE TRUE END AS is_nextgen_android,
   	CASE REGEXP_EXTRACT(integrationVersion, 'sdkios 2[0-9]\..*') WHEN NULL THEN FALSE ELSE TRUE END AS is_nextgen_ios,
   	integrationType AS sdk,
   	integrationVersion AS sdk_version,
   	ROUND(SUM(accountableRevenue)) AS revenue,
   	ROUND(SUM("count")) AS soma_services_performed
   FROM "supply-activities-inv"
   WHERE __time >= '2020-01-01 00:00:00'
   	AND __time < '2020-02-01 00:00:00'
   	AND multiplierId NOT IN ('2000', '200')
   	AND activityTypeId IN ('o', 'u', 'p', 'f')
   	AND integrationType IN ('sdkandroid', 'sdkios', 'ubsdkandroid', 'ubsdkios')
   GROUP BY 1, 2, 3, 4, 5
   ```
   
   This query also fails with the same exception, namely that v0 is being used twice, but in the above CASE statements, I believe that calcite cannot infer that the CASE condition is always null.
   I was trying to simplify this query to the point where I would be able to pinpoint what's going on.
   
   Interesitngly, it seems to also have to do with the ELSE clause being the same. For instance consider the followiing two queries. The first will fail the second will work:
   
   
   ```
   SELECT 
   	CASE cityName WHEN NULL THEN FALSE ELSE TRUE END AS col_a,
   	CASE countryName WHEN NULL THEN FALSE ELSE TRUE END AS col_b
   FROM wikipedia
   GROUP BY 1, 2
   ```
   FAILS
   
   ```
   SELECT 
   	CASE cityName WHEN NULL THEN FALSE ELSE TRUE END AS col_a,
   	CASE countryName WHEN NULL THEN TRUE ELSE FALSE END AS col_b
   FROM wikipedia
   GROUP BY 1, 2
   ```
   WORKS!
   
   The only difference is that the TRUE/FALSE in the second case statement is swapped. Now that the two projections don't both evaluate to the same value, the query works because I guess it cannot reduce the expressions down to a common one.

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services

---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org