[CALCITE-4486] UNIQUE predicate

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.28.0
• Component/s: core
• Labels: pull-request-available
• Assignee: xiong duan
• Reporter: Julian Hyde
• Created: 03/Feb/21 18:35
• Updated: 20/Oct/21 05:24
• Resolved: 22/Sep/21 18:36

Description

Implement the UNIQUE predicate, as specified by the SQL standard.

Example:

UNIQUE (SELECT publishedIn FROM Book
            WHERE authorId = 3)

NOT UNIQUE (SELECT publishedIn FROM BOOK
            WHERE authorId = 3)

UNIQUE returns FALSE if the query returns two or more rows that do not include NULL values and are equal. Like EXISTS, it never returns UNKNOWN.

More details at JOOQ.

This Issue support the UNIQUE predicate by set isExpand to return false, so sub-queries are converted to RexSubQuery expressions and handled later by SubQueryRemoveRule.

Issue Links

• is related to: CALCITE-4699 Support expand UNIQUE sub-queries into joins
• relates to: CALCITE-3524 Add methods to RelBuilder to create RexSubQuery
• links to: GitHub Pull Request #2495