Re: [sqlite] Infinity
I am looking for the answer too. anybody know it? On Sat, Oct 17, 2009 at 12:23 AM, Dan Bishop wrote: > I've noticed that I can use IEEE Infinity values in SQLite by writing > any literal too big for a double. > > sqlite> CREATE TABLE foo (x REAL); > sqlite> INSERT INTO foo VALUES (9e999); -- +Inf > sqlite> INSERT INTO foo VALUES (-9e999); -- -Inf > sqlite> INSERT INTO foo VALUES (9e999 / 9e999); -- NaN: gets converted > to NULL > sqlite> .null NULL > sqlite> select * FROM foo; > Inf > -Inf > NULL > sqlite> SELECT * FROM foo WHERE ABS(x) = 9e999; > Inf > -Inf > > Is it true on all platforms that 9e999 = Infinity and CAST(9e999 AS > TEXT) = 'Inf'? What's the preferred SQL syntax for infinity? > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Michael Chen Google Voice Phone.: 847-448-0647 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Infinity
SQLite stores the data however you give it. I'm not aware of any documentation requiring that 9e999 be considered infinity, nor any requiring that the command line treat invalid numbers as null. Most likely, treating NaN as null is simply a way for the command line to behave reasonably in an otherwise undefined situation. Practically, 9e999 is beyond the "infinity" limit for doubles on whatever compiler was used to build the command line. I think this limit is technically arbitrary, so on some compilers, either now, or in the future, 9e999 could very possibly NOT be infinity. std::numeric_limits::max() should be a standard (read "safe") way of getting the "infinity" value in C++. In the Visual C++ 2005 compiler, the max double is 1.7976931348623158e+308. I'm not sure that this is constant however, so don't count on it. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Chen Sent: Sunday, October 18, 2009 4:19 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Infinity I am looking for the answer too. anybody know it? On Sat, Oct 17, 2009 at 12:23 AM, Dan Bishop wrote: > I've noticed that I can use IEEE Infinity values in SQLite by writing > any literal too big for a double. > > sqlite> CREATE TABLE foo (x REAL); > sqlite> INSERT INTO foo VALUES (9e999); -- +Inf > sqlite> INSERT INTO foo VALUES (-9e999); -- -Inf > sqlite> INSERT INTO foo VALUES (9e999 / 9e999); -- NaN: gets converted > to NULL > sqlite> .null NULL > sqlite> select * FROM foo; > Inf > -Inf > NULL > sqlite> SELECT * FROM foo WHERE ABS(x) = 9e999; > Inf > -Inf > > Is it true on all platforms that 9e999 = Infinity and CAST(9e999 AS > TEXT) = 'Inf'? What's the preferred SQL syntax for infinity? > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Michael Chen Google Voice Phone.: 847-448-0647 ___ 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
Re: [sqlite] Infinity
//Dear there, it seems that I cannot insert std::numeric_limits::max(). I am on Mac Osx 10.5 //anybody can take a look at the code? I am new to sqlite3. comments on coding also welcome. #include #include #include #include using namespace std; int main(){ double infinity = std::numeric_limits::max(); double ninfinity = std::numeric_limits::min(); sqlite3 *db; char *zErrMsg = 0; int rc; rc = sqlite3_open(":memory:", &db); if( rc ){ fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } sqlite3_stmt* create_tl; rc = sqlite3_prepare_v2(db, "create table tl (number real)", -1, &create_tl,NULL); printf("%i\n",rc); if ( rc != SQLITE_OK) exit(rc); rc = sqlite3_step(create_tl); if ( rc != SQLITE_DONE) exit(rc); sqlite3_stmt* insert_tl; rc = sqlite3_prepare_v2(db,"insert into tl values(:number)",-1, &insert_tl,NULL); if ( rc != SQLITE_OK) exit(rc); sqlite3_bind_double(insert_tl, 1, 1.1); rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_bind_double(insert_tl, 1, infinity); //this line would fail, error code 21 rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_bind_double(insert_tl, 1, ninfinity); rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_bind_double(insert_tl, 1, 3.3); rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_stmt* select_tl; rc = sqlite3_prepare_v2(db, "select * from tl",-1,&select_tl,NULL); if (rc != SQLITE_OK) exit(rc); while ( sqlite3_step(select_tl) == SQLITE_ROW){ printf("%e",sqlite3_column_double(select_tl,0)); printf("\n"); } if (rc != SQLITE_DONE) exit(rc); sqlite3_finalize(select_tl); sqlite3_close(db); printf("exit normally\n"); } On Sun, Oct 18, 2009 at 5:58 PM, John Crenshaw wrote: > SQLite stores the data however you give it. I'm not aware of any > documentation requiring that 9e999 be considered infinity, nor any > requiring that the command line treat invalid numbers as null. Most > likely, treating NaN as null is simply a way for the command line to > behave reasonably in an otherwise undefined situation. > > Practically, 9e999 is beyond the "infinity" limit for doubles on > whatever compiler was used to build the command line. I think this limit > is technically arbitrary, so on some compilers, either now, or in the > future, 9e999 could very possibly NOT be infinity. > std::numeric_limits::max() should be a standard (read "safe") > way of getting the "infinity" value in C++. > > In the Visual C++ 2005 compiler, the max double is > 1.7976931348623158e+308. I'm not sure that this is constant however, so > don't count on it. > > -Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Chen > Sent: Sunday, October 18, 2009 4:19 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Infinity > > > I am looking for the answer too. anybody know it? > > On Sat, Oct 17, 2009 at 12:23 AM, Dan Bishop > wrote: > > > I've noticed that I can use IEEE Infinity values in SQLite by writing > > any literal too big for a double. > > > > sqlite> CREATE TABLE foo (x REAL); > > sqlite> INSERT INTO foo VALUES (9e999); -- +Inf > > sqlite> INSERT INTO foo VALUES (-9e999); -- -Inf > > sqlite> INSERT INTO foo VALUES (9e999 / 9e999); -- NaN: gets converted > > to NULL > > sqlite> .null NULL > > sqlite> select * FROM foo; > > Inf > > -Inf > > NULL > > sqlite> SELECT * FROM foo WHERE ABS(x) = 9e999; > > Inf > > -Inf > > > > Is it true on all platforms that 9e999 = Infinity and CAST(9e999 AS > > TEXT) = 'Inf'? What's the preferred SQL syntax for infinity? > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > Best regards, > Michael Chen > Google Voice Phone.: 847-448-0647 > ___ > 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 > -- Best regards, Michael Chen Google Voice Phone.: 847-448-0647 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Infinity
You need #include . Other than that, I don't know. I code on Windows, not Mac, but the code looks right. WARNING: min() != -infinity. For doubles, min is the smallest number greater than 0. -infinity == -max() John -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Chen Sent: Monday, October 19, 2009 4:59 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Infinity //Dear there, it seems that I cannot insert std::numeric_limits::max(). I am on Mac Osx 10.5 //anybody can take a look at the code? I am new to sqlite3. comments on coding also welcome. #include #include #include #include using namespace std; int main(){ double infinity = std::numeric_limits::max(); double ninfinity = std::numeric_limits::min(); sqlite3 *db; char *zErrMsg = 0; int rc; rc = sqlite3_open(":memory:", &db); if( rc ){ fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } sqlite3_stmt* create_tl; rc = sqlite3_prepare_v2(db, "create table tl (number real)", -1, &create_tl,NULL); printf("%i\n",rc); if ( rc != SQLITE_OK) exit(rc); rc = sqlite3_step(create_tl); if ( rc != SQLITE_DONE) exit(rc); sqlite3_stmt* insert_tl; rc = sqlite3_prepare_v2(db,"insert into tl values(:number)",-1, &insert_tl,NULL); if ( rc != SQLITE_OK) exit(rc); sqlite3_bind_double(insert_tl, 1, 1.1); rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_bind_double(insert_tl, 1, infinity); //this line would fail, error code 21 rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_bind_double(insert_tl, 1, ninfinity); rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_bind_double(insert_tl, 1, 3.3); rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_stmt* select_tl; rc = sqlite3_prepare_v2(db, "select * from tl",-1,&select_tl,NULL); if (rc != SQLITE_OK) exit(rc); while ( sqlite3_step(select_tl) == SQLITE_ROW){ printf("%e",sqlite3_column_double(select_tl,0)); printf("\n"); } if (rc != SQLITE_DONE) exit(rc); sqlite3_finalize(select_tl); sqlite3_close(db); printf("exit normally\n"); } On Sun, Oct 18, 2009 at 5:58 PM, John Crenshaw wrote: > SQLite stores the data however you give it. I'm not aware of any > documentation requiring that 9e999 be considered infinity, nor any > requiring that the command line treat invalid numbers as null. Most > likely, treating NaN as null is simply a way for the command line to > behave reasonably in an otherwise undefined situation. > > Practically, 9e999 is beyond the "infinity" limit for doubles on > whatever compiler was used to build the command line. I think this limit > is technically arbitrary, so on some compilers, either now, or in the > future, 9e999 could very possibly NOT be infinity. > std::numeric_limits::max() should be a standard (read "safe") > way of getting the "infinity" value in C++. > > In the Visual C++ 2005 compiler, the max double is > 1.7976931348623158e+308. I'm not sure that this is constant however, so > don't count on it. > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Chen > Sent: Sunday, October 18, 2009 4:19 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Infinity > > > I am looking for the answer too. anybody know it? > > On Sat, Oct 17, 2009 at 12:23 AM, Dan Bishop > wrote: > > > I've noticed that I can use IEEE Infinity values in SQLite by writing > > any literal too big for a double. > > > > sqlite> CREATE TABLE foo (x REAL); > > sqlite> INSERT INTO foo VALUES (9e999); -- +Inf > > sqlite> INSERT INTO foo VALUES (-9e999); -- -Inf > > sqlite> INSERT INTO foo VALUES (9e999 / 9e999); -- NaN: gets converted > > to NULL > > sqlite> .null NULL > > sqlite> select * FROM foo; > > Inf > > -Inf > > NULL > > sqlite> SELECT * FROM foo WHERE ABS(x) = 9e999; > > Inf > > -Inf > > > > Is it true on all platforms that 9e999 = Infinity and CAST(9e999 AS > > TEXT) = 'Inf'? What's the preferred SQL syntax for infinity? > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > Best regards, > Michael Chen > Google Voice Phone.: 847-448-0647 >
Re: [sqlite] Infinity
Thanks John. After incorporate a few changes, the code can compile and run. The result seems reasonable, the input infinity std::numeric_limits::max() is sent to and retrieved from a sqlite3 database correctly. --terminal output sqlite3 tempdb sqlite> select * from tl; 1.1 1.79769313486232e+308 -1.79769313486232e+308 3.3 --source code -- #include #include #include #include #include using namespace std; int main(){ double infinity = std::numeric_limits::max(); double ninfinity = - std::numeric_limits::max(); sqlite3 *db; char *zErrMsg = 0; int rc; rc = sqlite3_open("tempdb", &db); if( rc ){ fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } sqlite3_stmt* create_tl; rc = sqlite3_prepare_v2(db, "create table tl (number real)", -1, &create_tl,NULL); if ( rc != SQLITE_OK) exit(rc); rc = sqlite3_step(create_tl); if ( rc != SQLITE_DONE) exit(rc); sqlite3_stmt* insert_tl; rc = sqlite3_prepare_v2(db,"insert into tl values(:number)",-1, &insert_tl,NULL); if ( rc != SQLITE_OK) exit(rc); sqlite3_bind_double(insert_tl, 1, 1.1); rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_reset(insert_tl); sqlite3_bind_double(insert_tl, 1, infinity); rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_reset(insert_tl); sqlite3_bind_double(insert_tl, 1, ninfinity); rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_reset(insert_tl); sqlite3_bind_double(insert_tl, 1, 3.3); rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_stmt* select_tl; rc = sqlite3_prepare_v2(db, "select * from tl",-1,&select_tl,NULL); if (rc != SQLITE_OK) exit(rc); while ( sqlite3_step(select_tl) == SQLITE_ROW){ printf("%e",sqlite3_column_double(select_tl,0)); printf("\n"); } if (rc != SQLITE_DONE) exit(rc); sqlite3_finalize(select_tl); sqlite3_close(db); printf("exit normally\n"); } -- Best regards, Michael Chen Google Voice Phone.: 847-448-0647 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Infinity
Sorry, I think I gave you slightly buggy instructions. I just realized that max() should be the max true value capable of being stored, which should be less than the infinity value. std::numeric_limits provides another function named infinity() for getting positive infinity. I believe this value will be different than max(). Sorry for the mistake. John -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Chen Sent: Monday, October 19, 2009 11:40 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Infinity Thanks John. After incorporate a few changes, the code can compile and run. The result seems reasonable, the input infinity std::numeric_limits::max() is sent to and retrieved from a sqlite3 database correctly. --terminal output sqlite3 tempdb sqlite> select * from tl; 1.1 1.79769313486232e+308 -1.79769313486232e+308 3.3 --source code -- #include #include #include #include #include using namespace std; int main(){ double infinity = std::numeric_limits::max(); double ninfinity = - std::numeric_limits::max(); sqlite3 *db; char *zErrMsg = 0; int rc; rc = sqlite3_open("tempdb", &db); if( rc ){ fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } sqlite3_stmt* create_tl; rc = sqlite3_prepare_v2(db, "create table tl (number real)", -1, &create_tl,NULL); if ( rc != SQLITE_OK) exit(rc); rc = sqlite3_step(create_tl); if ( rc != SQLITE_DONE) exit(rc); sqlite3_stmt* insert_tl; rc = sqlite3_prepare_v2(db,"insert into tl values(:number)",-1, &insert_tl,NULL); if ( rc != SQLITE_OK) exit(rc); sqlite3_bind_double(insert_tl, 1, 1.1); rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_reset(insert_tl); sqlite3_bind_double(insert_tl, 1, infinity); rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_reset(insert_tl); sqlite3_bind_double(insert_tl, 1, ninfinity); rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_reset(insert_tl); sqlite3_bind_double(insert_tl, 1, 3.3); rc = sqlite3_step(insert_tl); if (rc != SQLITE_DONE) exit(rc); sqlite3_stmt* select_tl; rc = sqlite3_prepare_v2(db, "select * from tl",-1,&select_tl,NULL); if (rc != SQLITE_OK) exit(rc); while ( sqlite3_step(select_tl) == SQLITE_ROW){ printf("%e",sqlite3_column_double(select_tl,0)); printf("\n"); } if (rc != SQLITE_DONE) exit(rc); sqlite3_finalize(select_tl); sqlite3_close(db); printf("exit normally\n"); } -- Best regards, Michael Chen Google Voice Phone.: 847-448-0647 ___ 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
Re: [sqlite] Infinity
thanks for the notes. After change "double infinity" to std::numeric_limits(), here is the output: 1.10e+00 inf -inf 3.30e+00 it is clear that the sqlite3 does store "infinity", and select statement can retrieve it as well. The "printf" of C++ prints "infinity" as literal "inf". Michael Chen On Tue, Oct 20, 2009 at 12:20 AM, John Crenshaw wrote: > Sorry, I think I gave you slightly buggy instructions. I just realized > that max() should be the max true value capable of being stored, which > should be less than the infinity value. std::numeric_limits > provides another function named infinity() for getting positive > infinity. I believe this value will be different than max(). > > Sorry for the mistake. > > John > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Chen > Sent: Monday, October 19, 2009 11:40 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Infinity > > Thanks John. After incorporate a few changes, the code can compile and > run. > The result seems reasonable, the input infinity > std::numeric_limits::max() is sent to and retrieved from a > sqlite3 > database correctly. > > --terminal output > > sqlite3 tempdb > > sqlite> select * from tl; > 1.1 > 1.79769313486232e+308 > -1.79769313486232e+308 > 3.3 > > > --source code -- > > #include > #include > #include > #include > #include > using namespace std; > > int main(){ > double infinity = std::numeric_limits::max(); > double ninfinity = - std::numeric_limits::max(); > sqlite3 *db; > char *zErrMsg = 0; > int rc; > rc = sqlite3_open("tempdb", &db); > if( rc ){ >fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); >sqlite3_close(db); >exit(1); > } > > sqlite3_stmt* create_tl; > rc = sqlite3_prepare_v2(db, "create table tl (number real)", -1, > &create_tl,NULL); > > if ( rc != SQLITE_OK) exit(rc); > rc = sqlite3_step(create_tl); > if ( rc != SQLITE_DONE) exit(rc); > > sqlite3_stmt* insert_tl; > rc = sqlite3_prepare_v2(db,"insert into tl values(:number)",-1, > &insert_tl,NULL); > if ( rc != SQLITE_OK) exit(rc); > > sqlite3_bind_double(insert_tl, 1, 1.1); > rc = sqlite3_step(insert_tl); > if (rc != SQLITE_DONE) >exit(rc); > > sqlite3_reset(insert_tl); > sqlite3_bind_double(insert_tl, 1, infinity); > rc = sqlite3_step(insert_tl); > if (rc != SQLITE_DONE) >exit(rc); > > sqlite3_reset(insert_tl); > sqlite3_bind_double(insert_tl, 1, ninfinity); > rc = sqlite3_step(insert_tl); > if (rc != SQLITE_DONE) >exit(rc); > > sqlite3_reset(insert_tl); > sqlite3_bind_double(insert_tl, 1, 3.3); > rc = sqlite3_step(insert_tl); > if (rc != SQLITE_DONE) >exit(rc); > > > sqlite3_stmt* select_tl; > rc = sqlite3_prepare_v2(db, "select * from tl",-1,&select_tl,NULL); > if (rc != SQLITE_OK) >exit(rc); > > while ( sqlite3_step(select_tl) == SQLITE_ROW){ >printf("%e",sqlite3_column_double(select_tl,0)); >printf("\n"); > } > > if (rc != SQLITE_DONE) >exit(rc); > sqlite3_finalize(select_tl); > > sqlite3_close(db); > printf("exit normally\n"); > } > > > > > -- > Best regards, > Michael Chen > Google Voice Phone.: 847-448-0647 > ___ > 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 > -- Best regards, Michael Chen Google Voice Phone.: 847-448-0647 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users