You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "zengxl (Jira)" <ji...@apache.org> on 2022/01/12 09:02:00 UTC
[jira] [Commented] (HIVE-25614) Mapjoin then join left,the result is incorrect
[ https://issues.apache.org/jira/browse/HIVE-25614?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17474379#comment-17474379 ]
zengxl commented on HIVE-25614:
-------------------------------
i close this issue ,This test is not accurate, see the accurate test HIVE-25863
> Mapjoin then join left,the result is incorrect
> ----------------------------------------------
>
> Key: HIVE-25614
> URL: https://issues.apache.org/jira/browse/HIVE-25614
> Project: Hive
> Issue Type: Bug
> Components: Hive
> Affects Versions: 3.1.2
> Reporter: zengxl
> Assignee: Sumit Verma
> Priority: Critical
>
> Currently I join 3 tables, find the result of left join is *{color:#de350b}null{color}*
> Here is my SQL，The result of this SQL is NULL
> {code:java}
> //代码占位符
> CREATE TABLE `pdwd.pdwd_pf_hive_ah3_metastore_tbl_partitions_d_test_0831_3_tbl`(
> `tbl_id` bigint COMMENT 'TBL_ID',
> `tbl_create_time` bigint COMMENT 'TBL_CREATE_TIME',
> `db_id` bigint COMMENT 'DB_ID',
> `tbl_last_access_time` bigint COMMENT 'TBL_LAST_ACCESS_TIME',
> `owner` string COMMENT 'OWNER',
> `retention` bigint COMMENT 'RETENTION',
> `sd_id` bigint COMMENT 'SD_ID',
> `tbl_name` string COMMENT 'TBL_NAME',
> `tbl_type` string COMMENT 'TBL_TYPE',
> `view_expanded_text` string COMMENT 'VIEW_EXPANDED_TEXT',
> `view_original_text` string COMMENT 'VIEW_ORIGINAL_TEXT',
> `is_rewrite_enabled` bigint COMMENT 'IS_REWRITE_ENABLED',
> `tbl_owner_type` string COMMENT 'TBL_OWNER_TYPE',
> `cd_id` bigint COMMENT 'CD_ID',
> `input_format` string COMMENT 'INPUT_FORMAT',
> `is_compressed` bigint COMMENT 'IS_COMPRESSED',
> `is_storedassubdirectories` bigint COMMENT 'IS_STOREDASSUBDIRECTORIES',
> `tbl_or_part_location` string COMMENT 'tbl_or_part_location',
> `num_buckets` bigint COMMENT 'NUM_BUCKETS',
> `output_format` string COMMENT 'OUTPUT_FORMAT',
> `serde_id` bigint COMMENT 'SERDE_ID',
> `part_id` bigint COMMENT 'PART_ID',
> `part_create_time` bigint COMMENT 'PART_CREATE_TIME',
> `part_last_access_time` bigint COMMENT 'PART_LAST_ACCESS_TIME',
> `part_name` string COMMENT 'PART_NAME')
> PARTITIONED BY (
> `pt` string)
> ROW FORMAT SERDE
> 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
> STORED AS INPUTFORMAT
> 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
> OUTPUTFORMAT
> 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
>
> with tmp1 as (
> select
> part_id,
> create_time,
> last_access_time,
> part_name,
> sd_id,
> tbl_id
> from
> pods.pods_pf_hive_ah3_metastore_partitions_d
> where
> pt='2021-08-12'
> ),
> tmp2 as (
> select
> tbl_id,
> create_time,
> db_id,
> last_access_time,
> owner,
> retention,
> sd_id,
> tbl_name,
> tbl_type,
> view_expanded_text,
> view_original_text,
> is_rewrite_enabled,
> owner_type
> from
> pods.pods_pf_hive_ah3_metastore_tbls_d
> where
> pt='2021-08-12'
> ),
> tmp3 as (
> select
> sd_id,
> cd_id,
> input_format,
> is_compressed,
> is_storedassubdirectories,
> location,
> num_buckets,
> output_format,
> serde_id
> from
> pods.pods_pf_hive_ah3_metastore_sds_d
> where
> pt='2021-08-12'
> )insert overwrite table pdwd.pdwd_pf_hive_ah3_metastore_tbl_partitions_d_test_0831_3_tbl PARTITION(pt='2021-08-14')
> select
> a.tbl_id,
> b.create_time as tbl_create_time,
> b.db_id,
> b.last_access_time as tbl_last_access_time,
> b.owner,
> b.retention,
> a.sd_id,
> b.tbl_name,
> b.tbl_type,
> b.view_expanded_text,
> b.view_original_text,
> b.is_rewrite_enabled,
> b.owner_type as tbl_owner_type,
> d.cd_id,
> d.input_format,
> d.is_compressed,
> d.is_storedassubdirectories,
> d.location as tbl_location,
> d.num_buckets,
> d.output_format,
> d.serde_id,
> a.part_id,
> a.create_time as part_create_time,
> a.last_access_time as part_last_access_time,
> a.part_name
> from tmp1 a
> left join tmp2 b on a.tbl_id=b.tbl_id
> left join tmp3 d on a.sd_id=d.sd_id;
> {code}
> pods.pods_pf_hive_ah3_metastore_partitions_d、pods.pods_pf_hive_ah3_metastore_tbls_d、pods.pods_pf_hive_ah3_metastore_sds_d from {color:#de350b}*Metastore*{color} partitions、tbls、sds
> The sizes of the three tables are as follows:
> 80.3 M 240.9 M hdfs://ns4/apps/hive/warehouse/pods/pods_pf_hive_ah3_metastore_partitions_d/pt=2021-10-09/exchangis_hive_w__2585cbd4_8bf8_4fbb_8a90_f5a7939b62b3.snappy
> 179.8 K 539.5 K hdfs://ns4/apps/hive/warehouse/pods/pods_pf_hive_ah3_metastore_tbls_d/pt=2021-10-09/exchangis_hive_w__8a62acaa_6f82_442e_97db_ce960833612f.snappy
> 94.3 M 282.9 M hdfs://ns4/apps/hive/warehouse/pods/pods_pf_hive_ah3_metastore_sds_d/pt=2021-10-09/exchangis_hive_w__d25536e8_7018_4262_a00e_5af3b1f88925.snappy
> The result is as follows,select from pdwd.pdwd_pf_hive_ah3_metastore_tbl_partitions_d_test_0831_3_tbl table *{color:#de350b}is null{color}*
> {code:java}
> hive> select * from pdwd.pdwd_pf_hive_ah3_metastore_tbl_partitions_d_test_0831_3_tbl where pt='2021-08-14' and sd_id=21229815;
> OK
> 721213 1619403652 528181 0 op 0 21229815 pods_pf_yarn_logic_res_i_d MANAGED_TABLE NULL NULL 0 USER NULL NULL NULL NULL NULL NULL NULL NULL 20302818 1628697610 0 pt=2021-08-11 2021-08-14
> {code}
> The reality pods.pods_pf_hive_ah3_metastore_sds_d table is that the data in this table is {color:#de350b}*not null*{color}
> {code:java}
> > select * from pods.pods_pf_hive_ah3_metastore_sds_d where pt='2021-08-12' and sd_id=21229815;
> OK
> Interrupting...
> Be patient, this might take some time.
> Press Ctrl+C again to kill JVM
> 21229815 726105 org.apache.hadoop.hive.ql.io.orc.OrcInputFormat 0 0 hdfs://ns4/apps/hive/warehouse/pods/pods_pf_yarn_logic_res_i_d/pt=2021-08-11 -org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat 21229815 2021-08-12
> {code}
> When I change the order of join,The following SQL:
> {code:java}
> with tmp1 as (
> select
> part_id,
> create_time,
> last_access_time,
> part_name,
> sd_id,
> tbl_id
> from
> pods.pods_pf_hive_ah3_metastore_partitions_d
> where
> pt='2021-08-12'
> ),
> tmp2 as (
> select
> tbl_id,
> create_time,
> db_id,
> last_access_time,
> owner,
> retention,
> sd_id,
> tbl_name,
> tbl_type,
> view_expanded_text,
> view_original_text,
> is_rewrite_enabled,
> owner_type
> from
> pods.pods_pf_hive_ah3_metastore_tbls_d
> where
> pt='2021-08-12'
> ),
> tmp3 as (
> select
> sd_id,
> cd_id,
> input_format,
> is_compressed,
> is_storedassubdirectories,
> location,
> num_buckets,
> output_format,
> serde_id
> from
> pods.pods_pf_hive_ah3_metastore_sds_d
> where
> pt='2021-08-12'
> )insert overwrite table pdwd.pdwd_pf_hive_ah3_metastore_tbl_partitions_d_test_0831_3_tbl PARTITION(pt='2021-08-14')
> select
> a.tbl_id,
> b.create_time as tbl_create_time,
> b.db_id,
> b.last_access_time as tbl_last_access_time,
> b.owner,
> b.retention,
> a.sd_id,
> b.tbl_name,
> b.tbl_type,
> b.view_expanded_text,
> b.view_original_text,
> b.is_rewrite_enabled,
> b.owner_type as tbl_owner_type,
> d.cd_id,
> d.input_format,
> d.is_compressed,
> d.is_storedassubdirectories,
> d.location as tbl_location,
> d.num_buckets,
> d.output_format,
> d.serde_id,
> a.part_id,
> a.create_time as part_create_time,
> a.last_access_time as part_last_access_time,
> a.part_name
> from tmp1 a
> left join tmp3 d on a.sd_id=d.sd_id
> left join tmp2 b on a.tbl_id=b.tbl_id;
> {code}
> The result of this SQL execution is as follows,this result is true ,select from pdwd.pdwd_pf_hive_ah3_metastore_tbl_partitions_d_test_0831_3_tbl table is *{color:#de350b}not null{color}*
>
> {code:java}
> hive> select * from pdwd.pdwd_pf_hive_ah3_metastore_tbl_partitions_d_test_0831_3_tbl where pt='2021-08-14' and sd_id=21229815;
> OK
> Interrupting...
> Be patient, this might take some time.
> Press Ctrl+C again to kill JVM
> 721213 1619403652 528181 0 op 0 21229815 pods_pf_yarn_logic_res_i_d MANAGED_TABLE NULL NULL 0 USER 726105 org.apache.hadoop.hive.ql.io.orc.OrcInputFormat 0 0 hdfs://ns4/apps/hive/warehouse/pods/pods_pf_yarn_logic_res_i_d/pt=2021-08-11 -1 org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat 21229815 20302818 1628697610 0 pt=2021-08-11 2021-08-14
> {code}
> The query result is *{color:#de350b}null{color}* first mapjoin,then left join.Query results that are not *{color:#de350b}null{color}* are first left join ,then mapjoin
> I tested it again first left join,after mapjoin ,last left join .The result of the last left join is *{color:#de350b}null{color}*
> Test SQL as follows:
> {code:java}
> CREATE TABLE `pdwd.pdwd_pf_hive_ah3_metastore_tbl_partitions_d_test_0831_4_tbl`(
> `tbl_id` bigint COMMENT 'TBL_ID',
> `tbl_create_time` bigint COMMENT 'TBL_CREATE_TIME',
> `db_id` bigint COMMENT 'DB_ID',
> `tbl_last_access_time` bigint COMMENT 'TBL_LAST_ACCESS_TIME',
> `owner` string COMMENT 'OWNER',
> `retention` bigint COMMENT 'RETENTION',
> `sd_id` bigint COMMENT 'SD_ID',
> `tbl_name` string COMMENT 'TBL_NAME',
> `tbl_type` string COMMENT 'TBL_TYPE',
> `view_expanded_text` string COMMENT 'VIEW_EXPANDED_TEXT',
> `view_original_text` string COMMENT 'VIEW_ORIGINAL_TEXT',
> `is_rewrite_enabled` bigint COMMENT 'IS_REWRITE_ENABLED',
> `tbl_owner_type` string COMMENT 'TBL_OWNER_TYPE',
> `cd_id` bigint COMMENT 'CD_ID',
> `input_format` string COMMENT 'INPUT_FORMAT',
> `is_compressed` bigint COMMENT 'IS_COMPRESSED',
> `is_storedassubdirectories` bigint COMMENT 'IS_STOREDASSUBDIRECTORIES',
> `tbl_or_part_location` string COMMENT 'tbl_or_part_location',
> `num_buckets` bigint COMMENT 'NUM_BUCKETS',
> `output_format` string COMMENT 'OUTPUT_FORMAT',
> `serde_id` bigint COMMENT 'SERDE_ID',
> `part_id` bigint COMMENT 'PART_ID',
> `part_create_time` bigint COMMENT 'PART_CREATE_TIME',
> `part_last_access_time` bigint COMMENT 'PART_LAST_ACCESS_TIME',
> `part_name` string COMMENT 'PART_NAME',
> `cd_id_1` bigint COMMENT 'CD_ID_1',
> `input_format_1` string COMMENT 'INPUT_FORMAT_1',
> `is_compressed_1` bigint COMMENT 'IS_COMPRESSED_1',
> `is_storedassubdirectories_1` bigint COMMENT 'IS_STOREDASSUBDIRECTORIES_1',
> `tbl_or_part_location_1` string COMMENT 'tbl_or_part_location_1',
> `num_buckets_1` bigint COMMENT 'NUM_BUCKETS_1',
> `output_format_1` string COMMENT 'OUTPUT_FORMAT_1',
> `serde_id_1` bigint COMMENT 'SERDE_ID_1'
> )
> PARTITIONED BY (
> `pt` string)
> ROW FORMAT SERDE
> 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
> STORED AS INPUTFORMAT
> 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
> OUTPUTFORMAT
> 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat';
>
> with tmp1 as (
> select
> part_id,
> create_time,
> last_access_time,
> part_name,
> sd_id,
> tbl_id
> from
> pods.pods_pf_hive_ah3_metastore_partitions_d
> where
> pt='2021-08-12'
> ),
> tmp2 as (
> select
> tbl_id,
> create_time,
> db_id,
> last_access_time,
> owner,
> retention,
> sd_id,
> tbl_name,
> tbl_type,
> view_expanded_text,
> view_original_text,
> is_rewrite_enabled,
> owner_type
> from
> pods.pods_pf_hive_ah3_metastore_tbls_d
> where
> pt='2021-08-12'
> ),
> tmp3 as (
> select
> sd_id,
> cd_id,
> input_format,
> is_compressed,
> is_storedassubdirectories,
> location,
> num_buckets,
> output_format,
> serde_id
> from
> pods.pods_pf_hive_ah3_metastore_sds_d
> where
> pt='2021-08-12'
> )insert overwrite table pdwd.pdwd_pf_hive_ah3_metastore_tbl_partitions_d_test_0831_4_tbl PARTITION(pt='2021-08-14')
> select
> a.tbl_id,
> b.create_time as tbl_create_time,
> b.db_id,
> b.last_access_time as tbl_last_access_time,
> b.owner,
> b.retention,
> a.sd_id,
> b.tbl_name,
> b.tbl_type,
> b.view_expanded_text,
> b.view_original_text,
> b.is_rewrite_enabled,
> b.owner_type as tbl_owner_type,
> d.cd_id,
> d.input_format,
> d.is_compressed,
> d.is_storedassubdirectories,
> d.location as tbl_location,
> d.num_buckets,
> d.output_format,
> d.serde_id,
> a.part_id,
> a.create_time as part_create_time,
> a.last_access_time as part_last_access_time,
> a.part_name,
> e.cd_id as cd_id_1,
> e.input_format as input_format_1,
> e.is_compressed as is_compressed_1,
> e.is_storedassubdirectories as is_storedassubdirectories_1,
> e.location as tbl_location_1,
> e.num_buckets as num_buckets_1,
> e.output_format as output_format_1,
> e.serde_id as serde_id_1
> from tmp1 a
> left join tmp3 d on a.sd_id=d.sd_id
> left join tmp2 b on a.tbl_id=b.tbl_id
> left join tmp3 e on a.sd_id=e.sd_id
> {code}
> The result of this SQL execution is as follows:
> {code:java}
> > select * from pdwd.pdwd_pf_hive_ah3_metastore_tbl_partitions_d_test_0831_4_tbl where pt='2021-08-14' and sd_id=21229815;
> OK
> 721213 1619403652 528181 0 op 0 21229815 pods_pf_yarn_logic_res_i_d MANAGED_TABLE NULL NULL 0 USER 726105 org.apache.hadoop.hive.ql.io.orc.OrcInputFormat 0 0 hdfs://ns4/apps/hive/warehouse/pods/pods_pf_yarn_logic_res_i_d/pt=2021-08-11 -1 org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat 21229815 20302818 1628697610 0 pt=2021-08-11 NULL NULL NULL NULL NULL NULL NULL NULL 2021-08-14
> {code}
> fields cd_id, input_format, is_compressed, is_storedassubdirectories, tbl_or_part_location, num_buckets, output_format, serde_id result {color:#de350b}*is not null*{color} ,but fields cd_id_1, input_format_1, is_compressed_1, is_storedassubdirectories_1, tbl_or_part_location_1, num_buckets_1, output_format_1, serde_id_1 result{color:#de350b} is null{color}
>
>
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)