Re: [sqlite] [EXTERNAL] Either a bug or I don't understand SQL update

2020-01-30 Thread Hick Gunter
ilinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] Either a bug or I don't understand SQL update I've been using sqlite for some time but haven't used SQL update until recently. On my first real use, I've encountered a problem that I can't understand at all. To pare this down, I have two tables,

[sqlite] odd fts5 create bug

2019-09-18 Thread Wout Mertens
Hi, I don't know how to reproduce this, but I do know how I did it: I created an FTS5 table with the option "tokenizer" instead of "tokenize", and it created the table but not the support tables, and it returned an option error. The command was create VIRTUAL table "{sdb} houses-fts-en" using

Re: [sqlite] Documentation misunderstanding or bug (FTS3 and "^" character)?

2019-08-30 Thread mailing lists
Hi, yes, but unfortunately I have still a project using FTS3 tables to which I have to add new features.. Regards, Hardy > Am 2019-08-30 um 13:00 schrieb Dan Kennedy : > > > On 30/8/62 17:39, mailing lists wrote: >> Hi, >> >> but there is no token '^beta'! Even if in FTS3 '^' is not

Re: [sqlite] Documentation misunderstanding or bug (FTS3 and "^" character)?

2019-08-30 Thread Dan Kennedy
On 30/8/62 17:39, mailing lists wrote: Hi, but there is no token '^beta'! Even if in FTS3 '^' is not regarded as a special character. Is the '^' removed by the tokenizer? Right. It's removed by the tokenizer. FTS3/4 is focused on backwards compatibility. New work should really use FTS5 -

Re: [sqlite] Documentation misunderstanding or bug (FTS3 and "^" character)?

2019-08-30 Thread mailing lists
Hi, but there is no token '^beta'! Even if in FTS3 '^' is not regarded as a special character. Is the '^' removed by the tokenizer? Because it is regarded as a diacritical character? PS: I have to admit that I have overlooked the comment that '^' works only for FTS4 tables. Regards, Hardy >

Re: [sqlite] Documentation misunderstanding or bug (FTS3 and "^" character)?

2019-08-30 Thread Dan Kennedy
The fts3/4 documentation says: "If the FTS table is an FTS4 table (not FTS3), a token may also be prefixed with a "^" character. In this case, in order to match the token must appear as the very first token in any column of the matching row." So change "FTS3" to "FTS4" and it will likely

[sqlite] Documentation misunderstanding or bug (FTS3 and "^" character)?

2019-08-30 Thread mailing lists
Hi, I could not find an example showing a result in the documentation, therefore I created one by myself: CREATE VIRTUAL TABLE myData USING FTS3(content); INSERT INTO myData 'alpha beta'; 1) SELECT * FROM myData WHERE myData MATCH 'beta'; Result: content alpha beta This is what I expected.

Re: [sqlite] divide-by-zero bug in whereLoopAddBtreeIndex function

2019-08-15 Thread Xingwei Lin
Hi, Richard: Sorry for that, the poc is attached here. On Thu, Aug 15, 2019 at 9:08 PM Richard Hipp wrote: > The mailing list strips attachments as an anti-spam measure. Please > send the POC directly d...@sqlite.org. > > On 8/15/19, Xingwei Lin wrote: > > Attach is the poc sql file. > > I

Re: [sqlite] divide-by-zero bug in whereLoopAddBtreeIndex function

2019-08-15 Thread Richard Hipp
The mailing list strips attachments as an anti-spam measure. Please send the POC directly d...@sqlite.org. On 8/15/19, Xingwei Lin wrote: > Attach is the poc sql file. > I used the following command: -- D. Richard Hipp d...@sqlite.org ___

[sqlite] divide-by-zero bug in whereLoopAddBtreeIndex function

2019-08-15 Thread Xingwei Lin
Hi, all I found a divide-by-zero bug in *whereLoopAddBtreeIndex* function in version 3.29.0. The GDB debug traces are: > #0 0x561757f1e842 in whereLoopAddBtreeIndex (pBuilder=0x7ffea0f422a0, > pSrc=0x56175954b070, pProbe=0x561759565e78, nInMul=0) at sqlite3.c:143263 > #1

Re: [sqlite] Is this a bug?

2019-06-18 Thread Roman Fleysher
list Subject: Re: [sqlite] Is this a bug? Your nested select statement wants to return many rows. Technically I think this is illegal SQL (?), but it looks like SQLite is being lax about this as usual and doing what it normally does in this sort of situation: picks a row at random from the inner

Re: [sqlite] Is this a bug?

2019-06-18 Thread Barry
Your nested select statement wants to return many rows. Technically I think this is illegal SQL (?), but it looks like SQLite is being lax about this as usual and doing what it normally does in this sort of situation: picks a row at random from the inner select. Your statement is more or less

[sqlite] Is this a bug?

2019-06-18 Thread Roman Fleysher
Dear SQLiters, I can not figure out what I am doing wrong. In testing, I simplified to the following: CREATE TABLE cosSquared(refVolume INT, vecVolume INT, cosSquared REAL); SELECT refVolume, CAST(10*max(cosSquared) AS INT) FROM cosSquared GROUP BY refVolume; refVolume

Re: [sqlite] [EXTERNAL] Re: sqlite3_reset() bug?

2019-06-05 Thread Hick Gunter
@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] sqlite3_reset() bug? It doesn't look like my attachment worked, so here is the code: #include #include int main (void) { inti; sqlite3_stmt *stmt = NULL; sqlite3

Re: [sqlite] sqlite3Init null pointer bug

2019-01-04 Thread Richard Hipp
This is an issue with the CLI, not with the SQLite core. It is fixed here: https://www.sqlite.org/src/info/d7f55c62c3fa053b On 1/4/19, Xingwei Lin wrote: > Hi, > > I found a null pointer access problem in *sqlite3Init *function in version > 3.26.0. > > The poc is simple: > >> .open . > >

[sqlite] sqlite3Init null pointer bug

2019-01-04 Thread Xingwei Lin
Hi, I found a null pointer access problem in *sqlite3Init *function in version 3.26.0. The poc is simple: > .open . .selftest The GDB debug traces are: > #0 0x55d76316ae12 in sqlite3Init (db=0x55d76509de18, > pzErrMsg=0x7fffa3d64ac0) at sqlite3.c:123082 #1 0x55d7631a22d7 in

Re: [sqlite] Default Values Pragma bug

2018-08-06 Thread E.Pasma
Hello Ryan, Your already moderate complaint needs further moderation After reading https://www.sqlite.org/lang_createtable.html#dfltval I see that the default value may be a function name (when written inside parenthesis) or a special

[sqlite] Default Values Pragma bug

2018-08-05 Thread R Smith
This is a small niggle, perhaps not worthy of the term "bug". The Default values when specified in a CREATE TABLE statement will include C-style commented text in the output of the pragma table_info() (or its t.v.f. derivative) while the actual default value handling will parse it out.

Re: [sqlite] [EXTERNAL] UNION ALL bug in Multi-threading

2018-08-01 Thread Hick Gunter
] Im Auftrag von Dominique Devienne Gesendet: Mittwoch, 01. August 2018 11:34 An: General Discussion of SQLite Database Betreff: Re: [sqlite] [EXTERNAL] UNION ALL bug in Multi-threading On Wed, Aug 1, 2018 at 10:39 AM Hick Gunter wrote: > Are you sure it is not the special case documented

Re: [sqlite] [EXTERNAL] UNION ALL bug in Multi-threading

2018-08-01 Thread Dominique Devienne
On Wed, Aug 1, 2018 at 10:39 AM Hick Gunter wrote: > Are you sure it is not the special case documented in the ATTACH command? > (see https://sqlite.org/lang_attach.html) > Good point. OP will tell us if it applies to his/her case. > " Transactions involving multiple attached databases are

Re: [sqlite] [EXTERNAL] UNION ALL bug in Multi-threading

2018-08-01 Thread Hick Gunter
hanges where others might not." -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von sanhua.zh Gesendet: Mittwoch, 01. August 2018 09:57 An: sqlite-users ; drh Betreff: [EXTERNAL] [sqlite] UNION ALL bug in Multi-threading I find a bug that

Re: [sqlite] Is it a bug or "as designed"?

2018-03-09 Thread Simon Slavin
On 10 Mar 2018, at 7:15am, John Found wrote: > Simon Slavin wrote: > >> On 9 Mar 2018, at 7:49pm, John Found wrote: >> >>> In the current implementation "insert or replace" behave as the foreign >>> constraint is deferred.

Re: [sqlite] Is it a bug or "as designed"?

2018-03-09 Thread John Found
On Sat, 10 Mar 2018 01:17:38 + Simon Slavin wrote: > On 9 Mar 2018, at 7:49pm, John Found wrote: > > > In the current implementation "insert or replace" behave as the foreign > > constraint is deferred. > > But according to documentation, all

Re: [sqlite] Is it a bug or "as designed"?

2018-03-09 Thread Simon Slavin
On 9 Mar 2018, at 7:49pm, John Found wrote: > In the current implementation "insert or replace" behave as the foreign > constraint is deferred. > But according to documentation, all foreign constraints in SQLite are > immediate by default. John, The documentation

Re: [sqlite] Is it a bug or "as designed"?

2018-03-09 Thread R Smith
On 2018/03/09 9:49 PM, John Found wrote: On Fri, 9 Mar 2018 19:42:19 + Simon Slavin wrote: You are right. And Jay Kreibich in his post above. But then the second solution from my post should be the correct behavior. In the current implementation "insert or replace"

Re: [sqlite] Is it a bug or "as designed"?

2018-03-09 Thread Jay Kreibich
> On Mar 9, 2018, at 1:42 PM, Simon Slavin wrote: > > "replace" means "delete the original row, then insert a new one”. More properly, it means “delete any and all rows that might cause any conflict with inserting the new row.” There really isn’t a concept of an

Re: [sqlite] Is it a bug or "as designed"?

2018-03-09 Thread John Found
On Fri, 9 Mar 2018 19:42:19 + Simon Slavin wrote: > On 9 Mar 2018, at 7:11pm, John Found wrote: > > > "insert or replace" succeed without deleting the old rows from B. > > "replace" means "delete the original row, then insert a new one". > > In

Re: [sqlite] Is it a bug or "as designed"?

2018-03-09 Thread Simon Slavin
On 9 Mar 2018, at 7:11pm, John Found wrote: > "insert or replace" succeed without deleting the old rows from B. "replace" means "delete the original row, then insert a new one". In your code, figure out whether you need INSERT or UPDATE, and do the appropriate one.

Re: [sqlite] Is it a bug or "as designed"?

2018-03-09 Thread Jay Kreibich
Foreign keys enforcement can get tricky depending on the enforcement policy, transactions, and a lot of things. I don’t have enough experience to comment on that fully. I will say this, however, because it is a common mistake with a lot of different aspects of database behavior:

[sqlite] Is it a bug or "as designed"?

2018-03-09 Thread John Found
I have two tables with foreign constraint: create table A ( id primary key not null, single_data ); create table B ( aid references A(id) on delete cascade, multi_data); Now I am periodically inserting data in A and B with the following queries: insert or replace into A values (?1,

Re: [sqlite] Minor JSON output bug (infinity)

2018-01-22 Thread J Decker
On Mon, Jan 22, 2018 at 6:00 PM, Alexander Beedie < alexander.m.bee...@gmail.com> wrote: > Test-case / repro: > “SELECT JSON_ARRAY(1e,-1e,NULL)” > > Actual output: > ‘[Inf,-Inf,null]’ > > Expected output: > ‘[Infinity,-Infinity,null]’ > > All JSON parsers I have tried fail on “Inf”, but

Re: [sqlite] Minor JSON output bug (infinity)

2018-01-22 Thread J. King
RFC 8259 states: > Numeric values that cannot be represented in the grammar below (such as > Infinity and NaN) are not permitted. This is one of the cases that expose the fallacy of the "JS" part of "JSON". While SQLite should not be producing Inf as a bare word, it should not be producing

Re: [sqlite] Minor JSON output bug (infinity)

2018-01-22 Thread Richard Hipp
On 1/22/18, Alexander Beedie wrote: > Test-case / repro: > “SELECT JSON_ARRAY(1e,-1e,NULL)” > > Actual output: > ‘[Inf,-Inf,null]’ > > Expected output: > ‘[Infinity,-Infinity,null]’ > > All JSON parsers I have tried fail on “Inf”, but the majority will

[sqlite] Minor JSON output bug (infinity)

2018-01-22 Thread Alexander Beedie
Test-case / repro: “SELECT JSON_ARRAY(1e,-1e,NULL)” Actual output: ‘[Inf,-Inf,null]’ Expected output: ‘[Infinity,-Infinity,null]’ All JSON parsers I have tried fail on “Inf”, but the majority will succeed with “Infinity” (as this is the standard JS property name) eg: in standard

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

2018-01-02 Thread Cezary H. Noweta
Hello, On 2018-01-03 01:44, Cezary H. Noweta wrote: MySQL has a separator specified by a distinct clause. I'm sorry -- I meant ``distinct'' == ``separate/different'' (a clause named ``SEPARATOR''). Not to be confused with ``DISTINCT'' clause in SQLite's ``group_concat(DISTINCT...)''. --

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

2018-01-02 Thread petern
Scott. I almost forgot about this example. Consider group_concat's orthogonal function group_replace. I adapted this from SQLite replace and group_concat. The author disclaims all rights to the following code: --- struct StrRepl { const unsigned char* zStr; u32 nStr; u8

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

2018-01-02 Thread Scott Robison
On Tue, Jan 2, 2018 at 5:46 PM, petern wrote: > Hi Scott. > >>Are there other aggregate functions that take multiple arguments? > > Absolutely. I've got a few in my code which deserialize table rows into > runtime objects. Fortunately, the DISTINCT filter makes no

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

2018-01-02 Thread petern
Hi Scott. >Are there other aggregate functions that take multiple arguments? Absolutely. I've got a few in my code which deserialize table rows into runtime objects. Fortunately, the DISTINCT filter makes no sense in that use case, so I didn't bump into this issue myself. If you're looking

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

2018-01-02 Thread Cezary H. Noweta
Hello, On 2018-01-03 00:02, Tony Papadimitriou wrote: MySQL does not seem to have a problem with it. MySQL has a separator specified by a distinct clause. In SQLite it is specified by a second expression, which, in a canonical and intuitive point of view, is a constant string. However it

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

2018-01-02 Thread Scott Robison
On Tue, Jan 2, 2018 at 4:15 PM, petern wrote: > Hi Tony. Good. Yes, simpler test case is always better when posting > possible bugs. > > Unfortunately, as Cezary points out, this error is by design (from > select.c): > >if( pFunc->iDistinct>=0 ){ > Expr *pE

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

2018-01-02 Thread petern
Hi Tony. Good. Yes, simpler test case is always better when posting possible bugs. Unfortunately, as Cezary points out, this error is by design (from select.c): if( pFunc->iDistinct>=0 ){ Expr *pE = pFunc->pExpr; assert( !ExprHasProperty(pE, EP_xIsSelect) ); if(

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

2018-01-02 Thread Tony Papadimitriou
MySQL does not seem to have a problem with it. -Original Message- From: Scott Robison On Tue, Jan 2, 2018 at 1:36 PM, Tony Papadimitriou wrote: create table t(s); insert into t values ('A'),('A'),('B'); select group_concat(s,', ') from t group by null; --

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

2018-01-02 Thread Tony Papadimitriou
Even simpler, then... select group_concat(distinct 1,','); -Original Message- From: petern Simpler one line test case also parses incorrectly: WITH t(c) AS (VALUES (1)) SELECT group_concat(DISTINCT c) FROM t; "group_concat(DISTINCT c)" 1 WITH t(c) AS (VALUES (1)) SELECT

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

2018-01-02 Thread Cezary H. Noweta
Hello, On 2018-01-02 22:39, Scott Robison wrote: On Tue, Jan 2, 2018 at 1:36 PM, Tony Papadimitriou wrote: create table t(s); insert into t values ('A'),('A'),('B'); select group_concat(s,', ') from t group by null; -- OK select group_concat(distinct s) from t group

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

2018-01-02 Thread petern
Simpler one line test case also parses incorrectly: WITH t(c) AS (VALUES (1)) SELECT group_concat(DISTINCT c) FROM t; "group_concat(DISTINCT c)" 1 WITH t(c) AS (VALUES (1)) SELECT group_concat(DISTINCT c,',') FROM t; Error: DISTINCT aggregates must have exactly one argument On Tue, Jan 2, 2018

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

2018-01-02 Thread Scott Robison
On Tue, Jan 2, 2018 at 1:36 PM, Tony Papadimitriou wrote: > create table t(s); > insert into t values ('A'),('A'),('B'); > > select group_concat(s,', ') from t group by null; -- OK > select group_concat(distinct s) from t group by null; -- OK > select

[sqlite] GROUP_CONCAT with DISTINCT bug

2018-01-02 Thread Tony Papadimitriou
create table t(s); insert into t values ('A'),('A'),('B'); select group_concat(s,', ') from t group by null; -- OK select group_concat(distinct s) from t group by null; -- OK select group_concat(distinct s,', ') from t group by null; -- ERROR -- The moment the optional delimiter

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Radovan Antloga
Thank you Peter! I like sqlite so much and I think we all benefit if errors are fixed. I see I was not very clear with my first post. Will do better next time. Yes would be nice if people would try to understand first not just think on title text. Talking about sqlite I use it for web

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread petern
Radovan. Thank you for sticking to your guns. Your appeal to expected behavior under other DB engines was also a very good post. I see the usual suspects of the echo chamber uselessly piled on against you to clog the forum nevertheless. That happens too frequently. BTW, here is a simpler test

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Radovan Antloga
Thank you Klaus! Klaus Maas je 22.12.2017 ob 20:30 napisal: Radovan is correct. Executing the same command sequence in version 3.11.0 and 3.21.0 results in different column names for table test2. (Xubuntu 16.04 with sqlite3.11.0 and sqlite3.21.0) I marked the results with '<=' Klaus

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Radovan Antloga
THANK YOU! Richard Hipp je 22.12.2017 ob 20:29 napisal: Your trouble ticket is here: https://sqlite.org/src/tktview/3b4450072511 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Klaus Maas
Radovan is correct. Executing the same command sequence in version 3.11.0 and 3.21.0 results in different column names for table test2. (Xubuntu 16.04 with sqlite3.11.0 and sqlite3.21.0) I marked the results with '<=' Klaus SQLite version 3.11.0 2016-02-15 17:29:24 Enter ".help" for usage

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Richard Hipp
Your trouble ticket is here: https://sqlite.org/src/tktview/3b4450072511 -- 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] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Radovan Antloga
Just try this sql-s: create table test(a int, b int); insert into test values (1,1); select d from (select c as d from (select a as c from test)); will return name d. create table test2 as select d from (select c as d from (select a as c from test)); will create table test2 with column name

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Radovan Antloga
, 2017 1:45 PM To: SQLite mailing list Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0 On 12/22/17, Radovan Antloga <radovan.antl...@siol.net> wrote: In my example I have AS clause so rule 1. You did not understand my question. I think what you are asking for is t

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Richard Hipp
On 12/22/17, Radovan Antloga wrote: > My point is you do not have to change anything regards > to how select works. SELECT statement is working just > great or OK or fine. > > CREATE TABLE AS > SELECT > > does not give same name as SELECT does. > > SELECT give name

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread David Raymond
Correct. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Friday, December 22, 2017 1:45 PM To: SQLite mailing list Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0 On 12/22/17, Radovan

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Radovan Antloga
My point is you do not have to change anything regards to how select works. SELECT statement is working just great or OK or fine. CREATE TABLE AS SELECT does not give same name as SELECT does. SELECT give name d CREATE TABLE AS SELECT create table with name a instead of d. Richard Hipp

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Richard Hipp
On 12/22/17, Radovan Antloga wrote: > In my example I have AS clause so rule 1. You did not understand my question. I think what you are asking for is that we should enhance rule 1 so that it applies even if the AS clause is contained within a subquery. -- D. Richard

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Radovan Antloga
In my example I have AS clause so rule 1. sqlite select statement is correct or name is correct. sqlite create table as statement create table with different name that select statement return Problem is different result or name. Richard Hipp je 22.12.2017 ob 19:27 napisal: These are the

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Richard Hipp
These are the rules that SQLite uses to name a column in the result set: (1) If there is an AS clause, use it. (2) If the result-set value comes from a table column (even indirectly, such as through a subquery, but not if the value is altered by an expression) then use the name of the column as

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Radovan Antloga
Just tried my example with PostgreSQL that have create table as statement. It work as expected. It creates table test2 with column name d. sqlite3 creates table with column name a. sqlite3 try to mimic postgresql but in this example is not. Simon Slavin je 22.12.2017 ob 17:58 napisal: On 22

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Radovan Antloga
What? I have to write select d as d. You are not serious. You dont understand what I wrote. I get select correct but create table as does not have same name as select has. Why different result? If select gives some name I don't care what I expect create table as give me same name. Is this so

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Radovan Antloga
t. I definitely don't want to have to do queries in the form of "select foo as foo, bar as bar, baz as baz..." -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Friday, December 22, 2017 11:34 AM To: SQLite mailing

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread David Raymond
ite.org] On Behalf Of Simon Slavin Sent: Friday, December 22, 2017 11:34 AM To: SQLite mailing list Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0 On 22 Dec 2017, at 3:04pm, Tim Streater <t...@clothears.org.uk> wrote: > 2) I don't see this issue mentioned

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Simon Slavin
On 22 Dec 2017, at 4:50pm, Radovan Antloga wrote: > select d from (select c AS d from (select a AS c from test)); > > I get d as column name. If I create table with > create table as I get a as column name. As I wrote, you did not specify AS for d, so you cannot

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Radovan Antloga
Sorry but this is not related to my example. I have AS in inner select. My select return name as specified. Problem I have is with create table as where name is changed. I give example like this: select d from (select c AS d from (select a AS c from test)); I get d as column name. If I create

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Simon Slavin
On 22 Dec 2017, at 3:04pm, Tim Streater wrote: > 2) I don't see this issue mentioned when I read the PHP documentation about > their SQLite interface, nor do I see it in the Xojo docs about *their* > interface either. I assume their interfaces are not rewriting SELECT

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-22 Thread Tim Streater
On 22 Dec 2017, at 09:57, Hick Gunter wrote: > The problem lies in your reliance on unspecified behaviour. Unspecified > behaviour is allowed to change. > > I am sure you have read (and ignored) the following guarantee taken from > http://sqlite.org/c3ref/column_name.html : My

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-21 Thread Radovan Antloga
Behaviour is not consistent when using create table as or just select statement. Try this simple test. create table test(a int, b int); insert into test values (1, 1); select d from (select c as d from (select a as c from test)); you get column name d as expected but when you have create table

Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-21 Thread Richard Hipp
The behavior change is a bug fix. See http://sqlite.org/src/info/de3403bf5ae for details. On 12/21/17, Radovan Antloga wrote: > I have table (create statement): > > CREATE TABLE SOPP1 ( >STAT varchar(1) collate systemnocase, >RID varchar(2) collate

[sqlite] Changed behaviour or bug using field alias in 3.21.0

2017-12-21 Thread Radovan Antloga
I have table (create statement): CREATE TABLE SOPP1 (   STAT  varchar(1) collate systemnocase,   RID  varchar(2) collate systemnocase,   VP  integer,   BLANK  varchar(6) collate systemnocase,   NAZIV  varchar(24) collate systemnocase,   KN  varchar(12) collate systemnocase,   A  varchar(1)

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

2017-12-14 Thread Clemens Ladisch
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?

2017-12-14 Thread Peter Da Silva
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?

2017-12-14 Thread Richard Hipp
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?

2017-12-14 Thread Simon Slavin
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] Is this a bug with expression evaluation?

2017-12-14 Thread Tony Papadimitriou
-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] Is this a bug with expression evaluation?

2017-12-14 Thread 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. 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?

2017-12-14 Thread Marc L. Allen
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Tony Papadimitriou Sent: Thursday, December 14, 2017 11:36 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Is this a bug with expression evaluation? I’ve noticed this (very annoying) behavior: select column1*(24/100

[sqlite] Is this a bug with expression evaluation?

2017-12-14 Thread Tony Papadimitriou
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] Possible thread-safety bug in lemon parser with ParseTrace()

2017-09-07 Thread Richard Hipp
On 9/7/17, Fletcher T. Penney wrote: > 1) I hope I am reporting this to the right place. If not, my apologies. > > 2) I have been using lemon parsers for a year or more, but am by no > means an export on the lemon source itself. I did not see this issue >

[sqlite] Possible thread-safety bug in lemon parser with ParseTrace()

2017-09-07 Thread Fletcher T. Penney
1) I hope I am reporting this to the right place. If not, my apologies. 2) I have been using lemon parsers for a year or more, but am by no means an export on the lemon source itself. I did not see this issue referenced elsewhere, my apologies if I missed it. I *think* there is a

Re: [sqlite] [EXTERNAL] Re: SQLITE bug

2017-09-04 Thread Richard Damon
linglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] SQLITE bug On 9/3/17 10:16 AM, Joseph L. Casale wrote: -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Sunday, September 3, 2017 7:51 AM To: sqlite-users@mailinglists.sq

Re: [sqlite] [EXTERNAL] Re: SQLITE bug

2017-09-04 Thread Hick Gunter
:13 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] SQLITE bug On 9/3/17 10:16 AM, Joseph L. Casale wrote: > -Original Message- > From: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R > Smith > Sent: Sunday, September

Re: [sqlite] 3.19.3 README.md Doc bug

2017-06-14 Thread petern
I see. Replacing sqlite.tar.gz by sqlite-autoconf-3190300.tar.gz likewise produces the immediate directory 'sqlite-autoconf-3190300' rather than 'sqlite'. Taken literally, the instructions are indeed correct. I presumed, evidently incorrectly, that it would be preferrable to start in the

Re: [sqlite] 3.19.3 README.md Doc bug

2017-06-14 Thread John McKown
On Wed, Jun 14, 2017 at 10:40 AM, petern wrote: > Was there a version in the past where the compile instructions made sense? > > tar xzf sqlite.tar.gz;# Unpack the source tree into "sqlite" > mkdir bld;# Build will occur in a

[sqlite] 3.19.3 README.md Doc bug

2017-06-14 Thread petern
Was there a version in the past where the compile instructions made sense? tar xzf sqlite.tar.gz;# Unpack the source tree into "sqlite" mkdir bld;# Build will occur in a sibling directory cd bld ;# Change to the build directory

Re: [sqlite] tclsqlite 64-bit bug [Was: extension to run bash]

2017-01-12 Thread Hick Gunter
Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jan Nijtmans Gesendet: Donnerstag, 12. Jänner 2017 10:43 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [sqlite] tclsqlite 64-bit bug [Was: extension to run bash] 2017-0

[sqlite] tclsqlite 64-bit bug [Was: extension to run bash]

2017-01-12 Thread Jan Nijtmans
2017-01-12 3:04 GMT+01:00 Warren Young: > In fact, one improvement made to SQLite a few years ago was to switch it from > using native Windows file locking when built under Cygwin to use POSIX or BSD > locking mechanisms, so that two programs built under Cygwin that both used > SQLite would get

[sqlite] 3.16.0 Release notes bug

2017-01-03 Thread jose isaias cabrera
Happy new 2017 to everyone on this list. Once again, I want to thank Dr. Hipp for his wonderful provision to the world called SQLite. Anyway, in the release notes, https://www.sqlite.org/releaselog/3_16_0.html on the first bullet, between the parenthesis, it states: (See the CPU

Re: [sqlite] A possible double bug?

2016-10-17 Thread Jens Alfke
> On Oct 17, 2016, at 2:12 AM, Quan Yong Zhai wrote: > > Or after prepare “ INSERT INTO test VALUES(?) “ > Bind_text “62.027393” > Bind_double 62.027393 > > In all the four situation, the value insert into foo field is binary > identical, it’s a 8-bytes REAL value.

Re: [sqlite] A possible double bug?

2016-10-17 Thread David Raymond
(62.027393, 'real') or is one getting a real type and one getting a text type? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Quan Yong Zhai Sent: Monday, October 17, 2016 5:13 AM To: Keith Medcalf; SQLite mailing list Subject: R

Re: [sqlite] A possible double bug?

2016-10-17 Thread Bernardo Sulzbach
On 10/17/2016 08:29 AM, Quan Yong Zhai wrote: "The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. Quote: "In your machine, implementation, and SQLite installation the two value

Re: [sqlite] A possible double bug?

2016-10-17 Thread Quan Yong Zhai
in others it may not be." Does it mean the promise about cross-platform is broken? 发件人: Bernardo Sulzbach<mailto:mafagafogiga...@gmail.com> 发送时间: ‎2016/‎10/‎17 17:19 收件人: sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sql

Re: [sqlite] A possible double bug?

2016-10-17 Thread Bernardo Sulzbach
On 10/17/2016 07:12 AM, Quan Yong Zhai wrote: I can’t reproduce the problem, As it has already been pointed out, this is normal for floating point arithmetic. In your machine, implementation, and SQLite installation the two value representations may be identical, while in others it may not

Re: [sqlite] A possible double bug?

2016-10-17 Thread Quan Yong Zhai
I can’t reproduce the problem, http://sqlite.org/datatype3.html#type_affinity “When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible” So after “create table test

Re: [sqlite] A possible double bug?

2016-10-17 Thread Jens Alfke
I’d say the mistake here is converting a double to a string before inserting it into the database. Any time you convert between floating point and decimal (or vice versa) you can lose accuracy, and are not guaranteed round-trip fidelity. (0.1, 0.01, 0.001, etc. do not have finite-length exact

Re: [sqlite] A possible double bug?

2016-10-16 Thread Keith Medcalf
On Sunday, 16 October, 2016 12:03, Victor Evertsson wrote: > I was wondering about the different behavior of inserting a Double as a > String vs as a value with a prepare statement in C. > Consider an example when the value: 62.027393 is inserted as a String and >

[sqlite] A possible double bug?

2016-10-16 Thread Victor Evertsson
Hi, I was wondering about the different behavior of inserting a Double as a String vs as a value with a prepare statement in C. Consider an example when the value: 62.027393 is inserted as a String and as value with a prepared statement, for instance: "CREATE TABLE test (foo REAL)" "INSERT

Re: [sqlite] Strange problem (pehaps bug) with sqlite + json

2016-09-23 Thread Dominique Devienne
On Fri, Sep 23, 2016 at 11:58 AM, Hick Gunter wrote: > SQLite evaluates the WHERE clause from left to right, which means it needs > to evaluate xyz and thereby call json_extract first, even before it can > determine that no rows match the second condition. > > Maybe the

  1   2   3   4   >