Bug #29629 for DBD-SQLite: Failure with function in where clause

Wed Sep 26 17:34:50 2007 marc [...] questright.com - Ticket created

Subject:	Failure with function in where clause
Date:	Wed, 26 Sep 2007 14:30:18 -0700
To:	bug-DBD-SQLite [...] rt.cpan.org
From:	Marc Mims <marc [...] questright.com>

I have a test script, attached, which fails in all versions of DBD::SQLite that I've tested, including 1.12, 1.13, and 1.14. This simple SQL statement returns expected results when run from the sqlite3 command line, but not from DBI: select count(*) from artist where length(name) > 2 Am I missing something, or is this a bug? -Marc

Message body is not shown because sender requested not to inline it.

Tue Oct 02 02:49:05 2007 cpan [...] desert-island.me.uk - Correspondence added

From:

cpan [...] desert-island.me.uk

Your script is missing the bind param for that greater-than value. However adding it doesnt make it pass.. It seems the bind-param is the problem, as changing it to length(name) > 1 makes it pass. Does sqlite itself support bind params here at all? Jess On Wed Sep 26 17:34:50 2007, marc@questright.com wrote: Show quoted text

> I have a test script, attached, which fails in all versions of > DBD::SQLite that I've tested, including 1.12, 1.13, and 1.14. > > This simple SQL statement returns expected results when run from the > sqlite3 command line, but not from DBI: > > select count(*) from artist where length(name) > 2 > > Am I missing something, or is this a bug? > > -Marc

Tue Oct 02 02:49:06 2007 The RT System itself - Status changed from 'new' to 'open'

Tue Oct 02 11:39:35 2007 marc [...] questright.com - Correspondence added

Subject:	Re: [rt.cpan.org #29629] Failure with function in where clause
Date:	Tue, 2 Oct 2007 08:39:01 -0700
To:	Jess Robinson via RT <bug-DBD-SQLite [...] rt.cpan.org>
From:	Marc Mims <marc [...] questright.com>

* Jess Robinson via RT <bug-DBD-SQLite@rt.cpan.org> [071002 04:00]: Show quoted text

> <URL: http://rt.cpan.org/Ticket/Display.html?id=29629 > > > Your script is missing the bind param for that greater-than value. > However adding it doesnt make it pass.. It seems the bind-param is the > problem, as changing it to length(name) > 1 makes it pass. > > Does sqlite itself support bind params here at all?

Yep. I botched the test. I've attached a better test, with the correct MIME type this time, too. It confirms Jess' observation that the failure seems to be related to bind params. -Marc

Message body is not shown because sender requested not to inline it.

Sun Apr 05 13:34:38 2009 adamk [...] cpan.org - Correspondence added

ISHIGAKI has added regression test has been added for this ticket, and it passes. Flagging as resolved.

Sun Apr 05 13:34:39 2009 adamk [...] cpan.org - Status changed from 'open' to 'resolved'

Sat Apr 11 00:34:39 2009 ishigaki [...] cpan.org - Correspondence added

Sorry, DBD::SQLite 1.22_04 reopens this as the previous implementation was not good enough. There are two known workaround for this issue. See t/ rt_29629_sqlite_where_length.t for details. In short, 1) use "bind_param" with TYPE => SQL_INTEGER (or whatever) attribute explicitly, or 2) add "+0" to the appropriate part of your SQL. If you find a better solution, patches are welcome. On Wed Sep 26 17:34:50 2007, marc@questright.com wrote: Show quoted text

> I have a test script, attached, which fails in all versions of > DBD::SQLite that I've tested, including 1.12, 1.13, and 1.14. > > This simple SQL statement returns expected results when run from the > sqlite3 command line, but not from DBI: > > select count(*) from artist where length(name) > 2 > > Am I missing something, or is this a bug? > > -Marc

Sat Apr 11 00:34:39 2009 The RT System itself - Status changed from 'resolved' to 'open'

Sat Apr 11 00:34:41 2009 ishigaki [...] cpan.org - Status changed from 'open' to 'stalled'