Reflection is not being reading when "where" clause is used


#1

Hi everyone,
I’m facing an issue when I run the query:
SELECT “YearMonth” AS “YearMonth”,
SUM(“TotalAmountAct”) AS “SUM(TotalAmountAct)”
FROM X
GROUP BY “YearMonth”
ORDER BY “SUM(TotalAmountAct)” DESC

The reflection is use in the execution.
But when I applied a filter with where clause, the reflection is not being reading.

SELECT “YearMonth” AS “YearMonth”,
SUM(“TotalAmountAct”) AS “SUM(TotalAmountAct)”
FROM X
WHERE “YearMonth” BETWEEN ‘201801’ AND ‘201812’
GROUP BY “YearMonth”
ORDER BY “SUM(TotalAmountAct)” DESC

What can be happening? Why when I added the “where” the sql stop using the reflection?

Regards
Rosmar Torres


#2

@Rosmar_Torres, can you provide a profile for the query?


#3

Hi,
I attached the profile for both executions

Reflection is used - Query without filter
f4ca99a9-f418-4ac9-b547-0a51d0a4c4a3.zip (29,6 KB)

Reflection is not used - Query with filter
0a6ec085-83ff-4ce3-8893-deed973c6588.zip (621,1 KB)

Thanks!


#4

Hi @Rosmar_Torres,

The query with the filter that you provided is actually failing because one of the planning phases is taking too long. Do you have an example of the query executing successfully, or does it always fail?


#5

Hi Ben,
Today the “where” condition is running the reflection (I made some changes in the view to simplificate the logic), but when I put in the SQL the sentence LIMIT 10000 appear the error of planning is taking to long. What can be happening? :’(

SQL that runs OK:
SELECT “LastDayMonth” AS “LastDayMonth”,
((sum(TotalAmountAct) - sum(TotalAmountPrev))/ sum(TotalAmountPrev)) AS percentage_change
FROM “SalesComparativeMonth”
WHERE “LastDayMonth” >= ‘2018-06-08 00:00:00’
GROUP BY “LastDayMonth”
ORDER BY percentage_change DESC

SQL that is not finishing planning:
SELECT “LastDayMonth” AS “LastDayMonth”,
((sum(TotalAmountAct) - sum(TotalAmountPrev))/ sum(TotalAmountPrev)) AS percentage_change
FROM “SalesComparativeMonth”
WHERE “LastDayMonth” >= ‘2018-06-08 00:00:00’
GROUP BY “LastDayMonth”
ORDER BY percentage_change DESC
LIMIT 10000


#6

Hi @Rosmar_Torres

Can you please try the below?

Click on Admin-On the left side “cluster”-support-scroll down on the right to support key and enter “planner.experimental.pclean_logical”, click show, disable and save

Please try the query again with the LIMIT clause

Thanks
@balaji.ramaswamy


#7

I disabled the “planner.experimental.pclean_logical”, but I got same error. After execute with the limit the job doesn’t finish the planning. :frowning:


#8

Hi @Rosmar_Torres,

Can you attach that profile?