[JBoss JIRA] (TEIID-5379) LIMIT a OFFSET b
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5379?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5379:
----------------------------------
Component/s: Query Engine
Fix Version/s: 11.0
> LIMIT a OFFSET b
> ----------------
>
> Key: TEIID-5379
> URL: https://issues.jboss.org/browse/TEIID-5379
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Reporter: Lukáš Svačina
> Assignee: Steven Hawkins
> Priority: Minor
> Fix For: 11.0
>
>
> Tradition syntax for limiting result set in teiid sql dialect is not supported.
> *select * from X limit A offset B;*
> It is a problem for SQL builders like JOOQ to work properly with TEIID syntax. I need to build SQL queries by some builder, but i didn't find anything suitable for TEIID SQL syntax.
> JOOQ works quite good with DEFAULT dialect except for limit/offset syntax.
> Are there any other ways how to build syntax correct TEIID SQL queries?
> Please add this syntax support. More info here: https://groups.google.com/forum/#!topic/jooq-user/M1X6_I2JLpg
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5379) LIMIT a OFFSET b
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5379?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5379:
---------------------------------------
Teiid supports both:
LIMIT a, b
OFFSET a ROWS FETCH NEXT b ROWS ONLY
The latter form is the ANSI sql standard.
It should be possible though to add support for an explicit offset:
LIMIT b OFFSET a
> LIMIT a OFFSET b
> ----------------
>
> Key: TEIID-5379
> URL: https://issues.jboss.org/browse/TEIID-5379
> Project: Teiid
> Issue Type: Bug
> Reporter: Lukáš Svačina
> Assignee: Steven Hawkins
> Priority: Minor
> Fix For: 11.0
>
>
> Tradition syntax for limiting result set in teiid sql dialect is not supported.
> *select * from X limit A offset B;*
> It is a problem for SQL builders like JOOQ to work properly with TEIID syntax. I need to build SQL queries by some builder, but i didn't find anything suitable for TEIID SQL syntax.
> JOOQ works quite good with DEFAULT dialect except for limit/offset syntax.
> Are there any other ways how to build syntax correct TEIID SQL queries?
> Please add this syntax support. More info here: https://groups.google.com/forum/#!topic/jooq-user/M1X6_I2JLpg
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5379) LIMIT a OFFSET b
by Lukáš Svačina (JIRA)
Lukáš Svačina created TEIID-5379:
------------------------------------
Summary: LIMIT a OFFSET b
Key: TEIID-5379
URL: https://issues.jboss.org/browse/TEIID-5379
Project: Teiid
Issue Type: Bug
Reporter: Lukáš Svačina
Assignee: Steven Hawkins
Priority: Minor
Tradition syntax for limiting result set in teiid sql dialect is not supported.
*select * from X limit A offset B;*
It is a problem for SQL builders like JOOQ to work properly with TEIID syntax. I need to build SQL queries by some builder, but i didn't find anything suitable for TEIID SQL syntax.
JOOQ works quite good with DEFAULT dialect except for limit/offset syntax.
Are there any other ways how to build syntax correct TEIID SQL queries?
Please add this syntax support. More info here: https://groups.google.com/forum/#!topic/jooq-user/M1X6_I2JLpg
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5377) Type mismatch when issuing recursive common table expression query
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-5377?page=com.atlassian.jira.plugin... ]
Johnathon Lee updated TEIID-5377:
---------------------------------
Fix Version/s: 8.12.14.6_4
> Type mismatch when issuing recursive common table expression query
> ------------------------------------------------------------------
>
> Key: TEIID-5377
> URL: https://issues.jboss.org/browse/TEIID-5377
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.14.6_4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 11.0, 10.3.2, 10.2.3, 8.12.14.6_4
>
>
> Running the following query:
> {code:sql}
> WITH tmp_cte(id, name, fk, fkname, lvl) AS
> (SELECT id, name, fk, cast(NULL as string) as fkname, 0 as lvl
> FROM SourceModel.cte_source WHERE fk IS NULL
> UNION ALL
> SELECT e.id, e.name, e.fk, ecte.name as fkname, lvl + 1 as lvl
> FROM SourceModel.cte_source AS e
> INNER JOIN tmp_cte AS ecte ON ecte.id = e.fk
> )
> SELECT * FROM tmp_cte order by lvl
> {code}
> fails when run against an Oracle or MS SQL server (all versions).
> This issue only occurs with DV 6.4.3 CR1, not with previous versions. Probably caused by fix for TEIID-5313
> h4. For MS SQL Server:
> * name column in source is defined as {{varchar(255)}}
> * name column in VDB is defined as {{string(4000)}}
> * query plan same before and after 6.4.3
> * SOURCE SRC COMMAND before 6.4.3 (working): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(NULL AS varchar(4000)), cast(0 AS int) FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(g_1.name AS varchar(4000)), cast((g_1.lvl + 1) AS int) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
> * SOURCE SRC COMMAND after 6.4.3 (not working, note the only difference is casting to nvarchar): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(NULL AS varchar(4000)), cast(0 AS int) FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(g_1.name AS nvarchar(4000)), cast((g_1.lvl + 1) AS int) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
> h4. For Oracle:
> * name column in source is defined as {{varchar2(255)}}
> * name column in VDB is defined as {{string(4000)}}
> * query plan same before and after 6.4.3
> * SOURCE SRC COMMAND before 6.4.3 (working): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT g_0.id, g_0.name, g_0.fk, NULL, 0 FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT g_0.id, g_0.name, g_0.fk, g_1.name, (g_1.lvl + 1) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
> * SOURCE SRC COMMAND after 6.4.3 (not working, note the only difference is call to {{TO_NCHAR}}): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT g_0.id, g_0.name, g_0.fk, TO_NCHAR(NULL), 0 FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT g_0.id, g_0.name, g_0.fk, g_1.name, (g_1.lvl + 1) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5377) Type mismatch when issuing recursive common table expression query
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5377?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5377.
-----------------------------------
Fix Version/s: 11.0
10.3.2
10.2.3
Resolution: Done
The logic added was too aggressive - any string column from the recursive cte table or any inline view - would be seen as non-ascii as there isn't a metadata object associated with the column. This reverts to a more conservative approach.
> Type mismatch when issuing recursive common table expression query
> ------------------------------------------------------------------
>
> Key: TEIID-5377
> URL: https://issues.jboss.org/browse/TEIID-5377
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.14.6_4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 11.0, 10.3.2, 10.2.3
>
>
> Running the following query:
> {code:sql}
> WITH tmp_cte(id, name, fk, fkname, lvl) AS
> (SELECT id, name, fk, cast(NULL as string) as fkname, 0 as lvl
> FROM SourceModel.cte_source WHERE fk IS NULL
> UNION ALL
> SELECT e.id, e.name, e.fk, ecte.name as fkname, lvl + 1 as lvl
> FROM SourceModel.cte_source AS e
> INNER JOIN tmp_cte AS ecte ON ecte.id = e.fk
> )
> SELECT * FROM tmp_cte order by lvl
> {code}
> fails when run against an Oracle or MS SQL server (all versions).
> This issue only occurs with DV 6.4.3 CR1, not with previous versions. Probably caused by fix for TEIID-5313
> h4. For MS SQL Server:
> * name column in source is defined as {{varchar(255)}}
> * name column in VDB is defined as {{string(4000)}}
> * query plan same before and after 6.4.3
> * SOURCE SRC COMMAND before 6.4.3 (working): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(NULL AS varchar(4000)), cast(0 AS int) FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(g_1.name AS varchar(4000)), cast((g_1.lvl + 1) AS int) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
> * SOURCE SRC COMMAND after 6.4.3 (not working, note the only difference is casting to nvarchar): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(NULL AS varchar(4000)), cast(0 AS int) FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(g_1.name AS nvarchar(4000)), cast((g_1.lvl + 1) AS int) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
> h4. For Oracle:
> * name column in source is defined as {{varchar2(255)}}
> * name column in VDB is defined as {{string(4000)}}
> * query plan same before and after 6.4.3
> * SOURCE SRC COMMAND before 6.4.3 (working): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT g_0.id, g_0.name, g_0.fk, NULL, 0 FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT g_0.id, g_0.name, g_0.fk, g_1.name, (g_1.lvl + 1) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
> * SOURCE SRC COMMAND after 6.4.3 (not working, note the only difference is call to {{TO_NCHAR}}): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT g_0.id, g_0.name, g_0.fk, TO_NCHAR(NULL), 0 FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT g_0.id, g_0.name, g_0.fk, g_1.name, (g_1.lvl + 1) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5378) NPE when inserting into Global Temporary Table from a function or procedure
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5378?page=com.atlassian.jira.plugin... ]
Work on TEIID-5378 started by Steven Hawkins.
---------------------------------------------
> NPE when inserting into Global Temporary Table from a function or procedure
> ---------------------------------------------------------------------------
>
> Key: TEIID-5378
> URL: https://issues.jboss.org/browse/TEIID-5378
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.14.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Critical
>
> For following vdb:
> {code:xml}
> <vdb name="a" version="1">
> <model name="vm1" type="VIRTUAL">
> <metadata type="DDL"><![CDATA[
> CREATE GLOBAL TEMPORARY TABLE teiidtemp(val integer) OPTIONS (UPDATABLE 'TRUE');
> CREATE VIRTUAL FUNCTION f1() RETURNS string AS
> BEGIN
> INSERT INTO teiidtemp(val) VALUES (1);
> DECLARE string v1 = SELECT 'default'||COUNT(val) FROM teiidtemp;
> RETURN v1;
> END;
> CREATE VIRTUAL PROCEDURE p1() RETURNS (v1 string) AS
> BEGIN
> INSERT INTO teiidtemp(val) VALUES (1);
> SELECT 'default'||COUNT(val) FROM teiidtemp;
> END;
> ]]>
> </metadata>
> </model>
> </vdb>
> {code}
> The following errors occur when invoking procedure or function:
> # {code:sql}SELECT f1(){code}
> #* 15:49:17,608 WARN [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue14) TEIID30020 Processing exception for request 3rGTFUcDmmRP.0 'TEIID30328 Unable to evaluate f1(): TEIID30167 java.lang.NullPointerException'. Originally ExpressionEvaluationException TempTableDataManager.java:227. Enable more detailed logging to see the entire stacktrace.
> # {code:sql}SELECT a.v1 FROM (CALL p1()) a{code}
> #* 15:49:59,581 WARN [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue15) TEIID30020 Processing exception for request 3rGTFUcDmmRP.1 'TEIID30167 java.lang.NullPointerException'. Originally ProcedureErrorInstructionException TempTableDataManager.java:227. Enable more detailed logging to see the entire stacktrace.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-3750) Allow the parsing of an expression
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3750?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3750:
---------------------------------------
> FUNCTION execution does not work
That is not supported. There is no concept of a default value for a parameter and only positional invocation is supported (whereas procedures support named parameters)
> According to docs, VIRTUAL FUNCTION can have DEFAULT value specified.
Could have been a copy and paste error from procedures. It's not supported, so that should be corrected.
> Allow the parsing of an expression
> ----------------------------------
>
> Key: TEIID-3750
> URL: https://issues.jboss.org/browse/TEIID-3750
> Project: Teiid
> Issue Type: Sub-task
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.14.6_4
>
>
> Now that we have an extension property, we could allow an expression to be used in parsing rather than just a string.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5377) Type mismatch when issuing recursive common table expression query
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5377?page=com.atlassian.jira.plugin... ]
Steven Hawkins edited comment on TEIID-5377 at 6/15/18 10:23 AM:
-----------------------------------------------------------------
This is a regression from TEIID-5313 affecting rcte, but not regular unions. It affects master as well.
was (Author: shawkins):
This is a regression from TEIID-5313 affecting rcte, but not regular unions.
> Type mismatch when issuing recursive common table expression query
> ------------------------------------------------------------------
>
> Key: TEIID-5377
> URL: https://issues.jboss.org/browse/TEIID-5377
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.14.6_4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Priority: Critical
>
> Running the following query:
> {code:sql}
> WITH tmp_cte(id, name, fk, fkname, lvl) AS
> (SELECT id, name, fk, cast(NULL as string) as fkname, 0 as lvl
> FROM SourceModel.cte_source WHERE fk IS NULL
> UNION ALL
> SELECT e.id, e.name, e.fk, ecte.name as fkname, lvl + 1 as lvl
> FROM SourceModel.cte_source AS e
> INNER JOIN tmp_cte AS ecte ON ecte.id = e.fk
> )
> SELECT * FROM tmp_cte order by lvl
> {code}
> fails when run against an Oracle or MS SQL server (all versions).
> This issue only occurs with DV 6.4.3 CR1, not with previous versions. Probably caused by fix for TEIID-5313
> h4. For MS SQL Server:
> * name column in source is defined as {{varchar(255)}}
> * name column in VDB is defined as {{string(4000)}}
> * query plan same before and after 6.4.3
> * SOURCE SRC COMMAND before 6.4.3 (working): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(NULL AS varchar(4000)), cast(0 AS int) FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(g_1.name AS varchar(4000)), cast((g_1.lvl + 1) AS int) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
> * SOURCE SRC COMMAND after 6.4.3 (not working, note the only difference is casting to nvarchar): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(NULL AS varchar(4000)), cast(0 AS int) FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(g_1.name AS nvarchar(4000)), cast((g_1.lvl + 1) AS int) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
> h4. For Oracle:
> * name column in source is defined as {{varchar2(255)}}
> * name column in VDB is defined as {{string(4000)}}
> * query plan same before and after 6.4.3
> * SOURCE SRC COMMAND before 6.4.3 (working): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT g_0.id, g_0.name, g_0.fk, NULL, 0 FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT g_0.id, g_0.name, g_0.fk, g_1.name, (g_1.lvl + 1) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
> * SOURCE SRC COMMAND after 6.4.3 (not working, note the only difference is call to {{TO_NCHAR}}): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT g_0.id, g_0.name, g_0.fk, TO_NCHAR(NULL), 0 FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT g_0.id, g_0.name, g_0.fk, g_1.name, (g_1.lvl + 1) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5377) Type mismatch when issuing recursive common table expression query
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5377?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5377:
---------------------------------------
This is a regression from TEIID-5313 affecting rcte, but not regular unions.
> Type mismatch when issuing recursive common table expression query
> ------------------------------------------------------------------
>
> Key: TEIID-5377
> URL: https://issues.jboss.org/browse/TEIID-5377
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.14.6_4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Priority: Critical
>
> Running the following query:
> {code:sql}
> WITH tmp_cte(id, name, fk, fkname, lvl) AS
> (SELECT id, name, fk, cast(NULL as string) as fkname, 0 as lvl
> FROM SourceModel.cte_source WHERE fk IS NULL
> UNION ALL
> SELECT e.id, e.name, e.fk, ecte.name as fkname, lvl + 1 as lvl
> FROM SourceModel.cte_source AS e
> INNER JOIN tmp_cte AS ecte ON ecte.id = e.fk
> )
> SELECT * FROM tmp_cte order by lvl
> {code}
> fails when run against an Oracle or MS SQL server (all versions).
> This issue only occurs with DV 6.4.3 CR1, not with previous versions. Probably caused by fix for TEIID-5313
> h4. For MS SQL Server:
> * name column in source is defined as {{varchar(255)}}
> * name column in VDB is defined as {{string(4000)}}
> * query plan same before and after 6.4.3
> * SOURCE SRC COMMAND before 6.4.3 (working): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(NULL AS varchar(4000)), cast(0 AS int) FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(g_1.name AS varchar(4000)), cast((g_1.lvl + 1) AS int) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
> * SOURCE SRC COMMAND after 6.4.3 (not working, note the only difference is casting to nvarchar): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(NULL AS varchar(4000)), cast(0 AS int) FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(g_1.name AS nvarchar(4000)), cast((g_1.lvl + 1) AS int) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
> h4. For Oracle:
> * name column in source is defined as {{varchar2(255)}}
> * name column in VDB is defined as {{string(4000)}}
> * query plan same before and after 6.4.3
> * SOURCE SRC COMMAND before 6.4.3 (working): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT g_0.id, g_0.name, g_0.fk, NULL, 0 FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT g_0.id, g_0.name, g_0.fk, g_1.name, (g_1.lvl + 1) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
> * SOURCE SRC COMMAND after 6.4.3 (not working, note the only difference is call to {{TO_NCHAR}}): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT g_0.id, g_0.name, g_0.fk, TO_NCHAR(NULL), 0 FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT g_0.id, g_0.name, g_0.fk, g_1.name, (g_1.lvl + 1) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months