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

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

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

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

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

[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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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