Re: [sqlite] How to find records in a table which are not in a second table?

[Jay Kreibich]

> On Aug 28, 2018, at 1:22 PM, David Raymond  wrote:
> 
> Embarrassing confession time: I didn't think you could use "using" to do this 
> while selecting "a.*"
> 
> https://www.sqlite.org/lang_select.html
> "For each pair of columns identified by a USING clause, the column from the 
> right-hand dataset is omitted from the joined dataset. This is the only 
> difference between a USING clause and its equivalent ON constraint."
> 
> I thought it literally took that column out of the result set. So "a SOME 
> SORT OF JOIN b USING (foo)" literally got rid of a.foo and b.foo and put the 
> value of a.foo into a "foo" column, or literally got rid of b.foo. And that 
> trying to select a.foo, or especially b.foo would raise an error of it not 
> being an existing column.
> 
> I didn't realize that the column omission  happens with you "select * 
> from" and not for anything else.
> 
> The more you know.


Yeah, this relates to Relational Theory, SQL, and how the two are almost, 
sorta, kinda, but not-really the same.

One thing to keep in mind is that the source columns and the “working set” of 
columns (SELECT *) are not the same.  You can always explicitly name a source 
column.  While USING “collapses” the two columns in the working set (as does a 
NATURAL JOIN), it does not eliminate the ability to explicitly reference a 
specific source column using the table notation… that’s why the WHERE clause in 
my example works as well.  And that’s kind of what the .* notation does, 
just brings in all the columns from a specific source table.  In a NATURAL JOIN 
(or a standard [CROSS] JOIN...USING) the two columns will always be the same, 
so there is little need to explicitly reference a source column.  That’s not 
true with OUTER JOINs, however, but as shown that can be really useful.

  -j


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

Re: [sqlite] Shared memory cache files on disk?

[Keith Medcalf]

SQLITE_USE_URI

If this is not defined then URI's are not parsed.

https://www.sqlite.org/uri.html


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Dirkjan Ochtman
>Sent: Tuesday, 28 August, 2018 14:25
>To: SQLite mailing list
>Subject: [sqlite] Shared memory cache files on disk?
>
>Hi there,
>
>I've been using in-memory SQLite database for the automated tests in
>an
>application I'm writing. I did most of the initial development on
>macOS and
>things worked as I expected, but when I ran the tests on my Linux box
>it
>left behind files like "file:test-3210?mode=memory=shared".
>
>On my Mac, I have macOS 10.13 with its stock 3.19.3 version of
>SQLite; the
>Linux box is a Gentoo server that has 3.24.0 installed. Is there a
>compile-time option that could explain this difference, or is there
>some
>other subtle platform difference that I'm missing?
>
>Regards,
>
>Dirkjan
>___
>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] Shared memory cache files on disk?

[Dirkjan Ochtman]

Hi there,

I've been using in-memory SQLite database for the automated tests in an
application I'm writing. I did most of the initial development on macOS and
things worked as I expected, but when I ran the tests on my Linux box it
left behind files like "file:test-3210?mode=memory=shared".

On my Mac, I have macOS 10.13 with its stock 3.19.3 version of SQLite; the
Linux box is a Gentoo server that has 3.24.0 installed. Is there a
compile-time option that could explain this difference, or is there some
other subtle platform difference that I'm missing?

Regards,

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

Re: [sqlite] How to find records in a table which are not in a second table?

[R Smith]

On 2018/08/28 7:18 PM, Jay Kreibich wrote:

On Aug 28, 2018, at 11:30 AM, Joe  wrote:

A (perhaps silly ) beginners question:
My sqlite database contains several tables, two of them, table A and table B,  
have text colums called 'nam'. The tables have about 2 millions lines.
What's the most efficient way to select all lines from table A with nam values, 
which are not present in the nam values of table B?
Thanks —  Joe

Not sure about performance vs other suggestions, but this is a common way of 
doing that:

SELECT a.* FROM a LEFT JOIN b USING (nam) WHERE b.nam IS NULL


I have nothing to add in terms of the query - personally I prefer this 
version Jay offered (from a point of my internal clarity only).  Simon 
or Richard's versions will all work equally well in semantic terms 
(perhaps one is slightly better due to specific working of the sqlite 
internals - test it!), but in all cases one thing is paramount:  Ensure 
you have an Index on nam in at least the b table (but preferably in both).


The efficiency/speed gain in that will vastly overshadow any choice in 
lookup method.



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

Re: [sqlite] How to find records in a table which are not in a second table?

[David Raymond]

Embarrassing confession time: I didn't think you could use "using" to do this 
while selecting "a.*"

https://www.sqlite.org/lang_select.html
"For each pair of columns identified by a USING clause, the column from the 
right-hand dataset is omitted from the joined dataset. This is the only 
difference between a USING clause and its equivalent ON constraint."

I thought it literally took that column out of the result set. So "a SOME SORT 
OF JOIN b USING (foo)" literally got rid of a.foo and b.foo and put the value 
of a.foo into a "foo" column, or literally got rid of b.foo. And that trying to 
select a.foo, or especially b.foo would raise an error of it not being an 
existing column.

I didn't realize that the column omission  happens with you "select * 
from" and not for anything else.

The more you know.


SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table a (foo, bar);

sqlite> create table b (foo, baz);

sqlite> insert into a values (1, 2), (2, 3), (3, 4);

sqlite> insert into b values (1, 7), (4, 5);

sqlite> select * from a inner join b using (foo);--knew this would only have 3 
result columns
foo|bar|baz
1|2|7

sqlite> select a.*, b.* from a inner join b using (foo);--kinda thought this 
would be an error, or omit foo
foo|bar|foo|baz
1|2|1|7

sqlite> select b.foo from a inner join b using (foo);--definitely thought this 
was an error
foo
1

sqlite> delete from b;

sqlite> insert into b values (1.0, 7), (4, 5);

sqlite> select * from a inner join b using (foo);
foo|bar|baz
1|2|7

sqlite> select a.*, b.* from a inner join b using (foo);
foo|bar|foo|baz
1|2|1.0|7

sqlite> select foo, a.foo, b.foo, a.bar, b.baz from a inner join b using (foo);
foo|foo|foo|bar|baz
1|1|1.0|2|7

sqlite>


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jay Kreibich
Sent: Tuesday, August 28, 2018 1:18 PM
To: SQLite mailing list
Subject: Re: [sqlite] How to find records in a table which are not in a second 
table?


> On Aug 28, 2018, at 11:30 AM, Joe  wrote:
> 
> A (perhaps silly ) beginners question:
> My sqlite database contains several tables, two of them, table A and table B, 
>  have text colums called 'nam'. The tables have about 2 millions lines.
> What's the most efficient way to select all lines from table A with nam 
> values, which are not present in the nam values of table B?
> Thanks —  Joe

Not sure about performance vs other suggestions, but this is a common way of 
doing that:

SELECT a.* FROM a LEFT JOIN b USING (nam) WHERE b.nam IS NULL


  -j


___
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] How to find records in a table which are not in a second table?

[Jay Kreibich]

> On Aug 28, 2018, at 11:30 AM, Joe  wrote:
> 
> A (perhaps silly ) beginners question:
> My sqlite database contains several tables, two of them, table A and table B, 
>  have text colums called 'nam'. The tables have about 2 millions lines.
> What's the most efficient way to select all lines from table A with nam 
> values, which are not present in the nam values of table B?
> Thanks —  Joe

Not sure about performance vs other suggestions, but this is a common way of 
doing that:

SELECT a.* FROM a LEFT JOIN b USING (nam) WHERE b.nam IS NULL


  -j


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

Re: [sqlite] How to find records in a table which are not in a second table?

[Simon Slavin]

On 28 Aug 2018, at 5:32pm, Richard Hipp  wrote:

> I suppose:
> 
>   SELECT * FROM A WHERE nam NOT IN (SELECT nam FROM B);

Depending on how many names the tables have in column, a possible alternative 
might be to use the EXCEPT compound operator here.  Something like

SELECT nam FROM A
EXCEPT
SELECT nam FROM B

This will give you just the 'nam' values.  If that's all you need it might be 
faster than the "NOT IN" version.  If you need more columns than just "nam" 
then use something like

SELECT * FROM A WHERE nam IN (
SELECT nam FROM A
EXCEPT
SELECT nam FROM B
)

But this might be slower than the version Dr Hipp gave.

Make sure you have an index on both A.nam and B.nam to test the fastest these 
queries can return a result.

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

Re: [sqlite] How to find records in a table which are not in a second table?

[Richard Hipp]

On 8/28/18, Joe  wrote:
> A (perhaps silly ) beginners question:
> My sqlite database contains several tables, two of them, table A and
> table B,  have text colums called 'nam'. The tables have about 2
> millions lines.
> What's the most efficient way to select all lines from table A with nam
> values, which are not present in the nam values of table B?

I suppose:

   SELECT * FROM A WHERE nam NOT IN (SELECT nam FROM B);
-- 
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] How to find records in a table which are not in a second table?

[Joe]

A (perhaps silly ) beginners question:
My sqlite database contains several tables, two of them, table A and 
table B,  have text colums called 'nam'. The tables have about 2 
millions lines.
What's the most efficient way to select all lines from table A with nam 
values, which are not present in the nam values of table B?

Thanks --  Joe

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

Re: [sqlite] Blob and Text columns: what's the difference?

[Tim Streater]

On 28 Aug 2018, at 15:36, Keith Medcalf  wrote:

> On Tuesday, 28 August, 2018 07:50, Tim Streater  wrote:

>>How does it know not to do that if I want to send some binary data to a Text
>>column?
>
> Simply because you do not request that those things be done.
>
> So, the "things" that may occur depend on the APIs you use to read/write the
> data, and the "translations" that you might have asked SQLite3 to do.  For
> example, if your database is created with the 'encoding' set to one of the
> UTF-16 variants, and you use the sqlite3_bind_text() to put "text" in that
> column that is not valid UTF-8 that can be translated to UTF-16 then
> explosions will occur.  Similarly if the database encoding is UTF-8 (the
> default) and you use sqlite3_bind_text16() to try and store UTF16 data in the
> database, and that data is not valid UTF-16, then explosions will occur.  
>
> If however, you database encoding is UTF-8 (the default) and you use
> sqlite_bind_text() to put data in the database, then SQLite3 will not "tamper"
> with your data and you may put whatever you wish in there whether it is valid
> UTF-8 or not.  Similarly if the database encoding is UTF-16 and you use
> sqlite3_bind_text16() you can put whatever you wish in there whether it is
> valid UTF-16 or not.
>
> If you use the sqlite3_bind_blob() interface to put data in the database, it
> will be stored as a blob and the "affinity" (text) expressed in the create
> table statement will be ignored since the "affinity" specified in the table
> declaration will never attempt to translate a "blob" into something else.
>
> Assuming you managed to get data into the database, then how you ask to
> retrieve it may also cause explosions to occur.  For example, you might have
> stored your binary data (that is not valid UTF-8) using sqlite3_bind_text() in
> the database where the database encoding is UTF-8.  If you try and retrieve it
> using sqlite3_column_text16() then SQLite3 will, because *YOU* requested it,
> attempt to translate the data, and explosions will occur.  Mutatis mutandis if
> *you* request other translations that are invalid (ie, from a UTF-16 encoded
> database where the data is not valid UTF-16, and you try to retrieve it
> translated to UTF-8).
>
> However, if you attempt to retrieve the data using sqlite3_column_blob() you
> will always get back exactly what is in the database without translation.  If
> you store a blob and ask for some text, explosions may occur.
>
> So in short, just make sure that you store your "blob" of data using the
> appropriate storage type (sqlite3_bind_blob) when you store it, and always
> check what storage type it is before retrieving it and use the corresponding
> sqlite3_column_(type) to get the data out, and you will be fine.  SQLite3 will
> do *NOTHING* to your data that you have not explicitly asked it to do.

Thanks. I think the above is the clue I needed. I'm not familiar with the C 
interface; I'm using Xojo for this application. They have an SQLiteBlob class 
with its own read/write methods. It should therefore suffice for me to ask on 
their forum what they do.


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

Re: [sqlite] Blob and Text columns: what's the difference?

[Keith Medcalf]

On Tuesday, 28 August, 2018 07:50, Tim Streater  wrote:

>What is actually the difference between a column declared as TEXT and
>one declared as BLOB in an SQLite database? 

Not a thing.  You are free to store data of any type in any column in any row.  
The "TEXT" declaration only means that the column affinity is TEXT and if you 
attempt to put something that is not text in that column (such as an integer or 
a real) then it will be converted to text before being stored, since you have 
expressed that you would prefer the data in this column to be text wherever 
possible.

>What does SQLite do to textual data that I ask it to put into a TEXT column? 

Nothing that you do not ask it to do.

>How does it know not to do that if I want to send some binary data to a Text
>column?

Simply because you do not request that those things be done.

So, the "things" that may occur depend on the APIs you use to read/write the 
data, and the "translations" that you might have asked SQLite3 to do.  For 
example, if your database is created with the 'encoding' set to one of the 
UTF-16 variants, and you use the sqlite3_bind_text() to put "text" in that 
column that is not valid UTF-8 that can be translated to UTF-16 then explosions 
will occur.  Similarly if the database encoding is UTF-8 (the default) and you 
use sqlite3_bind_text16() to try and store UTF16 data in the database, and that 
data is not valid UTF-16, then explosions will occur.  

If however, you database encoding is UTF-8 (the default) and you use 
sqlite_bind_text() to put data in the database, then SQLite3 will not "tamper" 
with your data and you may put whatever you wish in there whether it is valid 
UTF-8 or not.  Similarly if the database encoding is UTF-16 and you use 
sqlite3_bind_text16() you can put whatever you wish in there whether it is 
valid UTF-16 or not.

If you use the sqlite3_bind_blob() interface to put data in the database, it 
will be stored as a blob and the "affinity" (text) expressed in the create 
table statement will be ignored since the "affinity" specified in the table 
declaration will never attempt to translate a "blob" into something else.

Assuming you managed to get data into the database, then how you ask to 
retrieve it may also cause explosions to occur.  For example, you might have 
stored your binary data (that is not valid UTF-8) using sqlite3_bind_text() in 
the database where the database encoding is UTF-8.  If you try and retrieve it 
using sqlite3_column_text16() then SQLite3 will, because *YOU* requested it, 
attempt to translate the data, and explosions will occur.  Mutatis mutandis if 
*you* request other translations that are invalid (ie, from a UTF-16 encoded 
database where the data is not valid UTF-16, and you try to retrieve it 
translated to UTF-8).

However, if you attempt to retrieve the data using sqlite3_column_blob() you 
will always get back exactly what is in the database without translation.  If 
you store a blob and ask for some text, explosions may occur.

So in short, just make sure that you store your "blob" of data using the 
appropriate storage type (sqlite3_bind_blob) when you store it, and always 
check what storage type it is before retrieving it and use the corresponding 
sqlite3_column_(type) to get the data out, and you will be fine.  SQLite3 will 
do *NOTHING* to your data that you have not explicitly asked it to do.

>The reason I'm interested is that I have a table with a TEXT column.
>For backwards compatibility reasons, I'd like not to change the
>column's type or even add another column with BLOB type. Up to now
>I've assumed that the data to go in the column was all ASCII or
>perhaps UTF-8. Now it seems that it's legal for it to be a mixture of
>encodings. So I want to treat it as binary and be able to just use
>the existing TEXT column. Anything I need to look out for?

Unless you explicitly request translations to occur then none will occur.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Blob and Text columns: what's the difference?

[Ben Asher]

Ah great to know. Thanks!

Ben

On Tue, Aug 28, 2018 at 7:29 AM Richard Hipp  wrote:

> On 8/28/18, Ben Asher  wrote:
> > I seem to remember that BLOBs cannot be indexed. I can’t find
> documentation
> > on that though. Does anyone else recall the same thing and have a link,
> or
> > maybe someone can correct me?
>
> You might be remembering the limitations of Oracle.  Other database
> engines might also have this limitation too.  But not SQLite.  SQLite
> allows any column to be indexed, including BLOB colums.
> --
> 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
>
-- 
Ben
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Blob and Text columns: what's the difference?

[Richard Hipp]

On 8/28/18, Ben Asher  wrote:
> I seem to remember that BLOBs cannot be indexed. I can’t find documentation
> on that though. Does anyone else recall the same thing and have a link, or
> maybe someone can correct me?

You might be remembering the limitations of Oracle.  Other database
engines might also have this limitation too.  But not SQLite.  SQLite
allows any column to be indexed, including BLOB colums.
-- 
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] Blob and Text columns: what's the difference?

[Simon Slavin]

On 28 Aug 2018, at 2:50pm, Tim Streater  wrote:

> What is actually the difference between a column declared as TEXT and one 
> declared as BLOB in an SQLite database? What does SQLite do to textual data 
> that I ask it to put into a TEXT column? 

BLOB data is always handled as a block of a certain number of octets, as you'd 
think.

Internally, some handling of TEXT data assumes that it's UTF-8, or perhaps some 
other encoding:



For instance, without testing it yourself or finding a reference, you're not 
entirely sure whether length() of a string proceeds past a NUL, are you ?  
SQlite is written to do the most likely most convenient thing, but it is not 
completely predictable to a programmer who frequently plays with different 
encodings.

Also, if it's expecting a number, SQLite will sometimes convert a string to a 
number.  However, SQLite has no understanding of BLOB data and will never 
convert it to anything.  See section See section 4.2 of 
.

There are also some niggles about meta-operations with BLOBs.  For instance, 
the "CREATE TABLE ... AS SELECT" construction handles BLOB values a little 
weirdly.

However, the biggest distinction between these two is not in the SQLite C API 
but in the countless libraries and shims written to allow languages to make 
SQLite calls.  String handling in those libraries can do all sorts of things to 
strings, including terminating them at NUL (as C would expect), parsing them as 
Unicode before returning a value, or internally labelling them as using a 
certain Windows code page.  So most of our questions resulting from unexpected 
string changes result not from the SQLite API but from what happens when a 
programming language tries to understand the string.  None of this should be 
done to a BLOB except under explicit programmer control.

> How does it know not to do that if I want to send some binary data to a Text 
> column?

See section 3.4 of .  Here you'll find 
demonstrated what happens if you put values of different types into columns of 
different types.

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

Re: [sqlite] Blob and Text columns: what's the difference?

[J. King]

On 2018-08-28 09:50:01, "Tim Streater"  wrote:

What is actually the difference between a column declared as TEXT and 
one declared as BLOB in an SQLite database? What does SQLite do to 
textual data that I ask it to put into a TEXT column? How does it know 
not to do that if I want to send some binary data to a Text column?



I don't know all the ins and outs, but text columnns are convertible 
between UTF-8 and UTF-16 upon request, whereas blobs are just byte 
strings.


--
J. King

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

Re: [sqlite] Blob and Text columns: what's the difference?

[Ben Asher]

I seem to remember that BLOBs cannot be indexed. I can’t find documentation
on that though. Does anyone else recall the same thing and have a link, or
maybe someone can correct me?

Ben

On Tue, Aug 28, 2018 at 6:50 AM Tim Streater  wrote:

> What is actually the difference between a column declared as TEXT and one
> declared as BLOB in an SQLite database? What does SQLite do to textual data
> that I ask it to put into a TEXT column? How does it know not to do that if
> I want to send some binary data to a Text column?
>
> The reason I'm interested is that I have a table with a TEXT column. For
> backwards compatibility reasons, I'd like not to change the column's type
> or even add another column with BLOB type. Up to now I've assumed that the
> data to go in the column was all ASCII or perhaps UTF-8. Now it seems that
> it's legal for it to be a mixture of encodings. So I want to treat it as
> binary and be able to just use the existing TEXT column. Anything I need to
> look out for?
>
>
> --
> Cheers  --  Tim
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Ben
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Blob and Text columns: what's the difference?

[Tim Streater]

What is actually the difference between a column declared as TEXT and one 
declared as BLOB in an SQLite database? What does SQLite do to textual data 
that I ask it to put into a TEXT column? How does it know not to do that if I 
want to send some binary data to a Text column?

The reason I'm interested is that I have a table with a TEXT column. For 
backwards compatibility reasons, I'd like not to change the column's type or 
even add another column with BLOB type. Up to now I've assumed that the data to 
go in the column was all ASCII or perhaps UTF-8. Now it seems that it's legal 
for it to be a mixture of encodings. So I want to treat it as binary and be 
able to just use the existing TEXT column. Anything I need to look out for?


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

Re: [sqlite] Cannot make index for non-main database

[R Smith]

The correct syntax is:

CREATE INDEX dbondisk.diskIndex on TestTable (Parent)


See: https://sqlite.org/lang_createindex.html


On 2018/08/27 5:37 PM, Jiawei Duan wrote:

SQLite version: 3.24.0
System info: macOS 10.13.6

The following SQL commands results an error of "Error: near ".": syntax error”. 
However the command can proceed within the main database.

ATTACH DATABASE ‘/Users/***/test.db' AS dbondisk;
CREATE INDEX diskIndex on dbondisk.TestTable (Parent)


___
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] Cannot make index for non-main database

[Jiawei Duan]

SQLite version: 3.24.0
System info: macOS 10.13.6 

The following SQL commands results an error of "Error: near ".": syntax error”. 
However the command can proceed within the main database. 

ATTACH DATABASE ‘/Users/***/test.db' AS dbondisk;
CREATE INDEX diskIndex on dbondisk.TestTable (Parent)


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

Re: [sqlite] [EXTERNAL] Query on TEMP view.

[Keith Medcalf]

HAVING is only applicable to GROUP BY's.  That is, the WHERE clauses constrain 
what goes into the sorter for the "group by" operation and the HAVING clauses 
constrain what comes out of the sorter from the "group by" operation and is 
returned as a query result.

I think that the issue is that the evaluation of conditions is not necessarily 
evaluated according to cost (though this may have changed in various versions 
of the optimizer).  The simplest way to evaluate the "cost" is to determine for 
each "expression1 op expression2" the number of "operations" involved and then 
to evaluate in order of ascending cost, where simple operations (+-*/<<>> <= = 
== => etc) have a cost of say 1, and function calls are assigned a higher 
(arbitrary) cost of say 10 (100, 1000?).  Each "expression op expression" can 
then have a cost assigned to it so that they can be evaluated in order of 
ascending cost thus hopefully bypassing the evaluation of more expensive 
conditional expressions in cases like this.

Whether and to what degree the optimizer does this I do not know.  Richard will 
know the answer to this one, and in particular which versions do what ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter
>Sent: Tuesday, 28 August, 2018 00:30
>To: 'SQLite mailing list'
>Subject: Re: [sqlite] [EXTERNAL] Query on TEMP view.
>
>In the sqlite shell, enter the .explain command and then
>
>EXPLAIN QUERY PLAN 
>
>for an explanation of the plan, and
>
>EXPLAIN 
>
>for the generated bytecode. This usually helps to understand what
>sqlite is thinking (although maybe not why).
>
>Note that WHERE constraints are applied to the input set whereas
>HAVING constraints are applied to the output set, so perhaps changing
>the "outer" constraint to HAVING smart_search() will yield the
>desired effect.
>
>-Ursprüngliche Nachricht-
>Von: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] Im Auftrag von Hegde, Deepakakumar
>(D.)
>Gesendet: Dienstag, 28. August 2018 07:47
>An: sqlite-users@mailinglists.sqlite.org
>Betreff: [EXTERNAL] [sqlite] Query on TEMP view.
>
>Hi All,
>
>
>I am facing a problem where in defined function registered to sqlite
>is called multiple time withput considering the filter.
>
>
>Ex:
>
>Table and entry:
>
>ID  NAME  PPID
>
>1a.mp3   2
>
>2b.mp3   3
>
>
>Query:
>
>SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE
>PPID=2) AS TEMP WHERE smart_search(NAME,ID)
>
>
>OutPut count = 1 (This is fine)
>
>
>Here smart_search() is defined and given to sqlite DB.
>
>We are expecting smart_search() to be called from sqlite only for the
>entry with PPID 2 and that will be for ID 1 a.mp3.
>
>But we see that this is called 2 times and for both the entry.
>
>
>As per my understanding. filter of PPID is added for the inner query
>smart_search() should have called only for one entry. Is this the
>expected behavior?
>
>
>If i change the query as below then the smart_search() is called for
>only one entry.
>
>SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE
>PPID=2) AS TEMP WHERE PPID=2 AND smart_search(NAME,ID)
>
>
>Please help to understand on this.
>
>
>Thanks and Regards
>
>Deepak
>___
>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



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

Re: [sqlite] [EXTERNAL] Query on TEMP view.

[Hick Gunter]

In the sqlite shell, enter the .explain command and then

EXPLAIN QUERY PLAN 

for an explanation of the plan, and

EXPLAIN 

for the generated bytecode. This usually helps to understand what sqlite is 
thinking (although maybe not why).

Note that WHERE constraints are applied to the input set whereas HAVING 
constraints are applied to the output set, so perhaps changing the "outer" 
constraint to HAVING smart_search() will yield the desired effect.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Hegde, Deepakakumar (D.)
Gesendet: Dienstag, 28. August 2018 07:47
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Query on TEMP view.

Hi All,


I am facing a problem where in defined function registered to sqlite is called 
multiple time withput considering the filter.


Ex:

Table and entry:

ID  NAME  PPID

1a.mp3   2

2b.mp3   3


Query:

SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE PPID=2) AS 
TEMP WHERE smart_search(NAME,ID)


OutPut count = 1 (This is fine)


Here smart_search() is defined and given to sqlite DB.

We are expecting smart_search() to be called from sqlite only for the entry 
with PPID 2 and that will be for ID 1 a.mp3.

But we see that this is called 2 times and for both the entry.


As per my understanding. filter of PPID is added for the inner query 
smart_search() should have called only for one entry. Is this the expected 
behavior?


If i change the query as below then the smart_search() is called for only one 
entry.

SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE PPID=2) AS 
TEMP WHERE PPID=2 AND smart_search(NAME,ID)


Please help to understand on this.


Thanks and Regards

Deepak
___
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] Query on TEMP view.

[Keith Medcalf]

There are a myriad of reasons for the behaviour you are seeing and they affect 
only performance and not correctness.  In other words, you think that your UDF 
is more "expensive" to compute than the PPID == 2 test, and therefore the least 
expensive test should be performed first so that the more expensive operation 
does not need to be performed where its result would merely be discarded by 
virtue of the lesser expensive ANDed condition.

The other thing is that the subquery is likely being flattened -- again this 
depends on the version of SQLite3 you are using.  However, assuming that the 
query is being flattened then:

SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE PPID=2) AS 
TEMP WHERE smart_search(NAME,ID)
which is exactly the same as (with the aliasing removed as it adds nothing of 
significance) 
SELECT COUNT(ID) FROM (SELECT ID, NAME, PPID FROM AUDIO WHERE PPID=2) WHERE 
smart_search(name,id)
which should be flattened to
SELECT COUNT(ID) FROM AUDIO WHERE PPID=2 and smart_search(name,id)

*See the query flattening rules at 
https://www.sqlite.org/optoverview.html#subquery_flattening

That said, however, I am unable to reproduce with the current tip of trunk.  

What version of sqlite3 are you using?


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Hegde, Deepakakumar
>(D.)
>Sent: Monday, 27 August, 2018 23:47
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Query on TEMP view.
>
>Hi All,
>
>
>I am facing a problem where in defined function registered to sqlite
>is called multiple time withput considering the filter.
>
>
>Ex:
>
>Table and entry:
>
>ID  NAME  PPID
>
>1a.mp3   2
>
>2b.mp3   3
>
>
>Query:
>
>SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE
>PPID=2) AS TEMP WHERE smart_search(NAME,ID)
>
>
>OutPut count = 1 (This is fine)
>
>
>Here smart_search() is defined and given to sqlite DB.
>
>We are expecting smart_search() to be called from sqlite only for the
>entry with PPID 2 and that will be for ID 1 a.mp3.
>
>But we see that this is called 2 times and for both the entry.
>
>
>As per my understanding. filter of PPID is added for the inner query
>smart_search() should have called only for one entry. Is this the
>expected behavior?
>
>
>If i change the query as below then the smart_search() is called for
>only one entry.
>
>SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE
>PPID=2) AS TEMP WHERE PPID=2 AND smart_search(NAME,ID)
>
>
>Please help to understand on this.
>
>
>Thanks and Regards
>
>Deepak
>___
>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