Re: [sqlite] Bug report: Wrong column name in a table in a certain case

[Bernd Lehmkuhl]

Most probably not a bug. I asked something similar a while ago. It's as 
easy as https://sqlite.org/faq.html#q28 .
As long as you don't explicitly assign an alias to a column name, sqlite 
is not guaranteed to return what you might expect.



Am 11.08.2017 um 22:52 schrieb Jürgen Palm:

Hi,

please have a look at the following sequence of statements executed on 
Windows 10 with sqlite3.exe, version 3.19.3 and 3.20.0:


CREATE TABLE test("column with space" TEXT);
CREATE TABLE test2 AS SELECT "column with space" FROM test;
CREATE TABLE test3 AS SELECT "column with space" FROM test GROUP BY 1;
CREATE TABLE test4 AS SELECT "column with space" AS "column with space" 
FROM test GROUP BY 1;

SELECT * FROM sqlite_master;
table|test|test|2|CREATE TABLE test("column with space" TEXT)
table|test2|test2|3|CREATE TABLE test2("column with space" TEXT)
table|test3|test3|4|CREATE TABLE test3("""column with space""" TEXT)
table|test4|test4|5|CREATE TABLE test4("column with space" TEXT)

As you can see the table "test3" has extra double quotes around the 
column name "column with space". There should be no extra double quotes 
for this table and table "test3" should look like the tables "test2" and 
"test4".


Regards,
Jürgen
___
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] SQLite used in our network engine (SLikeNet)

[Stefan Hett]

Hi,

I'd just like to quickly reach out to let you know that we released a new (open 
source) network engine in which we also utilize SQLite and want to thank you 
for the work you put into SQLite.

SQLite is used as the backend db for the logging mechanism utilized in the 
network engine.

In order to not have this acknowledgement mail be misinterpreted as an 
advertisement mail from our side, I refrained from adding links to the project 
pages. If you are interested in the project, feel free to send me a mail and 
I'll give you the links to the project pages.

Please note that I'm not subscribed to the mailing list and would appreciate if 
you could CC me directly if you chose to reply to this mail.

-- 
Regards,
Stefan Hett, CEO SLikeSoft

SLikeSoft UG (haftungsbeschränkt), Dresdener Str. 8, 52068 Aachen, Germany
Tel: +49 157 74050303
Geschäftsführer: Stefan Hett, Handelsregister Aachen HRB 20706

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

Re: [sqlite] Bug report: Wrong column name in a table in a certain case

[Richard Hipp]

Now fixed on trunk.

On 8/11/17, Jürgen Palm  wrote:
> Hi,
>
> please have a look at the following sequence of statements executed on
> Windows 10 with sqlite3.exe, version 3.19.3 and 3.20.0:
>
> CREATE TABLE test("column with space" TEXT);
> CREATE TABLE test2 AS SELECT "column with space" FROM test;
> CREATE TABLE test3 AS SELECT "column with space" FROM test GROUP BY 1;
> CREATE TABLE test4 AS SELECT "column with space" AS "column with space"
> FROM test GROUP BY 1;
> SELECT * FROM sqlite_master;
> table|test|test|2|CREATE TABLE test("column with space" TEXT)
> table|test2|test2|3|CREATE TABLE test2("column with space" TEXT)
> table|test3|test3|4|CREATE TABLE test3("""column with space""" TEXT)
> table|test4|test4|5|CREATE TABLE test4("column with space" TEXT)
>
> As you can see the table "test3" has extra double quotes around the
> column name "column with space". There should be no extra double quotes
> for this table and table "test3" should look like the tables "test2" and
> "test4".
>
> Regards,
> Jürgen
> ___
> 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] Fwd: How can I make this faster?

[J Decker]

(Sent to wrong address to start; forwarded here(?))
-- Forwarded message --
From: J Decker 
Date: Sun, Aug 13, 2017 at 10:57 PM
Subject: How can I make this faster?
To: General Discussion of SQLite Database 


So I have this sql script that inserts into a single table, and it is VERY
slow.
it is even slower without journal_mode=WAL

Also,  is a unique constraint also an index?

if I use this in my memory mapped VFS it is MUCH faster (but it doesn't
supply mmap, so it's only using -journal that it creates and deletes a lot).

The following is a snippet of this much longer script
https://drive.google.com/open?id=0B812EYiKwtkkSVBUVWtpYUR4WW8

it's only 1687 lines and about 50% are select statements, I would expect
this to complete in under 1 second.not 22 seconds.

(the command should also be replace into, not insert into; but given the
constraint, I wouldn't think that will matter)
---

#PRAGMA journal_mode=WAL;;
select tbl_name,sql from sqlite_master where type='table' and name='record';
create table `record` (`soul` char,`field` char,`value` char,`relation`
char,`state` char,CONSTRAINT `record_unique` UNIQUE (`soul`,`field`) ON
CONFLICT REPLACE);
create index if not exists soul_index on record(soul);
create index if not exists soul_field_index on record(soul,field);
select * from Record where soul='db';
select state from Record where soul='db' and field='hello';
insert into Record (soul,field,value,relation,state)
values('db','hello','"world"',NULL,1502689508373);
select state from Record where soul='db' and field='other';
insert into Record (soul,field,value,relation,state)
values('db','other','"test"',NULL,1502689508408);
select state from Record where soul='j6bqj9ec1JUbWzaheTEO' and
field='field';
insert into Record (soul,field,value,relation,state)
values('j6bqj9ec1JUbWzaheTEO','field','"randomkey"',NULL,1502689508436.001);
select state from Record where soul='db' and field='j6bqj9ec1JUbWzaheTEO';
insert into Record (soul,field,value,relation,state) values('db','
j6bqj9ec1JUbWzaheTEO',NULL,'j6bqj9ec1JUbWzaheTEO',1502689508466);
select * from Record where soul='j6bqj9ec1JUbWzaheTEO';
select state from Record where soul='j6bqj9fz01aP8aM1rLy7R3U' and
field='field';
insert into Record (soul,field,value,relation,state) values('
j6bqj9fz01aP8aM1rLy7R3U','field','"randomkey"',NULL,1502689508495.002);
select state from Record where soul='db' and field='
j6bqj9fz01aP8aM1rLy7R3U';
insert into Record (soul,field,value,relation,state) values('db','
j6bqj9fz01aP8aM1rLy7R3U',NULL,'j6bqj9fz01aP8aM1rLy7R3U',1502689508528);
select * from Record where soul='j6bqj9fz01aP8aM1rLy7R3U';
select state from Record where soul='j6bqj9ho02jCZpwwI9kz4Vi' and
field='field';
insert into Record (soul,field,value,relation,state) values('
j6bqj9ho02jCZpwwI9kz4Vi','field','"randomkey"',NULL,1502689508556.003);
select state from Record where soul='db' and field='
j6bqj9ho02jCZpwwI9kz4Vi';
insert into Record (soul,field,value,relation,state) values('db','
j6bqj9ho02jCZpwwI9kz4Vi',NULL,'j6bqj9ho02jCZpwwI9kz4Vi',1502689508611);
select * from Record where soul='j6bqj9ho02jCZpwwI9kz4Vi';
select state from Record where soul='j6bqj9kgu96XPzRp4HfD' and
field='field';
insert into Record (soul,field,value,relation,state)
values('j6bqj9kgu96XPzRp4HfD','field','"randomkey"',NULL,1502689508656.001);
select * from Record where soul='j6bqj9ec1JUbWzaheTEO';
select * from Record where soul='j6bqj9fz01aP8aM1rLy7R3U';
select * from Record where soul='j6bqj9ho02jCZpwwI9kz4Vi';
select * from Record where soul='j6bqj9kgu96XPzRp4HfD';


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

Re: [sqlite] How can I make this faster?

[J Decker]

A note; I was testing this script with the lastest sqlite3.exe

sqlite3 test.db < sql.log

the selects are really fast; the inserts are REALLY slow.

My own code I use get_autocommit but never set_autocommit(if there is such
a thing)... otherwise I'm using sqlite3_open_v2; sqlite3_prepare_v2, step,
and finalize so pretty vanilla; would expect sqlite3 to not be much
smarter; and it performs just as badly as my own code.

On Sun, Aug 13, 2017 at 11:00 PM, J Decker  wrote:

> (Sent to wrong address to start; forwarded here(?))
>
> -- Forwarded message --
> From: J Decker 
> Date: Sun, Aug 13, 2017 at 10:57 PM
> Subject: How can I make this faster?
> To: General Discussion of SQLite Database 
>
>
> So I have this sql script that inserts into a single table, and it is VERY
> slow.
> it is even slower without journal_mode=WAL
>
> Also,  is a unique constraint also an index?
>
> if I use this in my memory mapped VFS it is MUCH faster (but it doesn't
> supply mmap, so it's only using -journal that it creates and deletes a lot).
>
> The following is a snippet of this much longer script
> https://drive.google.com/open?id=0B812EYiKwtkkSVBUVWtpYUR4WW8
>
> it's only 1687 lines and about 50% are select statements, I would expect
> this to complete in under 1 second.not 22 seconds.
>
> (the command should also be replace into, not insert into; but given the
> constraint, I wouldn't think that will matter)
> ---
>
> #PRAGMA journal_mode=WAL;;
> select tbl_name,sql from sqlite_master where type='table' and
> name='record';
> create table `record` (`soul` char,`field` char,`value` char,`relation`
> char,`state` char,CONSTRAINT `record_unique` UNIQUE (`soul`,`field`) ON
> CONFLICT REPLACE);
> create index if not exists soul_index on record(soul);
> create index if not exists soul_field_index on record(soul,field);
> select * from Record where soul='db';
> select state from Record where soul='db' and field='hello';
> insert into Record (soul,field,value,relation,state)
> values('db','hello','"world"',NULL,1502689508373);
> select state from Record where soul='db' and field='other';
> insert into Record (soul,field,value,relation,state)
> values('db','other','"test"',NULL,1502689508408);
> select state from Record where soul='j6bqj9ec1JUbWzaheTEO' and
> field='field';
> insert into Record (soul,field,value,relation,state)
> values('j6bqj9ec1JUbWzaheTEO','field','"randomkey"',NULL,150
> 2689508436.001);
> select state from Record where soul='db' and field='j6bqj9ec1JUbWzaheTEO';
> insert into Record (soul,field,value,relation,state)
> values('db','j6bqj9ec1JUbWzaheTEO',NULL,'j6bqj9ec1JUbWzaheTE
> O',1502689508466);
> select * from Record where soul='j6bqj9ec1JUbWzaheTEO';
> select state from Record where soul='j6bqj9fz01aP8aM1rLy7R3U' and
> field='field';
> insert into Record (soul,field,value,relation,state)
> values('j6bqj9fz01aP8aM1rLy7R3U','field','"randomkey"',NULL,
> 1502689508495.002);
> select state from Record where soul='db' and field='j6bqj9fz01aP8aM1rLy7R3U
> ';
> insert into Record (soul,field,value,relation,state)
> values('db','j6bqj9fz01aP8aM1rLy7R3U',NULL,'j6bqj9fz01aP8aM1rLy7R3U',15026
> 89508528);
> select * from Record where soul='j6bqj9fz01aP8aM1rLy7R3U';
> select state from Record where soul='j6bqj9ho02jCZpwwI9kz4Vi' and
> field='field';
> insert into Record (soul,field,value,relation,state)
> values('j6bqj9ho02jCZpwwI9kz4Vi','field','"randomkey"',NULL,
> 1502689508556.003);
> select state from Record where soul='db' and field='j6bqj9ho02jCZpwwI9kz4Vi
> ';
> insert into Record (soul,field,value,relation,state)
> values('db','j6bqj9ho02jCZpwwI9kz4Vi',NULL,'j6bqj9ho02jCZpwwI9kz4Vi',15026
> 89508611);
> select * from Record where soul='j6bqj9ho02jCZpwwI9kz4Vi';
> select state from Record where soul='j6bqj9kgu96XPzRp4HfD' and
> field='field';
> insert into Record (soul,field,value,relation,state)
> values('j6bqj9kgu96XPzRp4HfD','field','"randomkey"',NULL,150
> 2689508656.001);
> select * from Record where soul='j6bqj9ec1JUbWzaheTEO';
> select * from Record where soul='j6bqj9fz01aP8aM1rLy7R3U';
> select * from Record where soul='j6bqj9ho02jCZpwwI9kz4Vi';
> select * from Record where soul='j6bqj9kgu96XPzRp4HfD';
>
> 
>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: How can I make this faster?

[Clemens Ladisch]

J Decker wrote:
> So I have this sql script that inserts into a single table, and it is VERY
> slow.

Wrap everything into a single transaction.



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