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

[Steven Hawkins (JIRA)]

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

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