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

[Hick Gunter]

You have fallen into the double quote trap.

SQLite uses double quotes to denote COLUMN NAMES, and single quotes to delimit 
STRING CONSTANTS.

When asking for "M" or "G", you get the contents of the column named m and g 
respectively (column names are case insensitive).

When asking for "P" or "R", there is no column with that name, so SQLite 
pretends that you meant to provide a string constant.

BTW: You have columns with NUMERIC affinity obviously representing amounts, 
which will get stored as REAL numbers. Don't do that if you expect to do 
anything financially relevant. You will incur rounding errors. Instead, store 
integer values of cents.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Paul Ausbeck
Gesendet: Donnerstag, 30. Januar 2020 00:11
An: sqlite-users@mailinglists.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, citytax and taxitems2, and I've attached 
a database with just these two tables, total size ~12Kbytes.

I perform the following update using the sqlite3 command line interface:

update citytax set salesm = (select sum(amount) from taxitems2 where 
taxitems2.citynum = citytax.number and taxitems2.taxrate = "M");

The result is that the salesm  column is set to NULL for all citytax rows. 
However, I would expect that rows 9 and 22 would be non-NULL. I get the the 
same all-NULL result for a taxrate of "G" as well, though I would expect that 
rows 14, 19, and 58 would be non-NULL.

The strangest part is that if I specify taxrates of "P", "R", or "", I get the 
result that I expect. I've included a listing of the taxitems2 table below. As 
one can see, there are rows where the second column, taxrate, is all of "", 
"P", "R", "G", and "M". The last column is the amount column, non-null for all 
rows. I must admit that I don't understand what is going on here.

Perhaps even stranger is if I extract the select from the update and give it an 
appropriate row number, I get the expected non-NULL result:

sqlite> select sum(amount) from taxitems2 where taxitems2.citynum = 9 and 
taxitems2.taxrate = "M";
1176.72

I've included the contents of the taxitems2 table as well as the schema for 
tables taxitems2 and citytax below. One the one hand, this seems almost 
certainly to be a bug, but on the other, it is so basic, that I can't believe 
that I'm the first to encounter it.

Any assistance that anyone can provide would of course be much appreciated. I'm 
using sqlite 3.30 under Windows 7 cygwin and 3.27 under Debian linux, both 
environments behave the same in this regard.

As I was writing I had yet another test idea:

update citytax set salesm = (select sum(amount) from taxitems2 where 
taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 77); /* 
taxrate = "M" */

and

update citytax set salesm = (select sum(amount) from taxitems2 where 
taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 71); /* 
taxrate = "G" */

Both of these updates perform as expected.

Regards,

Paul Ausbeck

sqlite> select * from taxitems2;
WAL,,68,10,2,1,4,0.75,2,1.5,4,142.5
SUM,,34,36,2,1,3.5,0.75,2,1.5,4,0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
USX,P,58,14,1,0,0,0,0,0,0,1133.0
DNT,,9,10,2,2,4,0.75,2,1.5,4,206.0
USX,P,58,14,1,0,0,0,0,0,0,7104.0
TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945
TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945
TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978
TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978
USX,P,58,14,1,0,0,0,0,0,0,5446.4
USX,P,58,14,1,0,0,0,0,0,0,1657.6
AAM,G,58,36,2,0,0,0.75,2,1.5,4,34.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,107.6
PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,6.3
PEM,G,58,10,2,0,0,0.75,2,1.5,4,8
PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4
HVY,R,58,14,1,0,0,0,0,0,0,72.5
HVY,R,58,14,1,0,0,0,0,0,0,176.4
VEI,G,14,10,2,1,4,0.75,2,1.5,4,134.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4
PEM,G,58,10,2,0,0,0.75,2,1.5,4,168
CSH,G,19,10,2,2,4,0.75,2,1.5,4,33.2
CSH,G,19,10,2,2,4,0.75,2,1.5,4,25.0
WAT,M,9,10,2,2,4,0.75,2,1.5,4,9.5
WAT,M,9,10,2,2,4,0.75,2,1.5,4,65
WAT,M,9,10,2,2,4,0.75,2,1.5,4,125.0
WAT,M,9,10,2,2,4,0.75,2,1.5,4,43.9
WAT,M,9,10,2,2,4,0.75,2,1.5,4,107.4
WAT,M,9,10,2,2,4,0.75,2,1.5,4,46.72
WAT,M,9,10,2,2,4,0.75,2,1.5,4,52.5
WAT,M,9,10,2,2,4,0.75,2,1.5,4,12.5
WAT,M,9,10,2,2,4,0.75,2,1.5,4,63.3
ICM,M,22,10,2,1,4,0.75,2,1.5,4,120.2000394
DNT,M,9,10,2,2,4,0.75,2,1.5,4,334.4
DNT,M,9,10,2,2,4,0.75,2,1.5,4,155.8
DNT,M,9,10,2,2,4,0.75,2,1.5,4,160.7

sqlite> .schema taxitems2
CREATE TABLE taxitems2(
   code TEXT,
   taxrate TEXT,
   citynum INT,
   countynum INT,
   statenum INT,
   citym NUM,
   cityg NUM,
   countym 

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

[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 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 
> - it's faster, has more features, is better tested and the query syntax is 
> properly defined.
> 
> https://sqlite.org/fts5.html
> 
> Dan.
> 
> 
> 
>> 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
>> 
>>> Am 2019-08-30 um 12:24 schrieb 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 work.
>>> 
>>> Dan.
>>> 
>>> 
>>> On 30/8/62 16:31, mailing lists wrote:
 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.
 
 2)
 SELECT * FROM myData WHERE myData MATCH '^beta';
 
 Result:
 
 content
 alpha beta
 
 This is what I did not expect. According to the documentation I expected 
 to get no rows because the column begins with alpha and not with beta.
 
 What's the issue here? I tested these examples using version 3.24.0.
 
 Regards,
 Hardy
 
 ___
 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
> ___
> 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

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

[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 - it's faster, has more features, is better tested and the query 
syntax is properly defined.


https://sqlite.org/fts5.html

Dan.




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


Am 2019-08-30 um 12:24 schrieb 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 work.

Dan.


On 30/8/62 16:31, mailing lists wrote:

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.

2)
SELECT * FROM myData WHERE myData MATCH '^beta';

Result:

content
alpha beta

This is what I did not expect. According to the documentation I expected to get 
no rows because the column begins with alpha and not with beta.

What's the issue here? I tested these examples using version 3.24.0.

Regards,
Hardy

___
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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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

> Am 2019-08-30 um 12:24 schrieb 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 work.
> 
> Dan.
> 
> 
> On 30/8/62 16:31, mailing lists wrote:
>> 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.
>> 
>> 2)
>> SELECT * FROM myData WHERE myData MATCH '^beta';
>> 
>> Result:
>> 
>> content
>> alpha beta
>> 
>> This is what I did not expect. According to the documentation I expected to 
>> get no rows because the column begins with alpha and not with beta.
>> 
>> What's the issue here? I tested these examples using version 3.24.0.
>> 
>> Regards,
>> Hardy
>> 
>> ___
>> 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

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

[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 work.

Dan.


On 30/8/62 16:31, mailing lists wrote:

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.

2)
SELECT * FROM myData WHERE myData MATCH '^beta';

Result:

content
alpha beta

This is what I did not expect. According to the documentation I expected to get 
no rows because the column begins with alpha and not with beta.

What's the issue here? I tested these examples using version 3.24.0.

Regards,
Hardy

___
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

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

[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 used the following command:
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>


-- 
Best regards，
Xingwei Lin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug?

[Roman Fleysher]

Oh, Thank you Barry.

I am glad it is not a bug. Bug is in my head.

Thank you,

Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Barry [smith.bar...@gmail.com]
Sent: Tuesday, June 18, 2019 7:59 PM
To: SQLite mailing 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 select.

Your statement is more or less equivalent to:
SELECT DISTINCT refVolume FROM cosSquered WHERE (refVolumn
CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM cosSquared LIMIT 1)

The statement that will output the same same list of refVolumes is:
SELECT DISTINCT refVolume FROM cosSquared WHERE CAST(10 * cosSquared AS
INT) = 9

On Tue, 18 Jun 2019 at 16:44, Roman Fleysher 
wrote:

> 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   CAST(10*max(cosSquared) AS INT)
> --  ---
> 2   9
> 3   9
> 4   9
> 5   9
> .
> 31  9
> 32  9
> 33  9
>
> That is, we see that for refVolumes between 2 and 33, the value of the
> CAST() is always 9. Thus, I expect the following statement to output the
> same list of refVolumes. But it does not:
>
> SELECT DISTINCT refVolume FROM cosSquared
>  WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM
> cosSquared);
>
> refVolume
> --
> 2
>
> What am I doing wrong? I am using version 3.16.
>
> Thank you for your help,
>
> Roman
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f35bb6726c3413690d208d6f4490695%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636964991807478228sdata=AJjpaa48jiKBcZI3eSsNRapqcBp%2FywsQ%2BWMoYCo1jOw%3Dreserved=0
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f35bb6726c3413690d208d6f4490695%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636964991807478228sdata=AJjpaa48jiKBcZI3eSsNRapqcBp%2FywsQ%2BWMoYCo1jOw%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug?

[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 equivalent to:
SELECT DISTINCT refVolume FROM cosSquered WHERE (refVolumn
CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM cosSquared LIMIT 1)

The statement that will output the same same list of refVolumes is:
SELECT DISTINCT refVolume FROM cosSquared WHERE CAST(10 * cosSquared AS
INT) = 9

On Tue, 18 Jun 2019 at 16:44, Roman Fleysher 
wrote:

> 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   CAST(10*max(cosSquared) AS INT)
> --  ---
> 2   9
> 3   9
> 4   9
> 5   9
> .
> 31  9
> 32  9
> 33  9
>
> That is, we see that for refVolumes between 2 and 33, the value of the
> CAST() is always 9. Thus, I expect the following statement to output the
> same list of refVolumes. But it does not:
>
> SELECT DISTINCT refVolume FROM cosSquared
>  WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM
> cosSquared);
>
> refVolume
> --
> 2
>
> What am I doing wrong? I am using version 3.16.
>
> Thank you for your help,
>
> Roman
>
> ___
> 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

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

[Hick Gunter]

The sqlite3_reset() call is used to reset the statement AFTER having called 
sqlite3_step().

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Josef Barnes
Gesendet: Mittwoch, 05. Juni 2019 15:07
An: sqlite-users@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   *db = NULL;

int vals[] = { 4, 5, 6 };

sqlite3_open_v2("test.db", , SQLITE_OPEN_READONLY, NULL);
sqlite3_prepare_v2(db, "SELECT id, value FROM test WHERE value = ?", -1, 
, NULL);

for (i = 0; i < 3; i++) {
   sqlite3_bind_int64(stmt, 1, vals[i]);
   printf("BOUND %d\n", vals[i]);
   sqlite3_reset(stmt);
   while (sqlite3_step(stmt) == SQLITE_ROW) {
  printf("FOUND %lld,%lld\n", sqlite3_column_int64(stmt, 0), 
sqlite3_column_int64(stmt, 1));
   }
}

sqlite3_finalize(stmt);
sqlite3_close_v2(db);

return 0;
}



On 5/6/19 11:04 pm, Josef Barnes wrote:
> Hi,
>
> We've come across a situation where we think there is a bug in the
> sqlite3_reset() function. The documentation states:
>
> "Any SQL statement variables that had values bound to them using the
> sqlite3_bind_*() API retain their values"
>
> The behaviour we are seeing appears to contradict this statement. I've
> attached a very simple example of searching for a few rows in a
> database. To run the example, create a database (test.db) with the
> following schema:
>
> CREATE table test ( id INTEGER PRIMARY KEY, value INTEGER NOT NULL );
> INSERT INTO test VALUES ( 1, 4 ); INSERT INTO test VALUES ( 2, 5 );
> INSERT INTO test VALUES ( 3, 6 );
>
> In the example code, notice that the call to sqlite3_reset() comes
> after the call to sqlite3_bind_int64(). When running the example, it
> will return the first row all three times. It seems that the call to
> sqlite3_reset() actually resets the binded variable to the value it
> had at the last call to sqlite3_step().
>
> Is this a bug? Or is it intended behaviour? If it's intended, I
> recommend updating the documentation to be clear about this behaviour.
>
> Thanks for any insight anyone can provide.
>
> Joe
>
> ___
> 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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3Init null pointer bug

[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 .
>
> .selftest
>
>
> The GDB debug traces are:
>
>> #0  0x55d76316ae12 in sqlite3Init (db=0x55d76509de18,
>> pzErrMsg=0x7fffa3d64ac0) at sqlite3.c:123082
>
> #1  0x55d7631a22d7 in sqlite3_table_column_metadata (db=0x55d76509de18,
>> zDbName=0x55d7631fa22e "main", zTableName=0x55d763200574 "selftest",
>> zColumnName=0x0, pzDataType=0x0, pzCollSeq=0x0, pNotNull=0x0,
>> pPrimaryKey=0x0, pAutoinc=0x0) at sqlite3.c:156312
>
> #2  0x55d7630e73f3 in do_meta_command (zLine=0x55d765090970
>> ".selftest", p=0x7fffa3d68090) at shell.c:14816
>
> #3  0x55d7630ea430 in process_input (p=0x7fffa3d68090,
>> in=0x55d76506f6d0) at shell.c:15712
>
> #4  0x55d7630e669c in do_meta_command (zLine=0x55d7650909f0 ".read",
>> p=0x7fffa3d68090) at shell.c:14369
>
> #5  0x55d7630ea430 in process_input (p=0x7fffa3d68090, in=0x0) at
>> shell.c:15712
>
> #6  0x55d7630ec173 in main (argc=1, argv=0x7fffa3d69668) at
>> shell.c:16479
>
>
> When we invoke "*.open .*", sqlite3 will fail to open "." database.
> However, sqlite3 will still create db("struct sqlite3") object without full
> initialization and not destroy it in "*shell.c:11306*".
>
> Then when we invoke "*.selftest"*, when the routine go to *sqlite3Init*
> function, it will call "*ENC(db) = SCHEMA_ENC(db)*;",
> which is a macro: "*#define SCHEMA_ENC(db) ((db)->aDb[0].pSchema->enc)*".
>
> "SCHEMA_ENC(db)" will access "pSchema" field, which has not been
> initialized yet for the failure create "." database.
>
> Attach is the poc sql file.
> I used the following command:
>
> ./sqlite3
>
> sqlite >.read crash.sql
>
>
> --
> Best regards，
> Xingwei Lin
> ___
> 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] Default Values Pragma bug

[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 
name like current_date. 
So it is not just a constant and needs parsing anyway.
This may make it better acceptable as it is, with comments included in the 
output of pragma table_info().

E. Pasma

PS there is a small typing mistake in above document:
..constant if it does contains
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Hick Gunter]

UNION ALL will just return whatever both sides produce, irrespective of 
duplicates, whereas
UNION will return only 1 copy of duplicated records.

asql> select 1 as x union select 1 as x;
x
-
1
asql> select 1 as x union all select 1 as x;
x
-
1
1attacho

So depending on the relative positions of the read and write transactions and 
the order of commits, the query will see either one (before or after both 
commits), two (NEW commits before OLD) or even zero (OLD commits before NEW) 
copies of any given record.

My guess is that NEW always commits before OLD and so UNION ALL is sometimes 
returning 2 copies of one record. I also expect that changing the order of the 
databases (i.e main database is OLD and NEW gets attached) will have 1 record 
missing both in UNION and UNION ALL.

Additionally, the query has an ORDER BY clause that requires sorting, which 
drastically changes the query plan.

asql> create table t1 (i integer);
asql> create table t2 (i integer);
asql> create view  un as select * from t1 union select * from t2;
asql> create view  ua as select * from t1 union all select * from t2;
asql> .explain
asql> explain query plan select * from un order by 1;
sele  order  from  deta
  -    
2 0  0 SCAN TABLE t1 (~100 rows)
3 0  0 SCAN TABLE t2 (~100 rows)
1 0  0 COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
0 0  0 SCAN SUBQUERY 1 (~200 rows)
0 0  0 USE TEMP B-TREE FOR ORDER BY
asql> explain query plan select * from ua order by 1;
sele  order  from  deta
  -    
1 0  0 SCAN TABLE t1 (~100 rows)
1 0  0 USE TEMP B-TREE FOR ORDER BY
2 0  0 SCAN TABLE t2 (~100 rows)
2 0  0 USE TEMP B-TREE FOR ORDER BY
0 0  0 COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] 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 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 atomic,
> assuming that the main database is not ":memory:" and the journal_mode is not 
> WAL.
> If the main database is ":memory:" or if the journal_mode is WAL, then
> transactions continue to be atomic within each individual database file.
> But if the host computer crashes in the middle of a COMMIT where two
> or more database files are updated, some of those files might get the
> changes where others might not."
>

Still, why would UNION behave differently from UNION ALL? That's the puzzling 
part IMHO. --DD ___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 atomic, assuming
> that the main database is not ":memory:" and the journal_mode is not WAL.
> If the main database is ":memory:" or if the journal_mode is WAL, then
> transactions continue to be atomic within each individual database file.
> But if the host computer crashes in the middle of a COMMIT where two or
> more database files are updated, some of those files might get the changes
> where others might not."
>

Still, why would UNION behave differently from UNION ALL? That's the
puzzling part IMHO. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Hick Gunter]

Are you sure it is not the special case documented in the ATTACH command? (see 
https://sqlite.org/lang_attach.html)

" Transactions involving multiple attached databases are atomic, assuming that 
the main database is not ":memory:" and the journal_mode is not WAL. If the 
main database is ":memory:" or if the journal_mode is WAL, then transactions 
continue to be atomic within each individual database file. But if the host 
computer crashes in the middle of a COMMIT where two or more database files are 
updated, some of those files might get the changes 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 exists in `UNION ALL`.


tldr: `UNION ALL` will not merge the data in different schemas in the moment 
that one of schema is committed but the another not. BUT, `UNION` will.


Here are the reproduce steps:


Preparation:
1. Prepare a database named "OLD"
1.1 Create a table for "OLD": `CREATE TABLE old(i INTEGER)`
1.2 Insert values into "OLD" to make it large enough: `INSERT INTO old 
VALUES(?1)` 2. Prepare a database named "NEW"
2.1 Create a table for "NEW": `CREATE TABLE new(i INTEGER)`


Migration:
For thread 1:
1. Attach "OLD" to "NEW" as "oldSchema": `ATTACH OLD AS oldSchema` 2. Migrate 
data from "OLD" to "NEW" in same transaction. Note that they should be executed 
with same handle using ATTACH mentioned in 1.
2.1 `BEGIN IMMEDIATE`
2.1 Select one of the row from "OLD": `SELECT i FROM oldSchema.old`
2.2 Insert the row into "NEW": `INSERT INTO main.new VALUES(?1)`
2.3 Delete the row from "OLD": `DELETE FROM oldSchema.old WHERE i == ?1`
2.4 `COMMIT`


For thread 2-N:
1. Create a view that union two tables: `CREATE TEMP VIEW v AS SELECT i FROM 
oldSchema.old UNION ALL SELECT i FROM main.new` 2. Select one of the value from 
view: `SELECT i FROM temp.v ORDER BY i LIMIT 1 OFFSET ?1`.
Here is the strange result:
As an example, if the values of 0-999 is inserted into "OLD", then value N 
should be selected as expected at offset N.
But in these kind of steps, it will not.


It can be a little bit hard to reproduce due to the multi-threading. BUT if it 
sleeps for a while when committing, it will be much easier to reproduce:
// vdbeCommit method of vdbeaux.c
for(i=0; rc==SQLITE_OK  idb-nDb; i++){
 Btree *pBt = db-aDb[i].pBt;
 sqlite3_sleep(10); // additional sleep here  if( pBt ){
  rc = sqlite3BtreeCommitPhaseOne(pBt, 0);  } }


It seems that the bug happens when one of the schema is committed but the 
another one is not.
On the other handle, if `UNION ALL` is changed to `UNION` while creating view, 
the bug will not happen too.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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.
>>> But according to documentation, all foreign constraints in SQLite are 
>>> immediate by default.
>> 
>>create table B (
>>aid references A(id) on delete cascade DEFERRABLE INITIALLY DEFERRED, 
>>ulti_data)
> 
> No difference at all. Because "insert or replace" always works as if the 
> constraint is deferred. 
> "insert or replace" always succeed to delete rows that are referenced by B 
> and defers the constraint enforcement until
> the end of the internal transaction. But at the end, a new row with the same 
> ID is inserted, so there is no 
> constraint violation anymore. 

John, I apologise.  I missed a paragraph in the documentation:

"If the current statement is not inside an explicit transaction (a 
BEGIN/COMMIT/ROLLBACK block), then an implicit transaction is committed as soon 
as the statement has finished executing. In this case deferred constraints 
behave the same as immediate constraints."

So please keep the CREATE TABLE statement the same as quoted above, but change 
your data commands to

BEGIN;
   insert or replace into A values (?1, ?2);
   insert into B values (?1, ?2);
COMMIT;

or even

BEGIN;
   insert or replace into A values (?1, ?2);
COMMIT;
insert into B values (?1, ?2);

Do these make things work the way you expect ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 foreign constraints in SQLite are 
> > immediate by default.
> 
> John,
> 
> The documentation suggests that in SQLite foreign keys are not deferred by 
> default.  Section 4.2 of
> 
> 
> 
> talks about this and shows how to set up a foreign key to be deferred:
> 
> create table B (
> aid references A(id) on delete cascade DEFERRABLE INITIALLY DEFERRED, 
> ulti_data)
> 

No difference at all. Because "insert or replace" always works as if the 
constraint is deferred. 
"insert or replace" always succeed to delete rows that are referenced by B and 
defers the constraint enforcement until
the end of the internal transaction. But at the end, a new row with the same ID 
is inserted, so there is no 
constraint violation anymore. 


> Assuming you are using atomic commit (not using BEGIN/COMMIT, so SQLite 
> "inserts" them around that single statement) I /think/ this should allow your 
> code to work the way you intended.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 suggests that in SQLite foreign keys are not deferred by 
default.  Section 4.2 of



talks about this and shows how to set up a foreign key to be deferred:

create table B (
aid references A(id) on delete cascade DEFERRABLE INITIALLY DEFERRED, 
ulti_data)

Assuming you are using atomic commit (not using BEGIN/COMMIT, so SQLite 
"inserts" them around that single statement) I /think/ this should allow your 
code to work the way you intended.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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" behave as the foreign 
constraint is deferred.
But according to documentation, all foreign constraints in SQLite are immediate 
by default.



If an FK is created as immediate, it will fail at the conclusion (read: 
END OF) the statement that hits the FK check. If it is declared as 
DEFERRED it will wait all the way until the end of the Transaction when 
you try to commit.


Either way, many people get confused with REPLACE because it feels like 
UPDATE, it is not, REPLACE means (as have been pointed out) "REMOVE 
constraint-violating rows, THEN, ADD the given new row" - and more 
importantly - all of this happens INSIDE one single statement so that 
any FK checks will happen at the end of said statement AFTER the new row 
is added back, and so no constraint violation exists come checking time.


Triggers are a whole different matter, they are like EVENT handlers, and 
have to fire by binding contract the very moment the triggerable offense 
happens, which in the case of REPLACE is right in the middle of the 
statement, if (and only if) there was one or more rows in there that 
needed ousting.


What you probably wanted is an "UPSERT", which in theory is more an 
UPDATE OR INSERT than a REPLACE, and will cause at best an UPDATE 
Trigger to fire and no deletions will happen.  SQLite doesn't have a 
command like that, but you can easily simulate it by just issuing two 
commands:
First do the UPDATE... WHERE Key = X - which, if the record doesn't 
exist yet, will fail quietly because of the WHERE clause,
then do the INSERT OR IGNORE(...) - which will again fail quietly if it 
did already exist,
both of which won't break a Transaction, mess with the wrong triggers or 
constraint checks, will be very fast (considering the PK is used and 
only one of the two statements gets to do any work in IO terms)... and 
will always work perfectly and reliably fire only ON INSERT, ON UPDATE 
and ON DELETE triggers when those really happen.


The obvious downside to it being you having to formulate both an UPDATE 
and an INSERT statement in your code, which can be irritating, but then, 
if it's the best tool for the job...



I hope this shed some light on how it works and why the triggers and FKs 
fire differently (by design) and how to get the results you want.


Cheers,
Ryan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 “original” 
row, it just happens that the most common conflict is primary key.  It’s also 
true that inserting a single row with “insert or replace” can cause multiple 
rows to be deleted (if there are multiple constraints across multiple columns, 
for example).

 -j

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 your code, figure out whether you need INSERT or UPDATE, and do the 
> appropriate one.
> 

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" behave as the foreign 
constraint is deferred.
But according to documentation, all foreign constraints in SQLite are immediate 
by default.


> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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

“Insert or replace” is NOT “insert or update.”

Insert always inserts a new row.  The only question is if it deletes 
conflicting rows (yes, plural) first.



With that in mind, the FK concept gets a little fuzzy.  It is a new row that 
just happens to have the same ID as an old row.  Does that mean the FK in B 
should still reference it?  I’d say no, because it is a brand new row… a 
different entity. If you want B to keep referencing the existing row, then 
update the row that is already there, don’t delete it and insert a new row on 
top of it.  I’d look into deferrable constraints to see if the behavior you’re 
looking for is supported.  Hopefully others can comment more on that.

 -j





> On Mar 9, 2018, at 1:11 PM, John Found  wrote:
> 
> 
> 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, ?2);
>insert into B values (?1, ?2); 
> 
> Unfortunately, after replacing some row in A, all previously inserted rows in 
> B got deleted, even if the value of ID does not changes. Here SQLite works 
> exactly as it first deletes the conflicting row from A and then inserting new.
> 
> Now, if I define the table B without "on delete":
> 
>create table B ( aid references A(id), multi_data);
> 
> "insert or replace" succeed without deleting the old rows from B.
> 
> In my opinion this behaviour is not consistent. The consistent solutions IMHO 
> are two:
> 
> 1. "insert or replace" succeed both in the first and in the second case 
> without deleting rows from B,
> 
> 2. "insert or delete" succeed in the first case, deleting all constrained 
> rows from B and fails in the second case with "FOREIGN KEY constraint failed".
> 
> The first case IMHO is more intuitive and natural. At least this was my 
> expectation when writing the code.
> 
> 
> -- 
> http://fresh.flatassembler.net
> http://asm32.info
> John Found 
> ___
> 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

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

[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 the majority will succeed
> with “Infinity” (as this is the standard JS property name)
>
JSON5 or JSON6 handle it.
http://json5.org/  https://github.com/d3x0r/json6  (
https://github.com/d3x0r/SACK/blob/master/src/netlib/html5.websocket/json/json6_parser.c
)

(also NaN)
but yes that is a deficiency in JSON.


> eg: in standard python -
>
> >> import json
> >> json.loads( ‘[Inf,-Inf,null]’ )
> ValueError: No JSON object could be decoded
> >> json.loads( ‘[Infinity,-Infinity,null]’ )
> [inf, -inf, None]
>
>
> Regards,
>
> -Alex
> --
> iPhoneから送信
> ___
> 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

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

[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 Infinity, either, as a conforming parser would reject both. 

What to do in such a case is undefined, but for the stated case there is 
actually a very sensible conforming output:

'[1e,-1e,null]'

I realize it is impractical for SQLite to do so, but given that JSON numbers 
convey arbitrary precision, only explicit infinity should, ideally, result in 
undefined behaviour. 

On January 22, 2018 9:00:35 PM EST, 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
>succeed with “Infinity” (as this is the standard JS property name)
>
>eg: in standard python -
>
>>> import json
>>> json.loads( ‘[Inf,-Inf,null]’ )
>ValueError: No JSON object could be decoded
>>> json.loads( ‘[Infinity,-Infinity,null]’ )
>[inf, -inf, None]
>
>
>Regards,
>
>-Alex
>--
>iPhoneから送信
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 succeed
> with “Infinity” (as this is the standard JS property name)

A strict reading of https://json.org/ suggests that neither "Inf" nor
"Infinity" ought to work.  I'm not sure how we ought to deal with
this

>
> eg: in standard python -
>
>>> import json
>>> json.loads( ‘[Inf,-Inf,null]’ )
> ValueError: No JSON object could be decoded
>>> json.loads( ‘[Infinity,-Infinity,null]’ )
> [inf, -inf, None]
>
>
> Regards,
>
> -Alex
> --
> iPhoneから送信
> ___
> 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] GROUP_CONCAT with DISTINCT bug

[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...)''.


-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[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 replError; /* STRREPL_NOMEM or STRREPL_TOOBIG */
};
typedef struct StrRepl StrRepl;
#define STRREPL_NOMEM   1
#define STRREPL_TOOBIG  2

/*
 ** Aggregate group_replace(A,B,C) result string is derived from A by
replacing
 ** every exact match occurrence of B with C.  A is presumed constant over
the group
 ** while B and C may vary at each step.  Collating sequences are not used.
 */
static void groupReplaceStep(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
  ) {

  const unsigned char *zStr; /* The input string A */
  const unsigned char *zPattern; /* The pattern string B */
  const unsigned char *zRepl; /* The replacement string C */
  unsigned char *zOut; /* The output */
  int nStr; /* Size of zStr */
  int nPattern; /* Size of zPattern */
  int nRepl; /* Size of zRep */
  i64 nOut; /* Maximum size of zOut */
  int loopLimit; /* Last zStr[] that might match zPattern[] */
  int i, j; /* Loop counters */

  assert(argc == 3);
  UNUSED_PARAMETER(argc);
  zStr = sqlite3_value_text(argv[0]);
  if (zStr == 0) return;
  nStr = sqlite3_value_bytes(argv[0]);
  assert(zStr == sqlite3_value_text(argv[0])); /* No encoding change */
  zPattern = sqlite3_value_text(argv[1]);
  if (zPattern == 0) {
assert(sqlite3_value_type(argv[1]) == SQLITE_NULL
  || sqlite3_context_db_handle(context)->mallocFailed);

return;
  }
  if (zPattern[0] == 0) {
assert(sqlite3_value_type(argv[1]) != SQLITE_NULL);
sqlite3_result_value(context, argv[0]);
return;
  }
  nPattern = sqlite3_value_bytes(argv[1]);
  assert(zPattern == sqlite3_value_text(argv[1])); /* No encoding change */
  zRepl = sqlite3_value_text(argv[2]);
  if (zRepl == 0) return;
  nRepl = sqlite3_value_bytes(argv[2]);
  assert(zRepl == sqlite3_value_text(argv[2]));

  StrRepl* pStrRepl = (StrRepl*) sqlite3_aggregate_context(context, sizeof
(StrRepl));
  if (pStrRepl) {
if (pStrRepl->replError) return;
if (!pStrRepl->zStr) {
  if (sqlite3_value_type(argv[0]) == SQLITE_NULL
|| sqlite3_value_type(argv[1]) == SQLITE_NULL) return;
  pStrRepl->nStr = sqlite3_value_bytes(argv[0]);
  pStrRepl->zStr = contextMalloc(context,pStrRepl->nStr);

memcpy((void*)pStrRepl->zStr,sqlite3_value_text(argv[0]),pStrRepl->nStr);
/* No encoding change */
}
zPattern = sqlite3_value_text(argv[1]);
nPattern = sqlite3_value_bytes(argv[1]);
assert(zPattern == sqlite3_value_text(argv[1])); /* No encoding change
*/
if (0 == pStrRepl->nStr || 0 == nPattern) return;
zRepl = sqlite3_value_text(argv[2]);
nRepl = sqlite3_value_bytes(argv[2]);
if (0 == zRepl) return;
nOut = pStrRepl->nStr + 1;
assert(nOut < SQLITE_MAX_LENGTH);
zOut = contextMalloc(context, (i64) nOut);
if (0 == zOut) return;
loopLimit = pStrRepl->nStr - nPattern;
for (i = j = 0; i <= loopLimit; i++) {
  if (pStrRepl->zStr[i] != zPattern[0] || memcmp(&(pStrRepl->zStr)[i],
zPattern, nPattern)) {
zOut[j++] = pStrRepl->zStr[i];
  } else {
u8 *zOld;
sqlite3 *db = sqlite3_context_db_handle(context);
nOut += nRepl - nPattern;
testcase(nOut - 1 == db->aLimit[SQLITE_LIMIT_LENGTH]);
testcase(nOut - 2 == db->aLimit[SQLITE_LIMIT_LENGTH]);
if (nOut - 1 > db->aLimit[SQLITE_LIMIT_LENGTH]) {
  pStrRepl->replError = STRREPL_TOOBIG;
  sqlite3_free(zOut);
  return;
}
zOld = zOut;
zOut = sqlite3_realloc64(zOut, (int) nOut);
if (zOut == 0) {
  pStrRepl->replError = STRREPL_NOMEM;
  sqlite3_free(zOld);
  return;
}
memcpy([j], zRepl, nRepl);
j += nRepl;
i += nPattern - 1;
  }
}
assert(j + pStrRepl->nStr - i + 1 == nOut);
memcpy([j], >zStr[i], pStrRepl->nStr - i);
j += pStrRepl->nStr - i;
assert(j <= nOut);
zOut[j] = 0;
void* pFree = (void*)pStrRepl->zStr;
pStrRepl->zStr = zOut;
pStrRepl->nStr = nOut;
sqlite3_free(pFree);
  }
}

static void groupReplaceFinalize(sqlite3_context *context) {
  const char **pzVal;
  StrRepl* pStrRepl = sqlite3_aggregate_context(context, 0);
  if (pStrRepl) {
if (pStrRepl->replError == STRREPL_TOOBIG) {
  sqlite3_result_error_toobig(context);
} else if (pStrRepl->replError == STRREPL_NOMEM) {
  sqlite3_result_error_nomem(context);
} else {
  sqlite3_result_text(context, pStrRepl->zStr, -1, sqlite3_free);
}
  }
}


On Tue, Jan 2, 2018 at 4:57 PM, Scott Robison 
wrote:

> 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 

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[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 sense in that
> use case, so I didn't bump into this issue myself.

Thanks for the info. In doing some quick searches, I found multiple
descriptions of "generic SQL" aggregates that gave a syntax of
"aggregate([DISTINCT|ALL] expression)", which led me to assume that
maybe the standard only allows that syntax with a single expression,
not an expression list. I say maybe because the examples I found were
clearly not the standard.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[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 for a genuine hypothetical DISTINCT filter aggregate
case, consider an aggregate which computes statistics about matrices.  In
some mode, such a aggregate could legitimately be invoked with a DISTINCT
filter.   Obviously the nested query workaround fixes that case too.

Given the nested workaround is always possible, short circuit optimizing
the SELECT syntax makes sense but it would be nice to know if that's the
only rationale.

There is also the sensible expectation of group_concat() to have rows
supplied in controlled order, by nested SELECT if needed.  The expectation
of a controlling nested SELECT is definitely already there.

Peter

On Tue, Jan 2, 2018 at 4:12 PM, Scott Robison 
wrote:

> 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 = pFunc->pExpr;
> >   assert( !ExprHasProperty(pE, EP_xIsSelect) );
> >   if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){
> > sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have exactly
> one "
> >"argument");
> > pFunc->iDistinct = -1;
> >   }else{
> >
> > It would be interesting to understand the harm avoided by disallowing
> > DISTINCT scope of all the aggregate parameters.   Probably slower, but
> what
> > else?  Usually, there is a comment in the source but not for this one.
>
> I'm not the guy who wrote it or decided how it should work, but it
> seems to me that "group_concat(distinct x,y)" would not work
> intuitively if y is a variable, though (somewhat surprisingly to me)
> it works:
>
> sqlite> create table t(x,y);
> sqlite> insert into t values (1,','),(1,'+'),(2,'.'),(2,'-');
> sqlite> select group_concat(x,y) from t;
> 1+1.2-2
> sqlite> select group_concat(distinct x,y) from t;
> Error: DISTINCT aggregates must have exactly one argument
> sqlite> select group_concat(x,y) from (select distinct x, y from t);
> 1+1.2-2
>
> Are there other aggregate functions that take multiple arguments? I
> can't find any examples online of aggregates that take more than one,
> which seems like the normal way something like this would be done.
> ___
> 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

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[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 can vary from record to 
record and can be used in many fancy and/or useful ways:


sqlite> WITH RECURSIVE numbers(n,p) AS (VALUES(random()/10,0) 
UNION ALL SELECT random()/10,n FROM numbers LIMIT 10) SELECT 
group_concat(n,substr('<=>',3*(n2879787174<=7821300466>-9054357747<=3166199899>-4120363042<=8151009951>-7018229290<=4454709919<=8212308797

-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[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 = pFunc->pExpr;
>   assert( !ExprHasProperty(pE, EP_xIsSelect) );
>   if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){
> sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have exactly one "
>"argument");
> pFunc->iDistinct = -1;
>   }else{
>
> It would be interesting to understand the harm avoided by disallowing
> DISTINCT scope of all the aggregate parameters.   Probably slower, but what
> else?  Usually, there is a comment in the source but not for this one.

I'm not the guy who wrote it or decided how it should work, but it
seems to me that "group_concat(distinct x,y)" would not work
intuitively if y is a variable, though (somewhat surprisingly to me)
it works:

sqlite> create table t(x,y);
sqlite> insert into t values (1,','),(1,'+'),(2,'.'),(2,'-');
sqlite> select group_concat(x,y) from t;
1+1.2-2
sqlite> select group_concat(distinct x,y) from t;
Error: DISTINCT aggregates must have exactly one argument
sqlite> select group_concat(x,y) from (select distinct x, y from t);
1+1.2-2

Are there other aggregate functions that take multiple arguments? I
can't find any examples online of aggregates that take more than one,
which seems like the normal way something like this would be done.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[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( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){
sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have exactly one "
   "argument");
pFunc->iDistinct = -1;
  }else{

It would be interesting to understand the harm avoided by disallowing
DISTINCT scope of all the aggregate parameters.   Probably slower, but what
else?  Usually, there is a comment in the source but not for this one.

Peter


On Tue, Jan 2, 2018 at 2:54 PM, Tony Papadimitriou  wrote:

> 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 group_concat(DISTINCT c,',') FROM t;
> Error: DISTINCT aggregates must have exactly one argument
>
>
> On Tue, Jan 2, 2018 at 12: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 group_concat(distinct s,', ') from t group by null;  -- ERROR
>>
>> -- The moment the optional delimiter is given along with DISTINCT you get
>> this error:
>> -- Error: near line 6: DISTINCT aggregates must have exactly one argument
>>
>> -- Thank you.
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[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;   -- 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 is given along with DISTINCT you get 
this error:

-- Error: near line 6: DISTINCT aggregates must have exactly one argument


A limitation of the SQL syntax. I suspect this might work for your
example use case: select group_concat(s, ',') from (select distinct s
as s from t) group by null;

Tested and confirmed here.
___
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

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[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 group_concat(DISTINCT c,',') FROM t;
Error: DISTINCT aggregates must have exactly one argument


On Tue, Jan 2, 2018 at 12: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 group_concat(distinct s,', ') from t group by null;  -- ERROR

-- The moment the optional delimiter is given along with DISTINCT you get
this error:
-- Error: near line 6: DISTINCT aggregates must have exactly one argument

-- Thank you.
___
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

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[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 by null;   -- OK
select group_concat(distinct s,', ') from t group by null;  -- ERROR

-- The moment the optional delimiter is given along with DISTINCT you get this 
error:
-- Error: near line 6: DISTINCT aggregates must have exactly one argument


A limitation of the SQL syntax.



Quite justly, not all the time params are obvious -- group functions are 
not group functions with 1 column and n one-bind-time parameters:


sqlite> CREATE TABLE a(a,sep);
sqlite> INSERT INTO a VALUES('Hello', ','),('world', ';'),('shmorld', ' 
AND ');

sqlite> SELECT group_concat(a,sep) FROM a GROUP BY NULL;
Hello;world AND shmorld

Hopefully, SELECT FROM SELECT DISTINCT mentioned previously by Scott, 
resolves the problem in an easy & painless way.


-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[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 at 12: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 group_concat(distinct s,', ') from t group by null;  -- ERROR
>
> -- The moment the optional delimiter is given along with DISTINCT you get
> this error:
> -- Error: near line 6: DISTINCT aggregates must have exactly one argument
>
> -- Thank you.
> ___
> 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

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[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 group_concat(distinct s,', ') from t group by null;  -- ERROR
>
> -- The moment the optional delimiter is given along with DISTINCT you get 
> this error:
> -- Error: near line 6: DISTINCT aggregates must have exactly one argument

A limitation of the SQL syntax. I suspect this might work for your
example use case: select group_concat(s, ',') from (select distinct s
as s from t) group by null;

Tested and confirmed here.
___
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

[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 development,
desktop apps. I also created tool or gui sqlite manager
and will try to sell it in near future. I needed very
good tool because I work with sqlite every day. It
is just for windows platform. I support different
datetime formats, blobs, compress etc. See this page:

http://www.arsistemi.si/izdelki/sqlite-4-all.html

Every report is created with sqlite tables. I read
data from different RDBMS to sqlite and then work
with data as needed.

petern je 22.12.2017 ob 23:19 napisal:

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 suite which pinpoints the problem:

SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
d
1

CREATE TABLE test AS SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
SELECT * FROM test;
c
1

CREATE TABLE test AS SELECT c AS d FROM (SELECT 1 AS c);
SELECT * FROM test;
d
1

And still one more that illustrates the contradictory and unstable
behavior. Use '*' instead of 'd' and it works as expected.

CREATE TABLE test AS SELECT * FROM (SELECT c AS d FROM (SELECT 1 AS c));
SELECT * FROM test;
d
1

Obviously the most immediate well formed alias 'd' should be carried
through irrespective of nesting.

FYI, there was a checkin just now with some changes to track intermediate
aliases:

http://www.sqlite.org/src/info/5efd854fe2147033

Peter

___
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

[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 suite which pinpoints the problem:

SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
d
1

CREATE TABLE test AS SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c));
SELECT * FROM test;
c
1

CREATE TABLE test AS SELECT c AS d FROM (SELECT 1 AS c);
SELECT * FROM test;
d
1

And still one more that illustrates the contradictory and unstable
behavior. Use '*' instead of 'd' and it works as expected.

CREATE TABLE test AS SELECT * FROM (SELECT c AS d FROM (SELECT 1 AS c));
SELECT * FROM test;
d
1

Obviously the most immediate well formed alias 'd' should be carried
through irrespective of nesting.

FYI, there was a checkin just now with some changes to track intermediate
aliases:

http://www.sqlite.org/src/info/5efd854fe2147033

Peter


On Fri, Dec 22, 2017 at 11:34 AM, Radovan Antloga 
wrote:

> 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
>>
>>
>> SQLite version 3.11.0 2016-02-15 17:29:24
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .header on
>> sqlite>
>> sqlite> create table test(a int, b int);
>> sqlite> insert into test values (1,1);
>> sqlite>
>> sqlite> select d from (select c as d from (select a as c from test));
>> d
>> 1
>> sqlite>
>> sqlite> create table test2 as
>>...> select d from (select c as d from (select a as c from test));
>> sqlite> SELECT * FROM test2;
>> d  <=
>> 1
>> sqlite>
>>
>>
>>
>> SQLite version 3.21.0 2017-10-24 18:55:49
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .header on
>> sqlite> create table test(a int, b int);
>> sqlite> insert into test values (1,1);
>> sqlite>
>> sqlite> select d from (select c as d from (select a as c from test));
>> d
>> 1
>> sqlite> create table test2 as
>>...> select d from (select c as d from (select a as c from test));
>> sqlite> SELECT * FROM test2;
>> a   <=
>> 1
>> sqlite>
>>
>> email signature Klaus Maas
>> 
>> On 2017-12-22 20:11, Radovan Antloga wrote:
>>
>>> 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 a.
>>>
>>> with PostgreSQL I get table test2 with name d.
>>>
>>> Thank you very much for your time!
>>>
>>>
>>> Richard Hipp je 22.12.2017 ob 20:06 napisal:
>>>
 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 d
>
> CREATE TABLE AS SELECT
> create table with name a instead of d.
>
 Can you provide a simple test case for this behavior?


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

[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


SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .header on
sqlite>
sqlite> create table test(a int, b int);
sqlite> insert into test values (1,1);
sqlite>
sqlite> select d from (select c as d from (select a as c from test));
d
1
sqlite>
sqlite> create table test2 as
   ...> select d from (select c as d from (select a as c from test));
sqlite> SELECT * FROM test2;
d  <=
1
sqlite>



SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .header on
sqlite> create table test(a int, b int);
sqlite> insert into test values (1,1);
sqlite>
sqlite> select d from (select c as d from (select a as c from test));
d
1
sqlite> create table test2 as
   ...> select d from (select c as d from (select a as c from test));
sqlite> SELECT * FROM test2;
a   <=
1
sqlite>

email signature Klaus Maas

On 2017-12-22 20:11, Radovan Antloga wrote:

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

with PostgreSQL I get table test2 with name d.

Thank you very much for your time!


Richard Hipp je 22.12.2017 ob 20:06 napisal:

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 d

CREATE TABLE AS SELECT
create table with name a instead of d.

Can you provide a simple test case for this behavior?



___
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

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

[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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .header on
sqlite>
sqlite> create table test(a int, b int);
sqlite> insert into test values (1,1);
sqlite>
sqlite> select d from (select c as d from (select a as c from test));
d
1
sqlite>
sqlite> create table test2 as
   ...> select d from (select c as d from (select a as c from test));
sqlite> SELECT * FROM test2;
d  <=
1
sqlite>



SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .header on
sqlite> create table test(a int, b int);
sqlite> insert into test values (1,1);
sqlite>
sqlite> select d from (select c as d from (select a as c from test));
d
1
sqlite> create table test2 as
   ...> select d from (select c as d from (select a as c from test));
sqlite> SELECT * FROM test2;
a   <=
1
sqlite>

email signature Klaus Maas

On 2017-12-22 20:11, Radovan Antloga wrote:

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

with PostgreSQL I get table test2 with name d.

Thank you very much for your time!


Richard Hipp je 22.12.2017 ob 20:06 napisal:

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 d

CREATE TABLE AS SELECT
create table with name a instead of d.

Can you provide a simple test case for this behavior?



___
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

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

[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

[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 a.

with PostgreSQL I get table test2 with name d.

Thank you very much for your time!


Richard Hipp je 22.12.2017 ob 20:06 napisal:

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 d

CREATE TABLE AS SELECT
create table with name a instead of d.

Can you provide a simple test case for this behavior?



___
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

[Radovan Antloga]

What is correct?

Do you understand what I write?
What is my point? Tell me please.

David Raymond je 22.12.2017 ob 20:04 napisal:

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 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 that we should enhance rule 1 so
that it applies even if the AS clause is contained within a subquery.


___
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

[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 d
>
> CREATE TABLE AS SELECT
> create table with name a instead of d.

Can you provide a simple test case for this behavior?

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

[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 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 that we should enhance rule 1 so
that it applies even if the AS clause is contained within a subquery.
-- 
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-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

[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 je 22.12.2017 ob 19:45 napisal:

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.


___
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

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

[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 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 it
appears in the original CREATE TABLE statement for the table.

(3) Otherwise, name the result-set column using the SQL text in the
SELECT statement.

What set of rules are you proposing that we should use instead?



___
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

[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 it
appears in the original CREATE TABLE statement for the table.

(3) Otherwise, name the result-set column using the SQL text in the
SELECT statement.

What set of rules are you proposing that we should use instead?

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

[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 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 depend on a column name.

If you want to know what the column will be called, specify AS:

select d AS myColumnNameHere from (select c AS d from (select a AS c from 
test));

Now you know what the column will be called.

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

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

[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 hard to understand.

Simon Slavin je 22.12.2017 ob 17:58 napisal:


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 depend on a column name.

If you want to know what the column will be called, specify AS:

select d AS myColumnNameHere from (select c AS d from (select a AS c from 
test));

Now you know what the column will be called.

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

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

[Radovan Antloga]

Thank you very much to understand my point!
This is exactly what I think. I have name
and select is working ok. It gives me my
name but create table as not.

David Raymond je 22.12.2017 ob 17:59 napisal:

I think the underlying feeling here is that if you're not doing anything 
tricky, and just straight up referencing a column name, that it should be fair 
to assume that the result will have that column name.

I.e. with
create table foo (bar);
if I run
select bar from foo;
I should be able to assume the result column is named "bar".

So...
select a as c from test
is certain to have a field named "c",

select c as d from (something with a field named "c")
is certain to have a field named "d",

but select d from (something with a field named "d")
doesn't give a field named "d"?

I'd say it's fair to be confused at that.
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 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 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 statements 
to include AS for every column selected, so should they be warning their users 
about it?

If you want to depend on the name of a column, use AS.  If column names without 
AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that 
standard, then there would need to be a warning.  But there’s no standard for 
column names without AS in the SQL standard so no warning is needed.

If you think that column names are simple, please consider this:

CREATE TABLE ta (ca INTEGER);
CREATE TABLE tb (cb INTEGER);
SELECT ca FROM ta UNION SELECT cb FROM tb;

What should the name of the resulting column be ?

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

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

[David Raymond]

I think the underlying feeling here is that if you're not doing anything 
tricky, and just straight up referencing a column name, that it should be fair 
to assume that the result will have that column name.

I.e. with
create table foo (bar);
if I run
select bar from foo;
I should be able to assume the result column is named "bar".

So...
select a as c from test
is certain to have a field named "c",

select c as d from (something with a field named "c")
is certain to have a field named "d",

but select d from (something with a field named "d")
doesn't give a field named "d"?

I'd say it's fair to be confused at that.
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 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 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 
> statements to include AS for every column selected, so should they be warning 
> their users about it?

If you want to depend on the name of a column, use AS.  If column names without 
AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that 
standard, then there would need to be a warning.  But there’s no standard for 
column names without AS in the SQL standard so no warning is needed.

If you think that column names are simple, please consider this:

CREATE TABLE ta (ca INTEGER);
CREATE TABLE tb (cb INTEGER);
SELECT ca FROM ta UNION SELECT cb FROM tb;

What should the name of the resulting column be ?

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

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

[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 depend on a column name.

If you want to know what the column will be called, specify AS:

select d AS myColumnNameHere from (select c AS d from (select a AS c from 
test));

Now you know what the column will be called.

Simon.
___
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

[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 table with
create table as I get a as column name.


Simon Slavin je 22.12.2017 ob 17:33 napisal:


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 statements 
to include AS for every column selected, so should they be warning their users 
about it?

If you want to depend on the name of a column, use AS.  If column names without 
AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that 
standard, then there would need to be a warning.  But there’s no standard for 
column names without AS in the SQL standard so no warning is needed.

If you think that column names are simple, please consider this:

CREATE TABLE ta (ca INTEGER);
CREATE TABLE tb (cb INTEGER);
SELECT ca FROM ta UNION SELECT cb FROM tb;

What should the name of the resulting column be ?

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

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

[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 
> statements to include AS for every column selected, so should they be warning 
> their users about it?

If you want to depend on the name of a column, use AS.  If column names without 
AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that 
standard, then there would need to be a warning.  But there’s no standard for 
column names without AS in the SQL standard so no warning is needed.

If you think that column names are simple, please consider this:

CREATE TABLE ta (ca INTEGER);
CREATE TABLE tb (cb INTEGER);
SELECT ca FROM ta UNION SELECT cb FROM tb;

What should the name of the resulting column be ?

Simon.
___
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

[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 questions are these:

1) That the name without AS is documented as being unspecified, is that the 
case with all/most SQL systems or is it SQLite specific?

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 statements 
to include AS for every column selected, so should they be warning their users 
about it?


-- 
Cheers  --  Tim
___
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

[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 as statement

create table test2 as
select d from (select c as d from (select a as c from test));

you get table test2 with column name a.

If you change to this

create table test2 as
select d as d from (select c as d from (select a as c from test));

you will get name correct. I think it should
be the same as when just using select statement.

Best Regards
Radovan


select a from (select b from (select c from test)))

Richard Hipp je 21.12.2017 ob 14:52 napisal:

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 systemnocase,
VP  integer,
BLANK  varchar(6) collate systemnocase,
NAZIV  varchar(24) collate systemnocase,
KN  varchar(12) collate systemnocase,
A  varchar(1) collate systemnocase,
B  varchar(1) collate systemnocase,
RACUN  varchar(1) collate systemnocase,
URE  varchar(1) collate systemnocase,
ZN  varchar(1) collate systemnocase,
TOCKE  varchar(1) collate systemnocase,
PRC  varchar(1) collate systemnocase,
UP  varchar(1) collate systemnocase,
IZPIS  varchar(1) collate systemnocase,
D  varchar(1) collate systemnocase,
F2U  varchar(1) collate systemnocase,
F2O  varchar(1) collate systemnocase,
F2T  varchar(1) collate systemnocase,
F2Z  varchar(1) collate systemnocase,
F2P_1  integer,
F2P_2  integer,
F2P_3  integer,
F5  varchar(1) collate systemnocase,
AJPES  varchar(1) collate systemnocase,
ZZ  integer,
VD  integer,
NS  integer,
MES  integer,
NORURE  varchar(1) collate systemnocase,
G  varchar(1) collate systemnocase,
E  varchar(1) collate systemnocase,
H  varchar(1) collate systemnocase,
I  varchar(1) collate systemnocase,
J  varchar(1) collate systemnocase,
SM  varchar(1) collate systemnocase,
NO  varchar(1) collate systemnocase,
PRIO  varchar(1) collate systemnocase,
V_1  varchar(1) collate systemnocase,
V_2  varchar(1) collate systemnocase,
V_3  varchar(1) collate systemnocase,
V_4  varchar(1) collate systemnocase,
V_5  varchar(1) collate systemnocase,
V_6  varchar(1) collate systemnocase,
V_7  varchar(1) collate systemnocase,
V_8  varchar(1) collate systemnocase,
V_9  varchar(1) collate systemnocase,
V_10  varchar(1) collate systemnocase,
V_11  varchar(1) collate systemnocase,
V_12  varchar(1) collate systemnocase,
FOR  integer,
P_1  integer,
P_2  integer,
P_3  integer,
P_4  integer,
P_5  integer,
P_6  integer,
primary key (RID, VP, BLANK));

When I create new table using this sql:

drop table if exists WM4P;
create table WM4P as
select
P, A, B, AB, U, H, ZZ,
case
  when AB in ('7') then 99
  when AB in ('57', '58', '59', '5M') then null
  when AB = '56' and ZZ = 12 then 01
  when AB = '56' then 02
  when A = '3' then 03
  when AB in ('1M') then 08
  when AB in ('10') then 07
  when AB in ('12') then null
  when A in ('1', '5') and H = '1' then 02
  when A in ('5') then 02
  when A in ('1') then 01
end as M4_OP
from (
select
  VP as P, ifnull(A,'') as A, ifnull(B,'') as B,
ifnull(A,'')||ifnull(B,'') as AB,
  ifnull(URE,'') as U, ifnull(H,'') as H, ZZ
from SOPP1
);

You will see that first column name is VP instead of P.
In previous versions name was P not VP.

Best Regards
Radovan Antloga
___
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

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

[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 systemnocase,
>VP  integer,
>BLANK  varchar(6) collate systemnocase,
>NAZIV  varchar(24) collate systemnocase,
>KN  varchar(12) collate systemnocase,
>A  varchar(1) collate systemnocase,
>B  varchar(1) collate systemnocase,
>RACUN  varchar(1) collate systemnocase,
>URE  varchar(1) collate systemnocase,
>ZN  varchar(1) collate systemnocase,
>TOCKE  varchar(1) collate systemnocase,
>PRC  varchar(1) collate systemnocase,
>UP  varchar(1) collate systemnocase,
>IZPIS  varchar(1) collate systemnocase,
>D  varchar(1) collate systemnocase,
>F2U  varchar(1) collate systemnocase,
>F2O  varchar(1) collate systemnocase,
>F2T  varchar(1) collate systemnocase,
>F2Z  varchar(1) collate systemnocase,
>F2P_1  integer,
>F2P_2  integer,
>F2P_3  integer,
>F5  varchar(1) collate systemnocase,
>AJPES  varchar(1) collate systemnocase,
>ZZ  integer,
>VD  integer,
>NS  integer,
>MES  integer,
>NORURE  varchar(1) collate systemnocase,
>G  varchar(1) collate systemnocase,
>E  varchar(1) collate systemnocase,
>H  varchar(1) collate systemnocase,
>I  varchar(1) collate systemnocase,
>J  varchar(1) collate systemnocase,
>SM  varchar(1) collate systemnocase,
>NO  varchar(1) collate systemnocase,
>PRIO  varchar(1) collate systemnocase,
>V_1  varchar(1) collate systemnocase,
>V_2  varchar(1) collate systemnocase,
>V_3  varchar(1) collate systemnocase,
>V_4  varchar(1) collate systemnocase,
>V_5  varchar(1) collate systemnocase,
>V_6  varchar(1) collate systemnocase,
>V_7  varchar(1) collate systemnocase,
>V_8  varchar(1) collate systemnocase,
>V_9  varchar(1) collate systemnocase,
>V_10  varchar(1) collate systemnocase,
>V_11  varchar(1) collate systemnocase,
>V_12  varchar(1) collate systemnocase,
>FOR  integer,
>P_1  integer,
>P_2  integer,
>P_3  integer,
>P_4  integer,
>P_5  integer,
>P_6  integer,
>primary key (RID, VP, BLANK));
>
> When I create new table using this sql:
>
> drop table if exists WM4P;
> create table WM4P as
> select
>P, A, B, AB, U, H, ZZ,
>case
>  when AB in ('7') then 99
>  when AB in ('57', '58', '59', '5M') then null
>  when AB = '56' and ZZ = 12 then 01
>  when AB = '56' then 02
>  when A = '3' then 03
>  when AB in ('1M') then 08
>  when AB in ('10') then 07
>  when AB in ('12') then null
>  when A in ('1', '5') and H = '1' then 02
>  when A in ('5') then 02
>  when A in ('1') then 01
>end as M4_OP
> from (
>select
>  VP as P, ifnull(A,'') as A, ifnull(B,'') as B,
> ifnull(A,'')||ifnull(B,'') as AB,
>  ifnull(URE,'') as U, ifnull(H,'') as H, ZZ
>from SOPP1
>);
>
> You will see that first column name is VP instead of P.
> In previous versions name was P not VP.
>
> Best Regards
> Radovan Antloga
> ___
> 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] Is this a bug with expression evaluation?

[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 subclause 6.12:

| 1) If the data type of both operands of a dyadic arithmetic opera-
|tor is exact numeric, then the data type of the result is exact
|numeric, with precision and scale determined as follows:
|[...]
|d) The precision and scale of the result of division is
|   implementation-defined.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 write code 
> that depends on it.

I think it already does:

sqlite> select 1/2;
0
sqlite> 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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

This is probably the reason that MySQL has the separate "DIV" operator
for integer division, whereas everybody else makes due with the
standard "/" operator.
-- 
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] Is this a bug with expression evaluation?

[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 it can change.  The next 
version of SQLite might decide that 1 / 2 is 0.  So don’t write code that 
depends on it.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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


MySQL example:
mysql> select 1/2;
++
| 1/2|
++
| 0.5000 |
++
1 row in set (0.13 sec)

PostgreSQL example:
psql=# select 1/2;
?column?
--
   0
(1 row)


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 need 
arbitrary precision as REAL rather than INTEGER, and SQLite column 
>affinity would do the rest.


SQLite3 (https://sqlite.org/datatype3.html) -- "Otherwise, an expression has 
no affinity. "

It seems that 'no affinity' gets translated to integer affinity, then.

Is there a way to default to float?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 need 
arbitrary precision as REAL rather than INTEGER, and SQLite column affinity 
would do the rest.

Otherwise, yes, I believe you would need to cast. 

On December 14, 2017 11:36:19 AM EST, Tony Papadimitriou  wrote:
>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 dot to either number (e.g., 24. or 100.) I get the correct
>result.
>But, with named fields, it’s not as easy as adding a dot:
>
>select column1*(column2/column3) wrong from (values(100,24,100));
>select column1*column2/column3 correct from (values(100,24,100));
>
>So, to get correct answer I have to use a cast for either field?
>
>select column1*(cast(column2 as float)/column3) correct from
>(values(100,24,100));
>
>In this example removing the parentheses is a simple solution.
>But if the expression was column1*(1+column2/column3) a cast is the
>only way?  (Hope not!)
>
>Anyway, if all this happens to be so by design, is there at least some
>way to default to float math rather than integer?
>
>Thanks.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Marc L. Allen]

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 [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) 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 dot to either number (e.g., 24. or 100.) I get the correct result.
But, with named fields, it’s not as easy as adding a dot:

select column1*(column2/column3) wrong from (values(100,24,100)); select 
column1*column2/column3 correct from (values(100,24,100));

So, to get correct answer I have to use a cast for either field?

select column1*(cast(column2 as float)/column3) correct from 
(values(100,24,100));

In this example removing the parentheses is a simple solution.
But if the expression was column1*(1+column2/column3) a cast is the only way?  
(Hope not!)

Anyway, if all this happens to be so by design, is there at least some way to 
default to float math rather than integer?

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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
> referenced elsewhere, my apologies if I missed it.
>
>
> I *think* there is a thread-safety issue in the ParseTrace() function:

Yeah, but ParseTrace() is a debugging function, not intended for use
in production systems.  It shouldn't ever be used by multiple threads.
It is not intended to be threadsafe.

If you found a thread-safety issue in the Parse() function, that would
be more interesting!



>
>   void ParseTrace(FILE *TraceFILE, char *zTracePrompt){
> yyTraceFILE = TraceFILE;
> yyTracePrompt = zTracePrompt;
> if( yyTraceFILE==0 ) yyTracePrompt = 0;
> else if( yyTracePrompt==0 ) yyTraceFILE = 0;
>   }
>
> It appears that `yyTraceFILE` and `yyTracePrompt` are global variables
> that can conceivably be written to simultaneously on two separate
> threads.  I suspect the negative effects of this would be low
> (overwriting of one prompt with another, and they would likely be
> identical strings anyway).
>
>
> It is detected by Xcode's Thread Sanitizer, and I wanted to report it in
> case there was a more untoward effect that I was missing.  If nothing
> needs to be done about it, that's fine too.
>
>
> Thanks!
>
> Fletcher
>
>
>
>
> --
> Fletcher T. Penney
> fletc...@fletcherpenney.net
> ___
> 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] [EXTERNAL] Re: SQLITE bug

[Richard Damon]

Depends on why you are restoring a backup (what data got corrupted). 
Even if you are restoring a full backup of all the tables, you still 
need to restore the ID fields rather than using the auto increment value 
or you still break the relationships.


On 9/4/17 2:42 AM, Hick Gunter wrote:

Are you really proposing to restore just one or a selected set of tables from a backup of the 
database? What state does a transaction that touches one or more tables that are restored and one 
or more tables that aren't go to? It can't be "commited" because some data is not in the 
final state, and it can't be "rolled back" because some data is not in it's original 
state.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Richard Damon
Gesendet: Sonntag, 03. September 2017 19: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 3, 2017 7:51 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SQLITE bug


Lastly, a comment I've made possibly more than once on this list:
There is no imperative to trust the SQL engine with ID assignments.
You are free to (and I prefer to) assign IDs yourself.

What exactly do you feel you benefit by taking ownership of the ID,
specifically that of which you feel supersedes the obvious perils in the cases 
you noted?

One BIG example of a place to overrule the default ID assignment via 
auto-increment is in restoring a backup. Here, you NEED to ID to be the same as 
before so Foreign keys in other tables stay pointing to the right record.

--
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
  Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Hick Gunter]

Are you really proposing to restore just one or a selected set of tables from a 
backup of the database? What state does a transaction that touches one or more 
tables that are restored and one or more tables that aren't go to? It can't be 
"commited" because some data is not in the final state, and it can't be "rolled 
back" because some data is not in it's original state.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Richard Damon
Gesendet: Sonntag, 03. September 2017 19: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 3, 2017 7:51 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] SQLITE bug
>
>> Lastly, a comment I've made possibly more than once on this list:
>> There is no imperative to trust the SQL engine with ID assignments.
>> You are free to (and I prefer to) assign IDs yourself.
> What exactly do you feel you benefit by taking ownership of the ID,
> specifically that of which you feel supersedes the obvious perils in the 
> cases you noted?
One BIG example of a place to overrule the default ID assignment via 
auto-increment is in restoring a backup. Here, you NEED to ID to be the same as 
before so Foreign keys in other tables stay pointing to the right record.

--
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.19.3 README.md Doc bug

[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 upacked source directory.  Thus, the bld directory would be created
under each source tree rather than a common bld directory where different
versions of the binaries would overwrite each other.



On Wed, Jun 14, 2017 at 8:48 AM, John McKown 
wrote:

> 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 sibling
> > directory
> > cd bld   ;#  Change to the build directory
> > ../sqlite/configure  ;#  Run the configure script
> >
> > Lost me there at the configure step...
> >
> > It seems to me the line should read simply
> >
> > ../configure  ;#  Run the configure script
> >
>
> ​Makes sense. At the time you issued the "tar" command, you are in
> directory "x". Perhaps the following will make more sense (stuff before the
> > is the current working directory)
>
> x>tar xzf sqlite.tar.gz
> x># above creates directory ./sqlite
> x>mkdir bld # make directory ./bld
> x>cd bld
> x/bld>../sqlite/configure
> x/bld> # up to directory x, then down in into directory sqlite & run
> configure residing there
> x/bld> # results of configure are put in this directory
> ​
>
>
> --
> Veni, Vidi, VISA: I came, I saw, I did a little shopping.
>
> Maranatha! <><
> John McKown
> ___
> 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

Re: [sqlite] 3.19.3 README.md Doc bug

[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 sibling
> directory
> cd bld   ;#  Change to the build directory
> ../sqlite/configure  ;#  Run the configure script
>
> Lost me there at the configure step...
>
> It seems to me the line should read simply
>
> ../configure  ;#  Run the configure script
>

​Makes sense. At the time you issued the "tar" command, you are in
directory "x". Perhaps the following will make more sense (stuff before the
> is the current working directory)

x>tar xzf sqlite.tar.gz
x># above creates directory ./sqlite
x>mkdir bld # make directory ./bld
x>cd bld
x/bld>../sqlite/configure
x/bld> # up to directory x, then down in into directory sqlite & run
configure residing there
x/bld> # results of configure are put in this directory
​


-- 
Veni, Vidi, VISA: I came, I saw, I did a little shopping.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Hick Gunter]

There are a couple more cases of Tcl_AppendResults( ..., 0) in tclsqlite.c

Unfortunately, Tcl_AppendResults() is defined as having varargs and thus 
lacking type checking.

I would prefer NULL over (char*)0 anyway, which BTW is also present at least 
once in tclsqlite.c

-Ursprüngliche 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 
Betreff: [sqlite] tclsqlite 64-bit bug [Was: extension to run bash]

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 the advisory locking semantics
> they expect, not the mandatory locking semantics Windows gives you by
> default.  (It’s more complicated than that, but I don’t want to go
> deeper into it here.)

Yeah ...  I'm slowly trying to submit all portability issues I discover back to 
the SQLite developers, so far with little success.

For example, the following patch fixes possible crashes in error-handling in 
64-bit builds (not Cygwin-specific). Explanation:
On 64-bit builds '0' is a 32-bit integer, but Tcl_AppendResult() expects a 
NULL-pointer as last element which is 64-bit.

Any chance for this to be in the next SQLite release?

Thanks!
Jan Nijtmans
===
$ fossil diff
Index: src/tclsqlite.c
==
--- src/tclsqlite.c
+++ src/tclsqlite.c
@@ -2534,11 +2534,11 @@
 for(i=3; i<(objc-1); i++){
   const char *z = Tcl_GetString(objv[i]);
   int n = strlen30(z);
   if( n>2 && strncmp(z, "-argcount",n)==0 ){
 if( i==(objc-2) ){
-  Tcl_AppendResult(interp, "option requires an argument: ", z, 0);
+  Tcl_AppendResult(interp, "option requires an argument: ",
z, (char *)0);
   return TCL_ERROR;
 }
 if( Tcl_GetIntFromObj(interp, objv[i+1], ) ) return TCL_ERROR;
 if( nArg<0 ){
   Tcl_AppendResult(interp, "number of arguments must be non-negative", 
@@ -2549,11 +2549,11 @@
   }else
   if( n>2 && strncmp(z, "-deterministic",n)==0 ){
 flags |= SQLITE_DETERMINISTIC;
   }else{
 Tcl_AppendResult(interp, "bad option \"", z,
-"\": must be -argcount or -deterministic", 0
+"\": must be -argcount or -deterministic", (char *)0
 );
 return TCL_ERROR;
   }
 }

@@ -3206,11 +3206,11 @@
 if( rc==SQLITE_OK ){
   Tcl_Obj *pObj;
   pObj = Tcl_NewStringObj((char*)sqlite3_value_text(pValue), -1);
   Tcl_SetObjResult(interp, pObj);
 }else{
-  Tcl_AppendResult(interp, sqlite3_errmsg(pDb->db), 0);
+  Tcl_AppendResult(interp, sqlite3_errmsg(pDb->db), (char *)0);
   return TCL_ERROR;
 }
   }
 }
 #endif /* SQLITE_ENABLE_PREUPDATE_HOOK */ 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A possible double bug?

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

I suspect the OP generated that number through computation, and it’s not 
exactly equal to a double parsed from the string “62.027393”. In other words, 
the OP ended up with a number n, such that
string(n) = “62.027393”  (using some particular format like “%.6f”)
but
n != parse(“62.027393”)

The original code must be doing Bind_double(n); whereas in your code the parser 
had to read the string “62.027393” at parse time and generate a double from it, 
which is not equal to n.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A possible double bug?

[David Raymond]

Discussions on floating point aside, I'm likewise getting results that are 
equal when trying it. So I'm curious as to the original poster's SQLite 
version, platform, language they're coding in, etc. When you run "select foo, 
typeof(foo) from test;" are you getting two results of (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: Re: [sqlite] A possible double bug?

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 (foo REAL)”. Foo field have “REAL” type affinity.

INSERT INTO test VALUES(62.027393);   or
INSERT INTO test VALUES(“62.027393”);

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.


#include 
#include 

int main() {
  sqlite3* db;
  sqlite3_stmt* stmt;

  sqlite3_open("double.sqlite", );
  sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS test(foo REAL);", 0, 0, 0);
  sqlite3_prepare(db, "INSERT INTO test VALUES(?)", -1, , 0);
  sqlite3_bind_text(stmt, 1, "62.027393", -1, SQLITE_STATIC);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  sqlite3_prepare(db, "INSERT INTO test VALUES(62.027393)", -1, , 0);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  sqlite3_close(db);
}


e:\Nana>sqlite3 double.sqlite

sqlite> select typeof(foo) from test;
real
real
sqlite> select * from test a cross join test b where a.foo=b.foo;
62.027393|62.027393
62.027393|62.027393
62.027393|62.027393
62.027393|62.027393
___
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

Re: [sqlite] A possible double bug?

[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 representations may be identical, while in others it may not be."

Does it mean the promise about cross-platform is broken?


A fair question, but no. Not as I see it, at least.

The statement - which you called a promise - still holds. It will work 
by just copying the data between 32-bit and 64-bit or BE and LE 
architecture.


The fact that two different implementations approximate a rational 
number differently does not violate that. See that fabs(a - b) > ε for 
some ε relatively close to zero and even if the value of fabs(a - b) may 
differ on these machines, the number is still a correct approximation 
for the rational in question. For instance, if it became a large 
negative number instead, it would be a portability problem, but this is 
a mere characteristic of a how floating point arithmetic works.


This is different from integer arithmetic, which is exact and should 
behave the same way on all systems, according to that statement.


--
Bernardo Sulzbach
http://www.mafagafogigante.org/
mafagafogiga...@mafagafogigante.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A possible double bug?

[Quan Yong Zhai]

"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 representations may be identical, while 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.sqlite.org>
主题: Re: [sqlite] A possible double bug?

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

fabs(a - b) is the simplest way to have an idea of how close two values
are, and dividing by the magnitude of one of them (after checking that
it is not zero, etc.) afterwards is also a good idea in some cases.

--
Bernardo Sulzbach
http://www.mafagafogigante.org/
mafagafogiga...@mafagafogigante.org
___
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

Re: [sqlite] A possible double bug?

[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 be.


fabs(a - b) is the simplest way to have an idea of how close two values 
are, and dividing by the magnitude of one of them (after checking that 
it is not zero, etc.) afterwards is also a good idea in some cases.


--
Bernardo Sulzbach
http://www.mafagafogigante.org/
mafagafogiga...@mafagafogigante.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A possible double bug?

[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 (foo REAL)”. Foo field have “REAL” type affinity.

INSERT INTO test VALUES(62.027393);   or
INSERT INTO test VALUES(“62.027393”);

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.


#include 
#include 

int main() {
  sqlite3* db;
  sqlite3_stmt* stmt;

  sqlite3_open("double.sqlite", );
  sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS test(foo REAL);", 0, 0, 0);
  sqlite3_prepare(db, "INSERT INTO test VALUES(?)", -1, , 0);
  sqlite3_bind_text(stmt, 1, "62.027393", -1, SQLITE_STATIC);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  sqlite3_prepare(db, "INSERT INTO test VALUES(62.027393)", -1, , 0);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  sqlite3_close(db);
}


e:\Nana>sqlite3 double.sqlite

sqlite> select typeof(foo) from test;
real
real
sqlite> select * from test a cross join test b where a.foo=b.foo;
62.027393|62.027393
62.027393|62.027393
62.027393|62.027393
62.027393|62.027393
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A possible double bug?

[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 representations in 
binary, just like 1/7 doesn’t in decimal. So most non-integers that look 
reasonable in decimal are in fact subject to round-off errors in binary 
floating point.)

As I said earlier today about strings: don’t hardcode data values into SQL 
statements. Use bindings instead.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A possible double bug?

[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
> as value with a prepared statement, for instance:

The value 62.027393 cannot be represented exactly in IEEE 754 double precision 
floating point.  The bounding values are 62.027392645 and 
62.027393355.  The 1 ULP (epsilon) value is 7.105427357601e-15.
 
> "CREATE TABLE test (foo REAL)"
> "INSERT INTO test (foo) VALUES (?)"
> "INSERT INTO test (foo) VALUES (62.027393)"
> "SELECT * FROM test"

> If the content of the table test, is printed, then the output of the
> values is equal i.e. 62.027393. However, if the stored value is compared 
> with for instance a cross join:
 
> select * from test as a cross join test as b where a.foo = b.foo;
 
> Then two rows are returned which indicates that the values are not equal
> (four rows should be returned if they are equal).

They are equal for all intents and purposes.  Your comparison is simply too 
exacting, requiring the approximations to be "equal", whereas both values are 
valid approximations of 62.027393.
 
> If the value 62.0273934 is inserted as value with the prepare
> statement instead of 62.027393 in the example, then the insert as String
> and the insert as prepare statement is equal.
 
> The double seems to be changed from 62.027393 to 62.0273934 when
> inserted as a String. This happens with some other values too (but not
> all).

> The values should be equal and i wonder if this is a bug or intendent
> behavior?

http://floating-point-gui.de/errors/comparison/
https://en.wikipedia.org/wiki/IEEE_floating_point

It is neither a bug nor intended behaviour.  It is simply how binary floating 
point works.  When you compare floating point numbers, you need to compute the 
distance between them in epsilon units of the comparand.  If they are within a 
reasonable "distance" of each other, then they are equal.

For example, if abs((x-y)/epsilon(x)) < T then then the numbers are equal.  For 
non-pathological computations, a value of 5 for T is more than adequate.





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 transformation algorithm can be changed to check the "subselect
> constraints" first?
>

Would be very clever indeed, to leverage the short-circuit semantic in that
case. Great idea! --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Hick Gunter]

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 transformation algorithm can be changed to check the "subselect 
constraints" first?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dan Kennedy
Gesendet: Freitag, 23. September 2016 11:28
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] Strange problem (pehaps bug) with sqlite + json

On 09/23/2016 03:09 PM, Gilles Vollant wrote:
> With sqlite.exe from tools zipfile, I reproduce the bug with this script:
>
> If I add, just after the create table the line
> create index if not exists idxtype on records(activitytype);
>
> I have a correct result
> If I don't add the index, I've "malformed json" error.
>
> Can you try reproduce?

Interesting one. The problem is that the query is being flattened by the SQLite 
optimizer. A simpler example is:

   CREATE TABLE t1(a, b);
   INSERT INTO t1 VALUES(4, 'nojson');
   SELECT json_extract(b, '$.Date') AS xyz FROM (SELECT * FROM t1 WHERE
a==22) WHERE xyz = ?;

In the above, the SELECT statement is being transformed to the following before 
being executed:

   SELECT json_extract(b, '$.Date') AS xyz FROM t1 WHERE xyz=? AND a=22;

And so the json_extract() function is executed despite the fact that the only 
row in the table does not match (a==22).

I'm not sure how to work around this. You could use a CASE statement I suppose. 
i.e. things like:

   SELECT CASE json_valid(b) THEN json_extract(b, '$.Date') ELSE NULL END AS xyz
   FROM (SELECT * FROM t1 WHERE a==22) WHERE xyz = ?;

Or avoid adding invalid json text to the table in the first place if it's 
possible.

Dan.





>
> Regards
> Gilles
>
>
> Here is the script without double quote
>
>
>
> drop table if exists records;
> create table if not exists records( activityId integer primary key,
> activityDate text, activityType integer,message text)   ;
>
> insert into records values (1,'2016-09-01',22,'{"Date":"09/01/2016
> 02:00:00","CountAnalyzedMails":44} '); insert into records values
> (3,'2016-09-02',22,'{"Date":"09/02/2016
> 02:00:00","CountAnalyzedMails":54} '); insert into records values
> (5,'2016-09-02',26,'nojson'); select * ,json_valid(message) from
> records;
>
> select activityDate,Date, CountEmails from (
>
> select activityDate, jsonstr, json_valid(jsonstr),
>   (activityDate) ||'_' || json_extract(jsonstr , '$.Date')   as multiinfo,
>   json_extract(jsonstr , '$.Date')  as Date,
>   json_extract(jsonstr , '$.CountAnalyzedMails') as CountEmails
>
>   from (
>  select *, length(message) as msglen,
>   message as jsonstr  ,
>   json_valid(message) as is_json_valid
>   from (select * from  records   where activitytype=22  ) where
> is_json_valid=1)
>
> ) as allrs0
>
>   where
>   multiinfo in
> (
> select max(activityDate) ||'_' || date  as concat_sel from
>
> (
> select activityDate, jsonstr, json_valid(jsonstr),
>   (activityDate) ||'_' || json_extract(jsonstr , '$.Date')   as multiinfo,
>   json_extract(jsonstr , '$.Date')  as Date,
>   json_extract(jsonstr , '$.CountAnalyzedMails') as CountEmails
>
>   from (
>
>  select *, length(message) as msglen,
>   message as jsonstr  ,
>   json_valid(message) as is_json_valid
>   from (select * from  records   where activitytype=22   ) where
> is_json_valid=1)
>
> ) as allrs1
>
>   group by date order by date);
>
>
> ___
> 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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Dan Kennedy]

On 09/23/2016 03:09 PM, Gilles Vollant wrote:

With sqlite.exe from tools zipfile, I reproduce the bug with this script:

If I add, just after the create table the line
create index if not exists idxtype on records(activitytype);

I have a correct result
If I don't add the index, I've "malformed json" error.

Can you try reproduce?


Interesting one. The problem is that the query is being flattened by the 
SQLite optimizer. A simpler example is:


  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(4, 'nojson');
  SELECT json_extract(b, '$.Date') AS xyz FROM (SELECT * FROM t1 WHERE 
a==22) WHERE xyz = ?;


In the above, the SELECT statement is being transformed to the following 
before being executed:


  SELECT json_extract(b, '$.Date') AS xyz FROM t1 WHERE xyz=? AND a=22;

And so the json_extract() function is executed despite the fact that the 
only row in the table does not match (a==22).


I'm not sure how to work around this. You could use a CASE statement I 
suppose. i.e. things like:


  SELECT CASE json_valid(b) THEN json_extract(b, '$.Date') ELSE NULL 
END AS xyz

  FROM (SELECT * FROM t1 WHERE a==22) WHERE xyz = ?;

Or avoid adding invalid json text to the table in the first place if 
it's possible.


Dan.







Regards
Gilles


Here is the script without double quote



drop table if exists records;
create table if not exists records( activityId integer primary key,
activityDate text, activityType integer,message text)   ;

insert into records values (1,'2016-09-01',22,'{"Date":"09/01/2016
02:00:00","CountAnalyzedMails":44} ');
insert into records values (3,'2016-09-02',22,'{"Date":"09/02/2016
02:00:00","CountAnalyzedMails":54} ');
insert into records values (5,'2016-09-02',26,'nojson');
select * ,json_valid(message) from records;

select activityDate,Date, CountEmails from
(

select activityDate, jsonstr, json_valid(jsonstr),
  (activityDate) ||'_' || json_extract(jsonstr , '$.Date')   as multiinfo,
  json_extract(jsonstr , '$.Date')  as Date,
  json_extract(jsonstr , '$.CountAnalyzedMails') as CountEmails

  from (
 select *, length(message) as msglen,
  message as jsonstr  ,
  json_valid(message) as is_json_valid
  from (select * from  records   where activitytype=22  ) where
is_json_valid=1)

) as allrs0

  where
  multiinfo in
(
select max(activityDate) ||'_' || date  as concat_sel from

(
select activityDate, jsonstr, json_valid(jsonstr),
  (activityDate) ||'_' || json_extract(jsonstr , '$.Date')   as multiinfo,
  json_extract(jsonstr , '$.Date')  as Date,
  json_extract(jsonstr , '$.CountAnalyzedMails') as CountEmails

  from (

 select *, length(message) as msglen,
  message as jsonstr  ,
  json_valid(message) as is_json_valid
  from (select * from  records   where activitytype=22   ) where
is_json_valid=1)

) as allrs1

  group by date order by date);


___
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

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

[Gilles Vollant]

With sqlite.exe from tools zipfile, I reproduce the bug with this script:

If I add, just after the create table the line
create index if not exists idxtype on records(activitytype);

I have a correct result
If I don't add the index, I've "malformed json" error.

Can you try reproduce?

Regards
Gilles


Here is the script without double quote



drop table if exists records;
create table if not exists records( activityId integer primary key,
activityDate text, activityType integer,message text)   ;

insert into records values (1,'2016-09-01',22,'{"Date":"09/01/2016
02:00:00","CountAnalyzedMails":44} ');
insert into records values (3,'2016-09-02',22,'{"Date":"09/02/2016
02:00:00","CountAnalyzedMails":54} ');
insert into records values (5,'2016-09-02',26,'nojson');
select * ,json_valid(message) from records;

select activityDate,Date, CountEmails from
(

select activityDate, jsonstr, json_valid(jsonstr),
 (activityDate) ||'_' || json_extract(jsonstr , '$.Date')   as multiinfo,
 json_extract(jsonstr , '$.Date')  as Date,
 json_extract(jsonstr , '$.CountAnalyzedMails') as CountEmails

 from (
select *, length(message) as msglen,
 message as jsonstr  ,
 json_valid(message) as is_json_valid
 from (select * from  records   where activitytype=22  ) where
is_json_valid=1)

) as allrs0

 where
 multiinfo in
(
select max(activityDate) ||'_' || date  as concat_sel from

(
select activityDate, jsonstr, json_valid(jsonstr),
 (activityDate) ||'_' || json_extract(jsonstr , '$.Date')   as multiinfo,
 json_extract(jsonstr , '$.Date')  as Date,
 json_extract(jsonstr , '$.CountAnalyzedMails') as CountEmails

 from (

select *, length(message) as msglen,
 message as jsonstr  ,
 json_valid(message) as is_json_valid
 from (select * from  records   where activitytype=22   ) where
is_json_valid=1)

) as allrs1

 group by date order by date);


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Gilles Vollant]

On 9/20/16, Richard Hipp wrote:

> Maybe don't double the double-quotes.  JSON expects just a single
> double-quote a either end of a string.

The tools I user (sqlite explorer) wanted it.
And  after I do
update  records set message=replace(message,'""','"');

So there is NO double quote in the database.

Regards
Gilles Vollant

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

On 9/20/16, Gilles Vollant  wrote:
>
> delete from records;
> insert into records values (1,'2016-09-01',22,'{""Date"":""09/01/2016
> 02:00:00"",""CountAnalyzedMails"":44} ');

Maybe don't double the double-quotes.  JSON expects just a single
double-quote a either end of a string.

-- 
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] Report a warning bug about Lemon parser

[Richard Hipp]

On 1/22/15, Tang Tianyong  wrote:
> Hi, yy_destructor function can not suppress warning about unused
> %extra_argument variable.

Sure it can.  Just add code to one of your destructors that references
the %extra_argument variable.  It doesn't have to actually do anything
with the variable.  If the variable is named "xyzzy" then it will
probably suffice to just say "(void)xyzzy;" inside one of your
destructors.

> My yy_destructor function that Lemon generated
> like this:
>
> ```
> static void yy_destructor(
>   yyParser *yypParser,/* The parser */
>   YYCODETYPE yymajor, /* Type code for object to destroy */
>   YYMINORTYPE *yypminor   /* The object to be destroyed */
> ){
>   COSStyleParseARG_FETCH;
>   switch( yymajor ){
> /* Here is inserted the actions which take place when a
> ** terminal or non-terminal is destroyed.  This can happen
> ** when the symbol is popped from the stack during a
> ** reduce or during error processing or when a parser is
> ** being destroyed before it is finished parsing.
> **
> ** Note: during a reduce, the only symbols destroyed are those
> ** which appear on the RHS of the rule, but which are not used
> ** inside the C code.
> */
> default:  break;   /* If no destructor action specified: do nothing */
>   }
> }
> ```
>
> --
> *By tan...@gmail.com *
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite problem possible bug

[RSmith]

On 2015/01/07 12:13, The Responsa Project wrote:

To Whom it amy concern


I am trying to use SQLITE and the like statement with wildcards and hebrew

when I put in an english string it works correctly, such as

Select  * from dbname where colname like '%123%'

I will get all the entries from that column that contain 123 anywhere in the 
column.

However if I substitute 123 with hebrew letters - it matches all the entries, 
not just the ones containing what I asked for.

If I do not use the wilcards in the like it matches the exact word properly. In 
version 3.2.2 of sqlite this worked fine (with wildcards), later versions it does 
not. So for example "SELECT * from dbname where colname like '%אב%' will give 
me all the entries not only the ones matching only אב.

I tried GLOB, which also did not work.?


I would like to (and need to) upgrade to the latest version of Sqlite but I 
cannot because of this issue.


Is this a bug? Am I doing something wrong?


Not a bug in the latest version - works fine for me, but I am not sure which other versions you have tested. Are you using the C api 
directly or going through some wrapper? (It might mess with the UTF8 or whatever encoding you start off with). Is  your DB in UTF-8 
mode?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite problem possible bug

[Richard Hipp]

On 1/7/15, The Responsa Project  wrote:
> To Whom it amy concern
>
>
> I am trying to use SQLITE and the like statement with wildcards and hebrew
>
> when I put in an english string it works correctly, such as
>
> Select  * from dbname where colname like '%123%'
>
> I will get all the entries from that column that contain 123 anywhere in the
> column.
>
> However if I substitute 123 with hebrew letters - it matches all the
> entries, not just the ones containing what I asked for.
>
> If I do not use the wilcards in the like it matches the exact word properly.
> In version 3.2.2 of sqlite this worked fine (with wildcards), later versions
> it does not. So for example "SELECT * from dbname where colname like '%אב%'
> will give me all the entries not only the ones matching only אב.
>

It should work.  Here is the test case I used:

CREATE TABLE t1(x TEXT);
INSERT INTO t1(x) VALUES('abc'),('אב'),
  ('בְּרֵאשִׁ֖ית בָּרָ֣א אֱלֹהִ֑ים אֵ֥ת הַשָּׁמַ֖יִם וְאֵ֥ת הָאָֽרֶץ'),
  ('וְהָאָ֗רֶץ הָיְתָ֥ה תֹ֙הוּ֙ וָבֹ֔הוּ '),('xyz');
.print --- all ---
SELECT rowid, x FROM t1;
.print --- Using %אב% ---
SELECT rowid, x FROM t1 WHERE x LIKE '%אב%';

The above gives me this output:

--- all ---
1|abc
2|אב
3|בְּרֵאשִׁ֖ית בָּרָ֣א אֱלֹהִ֑ים אֵ֥ת הַשָּׁמַ֖יִם וְאֵ֥ת הָאָֽרֶץ
4|וְהָאָ֗רֶץ הָיְתָ֥ה תֹ֙הוּ֙ וָבֹ֔הוּ
5|xyz
--- Using %אב% ---
2|אב

Which is exactly what you would expect, no?

Perhaps you can give us more details about how you are invoking
SQLite.  The problem might be in the interface to your programming
language, not in SQLite itself.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite problem possible bug

[Yongil Jang]

How about to use dynamic binding?
For example, is your SQL(SELECT * from dbname where colname like '%אב%'),
use '?' instead of 'אב'.
In my guess, 'אב' can have same ASCII code of wildcard(%).

Full SQL can be as like as follows.

SELECT * from dbname where colname like '%?%'

To do this, you need to use sqlite3_bind*** functions in c API.

Regards
YONGIL.
2015. 1. 7. 오후 9:34에 "The Responsa Project" 님이 작성:

> To Whom it amy concern
>
>
> I am trying to use SQLITE and the like statement with wildcards and hebrew
>
> when I put in an english string it works correctly, such as
>
> Select  * from dbname where colname like '%123%'
>
> I will get all the entries from that column that contain 123 anywhere in
> the column.
>
> However if I substitute 123 with hebrew letters - it matches all the
> entries, not just the ones containing what I asked for.
>
> If I do not use the wilcards in the like it matches the exact word
> properly. In version 3.2.2 of sqlite this worked fine (with wildcards),
> later versions it does not. So for example "SELECT * from dbname where
> colname like '%אב%' will give me all the entries not only the ones matching
> only אב.
>
> I tried GLOB, which also did not work.?
>
>
> I would like to (and need to) upgrade to the latest version of Sqlite but
> I cannot because of this issue.
>
>
> Is this a bug? Am I doing something wrong?
>
>
> All help is appreciated, thanks in advance
>
>
> Sincerely,Sharon Gottlieb
>
>
> The Responsa Project
> Bar-Ilan University
> Ramat-Gan 52900, ISRAEL
> Tel: 972-3-5318-411 / Fax: 972-3-5341-850
> Email: respo...@mail.biu.ac.il
> Internet http://responsa.biu.ac.il
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug? autoincrement in int primary key vs integer primary key

[Paul Sanderson]

Thanks for the link Bernard
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 16 November 2014 16:07, Bernardo Sulzbach  wrote:
> You are supposing that INT PRIMARY KEY == INTEGER PRIMARY KEY. Which,
> clearly, is not true.
> http://stackoverflow.com/questions/20289410/difference-between-int-primary-key-and-integer-primary-key-sqlite
> see this link for more on the subject.
>
> 2014-11-16 13:56 GMT-02:00 Igor Tandetnik :
>
>> On 11/16/2014 10:51 AM, Paul Sanderson wrote:
>>
>>> AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
>>>
>>
>> No it's not a bug. AUTOINCREMENT is only allowed on INTEGER PRIMARY KEY.
>> Which part of the error message do you find unclear?
>>
>> For details, see http://www.sqlite.org/autoinc.html
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Bernardo Sulzbach
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug? autoincrement in int primary key vs integer primary key

[Bernardo Sulzbach]

You are supposing that INT PRIMARY KEY == INTEGER PRIMARY KEY. Which,
clearly, is not true.
http://stackoverflow.com/questions/20289410/difference-between-int-primary-key-and-integer-primary-key-sqlite
see this link for more on the subject.

2014-11-16 13:56 GMT-02:00 Igor Tandetnik :

> On 11/16/2014 10:51 AM, Paul Sanderson wrote:
>
>> AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
>>
>
> No it's not a bug. AUTOINCREMENT is only allowed on INTEGER PRIMARY KEY.
> Which part of the error message do you find unclear?
>
> For details, see http://www.sqlite.org/autoinc.html
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Bernardo Sulzbach
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug? autoincrement in int primary key vs integer primary key

[Igor Tandetnik]

On 11/16/2014 10:51 AM, Paul Sanderson wrote:

AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY


No it's not a bug. AUTOINCREMENT is only allowed on INTEGER PRIMARY KEY. 
Which part of the error message do you find unclear?


For details, see http://www.sqlite.org/autoinc.html

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it a bug ?

[Simon Slavin]

On 6 Nov 2014, at 3:13am, Andrei Yakimov  wrote:

> Problem is incorrect journal file, which is created on system reboot.
> Reproduce this condition relativity simple:
> 
> step 1:  we open db
> step 2:  write/update something to db.
> step 3:  switch journal to memory
> step 4:  write/update something to db.
> Do not close you SW keep it running and DB open
> step 5:  reboot your system or kill you SW not gracefully.

Please see



"The MEMORY journaling mode stores the rollback journal in volatile RAM. This 
saves disk I/O but at the expense of database safety and integrity. If the 
application using SQLite crashes in the middle of a transaction when the MEMORY 
journaling mode is set, then the database file will very likely go corrupt."

In other words, SQLite is operating as designed.  The ability to keep the 
journal in memory is supplied for databases where, if the system crashes, you 
would have to begin the task again anyway.  If you need your database to be 
recoverable after a crash, sorry, but you can't use that mode.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Curious datetime/strftime BUG?

[John]

On 6/10/2014 18:35, Clemens Ladisch wrote:

John wrote:

On 5/10/2014 19:59, Clemens Ladisch wrote:

The documentation  says:
| These functions only work for dates between -01-01 00:00:00 and
| -12-31 23:59:59. For dates outside that range, the results of
| these functions are undefined.


All equivalent functions should return consistent results.


Why do you assume that "undefined" should imply consistency?  datetime()
could return "Cthulhu fhtagn" at the Ides of any month in such a year,
and there would be nothing wrong with it.  Undefined allows _anything_.



Ok Clemens, I concede, undefined does allow _anything_. God, it's got to 
be 40 years since I read Lovecraft.


Regards,
John

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Curious datetime/strftime BUG?

[Clemens Ladisch]

John wrote:
> On 5/10/2014 19:59, Clemens Ladisch wrote:
>> The documentation  says:
>> | These functions only work for dates between -01-01 00:00:00 and
>> | -12-31 23:59:59. For dates outside that range, the results of
>> | these functions are undefined.
>
> All equivalent functions should return consistent results.

Why do you assume that "undefined" should imply consistency?  datetime()
could return "Cthulhu fhtagn" at the Ides of any month in such a year,
and there would be nothing wrong with it.  Undefined allows _anything_.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Curious datetime/strftime BUG?

[John]

Sorry Clemens,

but you have missed the point, the point being that the year component 
of the date string returned by strftime for -ve (or BCE) years are 
truncated to the -ve sign + the first three digits of the year. This is 
not the case for the result returned by date or datetime, which return 
the -ve sign + the full four digits of the year.


All equivalent functions should return consistent results. I suspect it 
is a formatting issue in the output of strftime.


As to the reference below, Gregorian calendar dates are only accurate 
after its introduction in 1582, the proleptic Gregorian calendar 
projects the Gregorian calendar back from 1582 so that:


Gregorian 0001-01-01 == Julian 0001-01-01

Gregorian dates prior to that point in time are somewhat meaningless 
except as a reference to elapsed time wrt now.


Future dates are also somewhat meaningless except as a reference to 
elapsed time wrt now. I suspect the reference to the year  has to do 
with year format limited to four characters "", assuming, of coarse, 
that our descendants are still using the Gregorian calendar then.


The Julian Day Count (not the same Julius as the Julian Calendar) is an 
integer count of days from a reference point and is accurate up to the 
unsigned integer limit of the math module used to calculate it.


People who use dates outside stated limits below are usually well aware 
of the issues involved.


My issue, as stated above, is to do with the inconsistent returns from 
equivalent functions. This, I think should be corrected.


Regards,
John


On 5/10/2014 19:59, Clemens Ladisch wrote:

John wrote:

-4713-11-25 12:00:00<-- 4 digit year
-471-11-25 12:00:00 <-- 3 digit year
-001-12-31 <-- 3 digit year
-001-12-31 <-- 3 digit year
-1975-10-21<-- 4 digit year
-197-10-21 <-- 3 digit year


The documentation  says:
| These functions only work for dates between -01-01 00:00:00 and
| -12-31 23:59:59. For dates outside that range, the results of
| these functions are undefined.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users