Simon, thanks for the answer. your code helped track down the issue, which I *still* believe to be a bug:
The issue is not with parameters, after all, but with comparison of values, and something "magic" about count(), I suspect. It has to do with the fact that the layer I am using (PHP PDO) actually binds parameters as strings by defult. in general, this should not be an issue, since sqlite seems to be quite relaxed wrt to typing. However, in this case, it matters. The "bug" is now reproducible easily: give the bas SQL statmenent: select * from (select *," (select count(*) from ITEM_ARTIST where ARTIST_id=artists.artist_id) CNT from ARTISTS ) if you apply this WHERE : ' where artist_id=1' it works if you quote the value (making it a string), it still works : where artist_id='1' if you supply a value for CNT as integer, it works : where CNT=1 if you supply the value for CNT as string, it BREAKS: where CNT='1' Further investigation shows that the issue is somehow related to how sqlite compares values, and affinity. It seems that, while the rules defined in the documentation for comparison operators work as expected in most cases, something breaks in the case of pseudo-columns that are the result of a sub-select (like the case above, for CNT). If I use CAST to define an explicit affinity for the expression, it starts working : select * from (select *," cast((select count(*) from ITEM_ARTIST where ARTIST_id=artists.artist_id) as int) CNT from ARTISTS ) now, any WHERE clause works as expected. Ideas ? > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Davies > Sent: Tuesday, December 15, 2009 1:58 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Bug when uUsing Parameters with views > > 2009/12/15 Cariotoglou Mike <m...@singular.gr>: > > I checked in the bug database, and this does not seem to have been > > reported, and IMHO it is definitely a bug. > > workarounds exist,as pointed out by me and others. still, I > would like > > to hear from the core team whether this is recognized as a bug, and > > will be dealt with at some point in time. > > btw, I would have liked to post a script demonstrating the > bug, but I > > do not think this is possible, due to the fact that the > command-line > > sqlite does not handle parametric statements, or at least I > don't know > > how to write one :) > > > > I am unable to reproduce your problem (version 3.6.11): > > // > // create table item_artist( artist_id integer, data text ); > // create table artists( artist_id integer, data text ); // > insert into artists values( 1, 'a1_pic1' ); // insert into > artists values( 2, 'a1_pic2' ); // insert into item_artist > values( 1, 'a1_item1' ); // insert into item_artist values( > 2, 'a2_item1' ); // insert into item_artist values( 2, 'a2_item2' ); > > #include "stdafx.h" > #define SQLITE_PRIVATE > #include "sqlite3.c" > > int _tmain(int argc, _TCHAR* argv[]) > { > int cnt; > int dbStatus; > sqlite3* dbH; > sqlite3_stmt* stmt; > dbStatus = sqlite3_open( "tstBind.db", &dbH ); > > while( fscanf( stdin, "%d", &cnt ) ) > { > char* sql = "select * from ( select *," > "(select count(*) from ITEM_ARTIST where " > "ARTIST_id=artists.artist_id) CNT from > ARTISTS ) where " > "cnt = :a;"; > > const char* tail; > > if( 0 == cnt ) > { > exit(0); > } > > dbStatus = sqlite3_prepare_v2( dbH, sql, > strlen( sql ), &stmt, &tail ); > if( SQLITE_OK != dbStatus ) > { > printf( "%s\n", sqlite3_errmsg( dbH ) ); > } > > dbStatus = sqlite3_bind_int( stmt, 1, cnt ); > if( SQLITE_OK != dbStatus ) > { > printf( "%s\n", sqlite3_errmsg( dbH ) ); > } > > while( SQLITE_ROW == ( dbStatus = sqlite3_step( > stmt ) ) ) > { > printf( "%s ", sqlite3_column_text( stmt, 0 ) ); > printf( "%s ", sqlite3_column_text( stmt, 1 ) ); > printf( "%s\n", sqlite3_column_text( > stmt, 2 ) ); > } > sqlite3_finalize( stmt ); > } > sqlite3_close( dbH ); > > return 0; > } > > On executing if I enter 1 I get > 1 > 1 a1_pic1 1 > > and if 2, then > 2 > 2 a2_pic1 2 > > which all looks ok > > Regards, > Simon > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
