zabetak commented on code in PR #6673: URL: https://github.com/apache/hive/pull/6673#discussion_r3726878532
########## data/scripts/q_test_tpcds_external_tables_schema.postgres.sql: ########## @@ -0,0 +1,712 @@ +CREATE EXTERNAL TABLE IF NOT EXISTS `call_center`( + `cc_call_center_sk` int, + `cc_call_center_id` string, + `cc_rec_start_date` string, + `cc_rec_end_date` string, + `cc_closed_date_sk` int, + `cc_open_date_sk` int, + `cc_name` string, + `cc_class` string, + `cc_employees` int, + `cc_sq_ft` int, + `cc_hours` string, + `cc_manager` string, + `cc_mkt_id` int, + `cc_mkt_class` string, + `cc_mkt_desc` string, + `cc_market_manager` string, + `cc_division` int, + `cc_division_name` string, + `cc_company` int, + `cc_company_name` string, + `cc_street_number` string, + `cc_street_name` string, + `cc_street_type` string, + `cc_suite_number` string, + `cc_city` string, + `cc_county` string, + `cc_state` string, + `cc_zip` string, + `cc_country` string, + `cc_gmt_offset` decimal(5,2), + `cc_tax_percentage` decimal(5,2)) +STORED BY +'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "POSTGRES", + "hive.sql.jdbc.driver" = "org.postgresql.Driver", + "hive.sql.jdbc.url" = "jdbc:postgresql://localhost:5432/qtestDB", + "hive.sql.dbcp.username" = "qtestuser", + "hive.sql.dbcp.password" = "qtestpassword", Review Comment: Instead of hardcoding the JDBC URL, user, pwd, can we take advantage the system properties introduced by HIVE-29032? ########## itests/util/src/main/java/org/apache/hadoop/hive/cli/control/CoreJdbcCliDriver.java: ########## @@ -0,0 +1,100 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.hadoop.hive.cli.control; + +import org.apache.commons.io.FileUtils; +import org.apache.hadoop.hive.ql.externalDB.AbstractExternalDB; +import org.apache.hadoop.hive.ql.QTestUtil; +import org.junit.After; +import org.junit.AfterClass; +import org.junit.Before; +import org.junit.BeforeClass; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; + +import java.io.File; +import java.nio.charset.StandardCharsets; +import java.nio.file.Files; +import java.nio.file.Path; +import java.nio.file.Paths; + +public class CoreJdbcCliDriver extends CoreCliDriver { Review Comment: We could avoid adding another CliDriver extension by putting the setup and cleanup logic directly inside the test class (i.e., `TestMiniLlapLocalPostgresJdbcCliDriver`). What do you think? ########## itests/util/src/main/java/org/apache/hadoop/hive/cli/control/JdbcCliConfig.java: ########## @@ -0,0 +1,29 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.hadoop.hive.cli.control; + +import org.apache.hadoop.hive.ql.qoption.QTestDatabaseHandler; + +public interface JdbcCliConfig { Review Comment: Possibly this class can go away. In terms of design it does not help much since we are force to do an `instanceof` in order to use it. It would be roughly the same to do an `instanceof` directly on `MiniLlapLocalPostgresJdbcCliConfig`. ########## itests/util/src/main/java/org/apache/hadoop/hive/cli/control/CliConfigs.java: ########## @@ -201,7 +202,46 @@ public MiniLlapLocalCliConfig() { } } } - + + public static class MiniLlapLocalPostgresJdbcCliConfig extends AbstractCliConfig implements JdbcCliConfig { + private final QTestDatabaseHandler.DatabaseType databaseType; + private final String jdbcInitScript; + private final String externalTablesInitScript; + + public MiniLlapLocalPostgresJdbcCliConfig() { + super(CoreJdbcCliDriver.class); + try { + databaseType = QTestDatabaseHandler.DatabaseType.POSTGRES; + jdbcInitScript = "q_test_tpcds_schema.postgres.sql"; + externalTablesInitScript = "q_test_tpcds_external_tables_schema.postgres.sql"; + + setQueryDir("ql/src/test/queries/clientpositive/perf"); + setLogDir("itests/qtest/target/qfile-results/clientpositive/jdbc/postgres"); + setResultsDir("ql/src/test/results/clientpositive/jdbc/postgres"); + setHiveConfDir("data/conf/jdbc"); Review Comment: Should we use `setCustomConfigValueMap` method instead of introducing new conf dirs/files by. This suggestion makes sense if we just want to override a few properties and otherwise use the "standard" ones. ########## ql/src/test/results/clientpositive/jdbc/postgres/cbo_query10.q.out: ########## @@ -0,0 +1,62 @@ +CBO PLAN: +HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$4], sort4=[$6], sort5=[$8], sort6=[$10], sort7=[$12], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], dir5=[ASC], dir6=[ASC], dir7=[ASC], fetch=[100]) + HiveProject(cd_gender=[$0], cd_marital_status=[$1], cd_education_status=[$2], cnt1=[$8], cd_purchase_estimate=[$3], cnt2=[$8], cd_credit_rating=[$4], cnt3=[$8], cd_dep_count=[$5], cnt4=[$8], cd_dep_employed_count=[$6], cnt5=[$8], cd_dep_college_count=[$7], cnt6=[$8]) + HiveAggregate(group=[{6, 7, 8, 9, 10, 11, 12, 13}], agg#0=[count()]) + HiveFilter(condition=[OR(IS NOT NULL($14), IS NOT NULL($16))]) + HiveJoin(condition=[=($0, $17)], joinType=[left], algorithm=[none], cost=[not available]) + HiveJoin(condition=[=($0, $15)], joinType=[left], algorithm=[none], cost=[not available]) + HiveSemiJoin(condition=[=($0, $14)], joinType=[semi]) + HiveProject(c_customer_sk=[$0], c_current_cdemo_sk=[$1], c_current_addr_sk=[$2], ca_address_sk=[$3], ca_county=[$4], cd_demo_sk=[$5], cd_gender=[$6], cd_marital_status=[$7], cd_education_status=[$8], cd_purchase_estimate=[$9], cd_credit_rating=[$10], cd_dep_count=[$11], cd_dep_employed_count=[$12], cd_dep_college_count=[$13]) Review Comment: Some part of the query cannot be pushed to JDBC. Should we log a follow-up ticket to see if we can improve the pushdown? ########## ql/src/test/results/clientpositive/jdbc/postgres/cbo_query12.q.out: ########## @@ -0,0 +1,22 @@ +CBO PLAN: +HiveProject(i_item_desc=[$0], i_category=[$1], i_class=[$2], i_current_price=[$3], itemrevenue=[$4], revenueratio=[$5]) + HiveSortLimit(sort0=[$1], sort1=[$2], sort2=[$6], sort3=[$0], sort4=[$5], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], fetch=[100]) Review Comment: Can we push limit to JDBC? Follow-up? ########## ql/src/test/results/clientpositive/jdbc/postgres/cbo_query16.q.out: ########## @@ -0,0 +1,38 @@ +CBO PLAN: +HiveProject(order count=[$0], total shipping cost=[$1], total net profit=[$2]) + HiveAggregate(group=[{}], agg#0=[count(DISTINCT $4)], agg#1=[sum($5)], agg#2=[sum($6)]) + HiveAntiJoin(condition=[=($4, $14)], joinType=[anti]) + HiveSemiJoin(condition=[AND(=($4, $14), <>($3, $13))], joinType=[semi]) Review Comment: IT seems that ANTI/SEMI joins are blocking pushdown. I guess we have to investigate this in a follow-up. ########## ql/src/test/results/clientpositive/jdbc/postgres/cbo_query15.q.out: ########## @@ -0,0 +1,29 @@ +CBO PLAN: +HiveSortLimit(sort0=[$0], dir0=[ASC], fetch=[100]) + HiveProject(ca_zip=[$0], _c1=[$1]) + HiveAggregate(group=[{1}], agg#0=[sum($8)]) + HiveJoin(condition=[AND(=($7, $4), OR($2, $9, $3))], joinType=[inner], algorithm=[none], cost=[not available]) Review Comment: Why this inner join is not pushed? Follow-up? ########## ql/src/test/results/clientpositive/jdbc/postgres/cbo_query28.q.out: ########## @@ -0,0 +1,55 @@ +Warning: Shuffle Join MERGEJOIN[29][tables = [$hdt$_0, $hdt$_1]] in Stage 'Reducer 2' is a cross product +Warning: Shuffle Join MERGEJOIN[30][tables = [$hdt$_0, $hdt$_1, $hdt$_2]] in Stage 'Reducer 3' is a cross product +Warning: Shuffle Join MERGEJOIN[31][tables = [$hdt$_0, $hdt$_1, $hdt$_2, $hdt$_3]] in Stage 'Reducer 4' is a cross product +Warning: Shuffle Join MERGEJOIN[32][tables = [$hdt$_0, $hdt$_1, $hdt$_2, $hdt$_3, $hdt$_4]] in Stage 'Reducer 5' is a cross product +Warning: Shuffle Join MERGEJOIN[33][tables = [$hdt$_0, $hdt$_1, $hdt$_2, $hdt$_3, $hdt$_4, $hdt$_5]] in Stage 'Reducer 6' is a cross product +CBO PLAN: +HiveProject(b1.b1_lp=[$0], b1.b1_cnt=[$1], b1.b1_cntd=[$2], b2.b2_lp=[$15], b2.b2_cnt=[$16], b2.b2_cntd=[$17], b3.b3_lp=[$12], b3.b3_cnt=[$13], b3.b3_cntd=[$14], b4.b4_lp=[$9], b4.b4_cnt=[$10], b4.b4_cntd=[$11], b5.b5_lp=[$6], b5.b5_cnt=[$7], b5.b5_cntd=[$8], b6.b6_lp=[$3], b6.b6_cnt=[$4], b6.b6_cntd=[$5]) + HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available]) Review Comment: Are we skipping cartesian product pushdown on purpose? Follow-up to investigate? ########## ql/src/test/results/clientpositive/jdbc/postgres/cbo_query51.q.out: ########## @@ -0,0 +1,33 @@ +CBO PLAN: +HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) + HiveProject(y.item_sk=[$0], y.d_date=[$1], y.web_sales=[$2], y.store_sales=[$3], y.web_cumulative=[$4], y.store_cumulative=[$5]) + HiveFilter(condition=[>($4, $5)]) + HiveProject(item_sk=[CASE(IS NOT NULL($0), $0, $3)], d_date=[CASE(IS NOT NULL($1), $1, $4)], web_sales=[$2], store_sales=[$5], max_window_0=[max($2) OVER (PARTITION BY CASE(IS NOT NULL($0), $0, $3) ORDER BY CASE(IS NOT NULL($1), $1, $4) NULLS LAST ROWS UNBOUNDED PRECEDING)], max_window_1=[max($5) OVER (PARTITION BY CASE(IS NOT NULL($0), $0, $3) ORDER BY CASE(IS NOT NULL($1), $1, $4) NULLS LAST ROWS UNBOUNDED PRECEDING)]) + HiveJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[full], algorithm=[none], cost=[not available]) Review Comment: Full outer joins are not pushed by design? Follow-up? ########## ql/src/test/results/clientpositive/jdbc/postgres/query1.q.out: ########## @@ -0,0 +1,55 @@ +STAGE DEPENDENCIES: + Stage-0 is a root stage + +STAGE PLANS: + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + TableScan + alias: store_returns + properties: + hive.sql.query SELECT "t25"."c_customer_id" Review Comment: Does it make sense to add .qout files to verify the physical plan for JDBC pushdown? The SQL query is rather complex in most cases and it cannot be verified just by looking at it. We cannot reliably enforce correctness just by looking at the query. Ideally, we should run the queries against data but this is outside of scope for now. Compilation failures (even at the physical plan) are captured by running `EXPLAIN CBO` so I am not sure we need all these additional tests on the physical part. It will double the execution of the test and I am not sure if there is a noticable benefit in having those. Thoughts? I put the question here but obviously it applies for all query*.q.out files. ########## ql/src/test/results/clientpositive/jdbc/postgres/cbo_query14.q.out: ########## @@ -0,0 +1,332 @@ +Warning: Shuffle Join MERGEJOIN[334][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 28' is a cross product +Warning: Shuffle Join MERGEJOIN[340][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 9' is a cross product +Warning: Shuffle Join MERGEJOIN[346][tables = [$hdt$_2, $hdt$_3, $hdt$_1]] in Stage 'Reducer 17' is a cross product +Warning: Shuffle Join MERGEJOIN[352][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 20' is a cross product +CBO PLAN: +HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], fetch=[100]) + HiveProject(channel=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], _c4=[$4], _c5=[$5]) + HiveAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 1, 2}, {0, 1}, {0}, {}]], agg#0=[sum($4)], agg#1=[sum($5)]) + HiveProject(channel=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], sales=[$4], number_sales=[$5]) + HiveUnion(all=[true]) + HiveProject(channel=[_UTF-16LE'store':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"], i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], sales=[$3], number_sales=[$4]) + HiveJoin(condition=[>($3, $6)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4]) + HiveFilter(condition=[IS NOT NULL($3)]) + HiveAggregate(group=[{0, 1, 2}], agg#0=[sum($3)], agg#1=[count()]) + HiveProject($f0=[$5], $f1=[$6], $f2=[$7], $f3=[*(CAST($2):DECIMAL(10, 0), $3)]) + HiveSemiJoin(condition=[=($1, $11)], joinType=[semi]) + HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_quantity=[$2], ss_list_price=[$3], i_item_sk=[$4], i_brand_id=[$5], i_class_id=[$6], i_category_id=[$7], d_date_sk=[$8], d_year=[$9], d_moy=[$10]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_quantity=[$2], ss_list_price=[$3], i_item_sk=[$7], i_brand_id=[$8], i_class_id=[$9], i_category_id=[$10], d_date_sk=[$4], d_year=[$5], d_moy=[$6]) + JdbcJoin(condition=[=($1, $7)], joinType=[inner]) + JdbcJoin(condition=[=($0, $4)], joinType=[inner]) + JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_quantity=[$2], ss_list_price=[$3]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))]) + JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_quantity=[$10], ss_list_price=[$12]) + JdbcHiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) + JdbcProject(d_date_sk=[$0], d_year=[$1], d_moy=[$2]) + JdbcFilter(condition=[AND(=($1, 2000), =($2, 11), IS NOT NULL($0))]) + JdbcProject(d_date_sk=[$0], d_year=[$6], d_moy=[$8]) + JdbcHiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + JdbcFilter(condition=[IS NOT NULL($0)]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[item]) + HiveProject(i_item_sk=[$0]) + HiveJoin(condition=[AND(=($1, $4), =($2, $5), =($3, $6))], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + HiveProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + HiveProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($2), IS NOT NULL($3), IS NOT NULL($0))]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[item]) + HiveProject($f0=[$0], $f1=[$1], $f2=[$2]) + HiveFilter(condition=[=($3, 3)]) + HiveAggregate(group=[{0, 1, 2}], agg#0=[count($3)]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveUnion(all=[true]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcAggregate(group=[{4, 5, 6}], agg#0=[count()]) + JdbcJoin(condition=[=($1, $3)], joinType=[inner]) + JdbcJoin(condition=[=($0, $2)], joinType=[inner]) + JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$1]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))]) + JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$2]) + JdbcHiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) + JdbcProject(d_date_sk=[$0]) + JdbcFilter(condition=[AND(BETWEEN(false, $1, 1999, 2001), IS NOT NULL($0))]) + JdbcProject(d_date_sk=[$0], d_year=[$6]) + JdbcHiveTableScan(table=[[default, date_dim]], table:alias=[d1]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + JdbcFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1), IS NOT NULL($2), IS NOT NULL($3))]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[iss]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcAggregate(group=[{4, 5, 6}], agg#0=[count()]) + JdbcJoin(condition=[=($1, $3)], joinType=[inner]) + JdbcJoin(condition=[=($0, $2)], joinType=[inner]) + JdbcProject(cs_sold_date_sk=[$0], cs_item_sk=[$1]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))]) + JdbcProject(cs_sold_date_sk=[$0], cs_item_sk=[$15]) + JdbcHiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales]) + JdbcProject(d_date_sk=[$0]) + JdbcFilter(condition=[AND(BETWEEN(false, $1, 1999, 2001), IS NOT NULL($0))]) + JdbcProject(d_date_sk=[$0], d_year=[$6]) + JdbcHiveTableScan(table=[[default, date_dim]], table:alias=[d2]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + JdbcFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1), IS NOT NULL($2), IS NOT NULL($3))]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[ics]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) Review Comment: Many identical projects. Can we collapse them? Follow-up? ########## ql/src/test/results/clientpositive/jdbc/postgres/cbo_query14.q.out: ########## @@ -0,0 +1,332 @@ +Warning: Shuffle Join MERGEJOIN[334][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 28' is a cross product +Warning: Shuffle Join MERGEJOIN[340][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 9' is a cross product +Warning: Shuffle Join MERGEJOIN[346][tables = [$hdt$_2, $hdt$_3, $hdt$_1]] in Stage 'Reducer 17' is a cross product +Warning: Shuffle Join MERGEJOIN[352][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 20' is a cross product +CBO PLAN: +HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], fetch=[100]) + HiveProject(channel=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], _c4=[$4], _c5=[$5]) + HiveAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 1, 2}, {0, 1}, {0}, {}]], agg#0=[sum($4)], agg#1=[sum($5)]) + HiveProject(channel=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], sales=[$4], number_sales=[$5]) + HiveUnion(all=[true]) + HiveProject(channel=[_UTF-16LE'store':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"], i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], sales=[$3], number_sales=[$4]) + HiveJoin(condition=[>($3, $6)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4]) + HiveFilter(condition=[IS NOT NULL($3)]) + HiveAggregate(group=[{0, 1, 2}], agg#0=[sum($3)], agg#1=[count()]) + HiveProject($f0=[$5], $f1=[$6], $f2=[$7], $f3=[*(CAST($2):DECIMAL(10, 0), $3)]) + HiveSemiJoin(condition=[=($1, $11)], joinType=[semi]) + HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_quantity=[$2], ss_list_price=[$3], i_item_sk=[$4], i_brand_id=[$5], i_class_id=[$6], i_category_id=[$7], d_date_sk=[$8], d_year=[$9], d_moy=[$10]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_quantity=[$2], ss_list_price=[$3], i_item_sk=[$7], i_brand_id=[$8], i_class_id=[$9], i_category_id=[$10], d_date_sk=[$4], d_year=[$5], d_moy=[$6]) + JdbcJoin(condition=[=($1, $7)], joinType=[inner]) + JdbcJoin(condition=[=($0, $4)], joinType=[inner]) + JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_quantity=[$2], ss_list_price=[$3]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))]) + JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_quantity=[$10], ss_list_price=[$12]) + JdbcHiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) + JdbcProject(d_date_sk=[$0], d_year=[$1], d_moy=[$2]) + JdbcFilter(condition=[AND(=($1, 2000), =($2, 11), IS NOT NULL($0))]) + JdbcProject(d_date_sk=[$0], d_year=[$6], d_moy=[$8]) + JdbcHiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + JdbcFilter(condition=[IS NOT NULL($0)]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[item]) + HiveProject(i_item_sk=[$0]) + HiveJoin(condition=[AND(=($1, $4), =($2, $5), =($3, $6))], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + HiveProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + HiveProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($2), IS NOT NULL($3), IS NOT NULL($0))]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[item]) + HiveProject($f0=[$0], $f1=[$1], $f2=[$2]) + HiveFilter(condition=[=($3, 3)]) + HiveAggregate(group=[{0, 1, 2}], agg#0=[count($3)]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveUnion(all=[true]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcAggregate(group=[{4, 5, 6}], agg#0=[count()]) + JdbcJoin(condition=[=($1, $3)], joinType=[inner]) + JdbcJoin(condition=[=($0, $2)], joinType=[inner]) + JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$1]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))]) + JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$2]) + JdbcHiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) + JdbcProject(d_date_sk=[$0]) + JdbcFilter(condition=[AND(BETWEEN(false, $1, 1999, 2001), IS NOT NULL($0))]) + JdbcProject(d_date_sk=[$0], d_year=[$6]) + JdbcHiveTableScan(table=[[default, date_dim]], table:alias=[d1]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + JdbcFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1), IS NOT NULL($2), IS NOT NULL($3))]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[iss]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcAggregate(group=[{4, 5, 6}], agg#0=[count()]) + JdbcJoin(condition=[=($1, $3)], joinType=[inner]) + JdbcJoin(condition=[=($0, $2)], joinType=[inner]) + JdbcProject(cs_sold_date_sk=[$0], cs_item_sk=[$1]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))]) + JdbcProject(cs_sold_date_sk=[$0], cs_item_sk=[$15]) + JdbcHiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales]) + JdbcProject(d_date_sk=[$0]) + JdbcFilter(condition=[AND(BETWEEN(false, $1, 1999, 2001), IS NOT NULL($0))]) + JdbcProject(d_date_sk=[$0], d_year=[$6]) + JdbcHiveTableScan(table=[[default, date_dim]], table:alias=[d2]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + JdbcFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1), IS NOT NULL($2), IS NOT NULL($3))]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[ics]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcAggregate(group=[{4, 5, 6}], agg#0=[count()]) + JdbcJoin(condition=[=($1, $3)], joinType=[inner]) + JdbcJoin(condition=[=($0, $2)], joinType=[inner]) + JdbcProject(ws_sold_date_sk=[$0], ws_item_sk=[$1]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))]) + JdbcProject(ws_sold_date_sk=[$0], ws_item_sk=[$3]) + JdbcHiveTableScan(table=[[default, web_sales]], table:alias=[web_sales]) + JdbcProject(d_date_sk=[$0]) + JdbcFilter(condition=[AND(BETWEEN(false, $1, 1999, 2001), IS NOT NULL($0))]) + JdbcProject(d_date_sk=[$0], d_year=[$6]) + JdbcHiveTableScan(table=[[default, date_dim]], table:alias=[d3]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + JdbcFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1), IS NOT NULL($2), IS NOT NULL($3))]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[iws]) + HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(cnt=[$0]) + HiveFilter(condition=[sq_count_check($0)]) + HiveProject(cnt=[$0]) + HiveAggregate(group=[{}], cnt=[COUNT()]) + HiveTableScan(table=[[default, avg_sales]], table:alias=[avg_sales]) + HiveProject(average_sales=[$0]) + HiveFilter(condition=[IS NOT NULL($0)]) + HiveTableScan(table=[[default, avg_sales]], table:alias=[avg_sales]) + HiveProject(channel=[_UTF-16LE'catalog':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"], i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], sales=[$3], number_sales=[$4]) + HiveJoin(condition=[>($3, $6)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4]) + HiveFilter(condition=[IS NOT NULL($3)]) + HiveAggregate(group=[{0, 1, 2}], agg#0=[sum($3)], agg#1=[count()]) + HiveProject($f0=[$5], $f1=[$6], $f2=[$7], $f3=[*(CAST($2):DECIMAL(10, 0), $3)]) + HiveSemiJoin(condition=[=($1, $11)], joinType=[semi]) + HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$1], cs_quantity=[$2], cs_list_price=[$3], i_item_sk=[$4], i_brand_id=[$5], i_class_id=[$6], i_category_id=[$7], d_date_sk=[$8], d_year=[$9], d_moy=[$10]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcProject(cs_sold_date_sk=[$0], cs_item_sk=[$1], cs_quantity=[$2], cs_list_price=[$3], i_item_sk=[$7], i_brand_id=[$8], i_class_id=[$9], i_category_id=[$10], d_date_sk=[$4], d_year=[$5], d_moy=[$6]) + JdbcJoin(condition=[=($1, $7)], joinType=[inner]) + JdbcJoin(condition=[=($0, $4)], joinType=[inner]) + JdbcProject(cs_sold_date_sk=[$0], cs_item_sk=[$1], cs_quantity=[$2], cs_list_price=[$3]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))]) + JdbcProject(cs_sold_date_sk=[$0], cs_item_sk=[$15], cs_quantity=[$18], cs_list_price=[$20]) + JdbcHiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales]) + JdbcProject(d_date_sk=[$0], d_year=[$1], d_moy=[$2]) + JdbcFilter(condition=[AND(=($1, 2000), =($2, 11), IS NOT NULL($0))]) + JdbcProject(d_date_sk=[$0], d_year=[$6], d_moy=[$8]) + JdbcHiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + JdbcFilter(condition=[IS NOT NULL($0)]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[item]) + HiveProject(i_item_sk=[$0]) + HiveJoin(condition=[AND(=($1, $4), =($2, $5), =($3, $6))], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + HiveProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + HiveProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($2), IS NOT NULL($3), IS NOT NULL($0))]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[item]) + HiveProject($f0=[$0], $f1=[$1], $f2=[$2]) + HiveFilter(condition=[=($3, 3)]) + HiveAggregate(group=[{0, 1, 2}], agg#0=[count($3)]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveUnion(all=[true]) Review Comment: Is it normal that union is not pushed? ########## ql/src/test/results/clientpositive/jdbc/postgres/cbo_query14.q.out: ########## @@ -0,0 +1,332 @@ +Warning: Shuffle Join MERGEJOIN[334][tables = [$hdt$_1, $hdt$_2]] in Stage 'Reducer 28' is a cross product +Warning: Shuffle Join MERGEJOIN[340][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 9' is a cross product +Warning: Shuffle Join MERGEJOIN[346][tables = [$hdt$_2, $hdt$_3, $hdt$_1]] in Stage 'Reducer 17' is a cross product +Warning: Shuffle Join MERGEJOIN[352][tables = [$hdt$_1, $hdt$_2, $hdt$_0]] in Stage 'Reducer 20' is a cross product +CBO PLAN: +HiveSortLimit(sort0=[$0], sort1=[$1], sort2=[$2], sort3=[$3], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC], fetch=[100]) + HiveProject(channel=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], _c4=[$4], _c5=[$5]) + HiveAggregate(group=[{0, 1, 2, 3}], groups=[[{0, 1, 2, 3}, {0, 1, 2}, {0, 1}, {0}, {}]], agg#0=[sum($4)], agg#1=[sum($5)]) + HiveProject(channel=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3], sales=[$4], number_sales=[$5]) + HiveUnion(all=[true]) + HiveProject(channel=[_UTF-16LE'store':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"], i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], sales=[$3], number_sales=[$4]) + HiveJoin(condition=[>($3, $6)], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4]) + HiveFilter(condition=[IS NOT NULL($3)]) + HiveAggregate(group=[{0, 1, 2}], agg#0=[sum($3)], agg#1=[count()]) + HiveProject($f0=[$5], $f1=[$6], $f2=[$7], $f3=[*(CAST($2):DECIMAL(10, 0), $3)]) + HiveSemiJoin(condition=[=($1, $11)], joinType=[semi]) + HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_quantity=[$2], ss_list_price=[$3], i_item_sk=[$4], i_brand_id=[$5], i_class_id=[$6], i_category_id=[$7], d_date_sk=[$8], d_year=[$9], d_moy=[$10]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_quantity=[$2], ss_list_price=[$3], i_item_sk=[$7], i_brand_id=[$8], i_class_id=[$9], i_category_id=[$10], d_date_sk=[$4], d_year=[$5], d_moy=[$6]) + JdbcJoin(condition=[=($1, $7)], joinType=[inner]) + JdbcJoin(condition=[=($0, $4)], joinType=[inner]) + JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$1], ss_quantity=[$2], ss_list_price=[$3]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))]) + JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$2], ss_quantity=[$10], ss_list_price=[$12]) + JdbcHiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) + JdbcProject(d_date_sk=[$0], d_year=[$1], d_moy=[$2]) + JdbcFilter(condition=[AND(=($1, 2000), =($2, 11), IS NOT NULL($0))]) + JdbcProject(d_date_sk=[$0], d_year=[$6], d_moy=[$8]) + JdbcHiveTableScan(table=[[default, date_dim]], table:alias=[date_dim]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + JdbcFilter(condition=[IS NOT NULL($0)]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[item]) + HiveProject(i_item_sk=[$0]) + HiveJoin(condition=[AND(=($1, $4), =($2, $5), =($3, $6))], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + HiveProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + HiveProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($2), IS NOT NULL($3), IS NOT NULL($0))]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[item]) + HiveProject($f0=[$0], $f1=[$1], $f2=[$2]) + HiveFilter(condition=[=($3, 3)]) + HiveAggregate(group=[{0, 1, 2}], agg#0=[count($3)]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveUnion(all=[true]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcAggregate(group=[{4, 5, 6}], agg#0=[count()]) + JdbcJoin(condition=[=($1, $3)], joinType=[inner]) + JdbcJoin(condition=[=($0, $2)], joinType=[inner]) + JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$1]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))]) + JdbcProject(ss_sold_date_sk=[$0], ss_item_sk=[$2]) + JdbcHiveTableScan(table=[[default, store_sales]], table:alias=[store_sales]) + JdbcProject(d_date_sk=[$0]) + JdbcFilter(condition=[AND(BETWEEN(false, $1, 1999, 2001), IS NOT NULL($0))]) + JdbcProject(d_date_sk=[$0], d_year=[$6]) + JdbcHiveTableScan(table=[[default, date_dim]], table:alias=[d1]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + JdbcFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1), IS NOT NULL($2), IS NOT NULL($3))]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[iss]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcAggregate(group=[{4, 5, 6}], agg#0=[count()]) + JdbcJoin(condition=[=($1, $3)], joinType=[inner]) + JdbcJoin(condition=[=($0, $2)], joinType=[inner]) + JdbcProject(cs_sold_date_sk=[$0], cs_item_sk=[$1]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))]) + JdbcProject(cs_sold_date_sk=[$0], cs_item_sk=[$15]) + JdbcHiveTableScan(table=[[default, catalog_sales]], table:alias=[catalog_sales]) + JdbcProject(d_date_sk=[$0]) + JdbcFilter(condition=[AND(BETWEEN(false, $1, 1999, 2001), IS NOT NULL($0))]) + JdbcProject(d_date_sk=[$0], d_year=[$6]) + JdbcHiveTableScan(table=[[default, date_dim]], table:alias=[d2]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + JdbcFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1), IS NOT NULL($2), IS NOT NULL($3))]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[ics]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveProject(i_brand_id=[$0], i_class_id=[$1], i_category_id=[$2], $f3=[$3]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcAggregate(group=[{4, 5, 6}], agg#0=[count()]) + JdbcJoin(condition=[=($1, $3)], joinType=[inner]) + JdbcJoin(condition=[=($0, $2)], joinType=[inner]) + JdbcProject(ws_sold_date_sk=[$0], ws_item_sk=[$1]) + JdbcFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))]) + JdbcProject(ws_sold_date_sk=[$0], ws_item_sk=[$3]) + JdbcHiveTableScan(table=[[default, web_sales]], table:alias=[web_sales]) + JdbcProject(d_date_sk=[$0]) + JdbcFilter(condition=[AND(BETWEEN(false, $1, 1999, 2001), IS NOT NULL($0))]) + JdbcProject(d_date_sk=[$0], d_year=[$6]) + JdbcHiveTableScan(table=[[default, date_dim]], table:alias=[d3]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2], i_category_id=[$3]) + JdbcFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1), IS NOT NULL($2), IS NOT NULL($3))]) + JdbcProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9], i_category_id=[$11]) + JdbcHiveTableScan(table=[[default, item]], table:alias=[iws]) + HiveJoin(condition=[true], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject(cnt=[$0]) + HiveFilter(condition=[sq_count_check($0)]) + HiveProject(cnt=[$0]) + HiveAggregate(group=[{}], cnt=[COUNT()]) + HiveTableScan(table=[[default, avg_sales]], table:alias=[avg_sales]) + HiveProject(average_sales=[$0]) + HiveFilter(condition=[IS NOT NULL($0)]) + HiveTableScan(table=[[default, avg_sales]], table:alias=[avg_sales]) Review Comment: Is `avg_sales` a materialized CTE? Maybe for the sake of JDBC pushdown testing we should disable this feature. ########## ql/src/test/results/clientpositive/jdbc/postgres/cbo_query5.q.out: ########## @@ -0,0 +1,85 @@ +CBO PLAN: +HiveSortLimit(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100]) + HiveProject(channel=[$0], id=[$1], sales=[$2], returns=[$3], profit=[$4]) + HiveAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], agg#0=[sum($2)], agg#1=[sum($3)], agg#2=[sum($4)]) + HiveProject(channel=[$0], id=[$1], sales=[$2], returns=[$3], profit=[$4]) + HiveUnion(all=[true]) + HiveProject(channel=[_UTF-16LE'store channel':VARCHAR(2147483647) CHARACTER SET "UTF-16LE"], id=[||(_UTF-16LE'store':VARCHAR(2147483647) CHARACTER SET "UTF-16LE", $0)], sales=[$1], returns=[$3], profit=[-($2, $4)]) + HiveProject(s_store_id=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4]) + HiveJdbcConverter(convention=[JDBC.POSTGRES]) + JdbcAggregate(group=[{8}], agg#0=[sum($2)], agg#1=[sum($3)], agg#2=[sum($4)], agg#3=[sum($5)]) + JdbcJoin(condition=[=($0, $7)], joinType=[inner]) + JdbcJoin(condition=[=($1, $6)], joinType=[inner]) + JdbcProject(store_sk=[$0], date_sk=[$1], sales_price=[$2], profit=[$3], return_amt=[$4], net_loss=[$5]) + JdbcUnion(all=[true]) Review Comment: In some cases it seems that we are pushing down the union and in others (see above) not. Is it expected? Follow-up? ########## ql/src/test/results/clientpositive/jdbc/postgres/cbo_query57.q.out: ########## @@ -0,0 +1,82 @@ +CBO PLAN: +HiveProject(v2.i_category=[$0], v2.i_brand=[$1], v2.d_year=[$2], v2.d_moy=[$3], v2.avg_monthly_sales=[$4], v2.sum_sales=[$5], v2.psum=[$6], v2.nsum=[$7]) + HiveSortLimit(sort0=[$8], sort1=[$2], dir0=[ASC], dir1=[ASC], fetch=[100]) + HiveProject(i_category=[$0], i_brand=[$1], d_year=[$3], d_moy=[$4], avg_monthly_sales=[$6], sum_sales=[$5], psum=[$11], nsum=[$16], (- (tok_table_or_col sum_sales) (tok_table_or_col avg_monthly_sales))1=[-($5, $6)]) + HiveJoin(condition=[AND(=($0, $13), =($1, $14), =($7, $17), =($2, $15))], joinType=[inner], algorithm=[none], cost=[not available]) + HiveJoin(condition=[AND(=($0, $8), =($1, $9), =($7, $12), =($2, $10))], joinType=[inner], algorithm=[none], cost=[not available]) + HiveProject((tok_table_or_col i_category)=[$0], (tok_table_or_col i_brand)=[$1], (tok_table_or_col cc_name)=[$2], (tok_table_or_col d_year)=[$3], (tok_table_or_col d_moy)=[$4], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], avg_window_0=[$6], rank_window_1=[$7]) + HiveFilter(condition=[AND(>($6, 0:DECIMAL(1, 0)), =($3, 2000), CASE(>($6, 0:DECIMAL(1, 0)), >(/(ABS(-($5, $6)), $6), 0.1:DECIMAL(1, 1)), false), IS NOT NULL($7))]) + HiveProject((tok_table_or_col i_category)=[$2], (tok_table_or_col i_brand)=[$1], (tok_table_or_col cc_name)=[$0], (tok_table_or_col d_year)=[$3], (tok_table_or_col d_moy)=[$4], (tok_function sum (tok_table_or_col cs_sales_price))=[$5], avg_window_0=[avg($5) OVER (PARTITION BY $2, $1, $0, $3 ORDER BY $2 NULLS FIRST, $1 NULLS FIRST, $0 NULLS FIRST, $3 NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)], rank_window_1=[rank() OVER (PARTITION BY $2, $1, $0 ORDER BY $3 NULLS LAST, $4 NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)]) + HiveProject(cc_name=[$0], i_brand=[$1], i_category=[$2], d_year=[$3], d_moy=[$4], $f5=[$5]) Review Comment: Blocked pushdown. Why? Follow-up? -- 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]
