kgyrtkirk commented on code in PR #14007:
URL: https://github.com/apache/druid/pull/14007#discussion_r1363362711
##########
sql/src/test/resources/calcite/tests/window/wikipediaAggregationsMultipleOrderingDesc.sqlTest:
##########
@@ -10,16 +10,22 @@ sql: |
FROM wikipedia
GROUP BY 1, 2
ORDER BY 1 DESC, 2 DESC
+ LIMIT 2
expectedOperators:
+ - {type: "naiveSort", columns: [{column: "d0", direction: "ASC"}, {column:
"d1", direction: "DESC"}]}
Review Comment:
this is a bit confusing; especially by just looking at the window
operators...from these details nobody could guess what `d0` / `d1` is...
this query is running 3 stages: `windowOperator` -> `groupBy` ->
`table[wikipedia]`
* `groupBy` phase has:
* virtualCol: `v0 = timestamp_floor(\"__time\",'PT1H',null,'UTC')`
* dimension `d0=countryIsoCode`
* dimension `d1=v0`
* at `window` level this means:
* `order by countryIsoCode, timestamp_floor(\"__time\",'PT1H',null,'UTC')
desc`
* which looks good to me; as it will sort by the partition column
`countryIsoCode` first; and then by the other in `DESC`
* next operator uses `d0` for partitioning
* there is another sort to produce the other window:
I believe the PR's branch produces the correct windowed values; however it
may fail to produce correctly ordered outputs; it kinda like disregards the
`ORDER BY 1 DESC, 2 DESC` parts of the query entirely
this is pretty interesting as right now I can't even run this query because
it can't compile the query mostly because of those ordering-s
<details>
<summary>native query plan</summary>
```json
{
"queryType" : "windowOperator",
"dataSource" : {
"type" : "query",
"query" : {
"queryType" : "groupBy",
"dataSource" : {
"type" : "table",
"name" : "wikipedia"
},
"intervals" : {
"type" : "intervals",
"intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
},
"virtualColumns" : [ {
"type" : "expression",
"name" : "v0",
"expression" : "timestamp_floor(\"__time\",'PT1H',null,'UTC')",
"outputType" : "LONG"
} ],
"granularity" : {
"type" : "all"
},
"dimensions" : [ {
"type" : "default",
"dimension" : "countryIsoCode",
"outputName" : "d0",
"outputType" : "STRING"
}, {
"type" : "default",
"dimension" : "v0",
"outputName" : "d1",
"outputType" : "LONG"
} ],
"aggregations" : [ {
"type" : "longSum",
"name" : "a0",
"fieldName" : "delta"
} ],
"limitSpec" : {
"type" : "default",
"columns" : [ {
"dimension" : "d0",
"direction" : "descending",
"dimensionOrder" : {
"type" : "lexicographic"
}
}, {
"dimension" : "d1",
"direction" : "descending",
"dimensionOrder" : {
"type" : "numeric"
}
} ],
"limit" : 2
},
"context" : {
"sqlQueryId" : "d0e44422-b60a-49be-8950-aec76cd8f578",
"vectorize" : "false",
"vectorizeVirtualColumns" : "false",
"windowsAreForClosers" : true
}
}
},
"context" : {
"sqlQueryId" : "d0e44422-b60a-49be-8950-aec76cd8f578",
"vectorize" : "false",
"vectorizeVirtualColumns" : "false",
"windowsAreForClosers" : true
},
"outputSignature" : [ {
"name" : "d0",
"type" : "STRING"
}, {
"name" : "d1",
"type" : "LONG"
}, {
"name" : "a0",
"type" : "LONG"
}, {
"name" : "w0",
"type" : "LONG"
}, {
"name" : "w1",
"type" : "LONG"
} ],
"operatorDefinition" : [ {
"type" : "naiveSort",
"columns" : [ {
"column" : "d0",
"direction" : "ASC"
}, {
"column" : "d1",
"direction" : "DESC"
} ]
}, {
"type" : "naivePartition",
"partitionColumns" : [ "d0" ]
}, {
"type" : "window",
"processor" : {
"type" : "framedAgg",
"frame" : {
"peerType" : "ROWS",
"lowUnbounded" : false,
"lowOffset" : 3,
"uppUnbounded" : false,
"uppOffset" : 2
},
"aggregations" : [ {
"type" : "longSum",
"name" : "w0",
"fieldName" : "a0"
} ]
}
}, {
"type" : "naiveSort",
"columns" : [ {
"column" : "d1",
"direction" : "ASC"
}, {
"column" : "a0",
"direction" : "DESC"
} ]
}, {
"type" : "naivePartition",
"partitionColumns" : [ "d1" ]
}, {
"type" : "window",
"processor" : {
"type" : "rowNumber",
"outputColumn" : "w1"
}
} ],
"granularity" : {
"type" : "all"
},
"intervals" : {
"type" : "LegacySegmentSpec",
"intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
}
}
```
</details>
--
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.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
