Doing exact matches on floating point values will get you in trouble quite frequently. Most float numbers cannot be exactly represented internally so can cause such behavior.
Hopefully your application doesn't depend on this....if so you need to redesign what you're doing. But to fix your problem you need to bind your value as a double instead of text. strcpy( buf, "select sum(a) from test group by a having sum(a) = ?"); sqlite3_prepare(db, buf, sizeof(buf), &q, NULL); sqlite3_bind_double( q, 1, 123.456); print( q ); strcpy( buf, "select sum(a) from test group by a having sum(a) = 0+?"); sqlite3_prepare(db, buf, sizeof(buf), &q, NULL); sqlite3_bind_double( q, 1, 123.456 ); print( q ); Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of elmo [elm...@gmail.com] Sent: Tuesday, May 08, 2012 8:34 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Having in prepared statement with parameters I have code that looks like that: --- begin main.cpp --- #include <unistd.h> #include <stdio.h> #include <sqlite3.h> #include <string.h> void print( sqlite3_stmt * pStmt ) { const char* col1name = sqlite3_column_name( pStmt, 0 ); printf("res: "); while ( sqlite3_step( pStmt ) == SQLITE_ROW ){ printf("%lf ", sqlite3_column_double( pStmt, 0 )); } printf("\n"); sqlite3_finalize(pStmt); } int main( int argc, char ** argv ) { const char filename[] = "test.db3"; char * err = 0; unlink( filename ); sqlite3 *db; sqlite3_open( filename, &db ); sqlite3_exec(db, "create table test (a decimal(12,4))", 0, 0, &err); sqlite3_exec(db, "insert into test values (123.456)", 0, 0, &err); sqlite3_stmt * q; char buf[2048]; strcpy( buf, "select * from test"); sqlite3_prepare(db, buf, sizeof(buf), &q, NULL); print( q ); strcpy( buf, "select * from test where a = ?"); sqlite3_prepare(db, buf, sizeof(buf), &q, NULL); sqlite3_bind_text( q, 1, "123.456", 7, SQLITE_STATIC ); print( q ); strcpy( buf, "select sum(a) from test group by a"); sqlite3_prepare(db, buf, sizeof(buf), &q, NULL); print( q ); strcpy( buf, "select sum(a) from test group by a having sum(a) = 123.456"); sqlite3_prepare(db, buf, sizeof(buf), &q, NULL); print( q ); strcpy( buf, "select sum(a) from test group by a having sum(a) = ?"); sqlite3_prepare(db, buf, sizeof(buf), &q, NULL); sqlite3_bind_text( q, 1, "123.456", 7, SQLITE_STATIC ); print( q ); strcpy( buf, "select sum(a) from test group by a having sum(a) = 0+?"); sqlite3_prepare(db, buf, sizeof(buf), &q, NULL); sqlite3_bind_text( q, 1, "123.456", 7, SQLITE_STATIC ); print( q ); return 0; } --- end main.cpp --- The output is: res: 123.456000 res: 123.456000 res: 123.456000 res: 123.456000 res: res: 123.456000 Whereas I would expect it to be: res: 123.456000 res: 123.456000 res: 123.456000 res: 123.456000 res: 123.456000 res: 123.456000 As the last three statements are exactly the same (in meaning). Can someone explain what's happening? _______________________________________________ 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