On Wed, May 12, 2010 at 4:18 AM, Gerhard Häring <g...@ghaering.de> wrote:
> A pysqlite user reported a problem with COALESCE. I could find out
> that his test case worked ok with SQLite versions 3.6.12 through
> 3.6.20, but failed with 3.6.21 to 3.6.23.
>
The problem was actually introduced in 3.6.12. Here is a simpler test case:
SELECT CASE WHEN 1 THEN 99 ELSE ? END + ?
Your work-around is to use "?1" and "?2" instead of "?".
>
> I could narrow down his test case to this very simple one below. The
> combination of addition, named parameters and COALESCE causes
> problems. The first SQL produces the expected result, but if you swap
> the operands, NULL is produced.
> SQLite developers,
>
> ghaer...@ws124~/src/gh/pysqlite/build/lib.macosx-10.6-universal-2.6$ cat
> t.py
> from pysqlite2 import dbapi2 as sqlite3
> print "SQLite", sqlite3.sqlite_version
>
> con = sqlite3.connect(":memory:")
> print con.execute('SELECT ? + coalesce(max((select 1)), ?)', (1,
> 0)).fetchone()
> print con.execute('SELECT coalesce(max((select 1)), ?) + ?', (0,
> 1)).fetchone()
>
> ghaer...@ws124~/src/gh/pysqlite/build/lib.macosx-10.6-universal-2.6$
> python t.py
> SQLite 3.6.23.1
> (2,)
> (None,)
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
---------------------
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
