Re: [sqlite] Seasonal syntax

On 14 December 2017 at 01:19, Warren Young wrote: > On Dec 12, 2017, at 10:24 AM, Simon Slavin wrote: > > > > Santa Clause: SELECT name,hobbies,address FROM people WHERE > behaviour=‘nice’ > > I think you mean > > SELECT name,address > CASE

Re: [sqlite] Can a trigger recursively update a table?

brilliant! - it works - thanks On 14 December 2017 at 19:07, Clemens Ladisch wrote: > Shane Dev wrote: > > On 14 December 2017 at 12:59, Clemens Ladisch > wrote: > >> Shane Dev wrote: > >>> Can we conclude there is no single CTE or other SQL statement

Re: [sqlite] Is this a bug with expression evaluation?

Tony Papadimitriou wrote: > I really don't know what the standard says, but here are two different > opinions in implementation. > > MySQL example: You know that the "SQL" in "MySQL" is actually the abbreviation of "something quite loose"? ;-) Anyway, it appears even MySQL conforms to SQL-92

Re: [sqlite] Is this a bug with expression evaluation?

On 12/14/17, 12:08 PM, "sqlite-users on behalf of Simon Slavin" wrote: > Just to remind you that if something is not documented it can change. The > next version of SQLite might decide that 1 / 2 is 0. So don’t

Re: [sqlite] Is this a bug with expression evaluation?

On 12/14/17, Tony Papadimitriou wrote: > > MySQL example: > mysql> select 1/2; > ++ > | 1/2| > ++ > | 0.5000 | > ++ > 1 row in set (0.13 sec) MySQL is the only database engine that behaves this way. All others do integer arithmetic on integer values.

Re: [sqlite] Is this a bug with expression evaluation?

On 14 Dec 2017, at 5:03pm, Tony Papadimitriou wrote: > SQLite3 (https://sqlite.org/datatype3.html) -- "Otherwise, an expression has > no affinity. " > It seems that 'no affinity' gets translated to integer affinity, then. Just to remind you that if something is not documented

Re: [sqlite] Can a trigger recursively update a table?

Shane Dev wrote: > On 14 December 2017 at 12:59, Clemens Ladisch wrote: >> Shane Dev wrote: >>> Can we conclude there is no single CTE or other SQL statement which can >>> update a branch of the tree starting with a flexibly specified node? >> >> That should be possible when

Re: [sqlite] [EXTERNAL] Is this a bug with expression evaluation?

What you see is not a bug, it’s an annoying heritage of C syntax. Might even precede C. Here’s the problem: select column1*(24/100); And here’s what you’re meant to do for 24%: select column1*(24.0/100.0); Alternatively, the value in column1 should be real. That should also

Re: [sqlite] Is this a bug with expression evaluation?

-Original Message- From: J. King Someone please correct me if I'm wrong, but I believe it's mandated by the SQL standard that integer division is used when both operands are integers. I really don't know what the standard says, but here are two different opinions in implementation.

Re: [sqlite] [EXTERNAL] Is this a bug with expression evaluation?

This is well documented behaviour, see the explanation of affinity. See http://sqlite.org/datatype3.html#affinity If you require floating point arithmetic, you must introduce REAL affinity, either by including a field with storage class REAL, a cast operation or a real literal value

Re: [sqlite] Is this a bug with expression evaluation?

Someone please correct me if I'm wrong, but I believe it's mandated by the SQL standard that integer division is used when both operands are integers. Your synthetic example doesn't use a fixed table, but if it did the easiest solution for you would probably be to define any columns where you

Re: [sqlite] Is this a bug with expression evaluation?

I just multiply by 1.0 Select column1*(column2 * 1.0 / column3)... Removing the parentheses only provide the correct results in your example. It's still using integer math, it's just performing the multiply first, as per order of operations. -Original Message- From: sqlite-users

[sqlite] Is this a bug with expression evaluation?

I’ve noticed this (very annoying) behavior: select column1*(24/100) wrong from (values(100)); Removing the parentheses yields the correct result: select column1*24/100 correct from (values(100)); This obviously behaves like integer math is used and (24/100) gets truncated to zero. If I add a

Re: [sqlite] Can a trigger recursively update a table?

Hi Clemens, With your solution, how would you define the DELETE ON VHIERARCHY trigger? On 14 December 2017 at 12:59, Clemens Ladisch wrote: > Shane Dev wrote: > > Can we conclude there is no single CTE or other SQL statement which can > > update a branch of the tree

Re: [sqlite] DateTime kind stored as undefined

I'm sorry -- the following post was sent to a private e-mail by an accident: Hello, On 2017-12-13 12:51, Michał Niegrzybowski wrote: > I have a table which has a column of type DateTime in my code I insert > there an actual UTC Date (which is not the same as my local time). When I > want to

Re: [sqlite] [EXTERNAL] Re: How to index data based on custom comparisons?

Select from blob_index idx cross join data_table dt on (idx.rowid = dt.rowid) where ; Assuming that the rowid of the blob_index is generated from and identical to the rowid of the data table -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] How to index data based on custom comparisons?

On 14/12/2017 13:14, Richard Hipp wrote: On 12/14/17, Lifepillar wrote: I am not familiar with virtual tables yet, but I see that they are used, for example, to implement Rtree indexes. Would it be feasible to implement my own index structure as a virtual table and

Re: [sqlite] Does sqlite have official development testing tool?

On Thu, Dec 14, 2017 at 4:19 AM, advancenOO wrote: > Hello Richard, > > I hope to run some tests by myself and I think TCL tests in your link are > what I want. > There are so many .tcl and .test in Sqlite source tree. > Could someone share what commands I need to

Re: [sqlite] How to index data based on custom comparisons?

On 13/12/2017 22:20, Simon Slavin wrote: On 13 Dec 2017, at 8:34pm, Lifepillar wrote: But, (correct me if I am wrong), if I index the blob column directly, comparisons are based on memcpy(), which in my case is not what I want. Is it possible to create an index that

Re: [sqlite] Does sqlite have official development testing tool?

On 12/14/17, advancenOO wrote: > Hello Richard, > > I hope to run some tests by myself and I think TCL tests in your link are > what I want. > There are so many .tcl and .test in Sqlite source tree. > Could someone share what commands I need to run to start all TCL

Re: [sqlite] Does sqlite have official development testing tool?

Hello Richard, I hope to run some tests by myself and I think TCL tests in your link are what I want. There are so many .tcl and .test in Sqlite source tree. Could someone share what commands I need to run to start all TCL tests? Thanks. -- Sent from: http://sqlite.1065341.n5.nabble.com/

Re: [sqlite] the timing to truncate WAL file to journal_size_limit

On 12/14/17, advancenOO wrote: > I noticed that, > “The journal_size_limit pragma may be used to limit the size of WAL files > left in the file-system after transactions or checkpoints. Each time a WAL > file resets, SQLite compares the size of the WAL file left in

Re: [sqlite] How to index data based on custom comparisons?

On 12/14/17, Lifepillar wrote: > I am not familiar with virtual tables yet, but I see that they are used, > for example, to implement Rtree indexes. Would it be feasible to > implement my own index structure as a virtual table and use it to index > a blob column in a

Re: [sqlite] Can a trigger recursively update a table?

Shane Dev wrote: > Can we conclude there is no single CTE or other SQL statement which can > update a branch of the tree starting with a flexibly specified node? That should be possible when you enable recursive triggers: begin update hierarchy set status = null where id = old.id;

Re: [sqlite] How to index data based on custom comparisons?

On 14/12/2017 00:02, Keith Medcalf wrote: On Wednesday, 13 December, 2017 13:35, Lifepillar wrote: I am implementing an extension for manipulating IEEE754 decimal numbers. Numbers are stored as blobs using a standard encoding. Numbers that are mathematically equal

[sqlite] the timing to truncate WAL file to journal_size_limit

I noticed that, “The journal_size_limit pragma may be used to limit the size of WAL files left in the file-system after transactions or checkpoints. Each time a WAL file resets, SQLite compares the size of the WAL file left in the file-system to the size limit.” But I think only when the first