Re: [sqlite] Datatype for prices (1,500)
On 2 Dec 2016, at 2:30am, James K. Lowden wrote: > Jens Alfke wrote: > >> I understand that the consensus among those who do is to _not_ store >> monetary values as floating-point, due to the roundoff error. > > I wouldn't be so sure. I've worked in that space for 30 years designing > databases and applications. I don't remember once representing money as > anything but floating point, except on mainframes. Occasionally we > used exact-decimal database types for bulk-load tables, to verify the > vendor's promised 18,6 scaling factor. But in production? For > computation? Floats, always and ever. Floating point is fine for science. And for calculations in banking which inherently deal with non-integers (e.g. compound interest). But once you’ve finished your compound interest calculation you are going to round the result in exactly the way you were told to, and you will store it as an amount of money which you can exactly hand over to the account-holder in cash. To do anything else would mean that any audit required endless tedious calculations to prove you weren’t adding up fractions to sneak our an odd pound or penny. Plus endless checking to see that no amount corresponding to a transaction was a fractional unit of currency. (The fact that some prices and exchange rates manipulate currencies as "percentage in point", meaning that a dollar is 1 "pips" not 100 "cents", is considered unhelpful in the extreme to my point !) Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Datatype for prices (1,500)
On Thursday, 1 December, 2016 19:50, Jens Alfke said: > > On Nov 30, 2016, at 5:53 PM, Keith Medcalf wrote: > > Wattage problem based on incoherent understanding of how floating point > > numbers are stored. > You may not be aware of this, Keith, but that comes off as really snarky > and condescending. Factual information often does when it is contrary to something that has been deliberately oversimplified for mass consumption to the point of being incorrect. > I feel I have a fairly coherent (if not domain-expert) understanding of > FP, and it sounds like others here do too. And while I do not myself work > with database schema involving money, I understand that the consensus > among those who do is to _not_ store monetary values as floating-point, > due to the roundoff error. Some people reject algorithms out-of-hand that > accumulate even tiny amounts of roundoff error*. And of course the > roundoff error increases as the size of the integer portion of the number > increases. That ancient recommendation is from the good old days when computers used what is now called "fast floating point" which was designed to have about a digit more accuracy than a slide-rule -- and performed computations with about the same speed as a slide-rule. In IEEE754 that is called binary16 (or half precision) and has an accuracy of 3 decimal digits -- completely unsuitable for money. binary32 (single precision) has an accuracy of about 7 decimal digits and is good as long as no operand, intermediate, or result exceeds about $100. binary64 (double precision) has an accuracy of about 15 decimal digits and is good as long as no operand, intermediate, or result is greater than about $1,000,000,000. binary128 (quadruple precision) has an accuracy of about 33 decimal digits and is good up to $10^28. Binary256 (octuple precision) has an accuracy of about 71 decimal digits and is good up to $10^67. Decimal32, Decimal64 and Decimal128 have about the same precision as their binary counterparts (as do the various fixed point BCD or packed-decimal formats, or scaled integer binary types, or even bignums). Fixed or Floating arithmetic is equally accurate within the limits of precision associated with the storage and calculation format in use. Keith > —Jens > > * just as some people reject UUID schemes with a tiny-but-nonzero chance > of collisions, hmm? Not really. Using a storage format that has the required precision cannot lead to "happenstance" (probabilistic) errors, whether that storage format is fixed or floating point, binary, decimal, BCD, packed-decimal, or scaled integer. However, UUID schemes have a 100% certainty of collision. There is a huge difference. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Datatype for prices (1,500)
On Thu, 1 Dec 2016 16:50:21 -0800 Jens Alfke wrote: > I understand that the consensus among those who do is to _not_ store > monetary values as floating-point, due to the roundoff error. I wouldn't be so sure. I've worked in that space for 30 years designing databases and applications. I don't remember once representing money as anything but floating point, except on mainframes. Occasionally we used exact-decimal database types for bulk-load tables, to verify the vendor's promised 18,6 scaling factor. But in production? For computation? Floats, always and ever. I have seen quite a few discussions here suggesting integers instead because they're exact. To me, that sounds like nothing so much as Charles Moore. He made a virtue of necessity: since Forth had no floating point, he recommended using integers, and keeping track fo the decimal place yourself. The difficulty in using floating point for money is *not* accuracy. It's equivalency. The first-year programmer soon learns not to test for equivalency using "=", but by the roundabout process of comparing the absolute difference to an epsilon, usually about 1E-6. What's much more problematic is keeping anything, including money, in a nonstandard format, and introducing bespoke logic to manage the decimal place. Opportunities abound for error and misunderstanding, and instead of being off by a penny, you're off by 10 or 100 times. Percentages were a great one for that. Pounds and pence in Ireland, too. Not to put words in Keith's mouth, I think his point is that if you're surprised about floating point behavior in SQLite, you haven't mastered your trade. It's not a database problem; it's a question of understanding how IEEE floating point works. The decision to use integers or strings instead will only yield different, and worse, problems. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Scope of sqlite3_update_hook?
On 12/1/2016 7:51 PM, Jens Alfke wrote: Does a registered sqlite3_update_hook get called when _any_ SQLite connection modifies the database (not just the connection it's registered with)? No, only the connection it's registered with. If so, then does that include connections in other OS processes? (I'm looking for a way to detect this.) There's nothing in SQLite that would help with that, to my knowledge. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] locating a minimum in SQLite 2
On Thu, 1 Dec 2016 11:12:48 -0800 James Walker wrote: > SELECT MIN(PRICE), IDENT FROM INFO; > > and get what I want. But in SQLite 2 (legacy code), this doesn't > work... I get the minimum value, but NULL in the IDENT column. Does it work with both versions if you write it correctly? SELECT MIN(PRICE), IDENT FROM INFO GROUP BY IDENT; > I want to find the IDENT of the row with the minimum value of PRICE That's what quantification was invented for select * from info where price in ( SELECT MIN(PRICE) FROM INFO ); --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Scope of sqlite3_update_hook?
Does a registered sqlite3_update_hook get called when _any_ SQLite connection modifies the database (not just the connection it's registered with)? If so, then does that include connections in other OS processes? (I'm looking for a way to detect this.) —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Datatype for prices (1,500)
> On Nov 30, 2016, at 5:53 PM, Keith Medcalf wrote: > > Wattage problem based on incoherent understanding of how floating point > numbers are stored. You may not be aware of this, Keith, but that comes off as really snarky and condescending. I feel I have a fairly coherent (if not domain-expert) understanding of FP, and it sounds like others here do too. And while I do not myself work with database schema involving money, I understand that the consensus among those who do is to _not_ store monetary values as floating-point, due to the roundoff error. Some people reject algorithms out-of-hand that accumulate even tiny amounts of roundoff error*. And of course the roundoff error increases as the size of the integer portion of the number increases. —Jens * just as some people reject UUID schemes with a tiny-but-nonzero chance of collisions, hmm? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic anomaly
On 12/1/16, Mark Brand wrote: > Hi, > > Using SQLite version 3.15.2, the following SQL returns 0 rows, whereas > I believe it should return 1 row. Any of the commented out alternatives > produces the expected 1 row. > > Mark > > CREATE VIEW W AS > SELECT 0 show_a; > > CREATE VIEW X AS > SELECT 'A' a, 1 v > UNION SELECT 'B',1; > > CREATE VIEW Y AS > SELECT * FROM W JOIN X; > > SELECT CASE WHEN group_by_a THEN a END a, SUM(v) v > FROM Y > LEFT JOIN (SELECT NULL group_by_a UNION SELECT 1) x2 > ON show_a AND a IS NOT NULL >--ON show_a = 1 AND a IS NOT NULL >-- ON IFNULL(show_a, 0) AND a IS NOT NULL >--ON NOT(NOT(show_a)) AND a IS NOT NULL or: ON +show_a AND a IS NOT NULL So there are a lot of ways to work around this problem. The problem has been in SQLite for over 10 years, completely unnoticed, because it only comes up when you use a bare column from a subquery as a constraint in a LEFT JOIN, which is apparently something that not many people ever do. It is fixed now on trunk. Thanks again for the bug report. > GROUP BY CASE WHEN group_by_a THEN a END; > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A total with a GROUP BY
2016-12-01 21:37 GMT+01:00 Igor Tandetnik : > On 12/1/2016 1:57 PM, Cecil Westerhof wrote: >> >> At the moment I have the following code: >> SELECT totalUsed, COUNT(*) AS Count >> FROM tips >> GROUP BY totalUsed >> >> This shows the total number of records for every value of totalUsed. >> Would it be possible to get the total number of records also. (Sum of >> all the Count's.) > > > SELECT totalUsed, COUNT(*) AS Count FROM tips GROUP BY totalUsed > union all > SELECT null, COUNT(*) FROM tips; Works likes a charm and is significant faster as the solution from Nomad. But from him I took: SELECT 'Total:', COUNT(*) FROM tips Thanks, both. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A total with a GROUP BY
On Thu Dec 01, 2016 at 07:57:06PM +0100, Cecil Westerhof wrote: > At the moment I have the following code: > SELECT totalUsed, COUNT(*) AS Count > FROM tips > GROUP BY totalUsed > > This shows the total number of records for every value of totalUsed. > Would it be possible to get the total number of records also. (Sum of > all the Count's.) Here is one way which I find easy to read: create table tips( totalUsed integer, item varchar ); insert into tips values(10,'item'); insert into tips values(10,'item2'); insert into tips values(12,'item3'); insert into tips values(12,'item4'); insert into tips values(12,'item5'); with source(totalUsed,Counts) as ( select totalUsed, COUNT(*) from tips group by totalUsed ) select totalUsed,Counts from source union all select 'Total:', sum(Counts) from source order by 1 ; Result: totalUsed Counts -- -- 10 2 12 3 Total: 5 Unfortunately it is not very efficient because SQLite executes (expands?) the "source" select twice. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A total with a GROUP BY
On 12/1/2016 1:57 PM, Cecil Westerhof wrote: At the moment I have the following code: SELECT totalUsed, COUNT(*) AS Count FROM tips GROUP BY totalUsed This shows the total number of records for every value of totalUsed. Would it be possible to get the total number of records also. (Sum of all the Count's.) SELECT totalUsed, COUNT(*) AS Count FROM tips GROUP BY totalUsed union all SELECT null, COUNT(*) FROM tips; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] locating a minimum in SQLite 2
On 12/1/2016 11:55 AM, Baruch Burstein wrote: On Thu, Dec 1, 2016 at 9:12 PM, James Walker wrote: Let's say I have a table INFO with columns PRICE and IDENT, and I want to find the IDENT of the row with the minimum value of PRICE. In SQLite 3, I can say SELECT MIN(PRICE), IDENT FROM INFO; and get what I want. But in SQLite 2 (legacy code), this doesn't work... I get the minimum value, but NULL in the IDENT column. I could say SELECT PRICE, IDENT FROM INFO ORDER BY PRICE; and ignore all but the first row of the result, but I'm sure there must be a better way? LIMIT 1? Yes, LIMIT 1 does it. I knew there had to be a simple way. Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] locating a minimum in SQLite 2
On Thu, Dec 1, 2016 at 1:12 PM, James Walker wrote: > Let's say I have a table INFO with columns PRICE and IDENT, and I want to > find the IDENT of the row with the minimum value of PRICE. In SQLite 3, I > can say > > SELECT MIN(PRICE), IDENT FROM INFO; > > and get what I want. But in SQLite 2 (legacy code), this doesn't work... > I get the minimum value, but NULL in the IDENT column. I could say > > SELECT PRICE, IDENT FROM INFO ORDER BY PRICE; > > and ignore all but the first row of the result, but I'm sure there must be > a better way? > > Well, standard SQL seems to work, but would return multiple lines if multiple rows have the minimal price, but you could use LIMIT 1: SQLite version 3.14.2 2016-09-12 18:50:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table info(price int, ident text); sqlite> insert into info(price,ident) values(1,'a1'); sqlite> insert into info(price,ident) values(2,'b'); sqlite> insert into info(price,ident) values(3,'c'); sqlite> insert into info(price,ident) values(1,'a'); sqlite> select price, ident from info where price=(select min(price) from info); 1|a1 1|a sqlite> select price, ident from info where price=(select min(price) from info) limit 1; 1|a1 sqlite> In sqlite 2 the same works: SQLite version 2.8.17 Enter ".help" for instructions sqlite> create table info(ident text,price int); sqlite> insert into table(ident, price) values('a',1); SQL error: near "table": syntax error sqlite> insert into info(ident, price) values('a',1); sqlite> insert into info(ident, price) values('a1',1); sqlite> insert into info(ident, price) values('b',2); sqlite> insert into info(ident, price) values('c',3); sqlite> select min(price), ident from info; 1| sqlite> select price, ident from info where price=(select min(price) from info); 1|a 1|a1 sqlite> select price, ident from info where price=(select min(price) from info) limit 1; 1|a sqlite> -- Heisenberg may have been here. Unicode: http://xkcd.com/1726/ Maranatha! <>< John McKown ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] locating a minimum in SQLite 2
On Thu, Dec 1, 2016 at 9:12 PM, James Walker wrote: > Let's say I have a table INFO with columns PRICE and IDENT, and I want to > find the IDENT of the row with the minimum value of PRICE. In SQLite 3, I > can say > > SELECT MIN(PRICE), IDENT FROM INFO; > > and get what I want. But in SQLite 2 (legacy code), this doesn't work... > I get the minimum value, but NULL in the IDENT column. I could say > > SELECT PRICE, IDENT FROM INFO ORDER BY PRICE; > > and ignore all but the first row of the result, but I'm sure there must be > a better way? LIMIT 1? ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] locating a minimum in SQLite 2
Let's say I have a table INFO with columns PRICE and IDENT, and I want to find the IDENT of the row with the minimum value of PRICE. In SQLite 3, I can say SELECT MIN(PRICE), IDENT FROM INFO; and get what I want. But in SQLite 2 (legacy code), this doesn't work... I get the minimum value, but NULL in the IDENT column. I could say SELECT PRICE, IDENT FROM INFO ORDER BY PRICE; and ignore all but the first row of the result, but I'm sure there must be a better way? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL logic anomaly
On 12/1/16, Mark Brand wrote: > > Using SQLite version 3.15.2, the following SQL returns 0 rows, whereas > I believe it should return 1 row. Any of the commented out alternatives > produces the expected 1 row. Thanks for the clear and concise bug report. The ticket is https://www.sqlite.org/src/tktview/2df0107bd268 > > Mark > > CREATE VIEW W AS > SELECT 0 show_a; > > CREATE VIEW X AS > SELECT 'A' a, 1 v > UNION SELECT 'B',1; > > CREATE VIEW Y AS > SELECT * FROM W JOIN X; > > SELECT CASE WHEN group_by_a THEN a END a, SUM(v) v > FROM Y > LEFT JOIN (SELECT NULL group_by_a UNION SELECT 1) x2 > ON show_a AND a IS NOT NULL >--ON show_a = 1 AND a IS NOT NULL >-- ON IFNULL(show_a, 0) AND a IS NOT NULL >--ON NOT(NOT(show_a)) AND a IS NOT NULL > GROUP BY CASE WHEN group_by_a THEN a END; > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] A total with a GROUP BY
At the moment I have the following code: SELECT totalUsed, COUNT(*) AS Count FROM tips GROUP BY totalUsed This shows the total number of records for every value of totalUsed. Would it be possible to get the total number of records also. (Sum of all the Count's.) -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL logic anomaly
Hi, Using SQLite version 3.15.2, the following SQL returns 0 rows, whereas I believe it should return 1 row. Any of the commented out alternatives produces the expected 1 row. Mark CREATE VIEW W AS SELECT 0 show_a; CREATE VIEW X AS SELECT 'A' a, 1 v UNION SELECT 'B',1; CREATE VIEW Y AS SELECT * FROM W JOIN X; SELECT CASE WHEN group_by_a THEN a END a, SUM(v) v FROM Y LEFT JOIN (SELECT NULL group_by_a UNION SELECT 1) x2 ON show_a AND a IS NOT NULL --ON show_a = 1 AND a IS NOT NULL -- ON IFNULL(show_a, 0) AND a IS NOT NULL --ON NOT(NOT(show_a)) AND a IS NOT NULL GROUP BY CASE WHEN group_by_a THEN a END; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite does not use opportunity to optimise COUNT() queries using partial indexes
Thanks! That explains a lot. For some reason I thought that 'SELECT COUNT() FROM ' is optimised. > Gonna take a stab and answering this. > http://www.sqlite.org/opcode.html > > The explain output for select count() from foo; uses the "Count" opcode. The > description for that is > "Store the number of entries (an integer value) in the table or index opened > by cursor P1 in register P2" > So that is indeed going to scan through the whole table, as the OpenRead was > pointed to the table B-tree and not the index B-tree. > > In the second case "select count() from foo where ref_count = 0" the OpenRead > opens up the index (p4 isn't an integer) so that is indeed going through the > index. > > Remember also that you can get a more succinct explain by using "explain > query plan". > > Here's the output of me running this in a CLI I compiled with the pretty > explain comments. Using .eqp full it outputs the "explain query plan" > results, then the "explain" results, then the query results. > > (Hmm, random note: It looks like ".eqp full" makes it disregard ".header on" > when it gets down to outputting the results. Downgrading to only ".eqp on" > respects the ".header on" though.) > > SQLite version 3.15.1 2016-11-04 12:08:49 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > > sqlite> create table foo (id integer primary key, ref_count integer not null); > > sqlite> create index foo_ref_count_idx on foo (ref_count) where ref_count = 0; > > sqlite> .eqp full > > sqlite> select count() from foo; > --EQP-- 0,0,0,SCAN TABLE foo > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 7 000 Start at 7 > 1 OpenRead 1 2 0 1 00 root=2 iDb=0 > 2 Count 1 1 000 r[1]=count() > 3 Close 1 0 000 > 4 Copy 1 2 000 r[2]=r[1] > 5 ResultRow 2 1 000 output=r[2] > 6 Halt 0 0 000 > 7 Transaction0 0 2 0 01 usesStmtJournal=0 > 8 Goto 0 1 000 > 0 > > sqlite> select count() from foo where ref_count = 0; > --EQP-- 0,0,0,SEARCH TABLE foo USING COVERING INDEX foo_ref_count_idx > (ref_count=?) > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 13000 Start at 13 > 1 Null 0 1 100 r[1..1]=NULL > 2 OpenRead 1 3 0 k(2,,) 02 root=3 iDb=0; > foo_ref_count_idx > 3 Integer0 2 000 r[2]=0 > 4 SeekGE 1 8 2 1 00 key=r[2] > 5 IdxGT 1 8 2 1 00 key=r[2] > 6 AggStep0 0 0 1 count(0) 00 accum=r[1] > step(r[0]) > 7 Next 1 5 100 > 8 Close 1 0 000 > 9 AggFinal 1 0 0 count(0) 00 accum=r[1] N=0 > 10Copy 1 3 000 r[3]=r[1] > 11ResultRow 3 1 000 output=r[3] > 12Halt 0 0 000 > 13Transaction0 0 2 0 01 usesStmtJournal=0 > 14Goto 0 1 000 > 0 > > sqlite> select count() from foo where ref_count != 0; > --EQP-- 0,0,0,SCAN TABLE foo > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 13000 Start at 13 > 1 Null 0 1 100 r[1..1]=NULL > 2 OpenRead 0 2 0 2 00 root=2 iDb=0; foo > 3 Rewind 0 8 000 > 4 Column 0 1 200 r[2]=foo.ref_count > 5 Eq 3 7 2 (BINARY) 54 if r[2]==r[3] > goto 7 > 6 AggStep0 0 0 1 count(0) 00 accum=r[1] > step(r[0]) > 7 Next 0 4 001 > 8 Close 0 0 000 > 9 AggFinal 1 0 0 count(0) 00 accum=r[1] N=0 > 10Copy 1 4 000 r[4]=r[1] > 11ResultRow 4 1 000 output=r[4] > 12Halt 0 0 000 > 13Transaction0 0 2 0 01
Re: [sqlite] SQLite does not use opportunity to optimise COUNT() queries using partial indexes
Gonna take a stab and answering this. http://www.sqlite.org/opcode.html The explain output for select count() from foo; uses the "Count" opcode. The description for that is "Store the number of entries (an integer value) in the table or index opened by cursor P1 in register P2" So that is indeed going to scan through the whole table, as the OpenRead was pointed to the table B-tree and not the index B-tree. In the second case "select count() from foo where ref_count = 0" the OpenRead opens up the index (p4 isn't an integer) so that is indeed going through the index. Remember also that you can get a more succinct explain by using "explain query plan". Here's the output of me running this in a CLI I compiled with the pretty explain comments. Using .eqp full it outputs the "explain query plan" results, then the "explain" results, then the query results. (Hmm, random note: It looks like ".eqp full" makes it disregard ".header on" when it gets down to outputting the results. Downgrading to only ".eqp on" respects the ".header on" though.) SQLite version 3.15.1 2016-11-04 12:08:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table foo (id integer primary key, ref_count integer not null); sqlite> create index foo_ref_count_idx on foo (ref_count) where ref_count = 0; sqlite> .eqp full sqlite> select count() from foo; --EQP-- 0,0,0,SCAN TABLE foo addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 7 000 Start at 7 1 OpenRead 1 2 0 1 00 root=2 iDb=0 2 Count 1 1 000 r[1]=count() 3 Close 1 0 000 4 Copy 1 2 000 r[2]=r[1] 5 ResultRow 2 1 000 output=r[2] 6 Halt 0 0 000 7 Transaction0 0 2 0 01 usesStmtJournal=0 8 Goto 0 1 000 0 sqlite> select count() from foo where ref_count = 0; --EQP-- 0,0,0,SEARCH TABLE foo USING COVERING INDEX foo_ref_count_idx (ref_count=?) addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 13000 Start at 13 1 Null 0 1 100 r[1..1]=NULL 2 OpenRead 1 3 0 k(2,,) 02 root=3 iDb=0; foo_ref_count_idx 3 Integer0 2 000 r[2]=0 4 SeekGE 1 8 2 1 00 key=r[2] 5 IdxGT 1 8 2 1 00 key=r[2] 6 AggStep0 0 0 1 count(0) 00 accum=r[1] step(r[0]) 7 Next 1 5 100 8 Close 1 0 000 9 AggFinal 1 0 0 count(0) 00 accum=r[1] N=0 10Copy 1 3 000 r[3]=r[1] 11ResultRow 3 1 000 output=r[3] 12Halt 0 0 000 13Transaction0 0 2 0 01 usesStmtJournal=0 14Goto 0 1 000 0 sqlite> select count() from foo where ref_count != 0; --EQP-- 0,0,0,SCAN TABLE foo addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 13000 Start at 13 1 Null 0 1 100 r[1..1]=NULL 2 OpenRead 0 2 0 2 00 root=2 iDb=0; foo 3 Rewind 0 8 000 4 Column 0 1 200 r[2]=foo.ref_count 5 Eq 3 7 2 (BINARY) 54 if r[2]==r[3] goto 7 6 AggStep0 0 0 1 count(0) 00 accum=r[1] step(r[0]) 7 Next 0 4 001 8 Close 0 0 000 9 AggFinal 1 0 0 count(0) 00 accum=r[1] N=0 10Copy 1 4 000 r[4]=r[1] 11ResultRow 4 1 000 output=r[4] 12Halt 0 0 000 13Transaction0 0 2 0 01 usesStmtJournal=0 14Integer0 3 000 r[3]=0 15Goto 0 1 000 0 -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Paul Sent: Thursday, December 01,
Re: [sqlite] Datatype for prices (1,500)
Look, you want to store the same level of detail that a decimal(7,4) does? Easy, you just multiply the conceptual number by 10,000 and it represents hundredths of a cent, the exact same precision you are using in MySQL. Your examples would then be stored as 20 or 8 respectively. And every other possible value you could store in the MySQL you can now store in SQLite, consistently. -- Darren Duncan On 2016-12-01 12:08 AM, Werner Kleiner wrote: As I can see storing prices is a topic with different ways and different solutions. The advice to store prices in Cent or Integer: Yes you can do: but how will you sore hundredth cents amounts or tenth cent prices? I have prices like 0,0020 or 0,0008 Euro I think I have to manipulate the prices for viewing in the app with PHP. Fact is: we can have to DBs MySQL and SQlite. MySQL with decimal(7,4) stores a price 1.500 from a textfield exact so. If you want to show the price again in the app, there is nothing to do. But switching to SQLite the price is viewed as 1.5 (and stored) I know this is no error of SQLite. But I have now to differ between Sqlite and MySQL and have to optimize the SELECT and adding 00 programmatically to view correct if using Sqlite. My original post was if there is a way for both DBs with same behavior, but it seems not. Thanks to all for help. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] performance issue through Dll upgrade
On 12/01/2016 02:24 PM, Stephan Stauber wrote: Hello, we have following performance Issue since we upgraded from SQLite 3.8.5 to SQLite 3.10.0: SQLite 3.8.5: to INSERT 380.000 records into a in inMemory Database it takes 10 seconds SQLite 3.10.0 to INSERT 380.000 records into a in inMemory Database it takes 35 seconds Everything else is identical. Same configuration of SQLite DB and same data structure. (the INSERT statement is pre-compiled) Does 3.15.2 have the same problem? Dan. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Datatype for prices (1,500)
As I can see storing prices is a topic with different ways and different solutions. The advice to store prices in Cent or Integer: Yes you can do: but how will you sore hundredth cents amounts or tenth cent prices? I have prices like 0,0020 or 0,0008 Euro I think I have to manipulate the prices for viewing in the app with PHP. Fact is: we can have to DBs MySQL and SQlite. MySQL with decimal(7,4) stores a price 1.500 from a textfield exact so. If you want to show the price again in the app, there is nothing to do. But switching to SQLite the price is viewed as 1.5 (and stored) I know this is no error of SQLite. But I have now to differ between Sqlite and MySQL and have to optimize the SELECT and adding 00 programmatically to view correct if using Sqlite. My original post was if there is a way for both DBs with same behavior, but it seems not. Thanks to all for help. Werner 2016-12-01 2:53 GMT+01:00 Keith Medcalf : > > Wattage problem based on incoherent understanding of how floating point > numbers are stored. > > Not an actual problem if you do your comparisons properly: > > SQLite version 3.16.0 2016-11-30 05:08:59 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> CREATE TABLE transactions (amount REAL); > sqlite> INSERT INTO transactions VALUES (0.1),(0.1),(0.1); > sqlite> INSERT INTO transactions VALUES (-0.3); > sqlite> SELECT CASE WHEN (SELECT feq(0, sum(amount)) FROM transactions) >...> THEN 'zero' >...> ELSE 'not zero' >...>END; > zero > sqlite> > sqlite> SELECT sum(amount) FROM transactions; > 2.77555756156289e-17 > sqlite> > > Note that if you do not have a function that does floating-point comparisons > properly, you can always do something like this: > > sqlite> CREATE TABLE transactions (amount REAL); > sqlite> INSERT INTO transactions VALUES (0.1),(0.1),(0.1); > sqlite> INSERT INTO transactions VALUES (-0.3); > sqlite> SELECT CASE WHEN (SELECT sum(amount) FROM transactions) < 0.005 >...> THEN 'zero' >...> ELSE 'not zero' >...>END; > zero > sqlite> > sqlite> SELECT sum(amount) FROM transactions; > 2.77555756156289e-17 > > The representational limit of 0 is: > > sqlite> select ulp(0); > 1.11022302462516e-16 > > which is this far from the sum(amount) > > sqlite> select ulps(0, sum(amount)) from transactions; > -0.25 > > Note that the IEEE754 value of sum(amount) is less than 1 ulp from 0.0 (1/4 > ULP in my case, 1/2 ULP in your case). > > >> -Original Message- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf Of Simon Slavin >> Sent: Wednesday, 30 November, 2016 18:27 >> To: SQLite mailing list >> Subject: Re: [sqlite] Datatype for prices (1,500) >> >> >> On 30 Nov 2016, at 10:43pm, Keith Medcalf wrote: >> >> >> You were given a good recommendation save everything in "cents". Which >> >> might also be a good solution depending on the underlying language you >> >> use. as you can't store money in a float! >> > >> > And why can you not store money in a float? >> >> Because this: >> >> SQLite version 3.14.0 2016-07-26 15:17:14 >> Enter ".help" for usage hints. >> sqlite> CREATE TABLE transactions (amount REAL); >> sqlite> INSERT INTO transactions VALUES (0.1),(0.1),(0.1); >> sqlite> INSERT INTO transactions VALUES (-0.3); >> sqlite> SELECT CASE WHEN (SELECT sum(amount) FROM transactions) = 0 >> THEN 'zero' >> ELSE 'not zero' >>END; >> not zero >> sqlite> SELECT sum(amount) FROM transactions; >> 5.55111512312578e-17 >> sqlite> >> >> Please note that this is not just a problem with SQLite. One can >> demonstrate the equivalent problem in many programming languages and >> databases. >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users