[
https://issues.apache.org/jira/browse/HIVE-27533?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Soumyakanti Das updated HIVE-27533:
-----------------------------------
Description:
For a table that has a composite foreign key, SHOW CREATE TABLE returns
multiple ALTER STATEMENTS, which is incorrect.
For example, in show_create_table.q, we have FK constraints on table TEST3
referencing table TEST:
{code:java}
foreign key(col1, col2) references TEST(col1, col2) disable novalidate rely
{code}
In the output of {{{}SHOW CREATE TABLE TEST3;{}}}, we see that there are two
ALTER TABLE constraints for the composite key, which is incorrect as FK
constraint cannot be on a subset of a composite PK:
{code:java}
ALTER TABLE `default`.`test3` ADD CONSTRAINT `#### A masked pattern was here
####` FOREIGN KEY (`col1`) REFERENCES `default`.`test`(`col1`) DISABLE
NOVALIDATE RELY;
ALTER TABLE `default`.`test3` ADD CONSTRAINT `#### A masked pattern was here
####` FOREIGN KEY (`col2`) REFERENCES `default`.`test`(`col2`) DISABLE
NOVALIDATE RELY; {code}
For this case, we should get a single ALTER TABLE statement like:
{code:java}
ALTER TABLE `default`.`test3` ADD CONSTRAINT `#### A masked pattern was here
####` FOREIGN KEY (`col1`, `col2`) REFERENCES `default`.`test`(`col1`, `col2`)
DISABLE NOVALIDATE RELY; {code}
To reproduce this, please run:
{code:java}
mvn test -Dtest=TestMiniLlapLocalCliDriver -Dtest.output.overwrite=true
-Dqfile=show_create_table.q {code}
was:
For a table that has a composite foreign key, SHOW CREATE TABLE returns
multiple ALTER STATEMENTS, which is incorrect.
For example, for tpcds table {{{}catalog_returns{}}}, we see:
{code:java}
| ALTER TABLE tpcds_orc_def.catalog_returns ADD CONSTRAINT
3abe2c00-25ec-47ca-a5f9-38773995e8c1 FOREIGN KEY (cr_item_sk) REFERENCES
tpcds_orc_def.catalog_sales(cs_item_sk) DISABLE NOVALIDATE RELY; |
| ALTER TABLE tpcds_orc_def.catalog_returns ADD CONSTRAINT
3abe2c00-25ec-47ca-a5f9-38773995e8c1 FOREIGN KEY (cr_order_number) REFERENCES
tpcds_orc_def.catalog_sales(cs_order_number) DISABLE NOVALIDATE RELY; | {code}
Here we see two ALTER STATEMENTS with the same name, which reference primary
keys of table {{{}catalog_sales{}}}. However, this is incorrect as a FK
constraint cannot be on a subset of a composite PK.
> Incorrect FOREIGN KEY constraints in SHOW CREATE TABLE
> ------------------------------------------------------
>
> Key: HIVE-27533
> URL: https://issues.apache.org/jira/browse/HIVE-27533
> Project: Hive
> Issue Type: Bug
> Components: Hive
> Reporter: Soumyakanti Das
> Assignee: Soumyakanti Das
> Priority: Major
> Labels: pull-request-available
>
> For a table that has a composite foreign key, SHOW CREATE TABLE returns
> multiple ALTER STATEMENTS, which is incorrect.
> For example, in show_create_table.q, we have FK constraints on table TEST3
> referencing table TEST:
> {code:java}
> foreign key(col1, col2) references TEST(col1, col2) disable novalidate rely
> {code}
> In the output of {{{}SHOW CREATE TABLE TEST3;{}}}, we see that there are two
> ALTER TABLE constraints for the composite key, which is incorrect as FK
> constraint cannot be on a subset of a composite PK:
> {code:java}
> ALTER TABLE `default`.`test3` ADD CONSTRAINT `#### A masked pattern was here
> ####` FOREIGN KEY (`col1`) REFERENCES `default`.`test`(`col1`) DISABLE
> NOVALIDATE RELY;
> ALTER TABLE `default`.`test3` ADD CONSTRAINT `#### A masked pattern was here
> ####` FOREIGN KEY (`col2`) REFERENCES `default`.`test`(`col2`) DISABLE
> NOVALIDATE RELY; {code}
> For this case, we should get a single ALTER TABLE statement like:
> {code:java}
> ALTER TABLE `default`.`test3` ADD CONSTRAINT `#### A masked pattern was here
> ####` FOREIGN KEY (`col1`, `col2`) REFERENCES `default`.`test`(`col1`,
> `col2`) DISABLE NOVALIDATE RELY; {code}
> To reproduce this, please run:
> {code:java}
> mvn test -Dtest=TestMiniLlapLocalCliDriver -Dtest.output.overwrite=true
> -Dqfile=show_create_table.q {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
