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: [email protected] [[email protected]] on
behalf of elmo [[email protected]]
Sent: Tuesday, May 08, 2012 8:34 AM
To: [email protected]
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
