[teiid-issues] [JBoss JIRA] (TEIID-2993) Adding source table with quoted name results in wrong generated SQL statement

[Barry LaFond (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-2993?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12974236#comment-12974236 ] 

Barry LaFond commented on TEIID-2993:
-------------------------------------

Back in 8.3.1 we added the validation check to insure that users d-quoted names with "." delimiter because the Permission target's <resource-name> required D-Quotes because the name assumes a *schema.table.column* path. (see TEIIDDES-1993) Also, since users could opt to define their table names in the JDBC importer via "Use Fully Qualified Names" option, we added auto-double-quoting to the names.

EXAMPLE:  

Imported table name = "PARTS_XXX.SUPPLIER_PARTS"
Adding this table to an empty view SQL can result in 2 results
# Drag to T-Diagram >>>   SELECT * FROM  PartsOracle."""PARTS_XXX"."SUPPLIER_PARTS"""
* BTW... querying this works fine
# Paste SELECT * FROM Parts"PARTS_XXX.SUPPLIER_PARTS"  >>> SELECT * FROM PARTS_XXX.SUPPLIER_PARTS (ERROR: Group does not exist: PARTSTEST_VIEWS.PARTSSUPPLIER.SUPPLIER_PARTS)


I can also replace: "PARTSSUPPLIER.SUPPLIER_PARTS" with *PARTSSUPPLIER_SUPPLIER_PARTS* or whatever I want and if the Name In Source is OK then I can query the DB even though our names match, which was my understanding of how Teiid runtimes handles "unknown" names.... basically it falls back on NIS if name can't be found.

So now we have conflicting  requirements in Designer for using '.'  delimiters in names and inconsistent SQL generated depending on which path you choose in designer.

Any ideas on how to clean this up?


> Adding source table with quoted name results in wrong generated SQL statement
> -----------------------------------------------------------------------------
>
>                 Key: TEIID-2993
>                 URL: https://issues.jboss.org/browse/TEIID-2993
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 6.0.0
>            Reporter: Paul Richardson
>            Assignee: Steven Hawkins
>
> Cloned from TEIIDDES-2152:
> 1) Created source model with simple table named "My.Quoted.Table" (including quotes
> 2) Created simple virtual table and pasted:  SELECT	* FROM "My.Quoted.Table"
> 3) Parser returned a command resulting in the SQL: SELECT * FROM My.Quoted."Table"
> Parser should be able to handle quoted names properly. In this case the SQL should have been valid as is.
> Confirmed that this does go right back to the Teiid parser. To confirm, the following test was included in
> src/test/java/org/teiid/query/parser/TestParser.java
> {code}
> @Test
> public void testQuotedSQLString() {
>     String sql = "SELECT * FROM \"My.Quoted.Table\""; //$NON-NLS-1$
>     String expected = "SELECT * FROM \"MY.QUOTED.TABLE\""; //$NON-NLS-1$
>     helpTest(sql, expected, null);
> }
> {code}
> Test fails with a comparison error with the parser returning the SQL in item 3 above.



--
This message was sent by Atlassian JIRA
(v6.2.3#6260)