[JBoss JIRA] (TEIID-2027) maxSourceRows being incorrectly applied when query returns less rows than the limit
11:48 p.m.
Graeme Gillies created TEIID-2027:
-------------------------------------
Summary: maxSourceRows being incorrectly applied when query returns less rows
than the limit
Key: TEIID-2027
URL: https://issues.jboss.org/browse/TEIID-2027
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: Teiid 7.6 running on JBoss EAP 5.1 on RHEL 5.
JDK is
java version "1.6.0_22"
OpenJDK Runtime Environment (IcedTea6 1.10.6) (rhel-1.25.1.10.6.el5_8-x86_64)
OpenJDK 64-Bit Server VM (build 20.0-b11, mixed mode)
Reporter: Graeme Gillies
Assignee: Steven Hawkins
We have a user who is trying to run a particularly large query against our VDB but is
getting the error
{code}
ERROR MESSAGE:
BugzillaDS: The number of result rows has exceeded the maximum result
rows "100,000"
DETAIL: org.teiid.jdbc.TeiidSQLException: BugzillaDS: The number of
result rows has exceeded the maximum result rows "100,000"
{code}
Which seems fairly self explanatory, as we do have maxSourceRows set to 100000 in
teiid-jboss-beans.xml. Unfortunately though, the query in question should be returning
only about 2000 rows, so it shouldn't be hitting this error at all.
In fact, I am able to replicate this problem with the following simple script
{noformat}
#!/usr/bin/env python
import psycopg2
conn = psycopg2.connect("host=vdb.example.com port=35432 dbname=MyVDB user=user
password=password")
cursor = conn.cursor()
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id IN
(%s)" % ','.join(map(str, range(1,1002)))
cursor.execute(query)
rows = cursor.fetchall()
cols = [t[0] for t in cursor.description]
for row in rows:
print ", ".join(["%s = %s" % (col, value) for col, value in
zip(cols, row)])
conn.close()
{noformat}
If I change
{code}
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id IN
(%s)" % ','.join(map(str, range(1,1002)))
{code}
to
{code}
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id IN
(%s)" % ','.join(map(str, range(1,1001)))
{code}
The code works fine, so it seems that there is some 1000 row limit somewhere that Is being
used instead of the value we set (100000)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
8:55 a.m.
[
https://issues.jboss.org/browse/TEIID-2027?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-2027:
---------------------------------------
I don't think there is an issue here. Be sure to check the query plan for the actual
source query issued. This count refers to source rows, not the final row count of the
user query.
maxSourceRows being incorrectly applied when query returns less rows
than the limit
-----------------------------------------------------------------------------------
Key: TEIID-2027
URL: https://issues.jboss.org/browse/TEIID-2027
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: Teiid 7.6 running on JBoss EAP 5.1 on RHEL 5.
JDK is
java version "1.6.0_22"
OpenJDK Runtime Environment (IcedTea6 1.10.6) (rhel-1.25.1.10.6.el5_8-x86_64)
OpenJDK 64-Bit Server VM (build 20.0-b11, mixed mode)
Reporter: Graeme Gillies
Assignee: Steven Hawkins
We have a user who is trying to run a particularly large query against our VDB but is
getting the error
{code}
> ERROR MESSAGE:
> BugzillaDS: The number of result rows has exceeded the maximum result
> rows "100,000"
> DETAIL: org.teiid.jdbc.TeiidSQLException: BugzillaDS: The number of
> result rows has exceeded the maximum result rows "100,000"
{code}
Which seems fairly self explanatory, as we do have maxSourceRows set to 100000 in
teiid-jboss-beans.xml. Unfortunately though, the query in question should be returning
only about 2000 rows, so it shouldn't be hitting this error at all.
In fact, I am able to replicate this problem with the following simple script
{noformat}
#!/usr/bin/env python
import psycopg2
conn = psycopg2.connect("host=vdb.example.com port=35432 dbname=MyVDB user=user
password=password")
cursor = conn.cursor()
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1002)))
cursor.execute(query)
rows = cursor.fetchall()
cols = [t[0] for t in cursor.description]
for row in rows:
print ", ".join(["%s = %s" % (col, value) for col, value in
zip(cols, row)])
conn.close()
{noformat}
If I change
{code}
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1002)))
{code}
to
{code}
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1001)))
{code}
The code works fine, so it seems that there is some 1000 row limit somewhere that Is
being used instead of the value we set (100000)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
9:15 p.m.
[
https://issues.jboss.org/browse/TEIID-2027?page=com.atlassian.jira.plugin...
]
Graeme Gillies commented on TEIID-2027:
---------------------------------------
You are correct, it looks like the query going to the database is
{noformat}
SELECT g_0.bug_id AS c_0, g_0.creation_ts AS c_1 FROM BugzillaDS.bugs AS g_0 ORDER BY c_0
{noformat}
Which brings back 600,000 rows. I guess this makes sense to reject the issue, but is there
a a reason that teiid is not sending down a query to the db that is "smarter"?
Seems not the best way to go about it by bringing back all the data and filtering in teiid
itself.
Regards,
Graeme
maxSourceRows being incorrectly applied when query returns less rows
than the limit
-----------------------------------------------------------------------------------
Key: TEIID-2027
URL: https://issues.jboss.org/browse/TEIID-2027
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: Teiid 7.6 running on JBoss EAP 5.1 on RHEL 5.
JDK is
java version "1.6.0_22"
OpenJDK Runtime Environment (IcedTea6 1.10.6) (rhel-1.25.1.10.6.el5_8-x86_64)
OpenJDK 64-Bit Server VM (build 20.0-b11, mixed mode)
Reporter: Graeme Gillies
Assignee: Steven Hawkins
We have a user who is trying to run a particularly large query against our VDB but is
getting the error
{code}
> ERROR MESSAGE:
> BugzillaDS: The number of result rows has exceeded the maximum result
> rows "100,000"
> DETAIL: org.teiid.jdbc.TeiidSQLException: BugzillaDS: The number of
> result rows has exceeded the maximum result rows "100,000"
{code}
Which seems fairly self explanatory, as we do have maxSourceRows set to 100000 in
teiid-jboss-beans.xml. Unfortunately though, the query in question should be returning
only about 2000 rows, so it shouldn't be hitting this error at all.
In fact, I am able to replicate this problem with the following simple script
{noformat}
#!/usr/bin/env python
import psycopg2
conn = psycopg2.connect("host=vdb.example.com port=35432 dbname=MyVDB user=user
password=password")
cursor = conn.cursor()
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1002)))
cursor.execute(query)
rows = cursor.fetchall()
cols = [t[0] for t in cursor.description]
for row in rows:
print ", ".join(["%s = %s" % (col, value) for col, value in
zip(cols, row)])
conn.close()
{noformat}
If I change
{code}
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1002)))
{code}
to
{code}
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1001)))
{code}
The code works fine, so it seems that there is some 1000 row limit somewhere that Is
being used instead of the value we set (100000)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
9:20 p.m.
[
https://issues.jboss.org/browse/TEIID-2027?page=com.atlassian.jira.plugin...
]
Ramesh Reddy commented on TEIID-2027:
-------------------------------------
you are combining this with the LIMIT clause; Even in those cases depending upon the query
being executed it is not produce correct results with use of LIMIT pushdown.
maxSourceRows being incorrectly applied when query returns less rows
than the limit
-----------------------------------------------------------------------------------
Key: TEIID-2027
URL: https://issues.jboss.org/browse/TEIID-2027
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: Teiid 7.6 running on JBoss EAP 5.1 on RHEL 5.
JDK is
java version "1.6.0_22"
OpenJDK Runtime Environment (IcedTea6 1.10.6) (rhel-1.25.1.10.6.el5_8-x86_64)
OpenJDK 64-Bit Server VM (build 20.0-b11, mixed mode)
Reporter: Graeme Gillies
Assignee: Steven Hawkins
We have a user who is trying to run a particularly large query against our VDB but is
getting the error
{code}
> ERROR MESSAGE:
> BugzillaDS: The number of result rows has exceeded the maximum result
> rows "100,000"
> DETAIL: org.teiid.jdbc.TeiidSQLException: BugzillaDS: The number of
> result rows has exceeded the maximum result rows "100,000"
{code}
Which seems fairly self explanatory, as we do have maxSourceRows set to 100000 in
teiid-jboss-beans.xml. Unfortunately though, the query in question should be returning
only about 2000 rows, so it shouldn't be hitting this error at all.
In fact, I am able to replicate this problem with the following simple script
{noformat}
#!/usr/bin/env python
import psycopg2
conn = psycopg2.connect("host=vdb.example.com port=35432 dbname=MyVDB user=user
password=password")
cursor = conn.cursor()
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1002)))
cursor.execute(query)
rows = cursor.fetchall()
cols = [t[0] for t in cursor.description]
for row in rows:
print ", ".join(["%s = %s" % (col, value) for col, value in
zip(cols, row)])
conn.close()
{noformat}
If I change
{code}
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1002)))
{code}
to
{code}
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1001)))
{code}
The code works fine, so it seems that there is some 1000 row limit somewhere that Is
being used instead of the value we set (100000)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
8:46 a.m.
[
https://issues.jboss.org/browse/TEIID-2027?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-2027:
---------------------------------------
The maxSourceRows value is enforced across all translators and does not currently
influence the source query. It acts much like java.sql.Statement.setMaxRows, which
typically will just truncate results on the client side. You'd want to log an
enhancement to have the maxSourceRows value use a limit on the source query if possible.
maxSourceRows being incorrectly applied when query returns less rows
than the limit
-----------------------------------------------------------------------------------
Key: TEIID-2027
URL: https://issues.jboss.org/browse/TEIID-2027
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: Teiid 7.6 running on JBoss EAP 5.1 on RHEL 5.
JDK is
java version "1.6.0_22"
OpenJDK Runtime Environment (IcedTea6 1.10.6) (rhel-1.25.1.10.6.el5_8-x86_64)
OpenJDK 64-Bit Server VM (build 20.0-b11, mixed mode)
Reporter: Graeme Gillies
Assignee: Steven Hawkins
We have a user who is trying to run a particularly large query against our VDB but is
getting the error
{code}
> ERROR MESSAGE:
> BugzillaDS: The number of result rows has exceeded the maximum result
> rows "100,000"
> DETAIL: org.teiid.jdbc.TeiidSQLException: BugzillaDS: The number of
> result rows has exceeded the maximum result rows "100,000"
{code}
Which seems fairly self explanatory, as we do have maxSourceRows set to 100000 in
teiid-jboss-beans.xml. Unfortunately though, the query in question should be returning
only about 2000 rows, so it shouldn't be hitting this error at all.
In fact, I am able to replicate this problem with the following simple script
{noformat}
#!/usr/bin/env python
import psycopg2
conn = psycopg2.connect("host=vdb.example.com port=35432 dbname=MyVDB user=user
password=password")
cursor = conn.cursor()
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1002)))
cursor.execute(query)
rows = cursor.fetchall()
cols = [t[0] for t in cursor.description]
for row in rows:
print ", ".join(["%s = %s" % (col, value) for col, value in
zip(cols, row)])
conn.close()
{noformat}
If I change
{code}
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1002)))
{code}
to
{code}
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1001)))
{code}
The code works fine, so it seems that there is some 1000 row limit somewhere that Is
being used instead of the value we set (100000)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
1:36 p.m.
[
https://issues.jboss.org/browse/TEIID-2027?page=com.atlassian.jira.plugin...
]
Steven Hawkins resolved TEIID-2027.
-----------------------------------
Resolution: Rejected
Marking as rejected. Please open an enhancement request if you want to add a limit (of
max rows or max rows + 1 if exception on max row is turned on) if possible based upon the
maxSourceRows.
maxSourceRows being incorrectly applied when query returns less rows
than the limit
-----------------------------------------------------------------------------------
Key: TEIID-2027
URL: https://issues.jboss.org/browse/TEIID-2027
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: Teiid 7.6 running on JBoss EAP 5.1 on RHEL 5.
JDK is
java version "1.6.0_22"
OpenJDK Runtime Environment (IcedTea6 1.10.6) (rhel-1.25.1.10.6.el5_8-x86_64)
OpenJDK 64-Bit Server VM (build 20.0-b11, mixed mode)
Reporter: Graeme Gillies
Assignee: Steven Hawkins
We have a user who is trying to run a particularly large query against our VDB but is
getting the error
{code}
> ERROR MESSAGE:
> BugzillaDS: The number of result rows has exceeded the maximum result
> rows "100,000"
> DETAIL: org.teiid.jdbc.TeiidSQLException: BugzillaDS: The number of
> result rows has exceeded the maximum result rows "100,000"
{code}
Which seems fairly self explanatory, as we do have maxSourceRows set to 100000 in
teiid-jboss-beans.xml. Unfortunately though, the query in question should be returning
only about 2000 rows, so it shouldn't be hitting this error at all.
In fact, I am able to replicate this problem with the following simple script
{noformat}
#!/usr/bin/env python
import psycopg2
conn = psycopg2.connect("host=vdb.example.com port=35432 dbname=MyVDB user=user
password=password")
cursor = conn.cursor()
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1002)))
cursor.execute(query)
rows = cursor.fetchall()
cols = [t[0] for t in cursor.description]
for row in rows:
print ", ".join(["%s = %s" % (col, value) for col, value in
zip(cols, row)])
conn.close()
{noformat}
If I change
{code}
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1002)))
{code}
to
{code}
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1001)))
{code}
The code works fine, so it seems that there is some 1000 row limit somewhere that Is
being used instead of the value we set (100000)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
2:42 p.m.
[
https://issues.jboss.org/browse/TEIID-2027?page=com.atlassian.jira.plugin...
]
Steven Hawkins closed TEIID-2027.
---------------------------------
maxSourceRows being incorrectly applied when query returns less rows
than the limit
-----------------------------------------------------------------------------------
Key: TEIID-2027
URL: https://issues.jboss.org/browse/TEIID-2027
Project: Teiid
Issue Type: Bug
Affects Versions: 7.6
Environment: Teiid 7.6 running on JBoss EAP 5.1 on RHEL 5.
JDK is
java version "1.6.0_22"
OpenJDK Runtime Environment (IcedTea6 1.10.6) (rhel-1.25.1.10.6.el5_8-x86_64)
OpenJDK 64-Bit Server VM (build 20.0-b11, mixed mode)
Reporter: Graeme Gillies
Assignee: Steven Hawkins
We have a user who is trying to run a particularly large query against our VDB but is
getting the error
{code}
> ERROR MESSAGE:
> BugzillaDS: The number of result rows has exceeded the maximum result
> rows "100,000"
> DETAIL: org.teiid.jdbc.TeiidSQLException: BugzillaDS: The number of
> result rows has exceeded the maximum result rows "100,000"
{code}
Which seems fairly self explanatory, as we do have maxSourceRows set to 100000 in
teiid-jboss-beans.xml. Unfortunately though, the query in question should be returning
only about 2000 rows, so it shouldn't be hitting this error at all.
In fact, I am able to replicate this problem with the following simple script
{noformat}
#!/usr/bin/env python
import psycopg2
conn = psycopg2.connect("host=vdb.example.com port=35432 dbname=MyVDB user=user
password=password")
cursor = conn.cursor()
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1002)))
cursor.execute(query)
rows = cursor.fetchall()
cols = [t[0] for t in cursor.description]
for row in rows:
print ", ".join(["%s = %s" % (col, value) for col, value in
zip(cols, row)])
conn.close()
{noformat}
If I change
{code}
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1002)))
{code}
to
{code}
query = "SELECT bugs.bug_id, bugs.creation_ts FROM Bugzilla.bugs WHERE bugs.bug_id
IN (%s)" % ','.join(map(str, range(1,1001)))
{code}
The code works fine, so it seems that there is some 1000 row limit somewhere that Is
being used instead of the value we set (100000)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
4310
days inactive
4413
days old
6 comments
3 participants
participants (3)
-
Graeme Gillies (JIRA) -
Ramesh Reddy (JIRA)
-
Steven Hawkins (JIRA)