babelfish-for-postgresql / babelfish_extensions / 11119835728

75%

Build Type

push

github

Committed by web-flow

Commit Message

Fix CASE statement when branch expression is of String Datatype. (#2931)

Issue:

Return type of overall CASE expression when branch expression is of string datatype gives a different return type as
compared to TSQL expression.

eg. Return type of overall CASE expression is evaluated to become VARCHAR, if all branches are of NVARCHAR type
because Postgres tries to coerce result of every branch expression to base type (VARCHAR), whereas in TSQL, overall
return type is NVARCHAR in this example.

CREATE TABLE ALLTEXTS(
  CODE nvarchar(30) NOT NULL,
  LANG nvarchar(2) NOT NULL,
  TEXT nvarchar(2000) NULL
)

INSERT INTO ALLTEXTS (CODE, LANG, TEXT) VALUES ('53075', 'EN', N'Registration Card - Standard');

INSERT INTO ALLTEXTS (CODE, LANG, TEXT) VALUES ('53075', 'AR', N'بطاقة التسجيل - قياسية');

INSERT INTO ALLTEXTS (CODE, LANG, TEXT) VALUES ('53075', 'JP', N'登録カード–標準');

SELECT CASE WHEN 1 = 1 THEN TEXT END AS WrongValue2 FROM ALLTEXTS

Return type: varchar
expected type: nvarchar

Changes made to fix the issues:

tsql_select_common_type_hook() is set to return common_type for all branches when branch expression is of string
datatype, that calculate according to the TSQL precedence, whereas till now it was set according to the PG precedence
order (which was different from TSQL). This hook solved our problem as it returns result according to the TSQL
precedence order. 

We have also fixed the typmod of resultant expression by calculating the common typmod and setting all branches of
CASE statement to the calculated typmod.

Task: BABEL-5103, BABEL-4332
Signed-off-by: Yashneet Vinayak <yashneet@amazon.com>

Run Details

36 of 36 new or added lines in 1 file covered. (100.0%)

2 existing lines in 2 files now uncovered.

45121 of 60586 relevant lines covered (74.47%)

162232.64 hits per line