Hi, I have the following Druid Query

SELECT 
dim_field_1, 
LOOKUP(CONCAT(dim_field_1, ''), 'lookup_1') AS "lookup_1_val", 
LOOKUP(CONCAT(dim_field_2, ''), 'lookup_2') AS "lookup_2_val", 
LOOKUP(CONCAT(dim_field_2, ''), 'lookup_3') AS "lookup_3_val", 
dim_field_2, 
LOOKUP(CONCAT(dim_field_2, ''), 'lookup_4') AS "lookup_4_val", 
(SUM(cost)) AS "cost"  
FROM test_table
WHERE 
dim_field_2 IN (2040)  
AND __time >= '2024-03-01 00:00:00' AND __time <= '2024-03-31 23:59:59'  
AND dim_field_1 IN ('1608826050', '1608826054', '1608826048', '1608818855', '1608818825', '1609091475', '1608818895', '1608818852', '1608818865', '1608818753', '1608818775', '1608818724', '1609027835', '1608273535', '1608916347', '1608268938', '1608818799', '1608818766', '1608818852', '1609569765', '1608273487', '1609347997', '1609477098', '1608826096', '1609559940', '1608916420')  
GROUP BY 
LOOKUP(CONCAT(dim_field_2, ''), 'lookup_3'), 
dim_field_1, 
LOOKUP(CONCAT(dim_field_1, ''), 'lookup_1'), 
LOOKUP(CONCAT(dim_field_2, ''), 'lookup_3'), 
LOOKUP(CONCAT(dim_field_2, ''), 'lookup_4'), 
dim_field_2

In above query I have added all fields available in the select clause except the field on with aggregation function in group by clause in the different order then the field added to the select clause and in this case this query returns records with 1:M mapping between dim_field_2 field (key) and lookup_2 value, if I change the order of the group by and use the same order in which fields are added in the select clause then query returns records without including 1:M mapping. This seems to be an odd behavior. Could anyone please help here to understand the reason why the above query is returning records with 1:M mapping between dim_field_2 field (key) and lookup_2 value? I have also tried to query the lookup_2 by adding the filter on value field in whare clause with the list of values which I am getting as 1:M mapping in the above query result and this query return result with 1:1 mapping only, this is an expected behavior as Druid Lookups can have 1:1 mapping between the key and value.

Hi Swapnil, I can't answer to the difference in query output when you change the order of fields listed in the group by (that shouldn't matter, they should operate as a grouping key regardless of the order) ... However since you are already grouping by dim_field_1 and dim_field_2, then from a SQL perspective putting the lookup() functions also in the group by clause is redundant. What happens when you only group by dim_field_1 and dim_field_2? Thanks. John

Thanks @John Kowtko for sharing your feedback.

However since you are already grouping by dim_field_1 and dim_field_2, then from a SQL perspective putting the lookup() functions also in the group by clause is redundant.

Yes this is correct, actually this query is not manually prepared and instead of that it is prepared using and application workflow based on the user input and as per my understanding that is why it includes the lookup fields in group by too.

What happens when you only group by dim_field_1 and dim_field_2?

Query returns does not return the records with 1:M mapping dim_field_2 field (key) and lookup_2 value. Also in Druid lookup_2 for the dim_field_2 field (key) only unique values are present however still somehow Druid Query I have shared in the first message returns records with 1:M mapping dim_field_2 field (key) and lookup_2 value. Could you please let me know this could be possible?

Hi Swapnil, Unfortunately it is hard to guess what may be happening without seeing the actual data and being able to run the query against it. I think changing the order of columns in the Group By, if that is causing a change in results then I would think it is a bug and should be looked at. However why that simple switch in ordered is causing this I am curious. Can you tell me why the CONCAT(field, '') expression? That does not seem to have a function ... if NULLs are enabled in this database then CONCAT(null, anything) should evaluate to NULL, which in turn will fail the lookup. If this is just retrying to ensure that the field expression passed to Lookup is recognized as a string datatype, then I guess that's okay ... but not necessary. If you remove the CONCAT() function from the expressions does it change the behavior? Thanks. John

Hi John, thanks for sharing your feedback. Let me check if I can remove CONCAT() for Lookup fields in the query and execute it or not and get back.

Hi Swapnil, ah yes that makes sense. With some of the lookup optimizations it is possible that joins are being used to resolve lookups if it thinks there is only a 1:1 match, and that is creating a cartesian product here.

Hi John thanks, for sharing your feedback. Are you also suspecting that

"injective": true

could be possibly creating the issue because it has been set for the lookup with 1:M mapping between key and value? If yes then I believe the following behavior are supporting it and still bit difficult to understand the possible cause: • Why

lookup_2

returns the value which does not even exist in the lookup? • Why query returns records without 1:M mapping occurrences after using CAST(dim_field_1 as CHAR) instead of CONCAT() function in select and group by fields list? Could you please check above points and share your feedback?

Hi Swapnil, if you are on the most recent Druid release then there are some SQL query rewrite optimizations that could be causing these behaviors when the injective flag is set incorrectly. Try setting injective to false to see if all of the problems clear up.

Thanks for sharing your feedback and confirming that the possible reason for getting records with 1:M mapping in the query result could be injective flag set to true for lookup with 1:M mapping between the key and the field value. I will try to set

"injective": false

for the

lookup_2

with 1:M mapping between key and value field and check the results and get back if with this change still query will return records with 1:M mapping in the result.

Hi Swapnil, Druid 21 is an older version that pre-dates the query rewrite optimization that I know about (which would be in Druid 29). However the injective setting of the Lookup() to me still looks like the most suspicious thing here ... and I just found the doc page that explicitly states that: https://druid.apache.org/docs/latest/querying/lookups#injective-lookups