[jira] [Comment Edited] (CALCITE-1672) schema name alias doesn't work in INSERT INTO

["Lei Wei (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1672?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15899955#comment-15899955 ] 

Lei Wei edited comment on CALCITE-1672 at 3/7/17 7:07 PM:
----------------------------------------------------------

Thanks Julian for quick responding. 

The real schema name is "dis_20170216103138602" which is code generated. It has a logic/alias schema name "LEI_DEMO_SANDBOX2". I am using Calcite 1.11.0 and I love the alias function. I am able to run SELECT query against alias name, but it doesn't work for INSERT and UPDATE.

select * from LEI_DEMO_SANDBOX2.test_table; // this works fine, and returns resultset
Calcite generates: select * from dis_20170216103138602.test_table;

insert into LEI_DEMO_SANDBOX2.test_table(no) values ('1111111'); // this doesn't work (PSQLException: ERROR: schema "LEI_DEMO_SANDBOX2" does not exist)
update LEI_DEMO_SANDBOX2.test_table set no='1111111'; // this doesn't work (PSQLException: ERROR: schema "LEI_DEMO_SANDBOX2" does not exist)

Looks like Calcite is not using alias schema name in INSERT and UPDATE. I haven't tried DETELE and CREATE TABLE.

Thanks


was (Author: davy-wei):
Thanks Julian for quick responding. 

The real schema name is "dis_20170216103138602" which is code generated. It has a logic/alias schema name "LEI_DEMO_SANDBOX2". I am using Calcite 1.11.0 and I love the alias function. I am able to run SELECT query against alias name, but it doesn't work for INSERT and UPDATE.

select * from LEI_DEMO_SANDBOX2.test_table; // this works fine, and returns resultset

insert into LEI_DEMO_SANDBOX2.test_table(no) values ('1111111'); // this doesn't work (PSQLException: ERROR: schema "LEI_DEMO_SANDBOX2" does not exist)
update LEI_DEMO_SANDBOX2.test_table set no='1111111'; // this doesn't work (PSQLException: ERROR: schema "LEI_DEMO_SANDBOX2" does not exist)

Thanks

> schema name alias doesn't work in INSERT INTO
> ---------------------------------------------
>
>                 Key: CALCITE-1672
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1672
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Lei Wei
>            Assignee: Julian Hyde
>            Priority: Minor
>
> Hi, I setup calcite connecting with posgresql db, and starting avatica to take jdbc connectivities. I used alias on the schemas that added to the root schema. I'm able to run select statements using alias schema, but it doesn't work in INSERT and UPDATE.
> Exception: ExampleExceptionFormatter: exception message was: Error -1 (00000) : Error while executing SQL "update LEI_DEMO_SANDBOX2.test_table set no='1111111'": Remote driver error: RuntimeException: while executing SQL [UPDATE "LEI_DEMO_SANDBOX2"."test_table" SET "no" = '1111111'] -> PSQLException: ERROR: schema "LEI_DEMO_SANDBOX2" does not exist
> ExampleExceptionFormatter: exception message was: Error -1 (00000) : Error while executing SQL "insert into LEI_DEMO_SANDBOX2.test_table(no) values ('1111111')": Remote driver error: RuntimeException: while executing SQL [INSERT INTO "LEI_DEMO_SANDBOX2"."test_table" ("no")
> VALUES  ('1111111')] -> PSQLException: ERROR: schema "LEI_DEMO_SANDBOX2" does not exist



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)