This message was deleted Apache Druid #general

Join Slack

This message was deleted.

# general

Slackbot

12/28/2022, 11:18 AM

This message was deleted.

John Kowtko

12/28/2022, 7:31 PM

Hi @Rahul, You are using a CASE statement in the grouping key ... it sounds like Druid SQL doesn't like that. Since Machine_Date is the only real field being evaluated in that grouping expression it makes sense that it is allowed as the GROUP BY column though, and it looks like it should produce the correct result ... does it not? If I were writing this query, then if the underlying table 'metadata' isn't too large, what I would probable first try doing for readability is to put the entire case expression in a CTE, for example:

Copy code

With tmp as (
 select case when TIME_PARSE("Machine_Date", NULL, 'Asia/Kolkata') < TIME_FORMAT(CURRENT_TIMESTAMP,NULL,'Asia/Kolkata')  
              then TIME_FORMAT((TIME_PARSE("Machine_Date", NULL, 'Asia/Kolkata')),'yyyy-MM-dd','Asia/Kolkata') 
              else 'null' end) as a1,
        Machine_count
   From metadata
)
 select a1, sum(Machine_count)
   From tmp
  group by a1

If that does not work, and/or your initial query is not producing the correct result, then please explain a bit more what you are trying to do ... and also provide a count(*) and count(distinct Machine_Date) for the table. Thanks. John

John Kowtko

12/28/2022, 10:24 PM

I just re-read your problem statement ... you cannot change the phrase "group by 2"? If so, then the above query could be adjusted as:

Copy code

With tmp as (
 select case when TIME_PARSE("Machine_Date", NULL, 'Asia/Kolkata') < TIME_FORMAT(CURRENT_TIMESTAMP,NULL,'Asia/Kolkata')  
              then TIME_FORMAT((TIME_PARSE("Machine_Date", NULL, 'Asia/Kolkata')),'yyyy-MM-dd','Asia/Kolkata') 
              else 'null' end) as a1,
        Machine_count
   From metadata
)
 select sum(Machine_count), a1
   From tmp
  group by 2

you could also write the CTE as an inline table:

Copy code

select sum(Machine_count), a1
   From (
         select case when TIME_PARSE("Machine_Date", NULL, 'Asia/Kolkata') < TIME_FORMAT(CURRENT_TIMESTAMP,NULL,'Asia/Kolkata')  
                     then TIME_FORMAT((TIME_PARSE("Machine_Date", NULL, 'Asia/Kolkata')),'yyyy-MM-dd','Asia/Kolkata') 
                     else 'null' end) as a1,
                Machine_count
           From metadata
        )
  group by 2

not sure if any of this will work with whatever SQL syntax restrictions you are working with ...

John Kowtko

12/29/2022, 12:35 AM

cc: @Rahul Sharma

Vadim

12/29/2022, 7:05 AM

actually it seems that if you substitute the

with the full case expression it works. Not sure what is up. This is a bug as far as I can see (probably in calcite with how it handles the

shortcut)

Vadim

01/03/2023, 10:43 PM

I filed: https://github.com/apache/druid/issues/13626

Rahul Sharma

01/06/2023, 6:23 AM

Thank you all for replying I have one more issue with current time stamp This is normal date column which is working fine SELECT SUM("final_price"), (case when "final_price">50 then TIME_PARSE("date_purchased",null,'Asia/Kolkata') else 0 end) as a1 FROM ecommerce GROUP BY 2 This is current time column which is not working is there any issue with CURRENT_TIMESTAMP with case My query which is not working SELECT SUM("final_price"), (case when "final_price">50 then TIME_FORMAT(CURRENT_TIMESTAMP,'dd-MM-yyyy','Asia/Kolkata') else 0 end) as a1 FROM ecommerce GROUP BY 2 Let me know if i am doing anything wrong. Group by 2 is added by some other lib i am using cube so group by formula i can't put it

2 Views

Open in Slack

Previous Next