[Bug]: REGRESSION (worked in 15.3) Cannot use column named [primary] in DML statements - results in syntax error

[bill-ramos-rmoswi]

What happened?

Environment

• @@Version: Babelfish for Aurora PostgreSQL with SQL Server Compatibility - 12.0.2000.8Sep 16 2024 00:41:51Copyright (c) Amazon Web ServicesPostgreSQL 16.4 on aarch64-unknown-linux-gnu (Babelfish 4.3.0)

Repro steps

Use of direct column name

create table #temp
( 
[primary] smallint
);
select [primary] from #temp;

Select statement results in error:
Msg 33557097, Level 16, State 1, Line 1 syntax error near '[primary]' at line 1 and character position 7

Problem occurs when the column named [primary] is anywhere in the statement.

Use of Primary as alias

drop table if exists #temp;
create table #temp
( 
[primary_column] smallint
, CardBrand nvarchar(25)
, Span nvarchar(4)
);
select [primary_column] AS [Primary],CardBrand,Span from #temp;

Results in error: Msg 33557097, Level 16, State 1, Line 8 syntax error near 'AS' at line 8 and character position 24

Impact

This is a regression from Aurora PostgreSQL 15.3 - Babelfish 3.2.2

@@Version: Babelfish for Aurora PostgreSQL with SQL Server Compatibility - 12.0.2000.8 Sep 11 2024 18:23:43 Copyright (c) Amazon Web Services PostgreSQL 15.3 on x86_64-pc-linux-gnu (Babelfish 3.2.2)

I have a customer trying to go into production with Babelfish that has several tables that use a column named [PRIMARY] that will require many changes in the stored procedures and views that reference the column. Yes, I can change the column name

Version

BABEL_4_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

Which flavor of Linux are you using when you see the bug?

No response

Relevant log output

No response

Code of Conduct

• I agree to follow this project's Code of Conduct.

[staticlibs]

This seems to be caused by this bit in lexer (bracketed [primary] is added as a keyword along with bare primary, originates from #2587).

As a workaround, double quotes can be used instead of brackets:

create table #temp
( 
    "primary" smallint
);
select "primary" from #temp;

[robverschoor]

Yes, this is a regression

[bill-ramos-rmoswi]

Hi @robverschoor, It looks like the check-in with 16.4 is for supporting partition tables. I noticed special handling of [PRIMARY] that is likely the problem.

[robverschoor]

Ack