[jira] [Commented] (HIVE-16575) Support for 'UNIQUE' and 'NOT NULL' constraints

[Lefty Leverenz (JIRA)]

[ 
https://issues.apache.org/jira/browse/HIVE-16575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16216380#comment-16216380
 ] 

Lefty Leverenz commented on HIVE-16575:
---

Doc note:  UNIQUE needs to be added to the list of reserved keywords in the DDL 
wikidoc, and the UNIQUE and NOT NULL constraints need to be documented (with 
version information) in these Create Table and Alter Table sections:

* [DDL -- Reserved Keywords | 
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-ReservedKeywords]
* [DDL -- Create Table -- Constraints | 
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Constraints]
* [DDL -- Alter Table Constraints | 
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterTableConstraints]

> Support for 'UNIQUE' and 'NOT NULL' constraints
> ---
>
> Key: HIVE-16575
> URL: https://issues.apache.org/jira/browse/HIVE-16575
> Project: Hive
>  Issue Type: New Feature
>  Components: CBO, Logical Optimizer, Parser
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
>  Labels: TODOC3.0, incompatibleChange, releasenotes
> Fix For: 3.0.0
>
> Attachments: HIVE-16575.01.patch, HIVE-16575.02.patch, 
> HIVE-16575.03.patch, HIVE-16575.04.patch, HIVE-16575.patch
>
>
> Follow-up on HIVE-13076.
> This issue add support for SQL 'UNIQUE' and 'NOT NULL' constraints when we 
> create a table / alter a table 
> (https://www.postgresql.org/docs/9.6/static/sql-createtable.html).
> As with PK and FK constraints, currently we do not enforce them; thus, the 
> constraints need to use the DISABLE option, but they will be stored and can 
> be enabled for rewriting/optimization using RELY.
> This patch also adds support for inlining the constraints next to the column 
> type definition, i.e., 'column constraints'.
> Some examples of the extension to the syntax included in the patch:
> {code:sql}
> CREATE TABLE table3 (x string NOT NULL DISABLE, PRIMARY KEY (x) DISABLE, 
> CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES table2(a) DISABLE); 
> CREATE TABLE table4 (x string CONSTRAINT nn4_1 NOT NULL DISABLE, y string 
> CONSTRAINT nn4_2 NOT NULL DISABLE, UNIQUE (x) DISABLE, CONSTRAINT fk2 FOREIGN 
> KEY (x) REFERENCES table2(a) DISABLE, 
> CONSTRAINT fk3 FOREIGN KEY (y) REFERENCES table2(a) DISABLE);
> CREATE TABLE table12 (a STRING CONSTRAINT nn12_1 NOT NULL DISABLE NORELY, b 
> STRING);
> CREATE TABLE table13 (a STRING NOT NULL DISABLE RELY, b STRING);
> CREATE TABLE table14 (a STRING CONSTRAINT nn14_1 NOT NULL DISABLE RELY, b 
> STRING);
> CREATE TABLE table15 (a STRING REFERENCES table4(x) DISABLE, b STRING);
> CREATE TABLE table16 (a STRING CONSTRAINT nn16_1 REFERENCES table4(x) DISABLE 
> RELY, b STRING);
> ALTER TABLE table16 CHANGE a a STRING REFERENCES table4(x) DISABLE NOVALIDATE;
> ALTER TABLE table12 CHANGE COLUMN b b STRING CONSTRAINT nn12_2 NOT NULL 
> DISABLE NOVALIDATE;
> ALTER TABLE table13 CHANGE b b STRING NOT NULL DISABLE NOVALIDATE;
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (HIVE-16575) Support for 'UNIQUE' and 'NOT NULL' constraints

[Jesus Camacho Rodriguez (JIRA)]

[ 
https://issues.apache.org/jira/browse/HIVE-16575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16025953#comment-16025953
 ] 

Jesus Camacho Rodriguez commented on HIVE-16575:


'UNIQUE' has been added to reserved keywords (in standard from SQL92).

> Support for 'UNIQUE' and 'NOT NULL' constraints
> ---
>
> Key: HIVE-16575
> URL: https://issues.apache.org/jira/browse/HIVE-16575
> Project: Hive
>  Issue Type: New Feature
>  Components: CBO, Logical Optimizer, Parser
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
>  Labels: TODOC3.0, incompatibleChange, releasenotes
> Fix For: 3.0.0
>
> Attachments: HIVE-16575.01.patch, HIVE-16575.02.patch, 
> HIVE-16575.03.patch, HIVE-16575.04.patch, HIVE-16575.patch
>
>
> Follow-up on HIVE-13076.
> This issue add support for SQL 'UNIQUE' and 'NOT NULL' constraints when we 
> create a table / alter a table 
> (https://www.postgresql.org/docs/9.6/static/sql-createtable.html).
> As with PK and FK constraints, currently we do not enforce them; thus, the 
> constraints need to use the DISABLE option, but they will be stored and can 
> be enabled for rewriting/optimization using RELY.
> This patch also adds support for inlining the constraints next to the column 
> type definition, i.e., 'column constraints'.
> Some examples of the extension to the syntax included in the patch:
> {code:sql}
> CREATE TABLE table3 (x string NOT NULL DISABLE, PRIMARY KEY (x) DISABLE, 
> CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES table2(a) DISABLE); 
> CREATE TABLE table4 (x string CONSTRAINT nn4_1 NOT NULL DISABLE, y string 
> CONSTRAINT nn4_2 NOT NULL DISABLE, UNIQUE (x) DISABLE, CONSTRAINT fk2 FOREIGN 
> KEY (x) REFERENCES table2(a) DISABLE, 
> CONSTRAINT fk3 FOREIGN KEY (y) REFERENCES table2(a) DISABLE);
> CREATE TABLE table12 (a STRING CONSTRAINT nn12_1 NOT NULL DISABLE NORELY, b 
> STRING);
> CREATE TABLE table13 (a STRING NOT NULL DISABLE RELY, b STRING);
> CREATE TABLE table14 (a STRING CONSTRAINT nn14_1 NOT NULL DISABLE RELY, b 
> STRING);
> CREATE TABLE table15 (a STRING REFERENCES table4(x) DISABLE, b STRING);
> CREATE TABLE table16 (a STRING CONSTRAINT nn16_1 REFERENCES table4(x) DISABLE 
> RELY, b STRING);
> ALTER TABLE table16 CHANGE a a STRING REFERENCES table4(x) DISABLE NOVALIDATE;
> ALTER TABLE table12 CHANGE COLUMN b b STRING CONSTRAINT nn12_2 NOT NULL 
> DISABLE NOVALIDATE;
> ALTER TABLE table13 CHANGE b b STRING NOT NULL DISABLE NOVALIDATE;
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (HIVE-16575) Support for 'UNIQUE' and 'NOT NULL' constraints

[Ashutosh Chauhan (JIRA)]

[ 
https://issues.apache.org/jira/browse/HIVE-16575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16023542#comment-16023542
 ] 

Ashutosh Chauhan commented on HIVE-16575:
-

+1

> Support for 'UNIQUE' and 'NOT NULL' constraints
> ---
>
> Key: HIVE-16575
> URL: https://issues.apache.org/jira/browse/HIVE-16575
> Project: Hive
>  Issue Type: New Feature
>  Components: CBO, Logical Optimizer, Parser
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-16575.01.patch, HIVE-16575.02.patch, 
> HIVE-16575.03.patch, HIVE-16575.04.patch, HIVE-16575.patch
>
>
> Follow-up on HIVE-13076.
> This issue add support for SQL 'UNIQUE' and 'NOT NULL' constraints when we 
> create a table / alter a table 
> (https://www.postgresql.org/docs/9.6/static/sql-createtable.html).
> As with PK and FK constraints, currently we do not enforce them; thus, the 
> constraints need to use the DISABLE option, but they will be stored and can 
> be enabled for rewriting/optimization using RELY.
> This patch also adds support for inlining the constraints next to the column 
> type definition, i.e., 'column constraints'.
> Some examples of the extension to the syntax included in the patch:
> {code:sql}
> CREATE TABLE table3 (x string NOT NULL DISABLE, PRIMARY KEY (x) DISABLE, 
> CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES table2(a) DISABLE); 
> CREATE TABLE table4 (x string CONSTRAINT nn4_1 NOT NULL DISABLE, y string 
> CONSTRAINT nn4_2 NOT NULL DISABLE, UNIQUE (x) DISABLE, CONSTRAINT fk2 FOREIGN 
> KEY (x) REFERENCES table2(a) DISABLE, 
> CONSTRAINT fk3 FOREIGN KEY (y) REFERENCES table2(a) DISABLE);
> CREATE TABLE table12 (a STRING CONSTRAINT nn12_1 NOT NULL DISABLE NORELY, b 
> STRING);
> CREATE TABLE table13 (a STRING NOT NULL DISABLE RELY, b STRING);
> CREATE TABLE table14 (a STRING CONSTRAINT nn14_1 NOT NULL DISABLE RELY, b 
> STRING);
> CREATE TABLE table15 (a STRING REFERENCES table4(x) DISABLE, b STRING);
> CREATE TABLE table16 (a STRING CONSTRAINT nn16_1 REFERENCES table4(x) DISABLE 
> RELY, b STRING);
> ALTER TABLE table16 CHANGE a a STRING REFERENCES table4(x) DISABLE NOVALIDATE;
> ALTER TABLE table12 CHANGE COLUMN b b STRING CONSTRAINT nn12_2 NOT NULL 
> DISABLE NOVALIDATE;
> ALTER TABLE table13 CHANGE b b STRING NOT NULL DISABLE NOVALIDATE;
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (HIVE-16575) Support for 'UNIQUE' and 'NOT NULL' constraints

[Hive QA (JIRA)]

[ 
https://issues.apache.org/jira/browse/HIVE-16575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16021289#comment-16021289
 ] 

Hive QA commented on HIVE-16575:




Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12869459/HIVE-16575.04.patch

{color:green}SUCCESS:{color} +1 due to 20 test(s) being added or modified.

{color:red}ERROR:{color} -1 due to 1 failed/errored test(s), 10751 tests 
executed
*Failed tests:*
{noformat}
org.apache.hadoop.hive.cli.TestNegativeCliDriver.testCliDriver[create_with_constraints_validate]
 (batchId=88)
{noformat}

Test results: https://builds.apache.org/job/PreCommit-HIVE-Build/5399/testReport
Console output: https://builds.apache.org/job/PreCommit-HIVE-Build/5399/console
Test logs: http://104.198.109.242/logs/PreCommit-HIVE-Build-5399/

Messages:
{noformat}
Executing org.apache.hive.ptest.execution.TestCheckPhase
Executing org.apache.hive.ptest.execution.PrepPhase
Executing org.apache.hive.ptest.execution.ExecutionPhase
Executing org.apache.hive.ptest.execution.ReportingPhase
Tests exited with: TestsFailedException: 1 tests failed
{noformat}

This message is automatically generated.

ATTACHMENT ID: 12869459 - PreCommit-HIVE-Build

> Support for 'UNIQUE' and 'NOT NULL' constraints
> ---
>
> Key: HIVE-16575
> URL: https://issues.apache.org/jira/browse/HIVE-16575
> Project: Hive
>  Issue Type: New Feature
>  Components: CBO, Logical Optimizer, Parser
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-16575.01.patch, HIVE-16575.02.patch, 
> HIVE-16575.03.patch, HIVE-16575.04.patch, HIVE-16575.patch
>
>
> Follow-up on HIVE-13076.
> This issue add support for SQL 'UNIQUE' and 'NOT NULL' constraints when we 
> create a table / alter a table 
> (https://www.postgresql.org/docs/9.6/static/sql-createtable.html).
> As with PK and FK constraints, currently we do not enforce them; thus, the 
> constraints need to use the DISABLE option, but they will be stored and can 
> be enabled for rewriting/optimization using RELY.
> This patch also adds support for inlining the constraints next to the column 
> type definition, i.e., 'column constraints'.
> Some examples of the extension to the syntax included in the patch:
> {code:sql}
> CREATE TABLE table3 (x string NOT NULL DISABLE, PRIMARY KEY (x) DISABLE, 
> CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES table2(a) DISABLE); 
> CREATE TABLE table4 (x string CONSTRAINT nn4_1 NOT NULL DISABLE, y string 
> CONSTRAINT nn4_2 NOT NULL DISABLE, UNIQUE (x) DISABLE, CONSTRAINT fk2 FOREIGN 
> KEY (x) REFERENCES table2(a) DISABLE, 
> CONSTRAINT fk3 FOREIGN KEY (y) REFERENCES table2(a) DISABLE);
> CREATE TABLE table12 (a STRING CONSTRAINT nn12_1 NOT NULL DISABLE NORELY, b 
> STRING);
> CREATE TABLE table13 (a STRING NOT NULL DISABLE RELY, b STRING);
> CREATE TABLE table14 (a STRING CONSTRAINT nn14_1 NOT NULL DISABLE RELY, b 
> STRING);
> CREATE TABLE table15 (a STRING REFERENCES table4(x) DISABLE, b STRING);
> CREATE TABLE table16 (a STRING CONSTRAINT nn16_1 REFERENCES table4(x) DISABLE 
> RELY, b STRING);
> ALTER TABLE table16 CHANGE a a STRING REFERENCES table4(x) DISABLE NOVALIDATE;
> ALTER TABLE table12 CHANGE COLUMN b b STRING CONSTRAINT nn12_2 NOT NULL 
> DISABLE NOVALIDATE;
> ALTER TABLE table13 CHANGE b b STRING NOT NULL DISABLE NOVALIDATE;
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (HIVE-16575) Support for 'UNIQUE' and 'NOT NULL' constraints

[Hive QA (JIRA)]

[ 
https://issues.apache.org/jira/browse/HIVE-16575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16021158#comment-16021158
 ] 

Hive QA commented on HIVE-16575:




Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12869449/HIVE-16575.03.patch

{color:green}SUCCESS:{color} +1 due to 20 test(s) being added or modified.

{color:red}ERROR:{color} -1 due to 3 failed/errored test(s), 10751 tests 
executed
*Failed tests:*
{noformat}
org.apache.hadoop.hive.cli.TestBeeLineDriver.testCliDriver[materialized_view_create_rewrite]
 (batchId=236)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[sysdb] 
(batchId=150)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[vector_join30]
 (batchId=149)
{noformat}

Test results: https://builds.apache.org/job/PreCommit-HIVE-Build/5397/testReport
Console output: https://builds.apache.org/job/PreCommit-HIVE-Build/5397/console
Test logs: http://104.198.109.242/logs/PreCommit-HIVE-Build-5397/

Messages:
{noformat}
Executing org.apache.hive.ptest.execution.TestCheckPhase
Executing org.apache.hive.ptest.execution.PrepPhase
Executing org.apache.hive.ptest.execution.ExecutionPhase
Executing org.apache.hive.ptest.execution.ReportingPhase
Tests exited with: TestsFailedException: 3 tests failed
{noformat}

This message is automatically generated.

ATTACHMENT ID: 12869449 - PreCommit-HIVE-Build

> Support for 'UNIQUE' and 'NOT NULL' constraints
> ---
>
> Key: HIVE-16575
> URL: https://issues.apache.org/jira/browse/HIVE-16575
> Project: Hive
>  Issue Type: New Feature
>  Components: CBO, Logical Optimizer, Parser
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-16575.01.patch, HIVE-16575.02.patch, 
> HIVE-16575.03.patch, HIVE-16575.patch
>
>
> Follow-up on HIVE-13076.
> This issue add support for SQL 'UNIQUE' and 'NOT NULL' constraints when we 
> create a table / alter a table 
> (https://www.postgresql.org/docs/9.6/static/sql-createtable.html).
> As with PK and FK constraints, currently we do not enforce them; thus, the 
> constraints need to use the DISABLE option, but they will be stored and can 
> be enabled for rewriting/optimization using RELY.
> This patch also adds support for inlining the constraints next to the column 
> type definition, i.e., 'column constraints'.
> Some examples of the extension to the syntax included in the patch:
> {code:sql}
> CREATE TABLE table3 (x string NOT NULL DISABLE, PRIMARY KEY (x) DISABLE, 
> CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES table2(a) DISABLE); 
> CREATE TABLE table4 (x string CONSTRAINT nn4_1 NOT NULL DISABLE, y string 
> CONSTRAINT nn4_2 NOT NULL DISABLE, UNIQUE (x) DISABLE, CONSTRAINT fk2 FOREIGN 
> KEY (x) REFERENCES table2(a) DISABLE, 
> CONSTRAINT fk3 FOREIGN KEY (y) REFERENCES table2(a) DISABLE);
> CREATE TABLE table12 (a STRING CONSTRAINT nn12_1 NOT NULL DISABLE NORELY, b 
> STRING);
> CREATE TABLE table13 (a STRING NOT NULL DISABLE RELY, b STRING);
> CREATE TABLE table14 (a STRING CONSTRAINT nn14_1 NOT NULL DISABLE RELY, b 
> STRING);
> CREATE TABLE table15 (a STRING REFERENCES table4(x) DISABLE, b STRING);
> CREATE TABLE table16 (a STRING CONSTRAINT nn16_1 REFERENCES table4(x) DISABLE 
> RELY, b STRING);
> ALTER TABLE table16 CHANGE a a STRING REFERENCES table4(x) DISABLE NOVALIDATE;
> ALTER TABLE table12 CHANGE COLUMN b b STRING CONSTRAINT nn12_2 NOT NULL 
> DISABLE NOVALIDATE;
> ALTER TABLE table13 CHANGE b b STRING NOT NULL DISABLE NOVALIDATE;
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (HIVE-16575) Support for 'UNIQUE' and 'NOT NULL' constraints

[Jesus Camacho Rodriguez (JIRA)]

[ 
https://issues.apache.org/jira/browse/HIVE-16575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16012323#comment-16012323
 ] 

Jesus Camacho Rodriguez commented on HIVE-16575:


[~ashutoshc], could you review this one so we can check it in? 
https://github.com/apache/hive/pull/175
I plan to tackle HIVE-16603 and HIVE-16604 if follow-ups once it is pushed.

Patch is huge, but mostly because of auto-generated files.

Thanks

> Support for 'UNIQUE' and 'NOT NULL' constraints
> ---
>
> Key: HIVE-16575
> URL: https://issues.apache.org/jira/browse/HIVE-16575
> Project: Hive
>  Issue Type: New Feature
>  Components: CBO, Logical Optimizer, Parser
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-16575.01.patch, HIVE-16575.02.patch, 
> HIVE-16575.patch
>
>
> Follow-up on HIVE-13076.
> This issue add support for SQL 'UNIQUE' and 'NOT NULL' constraints when we 
> create a table / alter a table 
> (https://www.postgresql.org/docs/9.6/static/sql-createtable.html).
> As with PK and FK constraints, currently we do not enforce them; thus, the 
> constraints need to use the DISABLE option, but they will be stored and can 
> be enabled for rewriting/optimization using RELY.
> This patch also adds support for inlining the constraints next to the column 
> type definition, i.e., 'column constraints'.
> Some examples of the extension to the syntax included in the patch:
> {code:sql}
> CREATE TABLE table3 (x string NOT NULL DISABLE, PRIMARY KEY (x) DISABLE, 
> CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES table2(a) DISABLE); 
> CREATE TABLE table4 (x string CONSTRAINT nn4_1 NOT NULL DISABLE, y string 
> CONSTRAINT nn4_2 NOT NULL DISABLE, UNIQUE (x) DISABLE, CONSTRAINT fk2 FOREIGN 
> KEY (x) REFERENCES table2(a) DISABLE, 
> CONSTRAINT fk3 FOREIGN KEY (y) REFERENCES table2(a) DISABLE);
> CREATE TABLE table12 (a STRING CONSTRAINT nn12_1 NOT NULL DISABLE NORELY, b 
> STRING);
> CREATE TABLE table13 (a STRING NOT NULL DISABLE RELY, b STRING);
> CREATE TABLE table14 (a STRING CONSTRAINT nn14_1 NOT NULL DISABLE RELY, b 
> STRING);
> CREATE TABLE table15 (a STRING REFERENCES table4(x) DISABLE, b STRING);
> CREATE TABLE table16 (a STRING CONSTRAINT nn16_1 REFERENCES table4(x) DISABLE 
> RELY, b STRING);
> ALTER TABLE table16 CHANGE a a STRING REFERENCES table4(x) DISABLE NOVALIDATE;
> ALTER TABLE table12 CHANGE COLUMN b b STRING CONSTRAINT nn12_2 NOT NULL 
> DISABLE NOVALIDATE;
> ALTER TABLE table13 CHANGE b b STRING NOT NULL DISABLE NOVALIDATE;
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (HIVE-16575) Support for 'UNIQUE' and 'NOT NULL' constraints

[Hive QA (JIRA)]

[ 
https://issues.apache.org/jira/browse/HIVE-16575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16001097#comment-16001097
 ] 

Hive QA commented on HIVE-16575:




Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12866874/HIVE-16575.02.patch

{color:green}SUCCESS:{color} +1 due to 19 test(s) being added or modified.

{color:red}ERROR:{color} -1 due to 1 failed/errored test(s), 10659 tests 
executed
*Failed tests:*
{noformat}
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[vector_if_expr]
 (batchId=144)
{noformat}

Test results: https://builds.apache.org/job/PreCommit-HIVE-Build/5114/testReport
Console output: https://builds.apache.org/job/PreCommit-HIVE-Build/5114/console
Test logs: http://104.198.109.242/logs/PreCommit-HIVE-Build-5114/

Messages:
{noformat}
Executing org.apache.hive.ptest.execution.TestCheckPhase
Executing org.apache.hive.ptest.execution.PrepPhase
Executing org.apache.hive.ptest.execution.ExecutionPhase
Executing org.apache.hive.ptest.execution.ReportingPhase
Tests exited with: TestsFailedException: 1 tests failed
{noformat}

This message is automatically generated.

ATTACHMENT ID: 12866874 - PreCommit-HIVE-Build

> Support for 'UNIQUE' and 'NOT NULL' constraints
> ---
>
> Key: HIVE-16575
> URL: https://issues.apache.org/jira/browse/HIVE-16575
> Project: Hive
>  Issue Type: New Feature
>  Components: CBO, Logical Optimizer, Parser
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-16575.01.patch, HIVE-16575.02.patch, 
> HIVE-16575.patch
>
>
> Follow-up on HIVE-13076.
> This issue add support for SQL 'UNIQUE' and 'NOT NULL' constraints when we 
> create a table / alter a table 
> (https://www.postgresql.org/docs/9.6/static/sql-createtable.html).
> As with PK and FK constraints, currently we do not enforce them; thus, the 
> constraints need to use the DISABLE option, but they will be stored and can 
> be enabled for rewriting/optimization using RELY.
> This patch also adds support for inlining the constraints next to the column 
> type definition, i.e., 'column constraints'.
> Some examples of the extension to the syntax included in the patch:
> {code:sql}
> CREATE TABLE table3 (x string NOT NULL DISABLE, PRIMARY KEY (x) DISABLE, 
> CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES table2(a) DISABLE); 
> CREATE TABLE table4 (x string CONSTRAINT nn4_1 NOT NULL DISABLE, y string 
> CONSTRAINT nn4_2 NOT NULL DISABLE, UNIQUE (x) DISABLE, CONSTRAINT fk2 FOREIGN 
> KEY (x) REFERENCES table2(a) DISABLE, 
> CONSTRAINT fk3 FOREIGN KEY (y) REFERENCES table2(a) DISABLE);
> CREATE TABLE table12 (a STRING CONSTRAINT nn12_1 NOT NULL DISABLE NORELY, b 
> STRING);
> CREATE TABLE table13 (a STRING NOT NULL DISABLE RELY, b STRING);
> CREATE TABLE table14 (a STRING CONSTRAINT nn14_1 NOT NULL DISABLE RELY, b 
> STRING);
> CREATE TABLE table15 (a STRING REFERENCES table4(x) DISABLE, b STRING);
> CREATE TABLE table16 (a STRING CONSTRAINT nn16_1 REFERENCES table4(x) DISABLE 
> RELY, b STRING);
> ALTER TABLE table16 CHANGE a a STRING REFERENCES table4(x) DISABLE NOVALIDATE;
> ALTER TABLE table12 CHANGE COLUMN b b STRING CONSTRAINT nn12_2 NOT NULL 
> DISABLE NOVALIDATE;
> ALTER TABLE table13 CHANGE b b STRING NOT NULL DISABLE NOVALIDATE;
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (HIVE-16575) Support for 'UNIQUE' and 'NOT NULL' constraints

[Eugene Koifman (JIRA)]

[ 
https://issues.apache.org/jira/browse/HIVE-16575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15997069#comment-15997069
 ] 

Eugene Koifman commented on HIVE-16575:
---

One way to enforce not null constraint is to have the optimizer add 
enforce_not_null UDF which throws if it sees a NULL.
So if 'b' has not null constraint,
Insert into T select a,b,c... would become
Insert into T select a, enforce_not_null(b), c.

This would work for any table type.

> Support for 'UNIQUE' and 'NOT NULL' constraints
> ---
>
> Key: HIVE-16575
> URL: https://issues.apache.org/jira/browse/HIVE-16575
> Project: Hive
>  Issue Type: New Feature
>  Components: CBO, Logical Optimizer, Parser
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-16575.01.patch, HIVE-16575.patch
>
>
> Follow-up on HIVE-13076.
> This issue add support for SQL 'UNIQUE' and 'NOT NULL' constraints when we 
> create a table / alter a table 
> (https://www.postgresql.org/docs/9.6/static/sql-createtable.html).
> As with PK and FK constraints, currently we do not enforce them; thus, the 
> constraints need to use the DISABLE option, but they will be stored and can 
> be enabled for rewriting/optimization using RELY.
> This patch also adds support for inlining the constraints next to the column 
> type definition, i.e., 'column constraints'.
> Some examples of the extension to the syntax included in the patch:
> {code:sql}
> CREATE TABLE table3 (x string NOT NULL DISABLE, PRIMARY KEY (x) DISABLE, 
> CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES table2(a) DISABLE); 
> CREATE TABLE table4 (x string CONSTRAINT nn4_1 NOT NULL DISABLE, y string 
> CONSTRAINT nn4_2 NOT NULL DISABLE, UNIQUE (x) DISABLE, CONSTRAINT fk2 FOREIGN 
> KEY (x) REFERENCES table2(a) DISABLE, 
> CONSTRAINT fk3 FOREIGN KEY (y) REFERENCES table2(a) DISABLE);
> CREATE TABLE table12 (a STRING CONSTRAINT nn12_1 NOT NULL DISABLE NORELY, b 
> STRING);
> CREATE TABLE table13 (a STRING NOT NULL DISABLE RELY, b STRING);
> CREATE TABLE table14 (a STRING CONSTRAINT nn14_1 NOT NULL DISABLE RELY, b 
> STRING);
> CREATE TABLE table15 (a STRING REFERENCES table4(x) DISABLE, b STRING);
> CREATE TABLE table16 (a STRING CONSTRAINT nn16_1 REFERENCES table4(x) DISABLE 
> RELY, b STRING);
> ALTER TABLE table16 CHANGE a a STRING REFERENCES table4(x) DISABLE NOVALIDATE;
> ALTER TABLE table12 CHANGE COLUMN b b STRING CONSTRAINT nn12_2 NOT NULL 
> DISABLE NOVALIDATE;
> ALTER TABLE table13 CHANGE b b STRING NOT NULL DISABLE NOVALIDATE;
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (HIVE-16575) Support for 'UNIQUE' and 'NOT NULL' constraints

[Jesus Camacho Rodriguez (JIRA)]

[ 
https://issues.apache.org/jira/browse/HIVE-16575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15996813#comment-15996813
 ] 

Jesus Camacho Rodriguez commented on HIVE-16575:


[~cartershanklin], thanks for the feedback. I used with ENABLE/DISABLE and 
VALIDATE/NOVALIDATE because it was already introduced in HIVE-13076 . I can 
change it to [ NOT ] ENFORCED in a follow-up JIRA, as the standard defines it 
that way. RELY would stay the same then.

> Support for 'UNIQUE' and 'NOT NULL' constraints
> ---
>
> Key: HIVE-16575
> URL: https://issues.apache.org/jira/browse/HIVE-16575
> Project: Hive
>  Issue Type: New Feature
>  Components: CBO, Logical Optimizer, Parser
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-16575.01.patch, HIVE-16575.patch
>
>
> Follow-up on HIVE-13076.
> This issue add support for SQL 'UNIQUE' and 'NOT NULL' constraints when we 
> create a table / alter a table 
> (https://www.postgresql.org/docs/9.6/static/sql-createtable.html).
> As with PK and FK constraints, currently we do not enforce them; thus, the 
> constraints need to use the DISABLE option, but they will be stored and can 
> be enabled for rewriting/optimization using RELY.
> This patch also adds support for inlining the constraints next to the column 
> type definition, i.e., 'column constraints'.
> Some examples of the extension to the syntax included in the patch:
> {code:sql}
> CREATE TABLE table3 (x string NOT NULL DISABLE, PRIMARY KEY (x) DISABLE, 
> CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES table2(a) DISABLE); 
> CREATE TABLE table4 (x string CONSTRAINT nn4_1 NOT NULL DISABLE, y string 
> CONSTRAINT nn4_2 NOT NULL DISABLE, UNIQUE (x) DISABLE, CONSTRAINT fk2 FOREIGN 
> KEY (x) REFERENCES table2(a) DISABLE, 
> CONSTRAINT fk3 FOREIGN KEY (y) REFERENCES table2(a) DISABLE);
> CREATE TABLE table12 (a STRING CONSTRAINT nn12_1 NOT NULL DISABLE NORELY, b 
> STRING);
> CREATE TABLE table13 (a STRING NOT NULL DISABLE RELY, b STRING);
> CREATE TABLE table14 (a STRING CONSTRAINT nn14_1 NOT NULL DISABLE RELY, b 
> STRING);
> CREATE TABLE table15 (a STRING REFERENCES table4(x) DISABLE, b STRING);
> CREATE TABLE table16 (a STRING CONSTRAINT nn16_1 REFERENCES table4(x) DISABLE 
> RELY, b STRING);
> ALTER TABLE table16 CHANGE a a STRING REFERENCES table4(x) DISABLE NOVALIDATE;
> ALTER TABLE table12 CHANGE COLUMN b b STRING CONSTRAINT nn12_2 NOT NULL 
> DISABLE NOVALIDATE;
> ALTER TABLE table13 CHANGE b b STRING NOT NULL DISABLE NOVALIDATE;
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (HIVE-16575) Support for 'UNIQUE' and 'NOT NULL' constraints

[Carter Shanklin (JIRA)]

[ 
https://issues.apache.org/jira/browse/HIVE-16575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15996805#comment-15996805
 ] 

Carter Shanklin commented on HIVE-16575:


[~jcamachorodriguez] A note on the standard:

SQL:2011 spec, 10.8, uses [ NOT ] ENFORCED for column constraint 
characteristics.
I think Oracle invented DISABLE and NORELY and maybe others have copied it over 
time.
A quick check I see DB2 uses the [ NOT ] ENFORCED. Teradata uses something else 
entirely.
Personally I think there are advantages to adopting the standard approach.

There is no standard approach for hinting the optimizer so we're on our own 
there.

Second thing, since NOT NULL is so common it would be great to have 
transactional tables start to enforce that. Possibly follow up work. /cc 
[~ekoifman]

> Support for 'UNIQUE' and 'NOT NULL' constraints
> ---
>
> Key: HIVE-16575
> URL: https://issues.apache.org/jira/browse/HIVE-16575
> Project: Hive
>  Issue Type: New Feature
>  Components: CBO, Logical Optimizer, Parser
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-16575.01.patch, HIVE-16575.patch
>
>
> Follow-up on HIVE-13076.
> This issue add support for SQL 'UNIQUE' and 'NOT NULL' constraints when we 
> create a table / alter a table 
> (https://www.postgresql.org/docs/9.6/static/sql-createtable.html).
> As with PK and FK constraints, currently we do not enforce them; thus, the 
> constraints need to use the DISABLE option, but they will be stored and can 
> be enabled for rewriting/optimization using RELY.
> This patch also adds support for inlining the constraints next to the column 
> type definition, i.e., 'column constraints'.
> Some examples of the extension to the syntax included in the patch:
> {code:sql}
> CREATE TABLE table3 (x string NOT NULL DISABLE, PRIMARY KEY (x) DISABLE, 
> CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES table2(a) DISABLE); 
> CREATE TABLE table4 (x string CONSTRAINT nn4_1 NOT NULL DISABLE, y string 
> CONSTRAINT nn4_2 NOT NULL DISABLE, UNIQUE (x) DISABLE, CONSTRAINT fk2 FOREIGN 
> KEY (x) REFERENCES table2(a) DISABLE, 
> CONSTRAINT fk3 FOREIGN KEY (y) REFERENCES table2(a) DISABLE);
> CREATE TABLE table12 (a STRING CONSTRAINT nn12_1 NOT NULL DISABLE NORELY, b 
> STRING);
> CREATE TABLE table13 (a STRING NOT NULL DISABLE RELY, b STRING);
> CREATE TABLE table14 (a STRING CONSTRAINT nn14_1 NOT NULL DISABLE RELY, b 
> STRING);
> CREATE TABLE table15 (a STRING REFERENCES table4(x) DISABLE, b STRING);
> CREATE TABLE table16 (a STRING CONSTRAINT nn16_1 REFERENCES table4(x) DISABLE 
> RELY, b STRING);
> ALTER TABLE table16 CHANGE a a STRING REFERENCES table4(x) DISABLE NOVALIDATE;
> ALTER TABLE table12 CHANGE COLUMN b b STRING CONSTRAINT nn12_2 NOT NULL 
> DISABLE NOVALIDATE;
> ALTER TABLE table13 CHANGE b b STRING NOT NULL DISABLE NOVALIDATE;
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (HIVE-16575) Support for 'UNIQUE' and 'NOT NULL' constraints

[Hive QA (JIRA)]

[ 
https://issues.apache.org/jira/browse/HIVE-16575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15995454#comment-15995454
 ] 

Hive QA commented on HIVE-16575:




Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12866229/HIVE-16575.01.patch

{color:green}SUCCESS:{color} +1 due to 17 test(s) being added or modified.

{color:red}ERROR:{color} -1 due to 5 failed/errored test(s), 10556 tests 
executed
*Failed tests:*
{noformat}
org.apache.hadoop.hive.cli.TestAccumuloCliDriver.testCliDriver[accumulo_index] 
(batchId=225)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[vector_if_expr]
 (batchId=143)
org.apache.hadoop.hive.cli.TestNegativeCliDriver.testCliDriver[create_with_multi_pk_constraint]
 (batchId=87)
org.apache.hadoop.hive.ql.parse.TestHiveDecimalParse.testDecimalType7 
(batchId=259)
org.apache.hive.jdbc.TestJdbcDriver2.org.apache.hive.jdbc.TestJdbcDriver2 
(batchId=221)
{noformat}

Test results: https://builds.apache.org/job/PreCommit-HIVE-Build/5025/testReport
Console output: https://builds.apache.org/job/PreCommit-HIVE-Build/5025/console
Test logs: http://104.198.109.242/logs/PreCommit-HIVE-Build-5025/

Messages:
{noformat}
Executing org.apache.hive.ptest.execution.TestCheckPhase
Executing org.apache.hive.ptest.execution.PrepPhase
Executing org.apache.hive.ptest.execution.ExecutionPhase
Executing org.apache.hive.ptest.execution.ReportingPhase
Tests exited with: TestsFailedException: 5 tests failed
{noformat}

This message is automatically generated.

ATTACHMENT ID: 12866229 - PreCommit-HIVE-Build

> Support for 'UNIQUE' and 'NOT NULL' constraints
> ---
>
> Key: HIVE-16575
> URL: https://issues.apache.org/jira/browse/HIVE-16575
> Project: Hive
>  Issue Type: New Feature
>  Components: CBO, Logical Optimizer, Parser
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-16575.01.patch, HIVE-16575.patch
>
>
> Follow-up on HIVE-13076.
> This issue add support for SQL 'UNIQUE' and 'NOT NULL' constraints when we 
> create a table / alter a table 
> (https://www.postgresql.org/docs/9.6/static/sql-createtable.html).
> As with PK and FK constraints, currently we do not enforce them; thus, the 
> constraints need to use the DISABLE option, but they will be stored and can 
> be enabled for rewriting/optimization using RELY.
> This patch also adds support for inlining the constraints next to the column 
> type definition, i.e., 'column constraints'.
> Some examples of the extension to the syntax included in the patch:
> {code:sql}
> CREATE TABLE table3 (x string NOT NULL DISABLE, PRIMARY KEY (x) DISABLE, 
> CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES table2(a) DISABLE); 
> CREATE TABLE table4 (x string CONSTRAINT nn4_1 NOT NULL DISABLE, y string 
> CONSTRAINT nn4_2 NOT NULL DISABLE, UNIQUE (x) DISABLE, CONSTRAINT fk2 FOREIGN 
> KEY (x) REFERENCES table2(a) DISABLE, 
> CONSTRAINT fk3 FOREIGN KEY (y) REFERENCES table2(a) DISABLE);
> CREATE TABLE table12 (a STRING CONSTRAINT nn12_1 NOT NULL DISABLE NORELY, b 
> STRING);
> CREATE TABLE table13 (a STRING NOT NULL DISABLE RELY, b STRING);
> CREATE TABLE table14 (a STRING CONSTRAINT nn14_1 NOT NULL DISABLE RELY, b 
> STRING);
> CREATE TABLE table15 (a STRING REFERENCES table4(x) DISABLE, b STRING);
> CREATE TABLE table16 (a STRING CONSTRAINT nn16_1 REFERENCES table4(x) DISABLE 
> RELY, b STRING);
> ALTER TABLE table16 CHANGE a a STRING REFERENCES table4(x) DISABLE NOVALIDATE;
> ALTER TABLE table12 CHANGE COLUMN b b STRING CONSTRAINT nn12_2 NOT NULL 
> DISABLE NOVALIDATE;
> ALTER TABLE table13 CHANGE b b STRING NOT NULL DISABLE NOVALIDATE;
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (HIVE-16575) Support for 'UNIQUE' and 'NOT NULL' constraints

[Hive QA (JIRA)]

[ 
https://issues.apache.org/jira/browse/HIVE-16575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15995203#comment-15995203
 ] 

Hive QA commented on HIVE-16575:




Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12866198/HIVE-16575.patch

{color:green}SUCCESS:{color} +1 due to 7 test(s) being added or modified.

{color:red}ERROR:{color} -1 due to 21 failed/errored test(s), 10557 tests 
executed
*Failed tests:*
{noformat}
org.apache.hadoop.hive.cli.TestAccumuloCliDriver.testCliDriver[accumulo_index] 
(batchId=225)
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver[create_with_constraints] 
(batchId=64)
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver[vector_if_expr]
 (batchId=143)
org.apache.hadoop.hive.cli.TestNegativeCliDriver.testCliDriver[alter_table_constraint_duplicate_pk]
 (batchId=88)
org.apache.hadoop.hive.cli.TestNegativeCliDriver.testCliDriver[alter_table_constraint_invalid_fk_col1]
 (batchId=87)
org.apache.hadoop.hive.cli.TestNegativeCliDriver.testCliDriver[alter_table_constraint_invalid_fk_col2]
 (batchId=88)
org.apache.hadoop.hive.cli.TestNegativeCliDriver.testCliDriver[alter_table_constraint_invalid_fk_tbl1]
 (batchId=88)
org.apache.hadoop.hive.cli.TestNegativeCliDriver.testCliDriver[alter_table_constraint_invalid_fk_tbl2]
 (batchId=88)
org.apache.hadoop.hive.cli.TestNegativeCliDriver.testCliDriver[alter_table_constraint_invalid_pk_tbl]
 (batchId=87)
org.apache.hadoop.hive.cli.TestNegativeCliDriver.testCliDriver[create_with_constraints_enable]
 (batchId=88)
org.apache.hadoop.hive.cli.TestNegativeCliDriver.testCliDriver[create_with_fk_constraint_2]
 (batchId=87)
org.apache.hadoop.hive.cli.TestNegativeCliDriver.testCliDriver[drop_invalid_constraint1]
 (batchId=87)
org.apache.hadoop.hive.cli.TestNegativeCliDriver.testCliDriver[drop_invalid_constraint2]
 (batchId=87)
org.apache.hadoop.hive.cli.TestNegativeCliDriver.testCliDriver[drop_invalid_constraint3]
 (batchId=88)
org.apache.hadoop.hive.cli.TestNegativeCliDriver.testCliDriver[drop_invalid_constraint4]
 (batchId=88)
org.apache.hadoop.hive.ql.TestErrorMsg.testUniqueErrorCode (batchId=252)
org.apache.hadoop.hive.ql.parse.TestHiveDecimalParse.testDecimalType7 
(batchId=259)
org.apache.hive.hcatalog.pig.TestRCFileHCatStorer.testMultiPartColsInData 
(batchId=178)
org.apache.hive.hcatalog.pig.TestRCFileHCatStorer.testWriteDecimalX 
(batchId=178)
org.apache.hive.hcatalog.pig.TestTextFileHCatStorer.testWriteChar (batchId=178)
org.apache.hive.jdbc.TestJdbcDriver2.org.apache.hive.jdbc.TestJdbcDriver2 
(batchId=221)
{noformat}

Test results: https://builds.apache.org/job/PreCommit-HIVE-Build/5024/testReport
Console output: https://builds.apache.org/job/PreCommit-HIVE-Build/5024/console
Test logs: http://104.198.109.242/logs/PreCommit-HIVE-Build-5024/

Messages:
{noformat}
Executing org.apache.hive.ptest.execution.TestCheckPhase
Executing org.apache.hive.ptest.execution.PrepPhase
Executing org.apache.hive.ptest.execution.ExecutionPhase
Executing org.apache.hive.ptest.execution.ReportingPhase
Tests exited with: TestsFailedException: 21 tests failed
{noformat}

This message is automatically generated.

ATTACHMENT ID: 12866198 - PreCommit-HIVE-Build

> Support for 'UNIQUE' and 'NOT NULL' constraints
> ---
>
> Key: HIVE-16575
> URL: https://issues.apache.org/jira/browse/HIVE-16575
> Project: Hive
>  Issue Type: New Feature
>  Components: CBO, Logical Optimizer, Parser
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
> Attachments: HIVE-16575.patch
>
>
> Follow-up on HIVE-13076.
> This issue add support for SQL 'UNIQUE' and 'NOT NULL' constraints when we 
> create a table / alter a table 
> (https://www.postgresql.org/docs/9.6/static/sql-createtable.html).
> As with PK and FK constraints, currently we do not enforce them; thus, the 
> constraints need to use the DISABLE option, but they will be stored and can 
> be enabled for rewriting/optimization using RELY.
> This patch also adds support for inlining the constraints next to the column 
> type definition, i.e., 'column constraints'.
> Some examples of the extension to the syntax included in the patch:
> {code:sql}
> CREATE TABLE table3 (x string NOT NULL DISABLE, PRIMARY KEY (x) DISABLE, 
> CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES table2(a) DISABLE); 
> CREATE TABLE table4 (x string CONSTRAINT nn4_1 NOT NULL DISABLE, y string 
> CONSTRAINT nn4_2 NOT NULL DISABLE, UNIQUE (x) DISABLE, CONSTRAINT fk2 FOREIGN 
> KEY (x) REFERENCES table2(a) DISABLE, 
> CONSTRAINT fk3 FOREIGN KEY (y) REFERENCES table2(a) DISABLE);
> CREATE TABLE table12 (a STRING CONSTRAINT nn12_1 NOT NULL DISABLE NORELY, b 
> STRING);
> CREATE TABLE table13 (a STRING NOT NULL DISABLE RELY, b STRING);
> CREATE TABLE table14 (a STRING 

[jira] [Commented] (HIVE-16575) Support for 'UNIQUE' and 'NOT NULL' constraints

[Jesus Camacho Rodriguez (JIRA)]

[ 
https://issues.apache.org/jira/browse/HIVE-16575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15995071#comment-15995071
 ] 

Jesus Camacho Rodriguez commented on HIVE-16575:


[~ashutoshc], I have created a PR in https://github.com/apache/hive/pull/175 . 
Could you take a look?

One thing that I observed is that currently we do not enforce _foreign keys_ to 
refer to _primary keys_ or _unique keys_ (as opposed to PostgreSQL and others); 
I will tackle that extension in a follow-up.

> Support for 'UNIQUE' and 'NOT NULL' constraints
> ---
>
> Key: HIVE-16575
> URL: https://issues.apache.org/jira/browse/HIVE-16575
> Project: Hive
>  Issue Type: New Feature
>  Components: CBO, Logical Optimizer, Parser
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
>
> Follow-up on HIVE-13076.
> This issue add support for SQL 'UNIQUE' and 'NOT NULL' constraints when we 
> create a table / alter a table 
> (https://www.postgresql.org/docs/9.6/static/sql-createtable.html).
> As with PK and FK constraints, currently we do not enforce them; thus, the 
> constraints need to use the DISABLE option, but they will be stored and can 
> be enabled for rewriting/optimization using RELY.
> This patch also adds support for inlining the constraints next to the column 
> type definition, i.e., 'column constraints'.
> Some examples of the extension to the syntax included in the patch:
> {code:sql}
> CREATE TABLE table3 (x string NOT NULL DISABLE, PRIMARY KEY (x) DISABLE, 
> CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES table2(a) DISABLE); 
> CREATE TABLE table4 (x string CONSTRAINT nn4_1 NOT NULL DISABLE, y string 
> CONSTRAINT nn4_2 NOT NULL DISABLE, UNIQUE (x) DISABLE, CONSTRAINT fk2 FOREIGN 
> KEY (x) REFERENCES table2(a) DISABLE, 
> CONSTRAINT fk3 FOREIGN KEY (y) REFERENCES table2(a) DISABLE);
> CREATE TABLE table12 (a STRING CONSTRAINT nn12_1 NOT NULL DISABLE NORELY, b 
> STRING);
> CREATE TABLE table13 (a STRING NOT NULL DISABLE RELY, b STRING);
> CREATE TABLE table14 (a STRING CONSTRAINT nn14_1 NOT NULL DISABLE RELY, b 
> STRING);
> CREATE TABLE table15 (a STRING REFERENCES table4(x) DISABLE, b STRING);
> CREATE TABLE table16 (a STRING CONSTRAINT nn16_1 REFERENCES table4(x) DISABLE 
> RELY, b STRING);
> ALTER TABLE table16 CHANGE a a STRING REFERENCES table4(x) DISABLE NOVALIDATE;
> ALTER TABLE table12 CHANGE COLUMN b b STRING CONSTRAINT nn12_2 NOT NULL 
> DISABLE NOVALIDATE;
> ALTER TABLE table13 CHANGE b b STRING NOT NULL DISABLE NOVALIDATE;
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (HIVE-16575) Support for 'UNIQUE' and 'NOT NULL' constraints

[ASF GitHub Bot (JIRA)]

[ 
https://issues.apache.org/jira/browse/HIVE-16575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15995065#comment-15995065
 ] 

ASF GitHub Bot commented on HIVE-16575:
---

GitHub user jcamachor opened a pull request:

https://github.com/apache/hive/pull/175

HIVE-16575: Support for 'UNIQUE' and 'NOT NULL' constraints



You can merge this pull request into a Git repository by running:

$ git pull https://github.com/jcamachor/hive not_null

Alternatively you can review and apply these changes as the patch at:

https://github.com/apache/hive/pull/175.patch

To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:

This closes #175


commit 686ed38fdcf5b67f9748631795dbeaf2a8a2b692
Author: Jesus Camacho Rodriguez 
Date:   2017-05-03T09:09:49Z

HIVE-16575: Support for 'UNIQUE' and 'NOT NULL' constraints




> Support for 'UNIQUE' and 'NOT NULL' constraints
> ---
>
> Key: HIVE-16575
> URL: https://issues.apache.org/jira/browse/HIVE-16575
> Project: Hive
>  Issue Type: New Feature
>  Components: CBO, Logical Optimizer, Parser
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
>
> Follow-up on HIVE-13076.
> This issue add support for SQL 'UNIQUE' and 'NOT NULL' constraints when we 
> create a table / alter a table 
> (https://www.postgresql.org/docs/9.6/static/sql-createtable.html).
> As with PK and FK constraints, currently we do not enforce them; thus, the 
> constraints need to use the DISABLE option, but they will be stored and can 
> be enabled for rewriting/optimization using RELY.
> This patch also adds support for inlining the constraints next to the column 
> type definition, i.e., 'column constraints'.
> Some examples of the extension to the syntax included in the patch:
> {code:sql}
> CREATE TABLE table3 (x string NOT NULL DISABLE, PRIMARY KEY (x) DISABLE, 
> CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES table2(a) DISABLE); 
> CREATE TABLE table4 (x string CONSTRAINT nn4_1 NOT NULL DISABLE, y string 
> CONSTRAINT nn4_2 NOT NULL DISABLE, UNIQUE (x) DISABLE, CONSTRAINT fk2 FOREIGN 
> KEY (x) REFERENCES table2(a) DISABLE, 
> CONSTRAINT fk3 FOREIGN KEY (y) REFERENCES table2(a) DISABLE);
> CREATE TABLE table12 (a STRING CONSTRAINT nn12_1 NOT NULL DISABLE NORELY, b 
> STRING);
> CREATE TABLE table13 (a STRING NOT NULL DISABLE RELY, b STRING);
> CREATE TABLE table14 (a STRING CONSTRAINT nn14_1 NOT NULL DISABLE RELY, b 
> STRING);
> CREATE TABLE table15 (a STRING REFERENCES table4(x) DISABLE, b STRING);
> CREATE TABLE table16 (a STRING CONSTRAINT nn16_1 REFERENCES table4(x) DISABLE 
> RELY, b STRING);
> ALTER TABLE table16 CHANGE a a STRING REFERENCES table4(x) DISABLE NOVALIDATE;
> ALTER TABLE table12 CHANGE COLUMN b b STRING CONSTRAINT nn12_2 NOT NULL 
> DISABLE NOVALIDATE;
> ALTER TABLE table13 CHANGE b b STRING NOT NULL DISABLE NOVALIDATE;
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)