[teiid-issues] [JBoss JIRA] (TEIID-2027) maxSourceRows being incorrectly applied when query returns less rows than the limit

[Graeme Gillies (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-2027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12690252#comment-12690252 ] 

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