babelfish-for-postgresql / babelfish_extensions / 8795835939

76%

BABEL_5_X_DEV: 77%

Build Type

push

github

Committed by web-flow

Commit Message

Support unique constraints on nullable column with nulls not distinct (#2518)

Currently babelfish has supported UNIQUE indexes/constraints under the escape hatch 'escape_hatch_unique_constraint'. This is because the current implementation allows duplicate NULL values in a column even when there is a UNIQUE constraint on it.
While the correct expected behaviour for pltsql UNIQUE indexes should be to treat NULL values as NOT DISTINCT. So only one NULL value should be allowed in UNIQUE index. (reject duplicate nulls in column)
We use PGs null not distinct option on column definition to implement this. So pltsql UNIQUE COLUMN CONSTRAINT and CREATE UNIQUE INDEX would implicitly have the nulls not distinct option and only single NULL value will be allowed.

For UNIQUE COLUMN CONSTRAINT we re-write the query string in ANTLR parser while for CREATE INDEX we use the bison parser.

This PR also includes fix for NULLS ordering for UNIQUE indexes created through table or column constraints. Refer engine PR for more information.
Also INLINE INDEX is not currently supported and should throw unsupported error. Test cases added in BABEL-UNSUPPORTED
Remove the escape hatch 'escape_hatch_unique_constraint' since it is no longer needed.

Issues Resolved
[BABEL-3571]


Signed-off-by: Tanzeel Khan <tzlkhan@amazon.com>

Run Details

25 of 28 new or added lines in 3 files covered. (89.29%)

1 existing line in 1 file now uncovered.

41127 of 56711 relevant lines covered (72.52%)

65013.74 hits per line