Rows missing in a query using upper() function
David Hughes <dfh-SSyVZMUXQxBawGkVQGIbBlpr/1R2p/[email protected]>
| Newsgroups | gmane.comp.python.db.pysqlite.user |
|---|---|
| Message-ID | <[email protected]> |
The attached example shows the problem. It happens in Pysqlite2 using the form *...where upper(X) in (.......)* when X has numeric values. Using *...where upper(X) = ....* produces the correct result, as does everything in a sqlite3.exe command window. Am I doing something iffy, or is there a bug here? -- Regards, David Hughes _______________________________________________ pysqlite mailing list pysqlite-IAPFreCvJWPBWskQ1e/[email protected] http://lists.initd.org/mailman/listinfo/pysqlite
pysqlite2_session.log
(text/plain, 1.4 KB)
E:\PyDevSrc\Test>c:\python24\python
Python 2.4.3 (#69, Mar 29 2006, 17:35:34) [MSC v.1310 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> from pysqlite2 import dbapi2 as sqlite
>>> print 'pysqlite version %s' % sqlite.version
pysqlite version 2.3.0
>>> print 'SQLite version %s' % sqlite.sqlite_version
SQLite version 3.3.6
>>>
>>> con = sqlite.connect("test.sl3")
>>> cur = con.cursor()
>>> x = cur.execute("create table t1(refcode, name)")
>>>
>>>
>>> x = cur.execute("insert into t1 values (?,?)", ('1000','Numeric'))
>>> x = cur.execute("insert into t1 values (?,?)", ('10-10','Punctuated'))
>>> x = cur.execute("insert into t1 values (?,?)", ('WXYZ','Upper'))
>>> x = cur.execute("insert into t1 values (?,?)", ('wxyz','Lower'))
>>> x = cur.execute("insert into t1 values (?,?)", ('WxYz','Mixed'))
>>>
>>> sql = "select refcode, name from t1 where upper(refcode) in ('1000', '10-10', 'WXYZ')"
>>>
>>> x = cur.execute(sql)
>>> for row in cur.fetchall():
... print repr(row)
...
...
(u'10-10', u'Punctuated')
(u'WXYZ', u'Upper')
(u'wxyz', u'Lower')
(u'WxYz', u'Mixed')
>>> x=con.commit()
>>> ^Z
E:\PyDevSrc\Test>sqlite3 test.sl3
SQLite version 3.3.5
Enter ".help" for instructions
sqlite> select refcode, name from t1 where upper(refcode) in ('1000', '10-10', 'WXYZ');
1000|Numeric
10-10|Punctuated
WXYZ|Upper
wxyz|Lower
WxYz|Mixed
sqlite>