SQLite Forum

Timeline
Login

10 forum posts by user tb13

2020-08-14
14:55 Reply: decimal_div (artifact: 7996cae062 user: tb13)
I did not know that about the finite precision.  I don't happen to have any sample code for division (at least, not on me ;), so can't help in that regard.  Decimal math code looked pretty intense, last time I checked.

Thanks for adding this extension Dr. Hipp!
Tom
13:58 Post: decimal_div (artifact: 812c4d9b4e user: tb13)
Hi, the new decimal extension is great, thx for adding it.  Curious, why is there no decimal_div function?  Would be nice for finding averages.

select 1.1 + 2.2 = 3.3;
select decimal_add(1.1, 2.2) = decimal(3.3);

 Second result row is 1, super!
2020-06-13
16:45 Reply: current_date, etc are functions? (artifact: 1bb7b37479 user: tb13)
Interesting.  Seems like a better name for those entities would be "global variables" as opposed to "functions". I'm all for backwards compatibility so if they are considered functions in the 40+ years of SQL nomenclature then ok.

Thank you
Tom
00:45 Reply: current_date, etc are functions? (artifact: af3e865e61 user: tb13)
Ok I guess.  Yet the sqlite_version() function also takes no arguments but the parentheses are required.  Regardless it's no biggie.

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select current_date;
2020-06-13
sqlite> select current_date();
Error: near "(": syntax error
sqlite> select sqlite_version;
Error: no such column: sqlite_version
sqlite> select sqlite_version();
3.31.1
sqlite> 

Thanks guys
Tom
2020-06-12
18:43 Post: current_date, etc are functions? (artifact: f2397f7ea6 user: tb13)
Hi All,

Can someone explain why current_date, current_time, and current_timestamp show up in the list of available functions, even though they do not have function syntax?  CLI output below.

SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select name from pragma_function_list where name like 'current%';
current_timestamp
current_date
current_time
sqlite> select current_date();
Error: near "(": syntax error
sqlite> select current_date;
2020-06-12

Thanks
Tom
2020-06-02
02:15 Reply: SQLite turns 20 (artifact: a295e4cc9f user: tb13)
Cool graphic, awesome software!  I use it and advocate for it every day.  Thx Dr. Hipp and co!
2020-05-16
17:41 Reply: change in round() function (artifact: 4abba0550b user: tb13)
Excellent explanation, Keith.  

Thank you very much!
Tom
2020-05-15
18:43 Post: Documentation bug report re ROUND(9.95,1) (artifact: 319f672382 user: tb13)
Hi, in the FAQ on your website, it still warns of this rounding error, even though, as of v3.29, ROUND(9.95,1) rounds to 10.0.

https://sqlite.org/faq.html#q16

Thanks
Tom
18:21 Reply: change in round() function (artifact: 57693dd656 user: tb13)
Ah I see.  The round() function did not change, but the printf functions it uses did and they do the heavy lifting.  I enclosed the function from the 3.31.1 src below.

Do you think that this change makes SQLite sufficient for most financial calculations, to the cent?

Thanks Keith!  I am so glad to see you are still on this forum after the mailling list was deprecated!
Tom

====

static void roundFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
  int n = 0;
  double r;
  char *zBuf;
  assert( argc==1 || argc==2 );
  if( argc==2 ){
    if( SQLITE_NULL==sqlite3_value_type(argv[1]) ) return;
    n = sqlite3_value_int(argv[1]);
    if( n>30 ) n = 30;
    if( n<0 ) n = 0;
  }
  if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
  r = sqlite3_value_double(argv[0]);
  /* If Y==0 and X will fit in a 64-bit int,
  ** handle the rounding directly,
  ** otherwise use printf.
  */
  if( r<-4503599627370496.0 || r>+4503599627370496.0 ){
    /* The value has no fractional part so there is nothing to round */
  }else if( n==0 ){
    r = (double)((sqlite_int64)(r+(r<0?-0.5:+0.5)));
  }else{
    zBuf = sqlite3_mprintf("%.*f",n,r);
    if( zBuf==0 ){
      sqlite3_result_error_nomem(context);
      return;
    }
    sqlite3AtoF(zBuf, &r, sqlite3Strlen30(zBuf), SQLITE_UTF8);
    sqlite3_free(zBuf);
  }
  sqlite3_result_double(context, r);
}
17:01 Post: change in round() function (artifact: a0d9f741e5 user: tb13)
Hi, sorry if this has been asked before, or if I missed the thread in this forum or the old mailing list.

The behavior of the round() function seems to have changed between v3.28 and v3.29, but I don't see where it is mentioned in the release notes.

On my FreeBSD 11.3 server, v3.29 is the default SQLite, and I see this:
SQLite version 3.29.0 2019-07-10 17:32:03
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select round(1.15,1); select round(9.95,1);
1.2
10.0

On my iMac running macOS Catalina (10.15.3), the built-in sqlite3 is v3.28:
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select round(1.15,1); select round(9.95,1);
1.1
9.9

I found the example numbers in mailing list.

How is the newer round() implemented?

Here are the release notes for v3.29:
2019-07-10 (3.29.0)

Added the SQLITE_DBCONFIG_DQS_DML and SQLITE_DBCONFIG_DQS_DDL actions to sqlite3_db_config() for activating and deactivating the double-quoted string literal misfeature. Both default to "on" for legacy compatibility, but developers are encouraged to turn them "off", perhaps using the -DSQLITE_DQS=0 compile-time option.
-DSQLITE_DQS=0 is now a recommended compile-time option.
Improvements to the query planner:
Improved optimization of AND and OR operators when one or the other operand is a constant.
Enhancements to the LIKE optimization for cases when the left-hand side column has numeric affinity.
Added the "sqlite_dbdata" virtual table for extracting raw low-level content from an SQLite database, even a database that is corrupt.
Enhancements to the CLI:
Add the ".recover" command which tries to recover as much content as possible from a corrupt database file.
Add the ".filectrl" command useful for testing.
Add the long-standing ".testctrl" command to the ".help" menu.
Added the ".dbconfig" command