[Bug]: Index creation on table fails if table is created via SELECT INTO command

[andrevb-croesus]

What happened?

When creating an index on a temp table, errors occur.

Could a maintainer please let me know if this is fixed in versions later than 4.0 ?

Here's how to quickly reproduce the issue

create table #PROB(ID int)

select ID into #REPRO from #PROB 
create index IDX_REPRODUCTION on #REPRO(ID)

select #PROB.ID into #REPRO2 from #PROB 
create index IDX_REPRODUCTION2 on #REPRO2(ID)

This error is raised at each create index Msg 33557097, Level 16, State 1, Line 38, column "id" does not exist

Here are some notes I took to find the problem:

drop table if exists #UPPERCASE_TEST_PARENT
drop table if exists #UPPERCASE_TEST_ONE
drop table if exists #UPPERCASE_TEST_TWO

create table #UPPERCASE_TEST_PARENT (ID int, NAME varchar(50))
select * from #UPPERCASE_TEST_PARENT --Columns are created with upper case here.
create index INDEX_THAT_WORKS on #UPPERCASE_TEST_PARENT(ID,NAME) --Create index with upper case columns works.
create index INDEX_THAT_ALSO_WORKS on #UPPERCASE_TEST_PARENT(id,name) --Lower case columns also works.

select #UPPERCASE_TEST_PARENT.ID,#UPPERCASE_TEST_PARENT.NAME into #UPPERCASE_TEST_ONE from #UPPERCASE_TEST_PARENT

--BABEL3_3_0: Columns are created with lower case because of table alias.
--BABEL4_0_0: Upper case column names
select * from #UPPERCASE_TEST_ONE 

select ID,NAME into #UPPERCASE_TEST_TWO from #UPPERCASE_TEST_PARENT
select * from #UPPERCASE_TEST_TWO --Columns create with upper case when no table alias is present.

--This works on tag BABEL 3_3_0 but not on BABEL 4_0_0
create index INDEX_THAT_FAILS_ON_BABEL4 on #UPPERCASE_TEST_ONE(ID,NAME) -- fails only on babel 4_0_0:     column "id" does not exist, Msg 33557097, Level 16, State 1, Line 52

--This does not work.
create index INDEX_THAT_FAILS_ON_BOTH on   #UPPERCASE_TEST_TWO(ID,NAME) -- fails on both 4_0_0 and 3_3_0: column "id" does not exist, Msg 33557097, Level 16, State 1, Line 52

Shown here is the error when creating an index.

Shown here is the behavior difference between version 3.3 and 4

Version

BABEL_3_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

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

Ubuntu (Default)

Relevant log output

column "id" does not exist, Msg 33557097, Level 16, State 1, Line 52

Code of Conduct

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

[Deepesh125]

Thanks @andrevb-croesus for reporting this issue. Please note that this issue is also reproducing on normal table under same scenario.

[tanscorpio7]

Will be fixed in branches 3_X_DEV & 4_X_DEV with these pull requests. https://github.com/babelfish-for-postgresql/babelfish_extensions/pull/2893 https://github.com/babelfish-for-postgresql/babelfish_extensions/pull/2781