[JBoss JIRA] (TEIID-5331) LEAD/LAG ignores ORDER BY in the OVER clause
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-5331?page=com.atlassian.jira.plugin... ]
Johnathon Lee updated TEIID-5331:
---------------------------------
Fix Version/s: 8.12.13.6_4
> LEAD/LAG ignores ORDER BY in the OVER clause
> --------------------------------------------
>
> Key: TEIID-5331
> URL: https://issues.jboss.org/browse/TEIID-5331
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.13.6_4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 10.3, 8.12.13.6_4, 10.1.4, 10.2.2
>
>
> Consider the following query:
> {code:sql}
> select charvalue, intkey, lead(intkey, 1, NULL) over (order by charvalue, intkey) from smalla order by intkey
> {code}
> This is expected to mean that the database orders the rows by charvalue, then intkey, then for each row finds the lead value in the ordered data set, _then_ sorts all the results by intkey.
> Indeed, when run against e.g. postgres or oracle datasource, the results are as follows:
> ||charvalue||intkey||lead||
> |1|0|2|
> |0|1|3|
> |1|2|4|
> |0|3|5|
> |1|4|6|
> |...|...|...|
> The results are also the same when running the query against teiid with e.g. postgres datasource, as the lead/lag is pushed down.
> However, when run against teiid with datasource that does not support lead/lag pushdown (such as csv), the results are different:
> ||charvalue||intkey||lead||
> |1|0|1|
> |0|1|2|
> |1|2|3|
> |0|3|4|
> |1|4|5|
> |...|...|...|
> I. e. it appears that the ORDER BY in the OVER clause is ignored and the LEAD/LAG uses the (possibly arbitrary) ordering returned from the datasource (which in this case is actually sorted by intkey, but we've seen random ordering from other datasources such as couchbase).
> Please note that the same result comes from teiid even when removing either of the columns from the OVER (ORDER BY ...) clause, as well when removing the overall ORDER BY clause.
> Processor plan for the above query:
> {code}
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> ProjectNode(0) output=[A.CHARVALUE AS charvalue, A.INTKEY AS intkey, lead(A.INTKEY, 1, null) OVER (ORDER BY A.CHARVALUE, A.INTKEY)] [A.CHARVALUE AS charvalue, A.INTKEY AS intkey, lead(A.INTKEY, 1, null) OVER (ORDER BY A.CHARVALUE, A.INTKEY)]
> WindowFunctionProjectNode(1) output=[lead(A.INTKEY, 1, null) OVER (ORDER BY A.CHARVALUE, A.INTKEY), A.CHARVALUE, A.INTKEY]
> LimitNode(2) output=[A.CHARVALUE, A.INTKEY] limit 100
> SortNode(3) output=[A.CHARVALUE, A.INTKEY] [SORT] [A.INTKEY]
> JoinNode(4) [NESTED TABLE JOIN] [CROSS JOIN] output=[A.CHARVALUE, A.INTKEY]
> ProjectNode(5) output=[file] [sourceModel.getTextFiles.file]
> AccessNode(6) output=[sourceModel.getTextFiles.file, sourceModel.getTextFiles.filePath] EXEC sourceModel.getTextFiles('smallaCsv.csv')
> TextTableNode(7) output=[A.CHARVALUE, A.INTKEY]
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years
[JBoss JIRA] (TEIID-5331) LEAD/LAG ignores ORDER BY in the OVER clause
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-5331?page=com.atlassian.jira.plugin... ]
Johnathon Lee commented on TEIID-5331:
--------------------------------------
thank you [~shawkins].
> LEAD/LAG ignores ORDER BY in the OVER clause
> --------------------------------------------
>
> Key: TEIID-5331
> URL: https://issues.jboss.org/browse/TEIID-5331
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.13.6_4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 10.3, 8.12.13.6_4, 10.1.4, 10.2.2
>
>
> Consider the following query:
> {code:sql}
> select charvalue, intkey, lead(intkey, 1, NULL) over (order by charvalue, intkey) from smalla order by intkey
> {code}
> This is expected to mean that the database orders the rows by charvalue, then intkey, then for each row finds the lead value in the ordered data set, _then_ sorts all the results by intkey.
> Indeed, when run against e.g. postgres or oracle datasource, the results are as follows:
> ||charvalue||intkey||lead||
> |1|0|2|
> |0|1|3|
> |1|2|4|
> |0|3|5|
> |1|4|6|
> |...|...|...|
> The results are also the same when running the query against teiid with e.g. postgres datasource, as the lead/lag is pushed down.
> However, when run against teiid with datasource that does not support lead/lag pushdown (such as csv), the results are different:
> ||charvalue||intkey||lead||
> |1|0|1|
> |0|1|2|
> |1|2|3|
> |0|3|4|
> |1|4|5|
> |...|...|...|
> I. e. it appears that the ORDER BY in the OVER clause is ignored and the LEAD/LAG uses the (possibly arbitrary) ordering returned from the datasource (which in this case is actually sorted by intkey, but we've seen random ordering from other datasources such as couchbase).
> Please note that the same result comes from teiid even when removing either of the columns from the OVER (ORDER BY ...) clause, as well when removing the overall ORDER BY clause.
> Processor plan for the above query:
> {code}
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> ProjectNode(0) output=[A.CHARVALUE AS charvalue, A.INTKEY AS intkey, lead(A.INTKEY, 1, null) OVER (ORDER BY A.CHARVALUE, A.INTKEY)] [A.CHARVALUE AS charvalue, A.INTKEY AS intkey, lead(A.INTKEY, 1, null) OVER (ORDER BY A.CHARVALUE, A.INTKEY)]
> WindowFunctionProjectNode(1) output=[lead(A.INTKEY, 1, null) OVER (ORDER BY A.CHARVALUE, A.INTKEY), A.CHARVALUE, A.INTKEY]
> LimitNode(2) output=[A.CHARVALUE, A.INTKEY] limit 100
> SortNode(3) output=[A.CHARVALUE, A.INTKEY] [SORT] [A.INTKEY]
> JoinNode(4) [NESTED TABLE JOIN] [CROSS JOIN] output=[A.CHARVALUE, A.INTKEY]
> ProjectNode(5) output=[file] [sourceModel.getTextFiles.file]
> AccessNode(6) output=[sourceModel.getTextFiles.file, sourceModel.getTextFiles.filePath] EXEC sourceModel.getTextFiles('smallaCsv.csv')
> TextTableNode(7) output=[A.CHARVALUE, A.INTKEY]
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years
[JBoss JIRA] (TEIID-5331) LEAD/LAG ignores ORDER BY in the OVER clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5331?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5331:
---------------------------------------
It's an issue with the having the older JDK compiler, the analogous line that was already in 6.4 was:
offset = (Integer) args[1];
That is the conversion from the object type to the primitive is supported, but not the direct cast to the primitive.
> LEAD/LAG ignores ORDER BY in the OVER clause
> --------------------------------------------
>
> Key: TEIID-5331
> URL: https://issues.jboss.org/browse/TEIID-5331
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.13.6_4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 10.3, 10.1.4, 10.2.2
>
>
> Consider the following query:
> {code:sql}
> select charvalue, intkey, lead(intkey, 1, NULL) over (order by charvalue, intkey) from smalla order by intkey
> {code}
> This is expected to mean that the database orders the rows by charvalue, then intkey, then for each row finds the lead value in the ordered data set, _then_ sorts all the results by intkey.
> Indeed, when run against e.g. postgres or oracle datasource, the results are as follows:
> ||charvalue||intkey||lead||
> |1|0|2|
> |0|1|3|
> |1|2|4|
> |0|3|5|
> |1|4|6|
> |...|...|...|
> The results are also the same when running the query against teiid with e.g. postgres datasource, as the lead/lag is pushed down.
> However, when run against teiid with datasource that does not support lead/lag pushdown (such as csv), the results are different:
> ||charvalue||intkey||lead||
> |1|0|1|
> |0|1|2|
> |1|2|3|
> |0|3|4|
> |1|4|5|
> |...|...|...|
> I. e. it appears that the ORDER BY in the OVER clause is ignored and the LEAD/LAG uses the (possibly arbitrary) ordering returned from the datasource (which in this case is actually sorted by intkey, but we've seen random ordering from other datasources such as couchbase).
> Please note that the same result comes from teiid even when removing either of the columns from the OVER (ORDER BY ...) clause, as well when removing the overall ORDER BY clause.
> Processor plan for the above query:
> {code}
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> ProjectNode(0) output=[A.CHARVALUE AS charvalue, A.INTKEY AS intkey, lead(A.INTKEY, 1, null) OVER (ORDER BY A.CHARVALUE, A.INTKEY)] [A.CHARVALUE AS charvalue, A.INTKEY AS intkey, lead(A.INTKEY, 1, null) OVER (ORDER BY A.CHARVALUE, A.INTKEY)]
> WindowFunctionProjectNode(1) output=[lead(A.INTKEY, 1, null) OVER (ORDER BY A.CHARVALUE, A.INTKEY), A.CHARVALUE, A.INTKEY]
> LimitNode(2) output=[A.CHARVALUE, A.INTKEY] limit 100
> SortNode(3) output=[A.CHARVALUE, A.INTKEY] [SORT] [A.INTKEY]
> JoinNode(4) [NESTED TABLE JOIN] [CROSS JOIN] output=[A.CHARVALUE, A.INTKEY]
> ProjectNode(5) output=[file] [sourceModel.getTextFiles.file]
> AccessNode(6) output=[sourceModel.getTextFiles.file, sourceModel.getTextFiles.filePath] EXEC sourceModel.getTextFiles('smallaCsv.csv')
> TextTableNode(7) output=[A.CHARVALUE, A.INTKEY]
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years
[JBoss JIRA] (TEIID-5336) Improve TEIID-5253
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-5336:
-------------------------------------
Summary: Improve TEIID-5253
Key: TEIID-5336
URL: https://issues.jboss.org/browse/TEIID-5336
Project: Teiid
Issue Type: Quality Risk
Components: Server
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 10.3, 10.1.4, 10.2.2
The fix for TEIID-5253 still allows for a narrow chance of the resteasy and materializationmanager listeners being run twice. We can improve upon this by making these services required for the vdbservice.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years
[JBoss JIRA] (TEIID-5096) Using /*+ MAKEDEP */ blocks the deploy proces when using DDL based vdb
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5096?page=com.atlassian.jira.plugin... ]
Steven Hawkins reopened TEIID-5096:
-----------------------------------
> Using /*+ MAKEDEP */ blocks the deploy proces when using DDL based vdb
> ----------------------------------------------------------------------
>
> Key: TEIID-5096
> URL: https://issues.jboss.org/browse/TEIID-5096
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.3.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Attachments: threaddump.txt
>
>
> Hi,
> I've created a vdb that is depoyed using a vdb-ddl.ddl file.
> The deployment just stops and returned no error message when adding the SQL part below. Subsequent deployments are not handles. It seems like the deployment process just hangs. The only way to resolve this was to stop the wildfly server, remove the latest added content from the standalone/data/content directory and restart wildfly.
> {code:sql}
> CREATE VIEW tv_retrieve_monvals(
> admissionid integer not null,
> VariableID integer not null,
> Datetime timestamp not null,
> Entertime timestamp not null,
> varvalue double not null,
> primary key (admissionid, VariableID, Datetime)
> )
> AS
> SELECT v.PatientID AS admissionid, v.VariableID, v.Datetime, v.Entertime, v."Value" AS varvalue
> FROM (
> SELECT mv.PatientID, mv.VariableID, mv.Datetime, mv.Entertime, mv."Value",
> ROW_NUMBER() OVER (PARTITION BY mv.PatientID, mv.VariableID, mv.Datetime ORDER BY mv.Entertime DESC) AS rang
> FROM izisprod.P_GeneralData AS gd
> INNER JOIN /*+ MAKEDEP */ izisprod.P_MonVals AS mv ON
> gd.PatientID = mv.PatientID AND
> bitand(mv.Status, 8) = 8 AND
> bitand(mv.Status, 2) <> 2
> WHERE gd.Status = 1 OR (gd.Status >= 4 AND gd.Status <> 5)
> UNION
> SELECT mv.PatientID, mv.VariableID, mv.Datetime, mv.Entertime, mv."Value",
> ROW_NUMBER() OVER (PARTITION BY mv.PatientID, mv.VariableID, mv.Datetime ORDER BY mv.Entertime DESC) AS rang
> FROM iziswh.P_GeneralData AS gd
> INNER JOIN /*+ MAKEDEP */ iziswh.P_MonVals AS mv ON
> gd.PatientID = mv.PatientID AND
> bitand(mv.Status, 8) = 8 AND
> bitand(mv.Status, 2) <> 2
> ) AS v
> WHERE v.rang = 1;
> {code}
> Removing the /*+ MAKDEP */ references resolves the issue.
> I've tried creating a small ddl script that uses /*+ MAKEDEP */ to reproduce this issue but this script does deploy so currently I can not make a small example.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years
[JBoss JIRA] (TEIID-5096) Using /*+ MAKEDEP */ blocks the deploy proces when using DDL based vdb
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-5096?page=com.atlassian.jira.plugin... ]
Bram Gadeyne edited comment on TEIID-5096 at 5/3/18 5:06 AM:
-------------------------------------------------------------
[~shawkins],
It seems like I'm again encountering this issue but for version 10.2.1. The strange thing is that it sometimes happens and sometimes not.
I have a VDB where I now and then add new schema's, views and procedures to. It has worked for months now (even with the /*+ MAKEDEP */ in it and suddenly the Wildfly server stopped accepting new deployments (where a new schema was added). Sometimes it hangs and sometimes the CLI exits with a CancellationException error. I don't see any further errors or even other entries in the log file. A server restart does not help.
There was one /*+ MAKEDEP */ in one of the views. After removing this and restarting the server, the deployment is again accepted.
was (Author: gadeyne.bram):
[~shawkins],
It seems like I'm again encountering this issue but for version 10.2.1. The strange thing is that it sometimes happens and sometimes not.
I have a VDB where I now and then add new schema's, views and procedures to. It has worked for months now and suddenly the Wildfly server stopped accepting new deployments. Sometimes it hangs and sometimes the CLI exits with a CancellationException error. I don't see any further errors of even other entries in the log file. A server restart does not help.
There was one /*+ MAKEDEP */ in one of the views. After removing this and restarting the server, the deployment is again accepted.
> Using /*+ MAKEDEP */ blocks the deploy proces when using DDL based vdb
> ----------------------------------------------------------------------
>
> Key: TEIID-5096
> URL: https://issues.jboss.org/browse/TEIID-5096
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.3.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Attachments: threaddump.txt
>
>
> Hi,
> I've created a vdb that is depoyed using a vdb-ddl.ddl file.
> The deployment just stops and returned no error message when adding the SQL part below. Subsequent deployments are not handles. It seems like the deployment process just hangs. The only way to resolve this was to stop the wildfly server, remove the latest added content from the standalone/data/content directory and restart wildfly.
> {code:sql}
> CREATE VIEW tv_retrieve_monvals(
> admissionid integer not null,
> VariableID integer not null,
> Datetime timestamp not null,
> Entertime timestamp not null,
> varvalue double not null,
> primary key (admissionid, VariableID, Datetime)
> )
> AS
> SELECT v.PatientID AS admissionid, v.VariableID, v.Datetime, v.Entertime, v."Value" AS varvalue
> FROM (
> SELECT mv.PatientID, mv.VariableID, mv.Datetime, mv.Entertime, mv."Value",
> ROW_NUMBER() OVER (PARTITION BY mv.PatientID, mv.VariableID, mv.Datetime ORDER BY mv.Entertime DESC) AS rang
> FROM izisprod.P_GeneralData AS gd
> INNER JOIN /*+ MAKEDEP */ izisprod.P_MonVals AS mv ON
> gd.PatientID = mv.PatientID AND
> bitand(mv.Status, 8) = 8 AND
> bitand(mv.Status, 2) <> 2
> WHERE gd.Status = 1 OR (gd.Status >= 4 AND gd.Status <> 5)
> UNION
> SELECT mv.PatientID, mv.VariableID, mv.Datetime, mv.Entertime, mv."Value",
> ROW_NUMBER() OVER (PARTITION BY mv.PatientID, mv.VariableID, mv.Datetime ORDER BY mv.Entertime DESC) AS rang
> FROM iziswh.P_GeneralData AS gd
> INNER JOIN /*+ MAKEDEP */ iziswh.P_MonVals AS mv ON
> gd.PatientID = mv.PatientID AND
> bitand(mv.Status, 8) = 8 AND
> bitand(mv.Status, 2) <> 2
> ) AS v
> WHERE v.rang = 1;
> {code}
> Removing the /*+ MAKDEP */ references resolves the issue.
> I've tried creating a small ddl script that uses /*+ MAKEDEP */ to reproduce this issue but this script does deploy so currently I can not make a small example.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years
[JBoss JIRA] (TEIID-5096) Using /*+ MAKEDEP */ blocks the deploy proces when using DDL based vdb
by Bram Gadeyne (JIRA)
[ https://issues.jboss.org/browse/TEIID-5096?page=com.atlassian.jira.plugin... ]
Bram Gadeyne commented on TEIID-5096:
-------------------------------------
[~shawkins],
It seems like I'm again encountering this issue but for version 10.2.1. The strange thing is that it sometimes happens and sometimes not.
I have a VDB where I now and then add new schema's, views and procedures to. It has worked for months now and suddenly the Wildfly server stopped accepting new deployments. Sometimes it hangs and sometimes the CLI exits with a CancellationException error. I don't see any further errors of even other entries in the log file. A server restart does not help.
There was one /*+ MAKEDEP */ in one of the views. After removing this and restarting the server, the deployment is again accepted.
> Using /*+ MAKEDEP */ blocks the deploy proces when using DDL based vdb
> ----------------------------------------------------------------------
>
> Key: TEIID-5096
> URL: https://issues.jboss.org/browse/TEIID-5096
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.3.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
> Attachments: threaddump.txt
>
>
> Hi,
> I've created a vdb that is depoyed using a vdb-ddl.ddl file.
> The deployment just stops and returned no error message when adding the SQL part below. Subsequent deployments are not handles. It seems like the deployment process just hangs. The only way to resolve this was to stop the wildfly server, remove the latest added content from the standalone/data/content directory and restart wildfly.
> {code:sql}
> CREATE VIEW tv_retrieve_monvals(
> admissionid integer not null,
> VariableID integer not null,
> Datetime timestamp not null,
> Entertime timestamp not null,
> varvalue double not null,
> primary key (admissionid, VariableID, Datetime)
> )
> AS
> SELECT v.PatientID AS admissionid, v.VariableID, v.Datetime, v.Entertime, v."Value" AS varvalue
> FROM (
> SELECT mv.PatientID, mv.VariableID, mv.Datetime, mv.Entertime, mv."Value",
> ROW_NUMBER() OVER (PARTITION BY mv.PatientID, mv.VariableID, mv.Datetime ORDER BY mv.Entertime DESC) AS rang
> FROM izisprod.P_GeneralData AS gd
> INNER JOIN /*+ MAKEDEP */ izisprod.P_MonVals AS mv ON
> gd.PatientID = mv.PatientID AND
> bitand(mv.Status, 8) = 8 AND
> bitand(mv.Status, 2) <> 2
> WHERE gd.Status = 1 OR (gd.Status >= 4 AND gd.Status <> 5)
> UNION
> SELECT mv.PatientID, mv.VariableID, mv.Datetime, mv.Entertime, mv."Value",
> ROW_NUMBER() OVER (PARTITION BY mv.PatientID, mv.VariableID, mv.Datetime ORDER BY mv.Entertime DESC) AS rang
> FROM iziswh.P_GeneralData AS gd
> INNER JOIN /*+ MAKEDEP */ iziswh.P_MonVals AS mv ON
> gd.PatientID = mv.PatientID AND
> bitand(mv.Status, 8) = 8 AND
> bitand(mv.Status, 2) <> 2
> ) AS v
> WHERE v.rang = 1;
> {code}
> Removing the /*+ MAKDEP */ references resolves the issue.
> I've tried creating a small ddl script that uses /*+ MAKEDEP */ to reproduce this issue but this script does deploy so currently I can not make a small example.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years