[sqlite] SpeedTest1 Comparison of 32 vs 64 bit on Windows 10 13483.15

[Keith Medcalf]

Same code, same compile options, same compiler version
options -s -O3 -pipe -march=native -mtune=native -falign-functions=16 
-falign-loops=16 -flto

32-bit GCC 4.9.3 

>speedtest1 --size 1000
-- Speedtest1 for SQLite 3.15.0 2016-08-17 11:14:39 
a861713cc6a3868a1c89240e8340bc
 100 - 50 INSERTs into table with no index.0.781s
 110 - 50 ordered INSERTS with one index/PK1.266s
 120 - 50 unordered INSERTS with one index/PK..1.672s
 130 - 25 SELECTS, numeric BETWEEN, unindexed..1.281s
 140 - 10 SELECTS, LIKE, unindexed.3.031s
 142 - 10 SELECTS w/ORDER BY, unindexed3.032s
 145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..3.063s
 150 - CREATE INDEX five times.2.641s
 160 - 10 SELECTS, numeric BETWEEN, indexed8.814s
 161 - 10 SELECTS, numeric BETWEEN, PK.9.282s
 170 - 10 SELECTS, text BETWEEN, indexed...3.241s
 180 - 50 INSERTS with three indexes...2.781s
 190 - DELETE and REFILL one table.3.111s
 200 - VACUUM..2.282s
 210 - ALTER TABLE ADD COLUMN, and query...0.094s
 230 - 10 UPDATES, numeric BETWEEN, indexed8.969s
 240 - 50 UPDATES of individual rows...1.641s
 250 - One big UPDATE of the whole 50-row table0.453s
 260 - Query added column after filling0.078s
 270 - 10 DELETEs, numeric BETWEEN, indexed2.235s
 280 - 50 DELETEs of individual rows...2.078s
 290 - Refill two 50-row tables using REPLACE..5.110s
 300 - Refill a 50-row table using (b&1)==(a&1)2.798s
 310 - 10 four-ways joins..5.320s
 320 - subquery in result set..   22.936s
 980 - PRAGMA integrity_check..4.969s
 990 - ANALYZE.1.657s
   TOTAL...  104.616s

64-bit GCC 4.9.3

>speedtest1 --size 1000
-- Speedtest1 for SQLite 3.15.0 2016-08-17 11:14:39 
a861713cc6a3868a1c89240e8340bc
 100 - 50 INSERTs into table with no index.0.797s
 110 - 50 ordered INSERTS with one index/PK1.250s
 120 - 50 unordered INSERTS with one index/PK..1.609s
 130 - 25 SELECTS, numeric BETWEEN, unindexed..0.969s
 140 - 10 SELECTS, LIKE, unindexed.2.859s
 142 - 10 SELECTS w/ORDER BY, unindexed2.860s
 145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..2.813s
 150 - CREATE INDEX five times.2.219s
 160 - 10 SELECTS, numeric BETWEEN, indexed6.751s
 161 - 10 SELECTS, numeric BETWEEN, PK.7.229s
 170 - 10 SELECTS, text BETWEEN, indexed...2.719s
 180 - 50 INSERTS with three indexes...2.266s
 190 - DELETE and REFILL one table.2.266s
 200 - VACUUM..1.735s
 210 - ALTER TABLE ADD COLUMN, and query...0.062s
 230 - 10 UPDATES, numeric BETWEEN, indexed7.329s
 240 - 50 UPDATES of individual rows...1.516s
 250 - One big UPDATE of the whole 50-row table0.437s
 260 - Query added column after filling0.047s
 270 - 10 DELETEs, numeric BETWEEN, indexed2.047s
 280 - 50 DELETEs of individual rows...1.938s
 290 - Refill two 50-row tables using REPLACE..4.438s
 300 - Refill a 50-row table using (b&1)==(a&1)2.360s
 310 - 10 four-ways joins..5.109s
 320 - subquery in result set..   23.006s
 980 - PRAGMA integrity_check..4.563s
 990 - ANALYZE.1.501s
   TOTAL...   92.695s

CPU Core at 100% for entire test, x64 code is ~10% faster than x32 code.


---
Life should not be a journey to the grave with the intention of arriving safely 
in a pretty and well preserved body, but rather to skid in 

Re: [sqlite] 64-bit SQLite3.exe

[Michael Gratton]

Richard,

On Thu, Aug 18, 2016 at 2:23 AM, Rousselot, Richard A 
 wrote:
I was only interested in doing these calculations quickly; that is my 
real-world.  The SQL scripts I am using were built with multiple 
steps with intermediate temp tables that are used to create a final 
result table.


I understand my needs are not generally how others use SQLite.  I am 
using it as a platform for fast calculations that happens to store 
results in a way that can be quarried.  Every time, my process begins 
all prior results are deleted, only the input tables stay the same.



Out of curiosity, are you using these in an end-user application, i.e. 
as an interactive response to user actions? I would be curious to know 
if building intermediate temporary tables is fast enough for returning 
results for such uses.


Thanks,
//Mike

--
⊨ Michael Gratton, Percept Wrangler.
⚙ 


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

[sqlite] AsyncTableQuery "Where" clause can't handle "OR" (||).

[Tifa Lockhart]

Sorry got confused with Sqlite sites, created this ticket at
system.data.sqlite.org yesterday.

Regards,

Winter



Ticket UUID: 4ca56c780c92f6e308abf1ad5bb76be2a3e29a68
Title: AsyncTableQuery "Where" clause can't handle "OR" (||).
Status: Open Type: Code_Defect
Severity: Important Priority:
Subsystem: Resolution:
Last Modified: 2016-08-16 09:04:24
Version Found In: v3.1.1
User Comments:
anonymous added on 2016-08-16 09:04:24:

Table has data with ID 1.

var filteredIDs = new List{1};

_connection.Table().Where(x=>filteredIDs.Contains(x.ID)).ToListAsync()
Result has 1 row returned.

_connection.Table().Where(x=>filteredIDs.Count==0||filteredIDs.Contains(x.ID)).ToListAsync()
Result has 0 row returned.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Syntax fault on UPDATE statement

[flo]

Effectively,

Sorry about my mistake.

2016-08-17 10:33 GMT+02:00 Richard Hipp :

> On 8/17/16, flo  wrote:
> >
> > $ sqlite3 test.db "UPDATE test SET id=0 AND name='new_name' AND age=30
> > WHERE id=1;"
>
> The above is parsed like this:
>
>   UPDATE test SET id = (0 AND name='new_name' AND age=30) WHERE id=1;
>
> And since the expression in parentheses always evaluates to 0, the
> above is equivalent to:
>
>   UPDATE test SET id=0 WHERE id=1;
>
> Which is exactly what SQLite is doing.
>
> --
> 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] 64-bit SQLite3.exe

[Rousselot, Richard A]

Simon,

I was only interested in doing these calculations quickly; that is my 
real-world.  The SQL scripts I am using were built with multiple steps with 
intermediate temp tables that are used to create a final result table.

I understand my needs are not generally how others use SQLite.  I am using it 
as a platform for fast calculations that happens to store results in a way that 
can be quarried.  Every time, my process begins all prior results are deleted, 
only the input tables stay the same.

I will pass the baton on to someone else to do more realistic, real world, 
testing of 32-bit vs 64-bit.

Richard

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Wednesday, August 17, 2016 11:09 AM
To: SQLite mailing list
Subject: Re: [sqlite] 64-bit SQLite3.exe


On 17 Aug 2016, at 4:54pm, Rousselot, Richard A 
 wrote:

>PRAGMA journal_mode = Off;

This configuration is designed for cases where the resulting database will be 
thrown away soon (e.g. when the application quits).  It does increase speed 
greatly, but a consequence is that the benchmark is not useful as an indicator 
of performance in the real world.

Some of your other PRAGMAs also seem to be suitable only to running benchmarks 
quickly.  A fairer test, one more like real world performance, would be not to 
use any PRAGMAs at all apart from turning on FOREIGN KEYS.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
This communication is the property of CenturyLink and may contain confidential 
or privileged information. Unauthorized use of this communication is strictly 
prohibited and may be unlawful. If you have received this communication in 
error, please immediately notify the sender by reply e-mail and destroy all 
copies of the communication and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-bit SQLite3.exe

[Simon Slavin]

On 17 Aug 2016, at 4:54pm, Rousselot, Richard A 
 wrote:

>PRAGMA journal_mode = Off;

This configuration is designed for cases where the resulting database will be 
thrown away soon (e.g. when the application quits).  It does increase speed 
greatly, but a consequence is that the benchmark is not useful as an indicator 
of performance in the real world.

Some of your other PRAGMAs also seem to be suitable only to running benchmarks 
quickly.  A fairer test, one more like real world performance, would be not to 
use any PRAGMAs at all apart from turning on FOREIGN KEYS.

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

Re: [sqlite] How to customize "Bytes of unused 'reserved' spaceat the end of each page" ?

[Richard Hipp]

On 8/17/16, sanhua.zh  wrote:
> sqlite3_test_control() is an interface for testing. Is it safe to use it in
> released product?

It is "safe" if you statically link against a version of SQLite that
supports the behavior you want.  It is not safe if you try to use
whatever DLL/shared-library happens to be on the system.

Note also that the sqlite3_test_control() interface is omitted from
the SQLite shared library that comes bundled with Android.

-- 
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] 64-bit SQLite3.exe

[Rousselot, Richard A]

Speed improvements based on 64-bit MinGW.

SQLite 3.14.2, built with MinGW 64-bit. - 
https://sourceforge.net/projects/mingw-w64/?source=typ_redirect
gcc -m64 -O2 -DSQLITE_THREADSAFE=0 shell.c sqlite3.c -o sqlite3.exe
gcc -g -shared extension-functions.c -o extension-function.dll
Built with SQLITE options of default

Module SQL Script (146k records)T1 Sec  T2 Sec  T3 Sec  T4 Sec  Delta   
Delta   Delta
Processing Module 1 of 8: (CTEs)138 110 92  49  
125%150%282%
Processing Module 2 of 8:   26  20  17  14  130%
153%186%
Processing Module 3 of 8:   34  28  23  18  121%
148%189%
Processing Module 4 of 8:   14  7   6   5   200%
233%280%
Processing Module 5 of 8:   9   6   6   4   150%
150%225%
Processing Module 6 of 8:   30  14  12  11  214%
250%273%
Processing Module 7 of 8: (CTEs)290 222 212 86  131%
137%337%
Processing Module 8 of 8:   14  14  10  10  100%
140%140%
Total Processing Time:  557 424 374 200 131%
149%279%

Test 1: 32-Bit 3.13 (sqlite.org), No PRAGMA (Baseline)
Test 2: 32-bit 3.13 (sqlite.org), with PRAGMA but not temp_store = 2;
Test 3: 64 bit 3.14.2 MinGW, with PRAGMA but not temp_store = 2;
Test 4: 64 bit 3.14.2 MinGW, all PRAGMA

PRAGMAs used...
PRAGMA synchronous = 0;
PRAGMA read_uncommitted = 1;
PRAGMA locking_mode = exclusive;
PRAGMA journal_mode = Off;
PRAGMA temp_store = 2;

For reference both sets of tests on same machine; Windows 10, i5-3470 3.20GHz, 
12GB RAM and on internal spinning HDD.  There is a slight improvemnt (~5% 
faster) going to SSD but wanted to stay consistent.

I don't plan on testing Cygwin, I am happy with the MinGW results.

Thanks everyone for your help.

Richard

-Original Message-
From: Rousselot, Richard A
Sent: Monday, August 15, 2016 12:20 PM
To: sqlite-users@mailinglists.sqlite.org
Cc: Rousselot, Richard A
Subject: Re: [sqlite] 64-bit SQLite3.exe

Speed improvements based on 64-bit Visual Studio build.

SQLite 3.14.0, built with MSVC 18.00.30723 for x64.
Built with VS options /O2 /Ot /Ox.
Built with SQLITE options of SQLITE_ENABLE_FTS3 SQLITE_ENABLE_STAT2 
SQLITE_ENABLE_RTREE.

Module SQL Script (146k records)T1 Sec  T2 Sec  T3 Sec  T4 Sec  Sp Inc  
Sp Inc  Sp Inc
Processing Module 1 of 8: (CTEs)138  11096  56  
125%144%246%
Processing Module 2 of 8:   26  20  18  15  130%
144%173%
Processing Module 3 of 8:   34  28  26  21  121%
131%162%
Processing Module 4 of 8:   14  7   7   6   200%
200%233%
Processing Module 5 of 8:   9   6   5   4   150%
180%225%
Processing Module 6 of 8:   30  14  13  11  214%
231%273%
Processing Module 7 of 8: (CTEs)290 222 194 100 131%
149%290%
Processing Module 8 of 8:   14  14  11  11  100%
127%127%
Total Processing Time:  557 424 372 227 131%
150%245%

Test 1: 32-Bit 3.13 (sqlite.org), No PRAGMA (Baseline)
Test 2: 32-bit 3.13 (sqlite.org), with PRAGMA but not temp_store = 2;
Test 3: 64 bit 3.14.0 Visual Studio 18, with PRAGMA but not temp_store = 2;
Test 4: 64 bit 3.14.0 Visual Studio 18, all PRAGMA

PRAGMAs used...
PRAGMA synchronous = 0;
PRAGMA read_uncommitted = 1;
PRAGMA locking_mode = exclusive;
PRAGMA journal_mode = Off;
PRAGMA temp_store = 2;

I am still trying to figure out how to make a 64-bit build in MinGW.  There is 
a nice installer for 32-bit but not for 64-bit, go figure.

Hopefully I have the PRAGMAs right.  If there is a better configuration for 
speed, let me know.

Richard
This communication is the property of CenturyLink and may contain confidential 
or privileged information. Unauthorized use of this communication is strictly 
prohibited and may be unlawful. If you have received this communication in 
error, please immediately notify the sender by reply e-mail and destroy all 
copies of the communication and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] trouble with sqlite .import

[Aaron Paul]

Hi Richard,

Thanks for your answer.  I had been looking at the csv exported data in Excel 
and it looked right.  

I opened the csv file in a plain text editor and it was as you said, the data 
was already in date format.  Formatting the Column and re-exporting the csv 
file corrected my issue.

Much appreciated.


Aaron Paul
kalisti...@yahoo.com



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

Re: [sqlite] How to customize "Bytes of unused 'reserved' spaceat the end of each page" ?

[Clemens Ladisch]

sanhua.zh wrote:
> sqlite3_test_control() is an interface for testing. Is it safe to use it in 
> released product?

This question does not make sense.  You need to modify your copy of the
SQLite library to actually do something with the reserved bytes, so you
have complete control over setting SQLITE_OMIT_BUILTIN_TEST or not.


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

Re: [sqlite] page_size on ATTACH-ed databases

[Clemens Ladisch]

Ward WIllats wrote:
> sqlite> attach database '/tmp/RareData.db' as rd;   < ATTACH SECOND DB
> sqlite> pragma page_size=512;   <- SET MAIN DB PAGE SIZE
> ...
> sqlite> pragma journal_mode=WAL;

This sets the journal mode of _both_ databases to WAL.
This requires that both database files are actually created.

> sqlite> pragma rd.page_size=4096; <- SET ATTACHED DB PAGE SIZE TO 
> 4K

And now it's too late.


To execute a PRAGMA only on the main database, use:

  PRAGMA main.journal_mode=WAL;


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

Re: [sqlite] SQL Syntax fault on UPDATE statement

[R Smith]

On 2016/08/17 11:04 AM, Simon Davies wrote:

On 17 August 2016 at 09:39, R Smith  wrote:


On 2016/08/17 9:05 AM, flo wrote:

Hi everyone,

.
.
.

Well, it is perfectly valid to give boolean operations as an expression.
If I said " id = 3 AND 6 then the resulting value would be 2  (If you are
unsure why that is you need to read up on Boolean logic, check google for
it)

Boolean AND:
sqlite> select 3 and 6;
1

Bitwise and:
sqlite> select 3 & 6;
2


Indeed - thanks Simon.

I wasn't actually thinking in SQL terms there, just trying to explain a 
principle - which I should have checked to be clear on to the OP.


To be sure - "bitwise" isn't something else, it is still Boolean, it's 
just a way of doing boolean logic per bit (what makes the world of 
computing possible) as opposed to regarding the input as a single entity 
that evaluates to TRUE or FALSE. The fact that 3 and 6 both evaluate to 
TRUE (Because >0) in SQLite means they are regarded as entities (as they 
should when you use the word "AND" in stead of "&") and so rightly 
evaluate to TRUE (1) as above.


As an aside - I come from programming languages where that difference 
did not exist and "(3 and 6) --> 2", and "((i>0) or 2) -> 3" where i>0, 
which made setting flags and such much much faster and is much better to 
my mind with the small caveat that you couldn't hide error-return values 
in Boolean guise or directly refer a return value that might have 
multiple possible values (but you only want to know if it is or isn't 
so) which is a better construct for SQL.



In those days bytes were expensive. Now you can dedicate Integers or 
even strings and a whole column to one flag state without feeling any 
guilt - viva progress!  :)



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

Re: [sqlite] SQL Syntax fault on UPDATE statement

[Simon Davies]

On 17 August 2016 at 09:39, R Smith  wrote:
>
>
> On 2016/08/17 9:05 AM, flo wrote:
>>
>> Hi everyone,
.
.
.
> Well, it is perfectly valid to give boolean operations as an expression.
> If I said " id = 3 AND 6 then the resulting value would be 2  (If you are
> unsure why that is you need to read up on Boolean logic, check google for
> it)

Boolean AND:
sqlite> select 3 and 6;
1

Bitwise and:
sqlite> select 3 & 6;
2

> I hope that makes it clear!
> Ryan

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

Re: [sqlite] trouble with sqlite .import

[R Smith]

On 2016/08/17 4:54 AM, Aaron Paul wrote:

Greetings,

I’m importing a csv file into a newly created table with .import.  One of the 
columns contain a numerical tag which sometimes resembles a date (example 
08-17).

These are not dates, but the .import is converting 08-17 into 17-Aug

The odd thing is it is doing this even when the date doesn’t make sense,   like 
35-September.


SQLIte (and I believe SQLite3.exe) is very much agnostic to input types, 
I don't think it will ever try to think for you (which is what makes it 
better than some other systems btw). That conversion is either happening 
before you import the data (if SQLite3.exe's output shows it wrong) or 
perhaps after or when you view it somewhere that tries to show dates 
nice. Feel free to send a small extract of the CSV file (which you 
.import) if you'd like us to confirm.


SQLite won't mess with it.

Cheers,
Ryan

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

Re: [sqlite] SQL Syntax fault on UPDATE statement

[R Smith]

On 2016/08/17 9:05 AM, flo wrote:

Hi everyone,

I found a reproducible bug on the SQL UPDATE statement parsing. Here is the
details.

I 've try to update some data on a SQLite database with a outlandish syntax
with "AND" between the columns to be update.  The SQL didn't fail but the
data update was incomplete.


The SQLite version :

$ sqlite3 -version
3.13.0 2016-05-18 10:57:30 fc49f556e48970561d7ab6a2f24fdd7d9eb81ff2


The data base schema :

$ sqlite3 test.db ".schema"
CREATE TABLE test(id interger, name varchar, age integer)


INTEGER is spelt wrong here (for id) - won't be a problem in this case, 
but might cause other non-expected things.





The initial stat of the table :

$ sqlite3 test.db "SELECT * FROM test;"
1|toto|10
2|tita|10
2|tita|10
1|toto|10
2|tita|10


The oulandish SQL UPDATE :

$ sqlite3 test.db "UPDATE test SET id=0 AND name='new_name' AND age=30
WHERE id=1;"


The stat of the table after the outlandish update :

$ sqlite3 test.db "SELECT * FROM test;"
0|toto|10
2|tita|10
2|tita|10
0|toto|10
2|tita|10

==> The "id" column was updated but not the two other columns "name" and
"age".


There is nothing outlandish about this, it's a normal statement and 
reads like normal SQL to the parser - I think all that happened is maybe 
your expected meaning is not aligned with what the Parser sees. Firstly, 
AND is a boolean operator in SQL, not a concatenation or grouping 
mechanism, for that we need comma - To try and explain, let me first 
show the correct way to update:


UPDATE test SET id = 0, name = 'new_name', age = 30 WHERE id = 1;

That must work correctly.
So why did it not fail then?

Well, it is perfectly valid to give boolean operations as an expression.
If I said " id = 3 AND 6 then the resulting value would be 2  (If you 
are unsure why that is you need to read up on Boolean logic, check 
google for it)
Also for evaluating booleans I could go if this_is_true AND this_is_true 
AND _this_is_true  then I will get a return value that is true (1) if 
all three are true, and false (0) otherwise.


What happened in your case is it checked whether id is 0 AND name is 
'new_name' AND age is 30... which of course it wasn't, so it returned 0 
(false) and so updated your id with a 0 value where id was 1.


Perfectly behaving as expected.

I hope that makes it clear!
Ryan

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

Re: [sqlite] SQL Syntax fault on UPDATE statement

[Hick Gunter]

Your UPDATE statement does not mean what you think it means.

UPDATE test SET id=0 AND name='new_name' AND age=30 WHERE id=1;

Is parsed as:

UPDATE test SET id = (0 AND name='new_name' AND age=30) WHERE id=1;

The expression (0 AND ...) will always evaluate to 0.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von flo
Gesendet: Mittwoch, 17. August 2016 09:05
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] SQL Syntax fault on UPDATE statement

Hi everyone,

I found a reproducible bug on the SQL UPDATE statement parsing. Here is the 
details.

I 've try to update some data on a SQLite database with a outlandish syntax 
with "AND" between the columns to be update.  The SQL didn't fail but the data 
update was incomplete.


The SQLite version :

$ sqlite3 -version
3.13.0 2016-05-18 10:57:30 fc49f556e48970561d7ab6a2f24fdd7d9eb81ff2


The data base schema :

$ sqlite3 test.db ".schema"
CREATE TABLE test(id interger, name varchar, age integer)


The initial stat of the table :

$ sqlite3 test.db "SELECT * FROM test;"
1|toto|10
2|tita|10
2|tita|10
1|toto|10
2|tita|10


The oulandish SQL UPDATE :

$ sqlite3 test.db "UPDATE test SET id=0 AND name='new_name' AND age=30 WHERE 
id=1;"


The stat of the table after the outlandish update :

$ sqlite3 test.db "SELECT * FROM test;"
0|toto|10
2|tita|10
2|tita|10
0|toto|10
2|tita|10

==> The "id" column was updated but not the two other columns "name" and "age".


A common SQL Update syntaxe work perfectly :

$ sqlite3 test.db "UPDATE test SET id=6, name='new_name', age=30 WHERE id=2;"

$ sqlite3 test.db "SELECT * FROM test;"
0|toto|10
6|new_name|30
6|new_name|30
0|toto|10
6|new_name|30


Good Luck

der.nairolf
___
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] SQL Syntax fault on UPDATE statement

[Richard Hipp]

On 8/17/16, flo  wrote:
>
> $ sqlite3 test.db "UPDATE test SET id=0 AND name='new_name' AND age=30
> WHERE id=1;"

The above is parsed like this:

  UPDATE test SET id = (0 AND name='new_name' AND age=30) WHERE id=1;

And since the expression in parentheses always evaluates to 0, the
above is equivalent to:

  UPDATE test SET id=0 WHERE id=1;

Which is exactly what SQLite is doing.

-- 
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] trouble with sqlite .import

[Richard Hipp]

On 8/16/16, Aaron Paul  wrote:
> Greetings,
>
> I’m importing a csv file into a newly created table with .import.  One of
> the columns contain a numerical tag which sometimes resembles a date
> (example 08-17).
>
> These are not dates, but the .import is converting 08-17 into 17-Aug

No it's not.  The sqlite3.exe command-line shell does not know how to
do such things, nor does the SQLite core.  This undesired conversion
must be happening somewhere else in your tool chain.

Double-check your CSV file.  Are you *sure* that Excel is not doing
the conversion of 08-17 to 17-Aug before generating the CSV?

How are you viewing the resulting database file?  Are you using
sqlite3.exe?  Or are you using a third-party tool that might be doing
this undesired conversion upon display?

>
> The odd thing is it is doing this even when the date doesn’t make sense,
> like 35-September.
>
> Any thoughts on how to avoid this?  There are about 1500 rows in this table
> and I’d rather import them correctly than try some scheme to clean it up,
> but thoughts on that would be appreciated if nothing else.
>
> Thanks!
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] trouble with sqlite .import

[Aaron Paul]

Greetings,

I’m importing a csv file into a newly created table with .import.  One of the 
columns contain a numerical tag which sometimes resembles a date (example 
08-17).  

These are not dates, but the .import is converting 08-17 into 17-Aug

The odd thing is it is doing this even when the date doesn’t make sense,   like 
35-September.

Any thoughts on how to avoid this?  There are about 1500 rows in this table and 
I’d rather import them correctly than try some scheme to clean it up, but 
thoughts on that would be appreciated if nothing else.

Thanks!


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

[sqlite] SQL Syntax fault on UPDATE statement

[flo]

Hi everyone,

I found a reproducible bug on the SQL UPDATE statement parsing. Here is the
details.

I 've try to update some data on a SQLite database with a outlandish syntax
with "AND" between the columns to be update.  The SQL didn't fail but the
data update was incomplete.


The SQLite version :

$ sqlite3 -version
3.13.0 2016-05-18 10:57:30 fc49f556e48970561d7ab6a2f24fdd7d9eb81ff2


The data base schema :

$ sqlite3 test.db ".schema"
CREATE TABLE test(id interger, name varchar, age integer)


The initial stat of the table :

$ sqlite3 test.db "SELECT * FROM test;"
1|toto|10
2|tita|10
2|tita|10
1|toto|10
2|tita|10


The oulandish SQL UPDATE :

$ sqlite3 test.db "UPDATE test SET id=0 AND name='new_name' AND age=30
WHERE id=1;"


The stat of the table after the outlandish update :

$ sqlite3 test.db "SELECT * FROM test;"
0|toto|10
2|tita|10
2|tita|10
0|toto|10
2|tita|10

==> The "id" column was updated but not the two other columns "name" and
"age".


A common SQL Update syntaxe work perfectly :

$ sqlite3 test.db "UPDATE test SET id=6, name='new_name', age=30 WHERE
id=2;"

$ sqlite3 test.db "SELECT * FROM test;"
0|toto|10
6|new_name|30
6|new_name|30
0|toto|10
6|new_name|30


Good Luck

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