zabetak commented on code in PR #5207: URL: https://github.com/apache/hive/pull/5207#discussion_r1574264068
########## ql/src/test/queries/clientpositive/cbo_join_constraints.q: ########## @@ -0,0 +1,16 @@ +CREATE TABLE `store_sales` (`ss_item_sk` bigint); +CREATE TABLE `household_demographics` (`hd_demo_sk` bigint); +CREATE TABLE `item` (`i_item_sk` bigint); +ALTER TABLE `store_sales` ADD CONSTRAINT `pk_ss` PRIMARY KEY (`ss_item_sk`) DISABLE NOVALIDATE RELY; +ALTER TABLE `item` ADD CONSTRAINT `pk_i` PRIMARY KEY (`i_item_sk`) DISABLE NOVALIDATE RELY; +ALTER TABLE `store_sales` ADD CONSTRAINT `ss_i` FOREIGN KEY (`ss_item_sk`) REFERENCES `item`(`i_item_sk`) DISABLE NOVALIDATE RELY; + +EXPLAIN CBO +SELECT i_item_sk +FROM store_sales, household_demographics, item +WHERE ss_item_sk = i_item_sk; + +EXPLAIN +SELECT i_item_sk +FROM store_sales, household_demographics, item +WHERE ss_item_sk = i_item_sk; Review Comment: If we just want to verify that the NPE is gone then the `EXPLAIN CBO` is sufficient on its own. In general, we should strive to keep tests minimal to avoid the need of updating the .q.out file very frequently. I would suggest removing the full `EXPLAIN` statement unless there is something in the plan that we need to validate. ########## ql/src/test/queries/clientpositive/cbo_row_count_non_simple_filter.q: ########## @@ -0,0 +1,40 @@ +CREATE TABLE `store_sales` (`ss_item_sk` bigint, `ss_hdemo_sk` bigint); +CREATE TABLE `household_demographics` (`hd_demo_sk` bigint, `hd_income_band_sk` bigint); +CREATE TABLE `item` (`i_item_sk` bigint, `i_product_name` string); +CREATE TABLE `income_band`(`ib_income_band_sk` bigint); +CREATE TABLE `customer`(`c_current_hdemo_sk` bigint); +ALTER TABLE `store_sales` ADD CONSTRAINT `pk_ss` PRIMARY KEY (`ss_item_sk`) DISABLE NOVALIDATE RELY; +ALTER TABLE `item` ADD CONSTRAINT `pk_i` PRIMARY KEY (`i_item_sk`) DISABLE NOVALIDATE RELY; +ALTER TABLE `household_demographics` ADD CONSTRAINT `pk_hd` PRIMARY KEY (`hd_demo_sk`) DISABLE NOVALIDATE RELY; +ALTER TABLE `income_band` ADD CONSTRAINT `pk_ib` PRIMARY KEY (`ib_income_band_sk`) DISABLE NOVALIDATE RELY; +ALTER TABLE `store_sales` ADD CONSTRAINT `ss_i` FOREIGN KEY (`ss_item_sk`) REFERENCES `item`(`i_item_sk`) DISABLE NOVALIDATE RELY; +ALTER TABLE `household_demographics` ADD CONSTRAINT `hd_ib` FOREIGN KEY (`hd_income_band_sk`) REFERENCES `income_band`(`ib_income_band_sk`) DISABLE NOVALIDATE RELY; +ALTER TABLE `customer` ADD CONSTRAINT `c_hd` FOREIGN KEY (`c_current_hdemo_sk`) REFERENCES `household_demographics`(`hd_demo_sk`) DISABLE NOVALIDATE RELY; + +EXPLAIN CBO +SELECT i_product_name, i_item_sk +FROM store_sales + ,customer + ,household_demographics hd1 + ,household_demographics hd2 + ,income_band ib2 + ,item +WHERE ss_hdemo_sk = hd1.hd_demo_sk AND + ss_item_sk = i_item_sk and + c_current_hdemo_sk = hd2.hd_demo_sk AND + hd2.hd_income_band_sk = ib2.ib_income_band_sk +GROUP BY i_product_name, i_item_sk; + +EXPLAIN +SELECT i_product_name, i_item_sk +FROM store_sales + ,customer + ,household_demographics hd1 + ,household_demographics hd2 + ,income_band ib2 + ,item +WHERE ss_hdemo_sk = hd1.hd_demo_sk AND + ss_item_sk = i_item_sk and + c_current_hdemo_sk = hd2.hd_demo_sk AND + hd2.hd_income_band_sk = ib2.ib_income_band_sk +GROUP BY i_product_name, i_item_sk; Review Comment: Same comment as the other .q file. -- 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]
