You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by GitBox <gi...@apache.org> on 2020/05/28 14:36:30 UTC
[GitHub] [druid] suneet-s commented on a change in pull request #9941: Prevent JOIN reducing to a JOIN with constant in the ON condition
suneet-s commented on a change in pull request #9941:
URL: https://github.com/apache/druid/pull/9941#discussion_r431884081
##########
File path: sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
##########
@@ -13884,6 +13885,318 @@ public void testQueryContextOuterLimit() throws Exception
);
}
+ @Test
+ public void testCountOnSemiJoinSingleColumn() throws Exception
+ {
+ testQuery(
+ "SELECT dim1 FROM foo WHERE dim1 IN (SELECT dim1 FROM foo WHERE dim1 = '10.1')\n",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(
+ join(
+ new TableDataSource(CalciteTests.DATASOURCE1),
+ new QueryDataSource(
+ GroupByQuery.builder()
+ .setDataSource(CalciteTests.DATASOURCE1)
+ .setInterval(querySegmentSpec(Filtration.eternity()))
+ .setDimFilter(
+ selector("dim1", "10.1", null)
+ )
+ .setGranularity(Granularities.ALL)
+ .setDimensions(dimensions(new DefaultDimensionSpec("dim1", "d0")))
+ .setContext(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ "j0.",
+ equalsCondition(DruidExpression.fromColumn("dim1"), DruidExpression.fromColumn("j0.d0")),
+ JoinType.INNER
+ )
+ )
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(expressionVirtualColumn("v0", "\'10.1\'", ValueType.STRING))
+ .columns("v0")
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"10.1"}
+ )
+ );
+ }
+
+ @Test
+ public void testLeftJoinOnTwoInlineDataSourcesWithTimeFilter() throws Exception
+ {
+ testQuery(
+ "with abc as\n"
+ + "(\n"
+ + " SELECT dim1, \"__time\", m1 from foo WHERE \"dim1\" = '10.1' AND \"__time\" >= '1999'\n"
+ + ")\n"
+ + "SELECT t1.dim1, t1.\"__time\" from abc as t1 LEFT JOIN abc as t2 on t1.dim1 = t2.dim1 WHERE t1.dim1 = '10.1'\n",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(
+ join(
+ new QueryDataSource(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(
+ querySegmentSpec(
+ Intervals.utc(
+ DateTimes.of("1999-01-01").getMillis(),
+ JodaUtils.MAX_INSTANT
+ )
+ )
+ )
+ .filters(new SelectorDimFilter("dim1", "10.1", null))
+ .virtualColumns(expressionVirtualColumn("v0", "\'10.1\'", ValueType.STRING))
+ .columns(ImmutableList.of("__time", "v0"))
+ .resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ new QueryDataSource(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(
+ querySegmentSpec(
+ Intervals.utc(
+ DateTimes.of("1999-01-01").getMillis(),
+ JodaUtils.MAX_INSTANT
+ )
+ )
+ )
+ .filters(new SelectorDimFilter("dim1", "10.1", null))
+ .virtualColumns(expressionVirtualColumn("v0", "\'10.1\'", ValueType.STRING))
+ .columns(ImmutableList.of("v0"))
+ .resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ "j0.",
+ equalsCondition(DruidExpression.fromColumn("v0"), DruidExpression.fromColumn("j0.v0")),
+ JoinType.LEFT
+ )
+ )
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(expressionVirtualColumn("_v0", "\'10.1\'", ValueType.STRING))
+ .columns("__time", "_v0")
+ .filters(new SelectorDimFilter("v0", "10.1", null))
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"10.1", 946771200000L}
+ )
+ );
+ }
+
+ @Test
+ public void testLeftJoinOnTwoInlineDataSourcesWithOuterWhere() throws Exception
+ {
+ testQuery(
+ "with abc as\n"
+ + "(\n"
+ + " SELECT dim1, \"__time\", m1 from foo WHERE \"dim1\" = '10.1'\n"
+ + ")\n"
+ + "SELECT t1.dim1, t1.\"__time\" from abc as t1 LEFT JOIN abc as t2 on t1.dim1 = t2.dim1 WHERE t1.dim1 = '10.1'\n",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(
+ join(
+ new QueryDataSource(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .filters(new SelectorDimFilter("dim1", "10.1", null))
+ .virtualColumns(expressionVirtualColumn("v0", "\'10.1\'", ValueType.STRING))
+ .columns(ImmutableList.of("__time", "v0"))
+ .resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ new QueryDataSource(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .filters(new SelectorDimFilter("dim1", "10.1", null))
+ .columns(ImmutableList.of("dim1"))
+ .resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ "j0.",
+ equalsCondition(DruidExpression.fromColumn("v0"), DruidExpression.fromColumn("j0.dim1")),
+ JoinType.LEFT
+ )
+ )
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(expressionVirtualColumn("_v0", "\'10.1\'", ValueType.STRING))
+ .columns("__time", "_v0")
+ .filters(new SelectorDimFilter("v0", "10.1", null))
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"10.1", 946771200000L}
+ )
+ );
+ }
+
+ @Test
+ public void testLeftJoinOnTwoInlineDataSources() throws Exception
+ {
+ testQuery(
+ "with abc as\n"
+ + "(\n"
+ + " SELECT dim1, \"__time\", m1 from foo WHERE \"dim1\" = '10.1'\n"
+ + ")\n"
+ + "SELECT t1.dim1, t1.\"__time\" from abc as t1 LEFT JOIN abc as t2 on t1.dim1 = t2.dim1\n",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(
+ join(
+ new QueryDataSource(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .filters(new SelectorDimFilter("dim1", "10.1", null))
+ .virtualColumns(expressionVirtualColumn("v0", "\'10.1\'", ValueType.STRING))
+ .columns(ImmutableList.of("__time", "v0"))
+ .resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ new QueryDataSource(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .filters(new SelectorDimFilter("dim1", "10.1", null))
+ .columns(ImmutableList.of("dim1"))
+ .resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ "j0.",
+ equalsCondition(DruidExpression.fromColumn("v0"), DruidExpression.fromColumn("j0.dim1")),
+ JoinType.LEFT
+ )
+ )
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(expressionVirtualColumn("_v0", "\'10.1\'", ValueType.STRING))
+ .columns("__time", "_v0")
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"10.1", 946771200000L}
+ )
+ );
+ }
+
+ @Test
+ public void testInnerJoinOnTwoInlineDataSourcesWithOuterWhere() throws Exception
+ {
+ testQuery(
+ "with abc as\n"
+ + "(\n"
+ + " SELECT dim1, \"__time\", m1 from foo WHERE \"dim1\" = '10.1'\n"
+ + ")\n"
+ + "SELECT t1.dim1, t1.\"__time\" from abc as t1 INNER JOIN abc as t2 on t1.dim1 = t2.dim1 WHERE t1.dim1 = '10.1'\n",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(
+ join(
+ new QueryDataSource(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .filters(new SelectorDimFilter("dim1", "10.1", null))
+ .virtualColumns(expressionVirtualColumn("v0", "\'10.1\'", ValueType.STRING))
+ .columns(ImmutableList.of("__time", "v0"))
+ .resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ new QueryDataSource(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .filters(new SelectorDimFilter("dim1", "10.1", null))
+ .columns(ImmutableList.of("dim1"))
+ .resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ "j0.",
+ equalsCondition(DruidExpression.fromColumn("v0"), DruidExpression.fromColumn("j0.dim1")),
+ JoinType.INNER
+ )
+ )
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(expressionVirtualColumn("_v0", "\'10.1\'", ValueType.STRING))
+ .columns("__time", "_v0")
+ .filters(new NotDimFilter(new SelectorDimFilter("v0", null, null)))
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"10.1", 946771200000L}
+ )
+ );
+ }
+
+ @Test
+ public void testInnerJoinOnTwoInlineDataSources() throws Exception
+ {
+ testQuery(
+ "with abc as\n"
+ + "(\n"
+ + " SELECT dim1, \"__time\", m1 from foo WHERE \"dim1\" = '10.1'\n"
+ + ")\n"
+ + "SELECT t1.dim1, t1.\"__time\" from abc as t1 INNER JOIN abc as t2 on t1.dim1 = t2.dim1\n",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(
+ join(
+ new QueryDataSource(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .filters(new SelectorDimFilter("dim1", "10.1", null))
+ .virtualColumns(expressionVirtualColumn("v0", "\'10.1\'", ValueType.STRING))
+ .columns(ImmutableList.of("__time", "v0"))
+ .resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ new QueryDataSource(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .filters(new SelectorDimFilter("dim1", "10.1", null))
+ .columns(ImmutableList.of("dim1"))
+ .resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ "j0.",
+ equalsCondition(DruidExpression.fromColumn("v0"), DruidExpression.fromColumn("j0.dim1")),
+ JoinType.INNER
+ )
+ )
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(expressionVirtualColumn("_v0", "\'10.1\'", ValueType.STRING))
+ .columns("__time", "_v0")
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"10.1", 946771200000L}
+ )
+ );
+ }
+
Review comment:
Can you add a test that fails a query with the shape defined in #9942
##########
File path: sql/src/main/java/org/apache/druid/sql/calcite/planner/Rules.java
##########
@@ -130,7 +130,6 @@
ReduceExpressionsRule.FILTER_INSTANCE,
ReduceExpressionsRule.CALC_INSTANCE,
ReduceExpressionsRule.WINDOW_INSTANCE,
- ReduceExpressionsRule.JOIN_INSTANCE,
Review comment:
What are your thoughts on having a list of rules that are excluded in the codebase somewhere (maybe just commented out in here). That way we can add documentation in the code about why each rule was excluded.
If any of the reasoning for why a rule was removed from this list changes, it would be easy to add back the rules to transform to more efficient queries.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org