[teiid-issues] [JBoss JIRA] (TEIID-5223) Problem with union aliasing when bind variables used

[Mike Higgins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-5223?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mike Higgins updated TEIID-5223:
--------------------------------
    Description: 
When a query with a union, function call, and bind variables is issued in 9.3.5, an alias is not applied correctly.  When the same query is run with an explicit constraint list, it works. It works in both cases in  Teiid 9.3.4.

Query is:
SELECT Substances."SUB_ID", 
NADALM_FUNCS.MOLFILE(Substances."XCTAB") "MOLFILE" 
FROM (
SELECT NADALM.SUBSTANCE."SUB_ID", 
NADALM.SUBSTANCE."CTAB" "XCTAB", 
NADALM_FUNCS.MOLFILE(NADALM.SUBSTANCE."CTAB") "MOLFILE" 
FROM nadalm.substance 
UNION ALL 
SELECT D360_DC.DESIGN_COMPOUND."DC_ID" "SUB_ID", 
D360_DC.DESIGN_COMPOUND."CTAB" "XCTAB", 
NADALM_FUNCS.MOLFILE(D360_DC.DESIGN_COMPOUND."CTAB") "MOLFILE" 
FROM d360_dc.design_compound) Substances 
WHERE (Substances."SUB_ID" in (?, ?, ?, ?, ?, ?, ?,?, ?, ?))

Generated SQL with error is:
SELECT v_0.SUB_ID, NADAL_CERTARA.MOLFILE(v_0.c_1) FROM
 (SELECT g_1."SUB_ID" AS c_0, g_1."CTAB" AS c_1 FROM "NADAL_CERTARA"."SUBSTANCE" g_1 WHERE g_1."SUB_ID" IN (?, ?, ?, ?, ?, ?, ?,?, ?, ?) 
UNION ALL 
SELECT g_0."DC_ID" AS c_0, g_0."CTAB" AS c_1 FROM "NADAL_CERTARA"."DESIGN_COMPOUND" g_0 WHERE g_0."DC_ID" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?))) v_0

Note that top branch references v_0.Sub_id, which is not legal (should be c_0).


  was:
When a query with a union, function call, and bind variables is issued in 9.3.5, an alias is not applied correctly.  When the same query is run with an explicit constraint list, it works. It works in both cases in  Teiid 9.3.4.

Query is:
SELECT Substances."SUB_ID", 
NADALM_FUNCS.MOLFILE(Substances."XCTAB") "MOLFILE" 
FROM (
SELECT NADALM.SUBSTANCE."SUB_ID", 
NADALM.SUBSTANCE."CTAB" "XCTAB", 
NADALM_FUNCS.MOLFILE(NADALM.SUBSTANCE."CTAB") "MOLFILE" 
FROM nadalm.substance 
UNION ALL 
SELECT D360_DC.DESIGN_COMPOUND."DC_ID" "SUB_ID", 
D360_DC.DESIGN_COMPOUND."CTAB" "XCTAB", 
NADALM_FUNCS.MOLFILE(D360_DC.DESIGN_COMPOUND."CTAB") "MOLFILE" 
FROM d360_dc.design_compound) Substances 
WHERE (Substances."SUB_ID" in (?, ?, ?, ?, ?, ?, ?,?, ?, ?))

Generated SQL with error is:
SQL: SELECT v_0.SUB_ID, NADAL_CERTARA.MOLFILE(v_0.c_1) FROM (SELECT g_1."SUB_ID" AS c_0, g_1."CTAB" AS c_1 FROM "NADAL_CERTARA"."SUBSTANCE" g_1 WHERE g_1."SUB_ID" IN (?, ?, ?, ?, ?, ?, ?,?, ?, ?) 
UNION ALL 
SELECT g_0."DC_ID" AS c_0, g_0."CTAB" AS c_1 FROM "NADAL_CERTARA"."DESIGN_COMPOUND" g_0 WHERE g_0."DC_ID" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?))

Note that top branch references v_0, which is not legal.




> Problem with union aliasing when bind variables used
> ----------------------------------------------------
>
>                 Key: TEIID-5223
>                 URL: https://issues.jboss.org/browse/TEIID-5223
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 9.3.5
>            Reporter: Mike Higgins
>            Assignee: Steven Hawkins
>
> When a query with a union, function call, and bind variables is issued in 9.3.5, an alias is not applied correctly.  When the same query is run with an explicit constraint list, it works. It works in both cases in  Teiid 9.3.4.
> Query is:
> SELECT Substances."SUB_ID", 
> NADALM_FUNCS.MOLFILE(Substances."XCTAB") "MOLFILE" 
> FROM (
> SELECT NADALM.SUBSTANCE."SUB_ID", 
> NADALM.SUBSTANCE."CTAB" "XCTAB", 
> NADALM_FUNCS.MOLFILE(NADALM.SUBSTANCE."CTAB") "MOLFILE" 
> FROM nadalm.substance 
> UNION ALL 
> SELECT D360_DC.DESIGN_COMPOUND."DC_ID" "SUB_ID", 
> D360_DC.DESIGN_COMPOUND."CTAB" "XCTAB", 
> NADALM_FUNCS.MOLFILE(D360_DC.DESIGN_COMPOUND."CTAB") "MOLFILE" 
> FROM d360_dc.design_compound) Substances 
> WHERE (Substances."SUB_ID" in (?, ?, ?, ?, ?, ?, ?,?, ?, ?))
> Generated SQL with error is:
> SELECT v_0.SUB_ID, NADAL_CERTARA.MOLFILE(v_0.c_1) FROM
>  (SELECT g_1."SUB_ID" AS c_0, g_1."CTAB" AS c_1 FROM "NADAL_CERTARA"."SUBSTANCE" g_1 WHERE g_1."SUB_ID" IN (?, ?, ?, ?, ?, ?, ?,?, ?, ?) 
> UNION ALL 
> SELECT g_0."DC_ID" AS c_0, g_0."CTAB" AS c_1 FROM "NADAL_CERTARA"."DESIGN_COMPOUND" g_0 WHERE g_0."DC_ID" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?))) v_0
> Note that top branch references v_0.Sub_id, which is not legal (should be c_0).



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)