agesys-dev team mailing list archive

[zzzeek <mike_mp@xxxxxxxxxxxxxxxx>]

Public bug reported:

MySQL's documented syntax for selecting with an OFFSET and unlimited
LIMIT is to specify the large number  18446744073709551615 as the second
argument:

http://dev.mysql.com/doc/refman/5.0/en/select.html

    To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. 
    This statement retrieves all rows from the 96th row to the last:

        SELECT * FROM tbl LIMIT 95,18446744073709551615;

The following script fails in Python 3.1, OurSQL 0.9.2:

    import oursql

    conn = oursql.connect(db='test', user='scott', passwd='tiger',
host='localhost')

    cursor = conn.cursor()
    try:
        cursor.execute("drop table test")
    except:
        pass
    
    cursor.execute("""
    create table test(
        id integer primary key
    )
    """)

    cursor.execute("select * from test LIMIT ?, ? ", (5, 18446744073709551615))
    print("hi")
    cursor.fetchall()

hiTraceback (most recent call last):
  File "test.py", line 18, in <module>
    print("hi")
OverflowError: Python int too large to convert to C long


the "print("hi")" is important (in that there is some kind of method call before the fetchall()), as it triggers a particular oddness I've seen when Python has problems with native libraries. You'll notice it actually prints the "hi", then fails before getting to the fetchall().

The script works if the number is passed as a string.   The script works
as is in Python 2.x.

** Affects: oursql
     Importance: Undecided
         Status: New

-- 
You received this bug notification because you are a member of Agesys
Team, which is subscribed to oursql.
https://bugs.launchpad.net/bugs/686232

Title:
  large integer handling, as used in LIMIT for unlimited size, fails in 3.1 as a bind param

Status in oursql:
  New

Bug description:
  MySQL's documented syntax for selecting with an OFFSET and unlimited LIMIT is to specify the large number  18446744073709551615 as the second argument:

http://dev.mysql.com/doc/refman/5.0/en/select.html

    To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. 
    This statement retrieves all rows from the 96th row to the last:

        SELECT * FROM tbl LIMIT 95,18446744073709551615;

The following script fails in Python 3.1, OurSQL 0.9.2:

    import oursql

    conn = oursql.connect(db='test', user='scott', passwd='tiger', host='localhost')

    cursor = conn.cursor()
    try:
        cursor.execute("drop table test")
    except:
        pass
    
    cursor.execute("""
    create table test(
        id integer primary key
    )
    """)

    cursor.execute("select * from test LIMIT ?, ? ", (5, 18446744073709551615))
    print("hi")
    cursor.fetchall()

hiTraceback (most recent call last):
  File "test.py", line 18, in <module>
    print("hi")
OverflowError: Python int too large to convert to C long


the "print("hi")" is important (in that there is some kind of method call before the fetchall()), as it triggers a particular oddness I've seen when Python has problems with native libraries. You'll notice it actually prints the "hi", then fails before getting to the fetchall().

The script works if the number is passed as a string.   The script works as is in Python 2.x.