[
https://issues.apache.org/jira/browse/CALCITE-6123?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17793674#comment-17793674
]
Stamatis Zampetakis commented on CALCITE-6123:
----------------------------------------------
I can reproduce the problem consistently by performing the following steps:
* Initialize Druid and index the foodmart dataset as per instructions in
[calcite-druid-dataset|https://github.com/zabetak/calcite-druid-dataset/].
* Open [Druid's SQL query
client|http://localhost:8888/unified-console.html#query]
* Run the following foodmart queries in the order they appear below.
+Q1+
{code:sql}
select "store_state", "brand_name", "A"
from (
select "store_state", "brand_name", sum("store_sales") + sum("store_cost") as
A
from "foodmart"
group by "store_state", "brand_name")
order by "brand_name", "store_state"
limit 5
{code}
+RQ1+
{noformat}
"store_state","brand_name","A"
"CA","ADJ","222.1524"
"OR","ADJ","186.60359999999997"
"WA","ADJ","216.9912"
"CA","Akron","250.349"
"OR","Akron","278.6972"
{noformat}
+Q2+
{code:sql}
select "store_state", "brand_name", "A"
from (
select "store_state", "brand_name", sum("store_sales") - sum("store_cost") as
A
from "foodmart"
group by "store_state", "brand_name")
order by "A"
limit 5
{code}
+RAQ2: results of Q2, when the latter is executed after Q1+
{noformat}
"store_state","brand_name","A"
"OR","ADJ","83.8764"
"WA","Akron","85.84019999999998"
"OR","American","86.78979999999999"
"WA","ADJ","97.6488"
"CA","ADJ","98.00759999999997"
{noformat}
+RBQ2: results of Q2, when the latter is executed before Q1+
{noformat}
"store_state","brand_name","A"
"CA","King","21.463200000000008"
"OR","Symphony","32.176000000000016"
"CA","Toretti","32.24649999999999"
"WA","King","34.610400000000006"
"OR","Toretti","36.3"
{noformat}
The results in RAQ2 are wrong and this result set occurs only if Q1 has run
before.
The queries above were taken from the [DruidAdapterIT
class|https://github.com/apache/calcite/blob/d3ab0bc8e4d4c9ebc0fc4e33ce478d276f5d11e4/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java]
and in particular from the following methods:
* Q1, testInterleaveBetweenAggregateAndGroupOrderByOnDimension
* Q2, testInterleaveBetweenAggregateAndGroupOrderByOnMetrics
The problem can be reproduced also via Calcite by running the respective tests
in sequence:
{noformat}
./gradlew :druid:test --tests
DruidAdapterIT.testInterleaveBetweenAggregateAndGroupOrderByOnDimension
-Dcalcite.test.druid
./gradlew :druid:test --tests
DruidAdapterIT.testInterleaveBetweenAggregateAndGroupOrderByOnMetrics
-Dcalcite.test.druid
FAILURE 2.0sec, org.apache.calcite.test.DruidAdapterIT >
testInterleaveBetweenAggregateAndGroupOrderByOnMetrics()
java.lang.AssertionError:
Expected: "store_state=CA; brand_name=King; A=21.4632\nstore_state=OR;
brand_name=Symphony; A=32.176\nstore_state=CA; brand_name=Toretti;
A=32.2465\nstore_state=WA; brand_name=King; A=34.6104\nstore_state=OR;
brand_name=Toretti; A=36.3"
but: was "store_state=OR; brand_name=ADJ; A=83.8764\nstore_state=WA;
brand_name=Akron; A=85.8402\nstore_state=OR; brand_name=American;
A=86.7898\nstore_state=WA; brand_name=ADJ; A=97.6488\nstore_state=CA;
brand_name=ADJ; A=98.0076"
at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:18)
at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:6)
at
org.apache.calcite.test.CalciteAssert.lambda$checkResult$6(CalciteAssert.java:453)
at
org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:582)
at
org.apache.calcite.test.CalciteAssert$AssertQuery.lambda$returns$1(CalciteAssert.java:1495)
at
org.apache.calcite.test.CalciteAssert$AssertQuery.withConnection(CalciteAssert.java:1434)
at
org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1493)
at
org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1483)
at
org.apache.calcite.test.CalciteAssert$AssertQuery.returnsOrdered(CalciteAssert.java:1509)
at
org.apache.calcite.test.DruidAdapterIT.testInterleaveBetweenAggregateAndGroupOrderByOnMetrics(DruidAdapterIT.java:2336)
Suppressed: org.apache.calcite.util.TestUtil$ExtraInformation: With
materializationsEnabled=false, limit=-1, sql=select "store_state",
"brand_name", "A" from (
select sum("store_sales")-sum("store_cost") as a, "store_state",
"brand_name"
from "foodmart"
group by "store_state", "brand_name" ) subq
order by "A" limit 5
at org.apache.calcite.util.TestUtil.rethrow(TestUtil.java:389)
at
org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:598)
{noformat}
Druid has various level of caches for speeding up queries and some are on by
default. It turns out that the problem observed here can be avoided by
disabling the [query cache on the Historical
nodes|https://druid.apache.org/docs/latest/querying/using-caching#enabling-query-caching-on-historicals]
by setting {{druid.historical.cache.useCache}} to false. It seems that we are
hitting a Druid bug affecting the query cache implementation but couldn't find
a concrete bug reference describing this problem. I think for the purpose of
this ticket it is enough to run the tests with the Druid cache disabled since
the identified bug has nothing to do with the Calcite code base.
> DruidAdapter2IT is failing
> ---------------------------
>
> Key: CALCITE-6123
> URL: https://issues.apache.org/jira/browse/CALCITE-6123
> Project: Calcite
> Issue Type: Bug
> Components: tests
> Affects Versions: 1.36.0
> Reporter: Benchao Li
> Assignee: Stamatis Zampetakis
> Priority: Major
> Labels: pull-request-available
> Attachments: image-2023-11-19-15-17-53-699.png
>
>
> The test is all failing after
> [https://github.com/apache/calcite/commit/55034513b463c938035e5d2436949bbf734b84b6],
> I'm not sure whether it's related.
> See following jobs:
> * [https://github.com/apache/calcite/actions/runs/6886169664/job/18731435605]
> * [https://github.com/apache/calcite/actions/runs/6885301555/job/18729238762]
> * [https://github.com/apache/calcite/actions/runs/6871630651/job/18688793776]
> * [https://github.com/apache/calcite/actions/runs/6860287671/job/18653876601]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
