adarshsanjeev commented on code in PR #17541:
URL: https://github.com/apache/druid/pull/17541#discussion_r1883502621
##########
extensions-core/multi-stage-query/src/test/quidem/org.apache.druid.msq.quidem.MSQQuidemTest/msqNestedJoinHint.iq:
##########
@@ -0,0 +1,859 @@
+!use druidtest://?componentSupplier=DrillWindowQueryMSQComponentSupplier
+!set outputformat mysql
+
+select w1.cityName, w2.countryName
+from
+(
+ select w3.cityName AS cityName, w4.countryName AS countryName from wikipedia
w3 LEFT JOIN wikipedia w4 ON w3.regionName = w4.regionName
+) w1
+JOIN wikipedia w2 ON w1.cityName = w2.cityName
+where w1.cityName='New York';
+
+[ {
+ "stageNumber" : 0,
+ "definition" : {
+ "id" : "<taskId>_0",
+ "input" : [ {
+ "type" : "table",
+ "dataSource" : "wikipedia",
+ "intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ } ],
+ "processor" : {
+ "type" : "scan",
+ "query" : {
+ "queryType" : "scan",
+ "dataSource" : {
+ "type" : "inputNumber",
+ "inputNumber" : 0
+ },
+ "intervals" : {
+ "type" : "intervals",
+ "intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ },
+ "resultFormat" : "compactedList",
+ "columns" : [ "regionName" ],
+ "context" : {
+ "scanSignature" : "[{\"name\":\"regionName\",\"type\":\"STRING\"}]",
+ "sqlInsertSegmentGranularity" : null,
+ "sqlQueryId" : __SQL_QUERY_ID__
+ "sqlStringifyArrays" : false
+ },
+ "columnTypes" : [ "STRING" ],
+ "granularity" : {
+ "type" : "all"
+ },
+ "legacy" : false
+ }
+ },
+ "signature" : [ {
+ "name" : "__boost",
+ "type" : "LONG"
+ }, {
+ "name" : "regionName",
+ "type" : "STRING"
+ } ],
+ "shuffleSpec" : {
+ "type" : "maxCount",
+ "clusterBy" : {
+ "columns" : [ {
+ "columnName" : "__boost",
+ "order" : "ASCENDING"
+ } ]
+ },
+ "partitions" : 1
+ },
+ "maxWorkerCount" : 1
+ },
+ "phase" : "FINISHED",
+ "workerCount" : 1,
+ "partitionCount" : 1,
+ "shuffle" : "globalSort",
+ "output" : "localStorage",
+ "startTime" : __TIMESTAMP__
+ "duration" : __DURATION__
+ "sort" : true
+}, {
+ "stageNumber" : 1,
+ "definition" : {
+ "id" : "<taskId>_1",
+ "input" : [ {
+ "type" : "table",
+ "dataSource" : "wikipedia",
+ "intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ } ],
+ "processor" : {
+ "type" : "scan",
+ "query" : {
+ "queryType" : "scan",
+ "dataSource" : {
+ "type" : "inputNumber",
+ "inputNumber" : 0
+ },
+ "intervals" : {
+ "type" : "intervals",
+ "intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ },
+ "resultFormat" : "compactedList",
+ "columns" : [ "cityName", "countryName" ],
+ "context" : {
+ "scanSignature" :
"[{\"name\":\"cityName\",\"type\":\"STRING\"},{\"name\":\"countryName\",\"type\":\"STRING\"}]",
+ "sqlInsertSegmentGranularity" : null,
+ "sqlQueryId" : __SQL_QUERY_ID__
+ "sqlStringifyArrays" : false
+ },
+ "columnTypes" : [ "STRING", "STRING" ],
+ "granularity" : {
+ "type" : "all"
+ },
+ "legacy" : false
+ }
+ },
+ "signature" : [ {
+ "name" : "__boost",
+ "type" : "LONG"
+ }, {
+ "name" : "cityName",
+ "type" : "STRING"
+ }, {
+ "name" : "countryName",
+ "type" : "STRING"
+ } ],
+ "shuffleSpec" : {
+ "type" : "maxCount",
+ "clusterBy" : {
+ "columns" : [ {
+ "columnName" : "__boost",
+ "order" : "ASCENDING"
+ } ]
+ },
+ "partitions" : 1
+ },
+ "maxWorkerCount" : 1
+ },
+ "phase" : "FINISHED",
+ "workerCount" : 1,
+ "partitionCount" : 1,
+ "shuffle" : "globalSort",
+ "output" : "localStorage",
+ "startTime" : __TIMESTAMP__
+ "duration" : __DURATION__
+ "sort" : true
+}, {
+ "stageNumber" : 2,
+ "definition" : {
+ "id" : "<taskId>_2",
+ "input" : [ {
+ "type" : "table",
+ "dataSource" : "wikipedia",
+ "intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ],
+ "filter" : {
+ "type" : "equals",
+ "column" : "cityName",
+ "matchValueType" : "STRING",
+ "matchValue" : "New York"
+ },
+ "filterFields" : [ "cityName" ]
+ }, {
+ "type" : "stage",
+ "stage" : 0
+ }, {
+ "type" : "stage",
+ "stage" : 1
+ } ],
+ "broadcast" : [ 1, 2 ],
+ "processor" : {
+ "type" : "scan",
+ "query" : {
+ "queryType" : "scan",
+ "dataSource" : {
+ "type" : "join",
+ "left" : {
+ "type" : "join",
+ "left" : {
+ "type" : "inputNumber",
+ "inputNumber" : 0
+ },
+ "right" : {
+ "type" : "inputNumber",
+ "inputNumber" : 1
+ },
+ "rightPrefix" : "j0.",
+ "condition" : "(\"regionName\" == \"j0.regionName\")",
+ "joinType" : "LEFT",
+ "preferredJoinAlgorithm" : "broadcast"
+ },
+ "right" : {
+ "type" : "inputNumber",
+ "inputNumber" : 2
+ },
+ "rightPrefix" : "_j0.",
+ "condition" : "(\"cityName\" == \"_j0.cityName\")",
+ "joinType" : "INNER",
+ "preferredJoinAlgorithm" : "broadcast"
+ },
+ "intervals" : {
+ "type" : "intervals",
+ "intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ },
+ "virtualColumns" : [ {
+ "type" : "expression",
+ "name" : "v0",
+ "expression" : "'New York'",
+ "outputType" : "STRING"
+ } ],
+ "resultFormat" : "compactedList",
+ "filter" : {
+ "type" : "equals",
+ "column" : "cityName",
+ "matchValueType" : "STRING",
+ "matchValue" : "New York"
+ },
+ "columns" : [ "v0", "_j0.countryName" ],
+ "context" : {
+ "__user" : null,
+ "finalize" : true,
+ "maxParseExceptions" : 0,
+ "scanSignature" :
"[{\"name\":\"v0\",\"type\":\"STRING\"},{\"name\":\"_j0.countryName\",\"type\":\"STRING\"}]",
+ "sqlQueryId" : __SQL_QUERY_ID__
+ "sqlStringifyArrays" : false,
+ "windowFunctionOperatorTransformation" : true
+ },
+ "columnTypes" : [ "STRING", "STRING" ],
+ "granularity" : {
+ "type" : "all"
+ },
+ "legacy" : false
+ }
+ },
+ "signature" : [ {
+ "name" : "__boost",
+ "type" : "LONG"
+ }, {
+ "name" : "v0",
+ "type" : "STRING"
+ }, {
+ "name" : "_j0.countryName",
+ "type" : "STRING"
+ } ],
+ "shuffleSpec" : {
+ "type" : "maxCount",
+ "clusterBy" : {
+ "columns" : [ {
+ "columnName" : "__boost",
+ "order" : "ASCENDING"
+ } ]
+ },
+ "partitions" : 1
+ },
+ "maxWorkerCount" : 1
+ },
+ "phase" : "FINISHED",
+ "workerCount" : 1,
+ "partitionCount" : 1,
+ "shuffle" : "globalSort",
+ "output" : "localStorage",
+ "startTime" : __TIMESTAMP__
+ "duration" : __DURATION__
+ "sort" : true
+} ]
+!msqPlan
+
+select w1.cityName, w2.countryName
+from
+(
+ select /*+ broadcast */ w3.cityName AS cityName, w4.countryName AS
countryName from wikipedia w3 LEFT JOIN wikipedia w4 ON w3.regionName =
w4.regionName
+) w1
+JOIN wikipedia w2 ON w1.cityName = w2.cityName
+where w1.cityName='New York';
+
+LogicalJoin:[[broadcast inheritPath:[0]]]
+
+!hints
+
+[ {
+ "stageNumber" : 0,
+ "definition" : {
+ "id" : "<taskId>_0",
+ "input" : [ {
+ "type" : "table",
+ "dataSource" : "wikipedia",
+ "intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ } ],
+ "processor" : {
+ "type" : "scan",
+ "query" : {
+ "queryType" : "scan",
+ "dataSource" : {
+ "type" : "inputNumber",
+ "inputNumber" : 0
+ },
+ "intervals" : {
+ "type" : "intervals",
+ "intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ },
+ "resultFormat" : "compactedList",
+ "columns" : [ "regionName" ],
+ "context" : {
+ "scanSignature" : "[{\"name\":\"regionName\",\"type\":\"STRING\"}]",
+ "sqlInsertSegmentGranularity" : null,
+ "sqlQueryId" : __SQL_QUERY_ID__
+ "sqlStringifyArrays" : false
+ },
+ "columnTypes" : [ "STRING" ],
+ "granularity" : {
+ "type" : "all"
+ },
+ "legacy" : false
+ }
+ },
+ "signature" : [ {
+ "name" : "__boost",
+ "type" : "LONG"
+ }, {
+ "name" : "regionName",
+ "type" : "STRING"
+ } ],
+ "shuffleSpec" : {
+ "type" : "maxCount",
+ "clusterBy" : {
+ "columns" : [ {
+ "columnName" : "__boost",
+ "order" : "ASCENDING"
+ } ]
+ },
+ "partitions" : 1
+ },
+ "maxWorkerCount" : 1
+ },
+ "phase" : "FINISHED",
+ "workerCount" : 1,
+ "partitionCount" : 1,
+ "shuffle" : "globalSort",
+ "output" : "localStorage",
+ "startTime" : __TIMESTAMP__
+ "duration" : __DURATION__
+ "sort" : true
+}, {
+ "stageNumber" : 1,
+ "definition" : {
+ "id" : "<taskId>_1",
+ "input" : [ {
+ "type" : "table",
+ "dataSource" : "wikipedia",
+ "intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ } ],
+ "processor" : {
+ "type" : "scan",
+ "query" : {
+ "queryType" : "scan",
+ "dataSource" : {
+ "type" : "inputNumber",
+ "inputNumber" : 0
+ },
+ "intervals" : {
+ "type" : "intervals",
+ "intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ },
+ "resultFormat" : "compactedList",
+ "columns" : [ "cityName", "countryName" ],
+ "context" : {
+ "scanSignature" :
"[{\"name\":\"cityName\",\"type\":\"STRING\"},{\"name\":\"countryName\",\"type\":\"STRING\"}]",
+ "sqlInsertSegmentGranularity" : null,
+ "sqlQueryId" : __SQL_QUERY_ID__
+ "sqlStringifyArrays" : false
+ },
+ "columnTypes" : [ "STRING", "STRING" ],
+ "granularity" : {
+ "type" : "all"
+ },
+ "legacy" : false
+ }
+ },
+ "signature" : [ {
+ "name" : "__boost",
+ "type" : "LONG"
+ }, {
+ "name" : "cityName",
+ "type" : "STRING"
+ }, {
+ "name" : "countryName",
+ "type" : "STRING"
+ } ],
+ "shuffleSpec" : {
+ "type" : "maxCount",
+ "clusterBy" : {
+ "columns" : [ {
+ "columnName" : "__boost",
+ "order" : "ASCENDING"
+ } ]
+ },
+ "partitions" : 1
+ },
+ "maxWorkerCount" : 1
+ },
+ "phase" : "FINISHED",
+ "workerCount" : 1,
+ "partitionCount" : 1,
+ "shuffle" : "globalSort",
+ "output" : "localStorage",
+ "startTime" : __TIMESTAMP__
+ "duration" : __DURATION__
+ "sort" : true
+}, {
+ "stageNumber" : 2,
+ "definition" : {
+ "id" : "<taskId>_2",
+ "input" : [ {
+ "type" : "table",
+ "dataSource" : "wikipedia",
+ "intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ],
+ "filter" : {
+ "type" : "equals",
+ "column" : "cityName",
+ "matchValueType" : "STRING",
+ "matchValue" : "New York"
+ },
+ "filterFields" : [ "cityName" ]
+ }, {
+ "type" : "stage",
+ "stage" : 0
+ }, {
+ "type" : "stage",
+ "stage" : 1
+ } ],
+ "broadcast" : [ 1, 2 ],
+ "processor" : {
+ "type" : "scan",
+ "query" : {
+ "queryType" : "scan",
+ "dataSource" : {
+ "type" : "join",
+ "left" : {
+ "type" : "join",
+ "left" : {
+ "type" : "inputNumber",
+ "inputNumber" : 0
+ },
+ "right" : {
+ "type" : "inputNumber",
+ "inputNumber" : 1
+ },
+ "rightPrefix" : "j0.",
+ "condition" : "(\"regionName\" == \"j0.regionName\")",
+ "joinType" : "LEFT",
+ "preferredJoinAlgorithm" : "broadcast"
+ },
+ "right" : {
+ "type" : "inputNumber",
+ "inputNumber" : 2
+ },
+ "rightPrefix" : "_j0.",
+ "condition" : "(\"cityName\" == \"_j0.cityName\")",
+ "joinType" : "INNER",
+ "preferredJoinAlgorithm" : "broadcast"
+ },
+ "intervals" : {
+ "type" : "intervals",
+ "intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ },
+ "virtualColumns" : [ {
+ "type" : "expression",
+ "name" : "v0",
+ "expression" : "'New York'",
+ "outputType" : "STRING"
+ } ],
+ "resultFormat" : "compactedList",
+ "filter" : {
+ "type" : "equals",
+ "column" : "cityName",
+ "matchValueType" : "STRING",
+ "matchValue" : "New York"
+ },
+ "columns" : [ "v0", "_j0.countryName" ],
+ "context" : {
+ "__user" : null,
+ "finalize" : true,
+ "maxParseExceptions" : 0,
+ "scanSignature" :
"[{\"name\":\"v0\",\"type\":\"STRING\"},{\"name\":\"_j0.countryName\",\"type\":\"STRING\"}]",
+ "sqlQueryId" : __SQL_QUERY_ID__
+ "sqlStringifyArrays" : false,
+ "windowFunctionOperatorTransformation" : true
+ },
+ "columnTypes" : [ "STRING", "STRING" ],
+ "granularity" : {
+ "type" : "all"
+ },
+ "legacy" : false
+ }
+ },
+ "signature" : [ {
+ "name" : "__boost",
+ "type" : "LONG"
+ }, {
+ "name" : "v0",
+ "type" : "STRING"
+ }, {
+ "name" : "_j0.countryName",
+ "type" : "STRING"
+ } ],
+ "shuffleSpec" : {
+ "type" : "maxCount",
+ "clusterBy" : {
+ "columns" : [ {
+ "columnName" : "__boost",
+ "order" : "ASCENDING"
+ } ]
+ },
+ "partitions" : 1
+ },
+ "maxWorkerCount" : 1
+ },
+ "phase" : "FINISHED",
+ "workerCount" : 1,
+ "partitionCount" : 1,
+ "shuffle" : "globalSort",
+ "output" : "localStorage",
+ "startTime" : __TIMESTAMP__
+ "duration" : __DURATION__
+ "sort" : true
+} ]
+!msqPlan
+
+select /*+ sort_merge */ w1.cityName, w2.countryName
+from
+(
+ select /*+ broadcast */ w3.cityName AS cityName, w4.countryName AS
countryName from wikipedia w3 LEFT JOIN wikipedia w4 ON w3.regionName =
w4.regionName
+) w1
+JOIN wikipedia w2 ON w1.cityName = w2.cityName
+where w1.cityName='New York';
+
+[ {
+ "stageNumber" : 0,
+ "definition" : {
+ "id" : "<taskId>_0",
+ "input" : [ {
+ "type" : "table",
+ "dataSource" : "wikipedia",
+ "intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ } ],
+ "processor" : {
+ "type" : "scan",
+ "query" : {
+ "queryType" : "scan",
+ "dataSource" : {
+ "type" : "inputNumber",
+ "inputNumber" : 0
+ },
+ "intervals" : {
+ "type" : "intervals",
+ "intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ },
+ "resultFormat" : "compactedList",
+ "columns" : [ "regionName" ],
+ "context" : {
+ "scanSignature" : "[{\"name\":\"regionName\",\"type\":\"STRING\"}]",
+ "sqlInsertSegmentGranularity" : null,
+ "sqlQueryId" : __SQL_QUERY_ID__
+ "sqlStringifyArrays" : false
+ },
+ "columnTypes" : [ "STRING" ],
+ "granularity" : {
+ "type" : "all"
+ },
+ "legacy" : false
+ }
+ },
+ "signature" : [ {
+ "name" : "__boost",
+ "type" : "LONG"
+ }, {
+ "name" : "regionName",
+ "type" : "STRING"
+ } ],
+ "shuffleSpec" : {
+ "type" : "maxCount",
+ "clusterBy" : {
+ "columns" : [ {
+ "columnName" : "__boost",
+ "order" : "ASCENDING"
+ } ]
+ },
+ "partitions" : 1
+ },
+ "maxWorkerCount" : 1
+ },
+ "phase" : "FINISHED",
+ "workerCount" : 1,
+ "partitionCount" : 1,
+ "shuffle" : "globalSort",
+ "output" : "localStorage",
+ "startTime" : __TIMESTAMP__
+ "duration" : __DURATION__
+ "sort" : true
+}, {
+ "stageNumber" : 1,
+ "definition" : {
+ "id" : "<taskId>_1",
+ "input" : [ {
+ "type" : "table",
+ "dataSource" : "wikipedia",
+ "intervals" : [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ]
+ }, {
+ "type" : "stage",
+ "stage" : 0
+ } ],
+ "broadcast" : [ 1 ],
+ "processor" : {
+ "type" : "scan",
+ "query" : {
+ "queryType" : "scan",
+ "dataSource" : {
+ "type" : "join",
+ "left" : {
+ "type" : "inputNumber",
+ "inputNumber" : 0
+ },
+ "right" : {
+ "type" : "inputNumber",
+ "inputNumber" : 1
+ },
+ "rightPrefix" : "j0.",
+ "condition" : "(\"regionName\" == \"j0.regionName\")",
+ "joinType" : "LEFT",
+ "preferredJoinAlgorithm" : "broadcast"
Review Comment:
Changed
##########
sql/src/main/java/org/apache/druid/sql/calcite/planner/QueryUtils.java:
##########
@@ -55,4 +58,20 @@ public static List<ColumnMapping> buildColumnMappings(
return columnMappings;
}
+
+ public static JoinAlgorithm getJoinAlgorithm(Join join, PlannerContext
plannerContext)
+ {
+ RelHint closestHint = null;
+ for (RelHint hint : join.getHints()) {
+ if (closestHint == null || hint.inheritPath.size() <
closestHint.inheritPath.size()) {
+ closestHint = hint;
+ }
Review Comment:
Added a check here
--
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]
