Re: [sqlite] Datatype for prices (1,500)

[Simon Slavin]

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)

[Keith Medcalf]

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)

[James K. Lowden]

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?

[Igor Tandetnik]

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

[James K. Lowden]

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?

[Jens Alfke]

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)

[Jens Alfke]

> 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

[Richard Hipp]

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

[Cecil Westerhof]

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

[nomad]

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

[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;

--
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

[James Walker]

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

[John McKown]

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

[Baruch Burstein]

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

[James Walker]

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

[Richard Hipp]

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

[Cecil Westerhof]

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

[Mark Brand]

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

[Paul]

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  

Re: [sqlite] SQLite does not use opportunity to optimise COUNT() queries using partial indexes

[David Raymond]

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)

[Darren Duncan]

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

[Dan Kennedy]

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)

[Werner Kleiner]

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