7:58 p.m.
LIMIT not pushed down for queries with subselects and ORDER BY
--------------------------------------------------------------
Key: TEIID-1529
URL: https://issues.jboss.org/browse/TEIID-1529
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.3
Reporter: Mark Addleman
Assignee: Steven Hawkins
It doesn't look like the engine pushes down LIMIT in the following query:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B ORDER BY B."USERID" ASC LIMIT 10
However, the engine does push down the LIMIT in the same query without the ORDER BY:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B LIMIT 10
Another piece of relevant information is notes.RETRIEVE_NOTE is a stored procedure backed
by java code in a translator.
Since SECURITY.BASEER contains ~20,000 rows and each row requires a select against the
RETRIEVE_NOTE stored procedure, the difference in processing time is huge: about two
minutes with the ORDER BY versus a few hundred milliseconds without the ORDER BY.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
8:37 p.m.
New subject: [JBoss JIRA] Commented: (TEIID-1529) LIMIT not pushed down for queries with subselects and ORDER BY
[
https://issues.jboss.org/browse/TEIID-1529?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1529:
---------------------------------------
The processing order here is the same as the logical application of the clauses, since we
can't push limits past order bys. So we have a plan that looks like:
limit
order by
project
...
What you are looking for is an enhancement to push the application of the order by and the
limit ahead of the projection, or alternative to raise the subquery evaluation above the
ordered limit.
That should be easily doable in 7.4.
LIMIT not pushed down for queries with subselects and ORDER BY
--------------------------------------------------------------
Key: TEIID-1529
URL: https://issues.jboss.org/browse/TEIID-1529
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.3
Reporter: Mark Addleman
Assignee: Steven Hawkins
It doesn't look like the engine pushes down LIMIT in the following query:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B ORDER BY B."USERID" ASC LIMIT 10
However, the engine does push down the LIMIT in the same query without the ORDER BY:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B LIMIT 10
Another piece of relevant information is notes.RETRIEVE_NOTE is a stored procedure backed
by java code in a translator.
Since SECURITY.BASEER contains ~20,000 rows and each row requires a select against the
RETRIEVE_NOTE stored procedure, the difference in processing time is huge: about two
minutes with the ORDER BY versus a few hundred milliseconds without the ORDER BY.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
8:58 p.m.
New subject: [JBoss JIRA] Commented: (TEIID-1529) LIMIT not pushed down for queries with subselects and ORDER BY
[
https://issues.jboss.org/browse/TEIID-1529?page=com.atlassian.jira.plugin...
]
Mark Addleman commented on TEIID-1529:
--------------------------------------
Thanks. 7.4 targetted for Apr 28th? That is pushing close to our beta ship date. What
are the changes we could get a patch?
LIMIT not pushed down for queries with subselects and ORDER BY
--------------------------------------------------------------
Key: TEIID-1529
URL: https://issues.jboss.org/browse/TEIID-1529
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.3
Reporter: Mark Addleman
Assignee: Steven Hawkins
It doesn't look like the engine pushes down LIMIT in the following query:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B ORDER BY B."USERID" ASC LIMIT 10
However, the engine does push down the LIMIT in the same query without the ORDER BY:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B LIMIT 10
Another piece of relevant information is notes.RETRIEVE_NOTE is a stored procedure backed
by java code in a translator.
Since SECURITY.BASEER contains ~20,000 rows and each row requires a select against the
RETRIEVE_NOTE stored procedure, the difference in processing time is huge: about two
minutes with the ORDER BY versus a few hundred milliseconds without the ORDER BY.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
9 p.m.
New subject: [JBoss JIRA] Commented: (TEIID-1529) LIMIT not pushed down for queries with subselects and ORDER BY
[
https://issues.jboss.org/browse/TEIID-1529?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1529:
---------------------------------------
You should be able to get a patch from the change set when applied without waiting for the
full release.
LIMIT not pushed down for queries with subselects and ORDER BY
--------------------------------------------------------------
Key: TEIID-1529
URL: https://issues.jboss.org/browse/TEIID-1529
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.3
Reporter: Mark Addleman
Assignee: Steven Hawkins
It doesn't look like the engine pushes down LIMIT in the following query:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B ORDER BY B."USERID" ASC LIMIT 10
However, the engine does push down the LIMIT in the same query without the ORDER BY:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B LIMIT 10
Another piece of relevant information is notes.RETRIEVE_NOTE is a stored procedure backed
by java code in a translator.
Since SECURITY.BASEER contains ~20,000 rows and each row requires a select against the
RETRIEVE_NOTE stored procedure, the difference in processing time is huge: about two
minutes with the ORDER BY versus a few hundred milliseconds without the ORDER BY.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
9:11 p.m.
New subject: [JBoss JIRA] Commented: (TEIID-1529) LIMIT not pushed down for queries with subselects and ORDER BY
[
https://issues.jboss.org/browse/TEIID-1529?page=com.atlassian.jira.plugin...
]
Mark Addleman commented on TEIID-1529:
--------------------------------------
Thanks!
LIMIT not pushed down for queries with subselects and ORDER BY
--------------------------------------------------------------
Key: TEIID-1529
URL: https://issues.jboss.org/browse/TEIID-1529
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.3
Reporter: Mark Addleman
Assignee: Steven Hawkins
It doesn't look like the engine pushes down LIMIT in the following query:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B ORDER BY B."USERID" ASC LIMIT 10
However, the engine does push down the LIMIT in the same query without the ORDER BY:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B LIMIT 10
Another piece of relevant information is notes.RETRIEVE_NOTE is a stored procedure backed
by java code in a translator.
Since SECURITY.BASEER contains ~20,000 rows and each row requires a select against the
RETRIEVE_NOTE stored procedure, the difference in processing time is huge: about two
minutes with the ORDER BY versus a few hundred milliseconds without the ORDER BY.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
4:43 a.m.
New subject: [JBoss JIRA] Resolved: (TEIID-1529) LIMIT not pushed down for queries with subselects and ORDER BY
[
https://issues.jboss.org/browse/TEIID-1529?page=com.atlassian.jira.plugin...
]
Steven Hawkins resolved TEIID-1529.
-----------------------------------
Fix Version/s: 7.4
Resolution: Done
Added a check in ruleplansorts to move projection above ordered limits when possible. The
logic is fairly basic and targeted at simple ordering (ordering on an expression may cause
the optimization not to happen) situations instead of being a general approach to raising
projection.
LIMIT not pushed down for queries with subselects and ORDER BY
--------------------------------------------------------------
Key: TEIID-1529
URL: https://issues.jboss.org/browse/TEIID-1529
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.3
Reporter: Mark Addleman
Assignee: Steven Hawkins
Fix For: 7.4
It doesn't look like the engine pushes down LIMIT in the following query:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B ORDER BY B."USERID" ASC LIMIT 10
However, the engine does push down the LIMIT in the same query without the ORDER BY:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B LIMIT 10
Another piece of relevant information is notes.RETRIEVE_NOTE is a stored procedure backed
by java code in a translator.
Since SECURITY.BASEER contains ~20,000 rows and each row requires a select against the
RETRIEVE_NOTE stored procedure, the difference in processing time is huge: about two
minutes with the ORDER BY versus a few hundred milliseconds without the ORDER BY.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
12:23 a.m.
New subject: [JBoss JIRA] Commented: (TEIID-1529) LIMIT not pushed down for queries with subselects and ORDER BY
[
https://issues.jboss.org/browse/TEIID-1529?page=com.atlassian.jira.plugin...
]
Gaurav Chaudhary commented on TEIID-1529:
-----------------------------------------
We are moving on to the 7.3 now from 7.2. For this problem, we made a patch earlier for
7.2. Should we make a patch for 7.3 as well for this bug? Can I simply replace the files
changed and make a build for 7.3?
LIMIT not pushed down for queries with subselects and ORDER BY
--------------------------------------------------------------
Key: TEIID-1529
URL: https://issues.jboss.org/browse/TEIID-1529
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.3
Reporter: Mark Addleman
Assignee: Steven Hawkins
Fix For: 7.4
It doesn't look like the engine pushes down LIMIT in the following query:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B ORDER BY B."USERID" ASC LIMIT 10
However, the engine does push down the LIMIT in the same query without the ORDER BY:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B LIMIT 10
Another piece of relevant information is notes.RETRIEVE_NOTE is a stored procedure backed
by java code in a translator.
Since SECURITY.BASEER contains ~20,000 rows and each row requires a select against the
RETRIEVE_NOTE stored procedure, the difference in processing time is huge: about two
minutes with the ORDER BY versus a few hundred milliseconds without the ORDER BY.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
3:42 a.m.
New subject: [JBoss JIRA] Commented: (TEIID-1529) LIMIT not pushed down for queries with subselects and ORDER BY
[
https://issues.jboss.org/browse/TEIID-1529?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1529:
---------------------------------------
You should use the patch from the change set
http://fisheye.jboss.org/rdiff/Teiid?csid=3031&u&N, rather than just replacing
whole files. For reference, you can also get to the change sets for any given issue
clicking on the Subversion Commits link above.
LIMIT not pushed down for queries with subselects and ORDER BY
--------------------------------------------------------------
Key: TEIID-1529
URL: https://issues.jboss.org/browse/TEIID-1529
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.3
Reporter: Mark Addleman
Assignee: Steven Hawkins
Fix For: 7.4
It doesn't look like the engine pushes down LIMIT in the following query:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B ORDER BY B."USERID" ASC LIMIT 10
However, the engine does push down the LIMIT in the same query without the ORDER BY:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B LIMIT 10
Another piece of relevant information is notes.RETRIEVE_NOTE is a stored procedure backed
by java code in a translator.
Since SECURITY.BASEER contains ~20,000 rows and each row requires a select against the
RETRIEVE_NOTE stored procedure, the difference in processing time is huge: about two
minutes with the ORDER BY versus a few hundred milliseconds without the ORDER BY.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
10:30 p.m.
New subject: [JBoss JIRA] Closed: (TEIID-1529) LIMIT not pushed down for queries with subselects and ORDER BY
[
https://issues.jboss.org/browse/TEIID-1529?page=com.atlassian.jira.plugin...
]
Steven Hawkins closed TEIID-1529.
---------------------------------
LIMIT not pushed down for queries with subselects and ORDER BY
--------------------------------------------------------------
Key: TEIID-1529
URL: https://issues.jboss.org/browse/TEIID-1529
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 7.3
Reporter: Mark Addleman
Assignee: Steven Hawkins
Fix For: 7.4
It doesn't look like the engine pushes down LIMIT in the following query:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B ORDER BY B."USERID" ASC LIMIT 10
However, the engine does push down the LIMIT in the same query without the ORDER BY:
SELECT B."SYSID", B."USERID", (SELECT COUNT(*) FROM
(SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY =
XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER",
XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as
C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM
"SECURITY.BASEUSER" as B LIMIT 10
Another piece of relevant information is notes.RETRIEVE_NOTE is a stored procedure backed
by java code in a translator.
Since SECURITY.BASEER contains ~20,000 rows and each row requires a select against the
RETRIEVE_NOTE stored procedure, the difference in processing time is huge: about two
minutes with the ORDER BY versus a few hundred milliseconds without the ORDER BY.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
4714
days inactive
4845
days old
8 comments
3 participants
participants (3)
-
Gaurav Chaudhary (JIRA) -
Mark Addleman (JIRA)
-
Steven Hawkins (JIRA)