Re: [sqlite] LEFT joins affect query plan for semantically equal queries

[James K. Lowden]

On Wed, 05 Nov 2014 08:24:47 -0700
"Keith Medcalf"  wrote:

> The two queries are different.  They may end up with the same result,
> but you are asking different questions.  In the first you are
> returning only matching rows.  In the later you are requesting a
> projection (outer join) then applying the conditional (where) to the
> projection.  

I'm surprised to see you say that, Keith, because it's not true.  

An SQL query is one piece, integral and whole.  There's no 
before & after, no first this then that. Projection and selection are
independent operations and can be applied in either order, at the
discretion of the implementation. The only requirement, as you know, is
that the criteria be met. 

> An index on profiles.userid will speed it up, but you are still
> asking a different question.  You cannot change the table visitation
> order around a left outer join because it changes the question.

Actually, he can't change the visitation order because he doesn't
*control* the visitation order.  That's up to SQLite.  He can only ask a
question.  

Equivalent to an outer join is a union.  Let's look at it that way
(simplified slightly for clarity):

>SELECT * FROM bugs b LEFT JOIN profiles p ON p.userid=b.assigned_to
>WHERE p.login_name='vita...@yourcmc.ru'

select b.*, p.login_name 
from bugs as b join profiles as p 
on p.userid=b.assigned_to 
where login_name='vita...@yourcmc.ru'
UNION
select *, NULL
from bugs
where assigned_to not in (select userid from profiles)
and NULL = 'vita...@yourcmc.ru'

Same question, differently expressed.  How much work would you suggest
the system do to answer the second part of that query?  ;-)  

--jkl


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

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

[nicolas riesch]

Hello, Maxim,

I use your excellent driver, indeed ;-)

About my post, the poison of doubt has just crept into my mind when I read
this:

  http://www.sqlite.org/cvstrac/wiki?p=MultiThreading

  There is a bug in some Linux implementations (RedHat9 is the canonical
example) that prevents fcntl() locks created by one thread from being
modified in a different thread.
  If you are running on one of those systems, then you should always use an
SQLite database connection in the same thread in which it was originally
created.
  It is not safe to open the database in one thread and then pass the
connection off to a different thread for processing.

  The restriction of the previous paragraph has been relaxed somewhat as of
SQLite version 3.3.1.
  Beginning with version 3.3.1, you should be able to move database
connections from one thread to another as long as there are no locks
outstanding at the time you move the thread.
  If you are not running on one of the systems effected by the fcntl()
locking bug, then you can move your database connections at any time you
want.
  But for portability, you probably should assume your system has the bug.

It is an old issue, 8 years ago, and has certainly been resolved.
As I have a quite paranoid personality, I prefer to ask to be sure that
there is no similar problem I am not aware of.
But I have found nothing so far, so I think you are right and there is no
problem.


2014-11-06 3:06 GMT+01:00 Maxim Khitrov :

> On Wed, Nov 5, 2014 at 7:10 PM, nicolas riesch 
> wrote:
> > Pardon me, I will try to reformulate my question more clearly.
> >
> > My scenario:
> >
> >   - sqlite is set to Multi-thread mode (SQLITE_THREADSAFE=2), or
> Serialized
> > mode (SQLITE_THREADSAFE=1)
> >   - I create N logical threads in my "Go" program.
> >   - Each logical thread creates a database connection, for its
> "exclusive"
> > usage.
> > Logical thread LT1 creates connection C1, logical thread LT2 creates
> > connection C2, etc.
> > Logical thread LT1 only makes call to connection C1, never to
> > connection C2, C3, etc. Same for other threads.
> >
> > Normally, in any mainstream language (C, PHP, etc), the same OS thread
> > makes the successive calls to sqlite3_prepare(), sqlite3_step(),
> > sqlite3_column(), sqlite3_finalize(), etc.
> > In the loop to retrieve all records in a table, there is no reason to
> call
> > sqlite3_step() on a different OS thread each time.
> >
> > But in Go, it is possible that each call to sqlite3_step() is scheduled
> to
> > run on a different OS thread.
> > Indeed, the execution of a logical Go thread (called a Goroutine) can
> > switch from one OS thread to another one, without the user being aware of
> > it, at each function call.
>
> I'm the author of https://github.com/mxk/go-sqlite. You can't use a
> single connection and its derived objects from multiple goroutines
> without external synchronization, but as far as I know, there are no
> problems with the same goroutine being scheduled to different OS
> threads. I'm pretty sure that this applies to all other Go SQLite
> drivers as well.
>
> You actually can lock the current goroutine to a specific OS thread by
> calling runtime.LockOSThread(). Some things, like OpenGL, need this
> for thread-local storage and the other reasons that you mentioned. I
> don't think SQLite falls into this category.
>
> - Max
> ___
> 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 sqlite thread-safety sufficient for use with "Go" language ?

[Maxim Khitrov]

On Wed, Nov 5, 2014 at 7:10 PM, nicolas riesch  wrote:
> Pardon me, I will try to reformulate my question more clearly.
>
> My scenario:
>
>   - sqlite is set to Multi-thread mode (SQLITE_THREADSAFE=2), or Serialized
> mode (SQLITE_THREADSAFE=1)
>   - I create N logical threads in my "Go" program.
>   - Each logical thread creates a database connection, for its "exclusive"
> usage.
> Logical thread LT1 creates connection C1, logical thread LT2 creates
> connection C2, etc.
> Logical thread LT1 only makes call to connection C1, never to
> connection C2, C3, etc. Same for other threads.
>
> Normally, in any mainstream language (C, PHP, etc), the same OS thread
> makes the successive calls to sqlite3_prepare(), sqlite3_step(),
> sqlite3_column(), sqlite3_finalize(), etc.
> In the loop to retrieve all records in a table, there is no reason to call
> sqlite3_step() on a different OS thread each time.
>
> But in Go, it is possible that each call to sqlite3_step() is scheduled to
> run on a different OS thread.
> Indeed, the execution of a logical Go thread (called a Goroutine) can
> switch from one OS thread to another one, without the user being aware of
> it, at each function call.

I'm the author of https://github.com/mxk/go-sqlite. You can't use a
single connection and its derived objects from multiple goroutines
without external synchronization, but as far as I know, there are no
problems with the same goroutine being scheduled to different OS
threads. I'm pretty sure that this applies to all other Go SQLite
drivers as well.

You actually can lock the current goroutine to a specific OS thread by
calling runtime.LockOSThread(). Some things, like OpenGL, need this
for thread-local storage and the other reasons that you mentioned. I
don't think SQLite falls into this category.

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

[sqlite] OT: Testers needed for full database encryption support

[Justin Clift]

As a quick FYI, we've just added initial full database encryption
support to DB Broweser for SQLite (using SQLCipher).  We're looking for
people using MacOS X, Linux, or FreeBSD to try it out and report
success/failure/etc back to us (on GitHub).

Initial MacOS X build:

  
http://mirror.salasaga.org/sqlitebrowser/onceoffs/sqlitebrowser_201411051855.dmg

For Linux and FreeBSD, you'll need to compile it yourself (pretty
easy):

  https://github.com/sqlitebrowser/sqlitebrowser/blob/master/BUILDING.md

For anyone with time to do so, this will really help us out. :)

Regards and best wishes,

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

Re: [sqlite] SQLite as a meta database

[Edward Lau]

Exactly.  Can someone put this link of the SQLite home page?  I didn't know it 
existed.  I will read it and post my comments later.

Thanks.


-Original Message-
From: Simon Slavin 
To: General Discussion of SQLite Database 
Sent: Wed, Nov 5, 2014 2:09 pm
Subject: Re: [sqlite] SQLite as a meta database



On 5 Nov 2014, at 9:28pm, Edward Lau  wrote:

> Maybe some time in the future a version 4 be started that incorporates many 
new advancement in the industry.  Version 3 can still be continued for 
backwards 
compatibility and version 4 will break some but set the stage for the future.

Like this, you mean ?



You might be particularly interested in the fact that there will be two 
different types of storage engine, both pluggable:



I don't understand the details on that page, but one of those two looks like it 
may allow more flexibility for external data supply than the current virtual 
table implementation.  But I may have misunderstood that.

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

[sqlite] default ignore-glob for sqlite

[E. Timothy Uy]

Hi, I noticed that the ignore-glob for sqlite is empty. Is it just because
fossil does not transfer this information? What should I be using?

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

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

[nicolas riesch]

Pardon me, I will try to reformulate my question more clearly.

My scenario:

  - sqlite is set to Multi-thread mode (SQLITE_THREADSAFE=2), or Serialized
mode (SQLITE_THREADSAFE=1)
  - I create N logical threads in my "Go" program.
  - Each logical thread creates a database connection, for its "exclusive"
usage.
Logical thread LT1 creates connection C1, logical thread LT2 creates
connection C2, etc.
Logical thread LT1 only makes call to connection C1, never to
connection C2, C3, etc. Same for other threads.

Normally, in any mainstream language (C, PHP, etc), the same OS thread
makes the successive calls to sqlite3_prepare(), sqlite3_step(),
sqlite3_column(), sqlite3_finalize(), etc.
In the loop to retrieve all records in a table, there is no reason to call
sqlite3_step() on a different OS thread each time.

But in Go, it is possible that each call to sqlite3_step() is scheduled to
run on a different OS thread.
Indeed, the execution of a logical Go thread (called a Goroutine) can
switch from one OS thread to another one, without the user being aware of
it, at each function call.

E.g. logical thread LT1 can dispatch function calls on connection C1 like
this:
OS thread a --sqlite3_prepare(C1)--
--sqlite3_column(C1)--
OS thread b
--sqlite3_step(C1)--
--sqlite3_column(C1)--
OS thread
c
--sqlite3_step(C1)--  --sqlite3_finalize(C1)--

For each connection, function calls always occur sequentially, but possibly
on a different OS thread each time.

Logical thread LT2 executes simultaneously, but calling functions only on
connection C2.
Logical thread LT3 executes simultaneously, but calling functions only on
connection C3.
etc...

So, in this scenario, I imagine that with SQLITE_THREADSAFE=1 or
SQLITE_THREADSAFE=2, there should be no problem ?

Is it correct to say that each function of the C API doesn't care on which
OS thread it is run, as long as the sequence of calls is correct ?

I know that in www.sqlite.org/threadsafe.html, it is written that "In
serialized mode, SQLite can be safely used by multiple threads with no
restriction.", but I just wanted to have a confirmation that it really
applies in the particular scenario above.


2014-11-05 23:13 GMT+01:00 Simon Slavin :

>
> On 5 Nov 2014, at 10:05pm, nicolas riesch 
> wrote:
>
> > Even if the user writes a Go program with only one logical thread, he has
> > no control about which OS thread will process a function call.
> >
> > This means that EACH SUCCESSIVE function in the sequence above can be
> > processed on a DIFFERENT OS THREAD.
> >
> > It means that to run safely, sqlite source code should not depend in any
> > way on the identity of the threads, which must be fully interchangeable.
> > So, the following conditions should be true. Are these sentences correct
> ?
> >
> > 1) no local-thread-storage is used in sqlite code.
> > 2) thread id (gettid()) are not used.
> > 3) when a function of the API enters a mutex, it leaves it before the
> > function returns.
> >   Between two API function calls, no mutex should be locked (else, it
> > would be impossible to ensure that the mutex is unlocked by the same
> thread
> > that locked it).
> > 4) all file locking information is attached to connections, and not to
> > threads.
>
> Since you don't already refer to it, can I ask that you read this page
>
> 
>
> and then ask any questions which remain, plus any new ones ?  You should
> probably tell us which threading mode you intend to use based on the needs
> you outline above.
>
> Simon.
> ___
> 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] SQLite as a meta database

[Mike Beddo]

Hi,

I am intruding into this thread, because I face a similar issue. At our company 
we have a "proprietary" database storing customer grocery transactions which 
basically only a C programmer can get to. It is by no stretch of the 
imagination a "relational" database. Basically, everything is an integer and we 
rely on associative tables in our C codes to map integers back into human 
readable labels for output purposes. For instance, a particular UPC 
"0" might map to integer 123, so we work with 123 in our codes, and 
when we output the results we map 123 back to "0".

There is intense interest in providing a SQL front-end. I thought to start 
with, I'd see if I could develop a virtual table for our various associative 
tables. Doing this would help educate me before embarking on the bigger chore 
of handling the database itself. Sounds simple enough, but I could use some 
help/advice.

There are several associative tables that I would need a virtual table for: 
items, stores, customers. I thought something like

CREATE VIRTUAL TABLE items USING aa_module('/path/to/database', 'items');

The arguments to aa_module (the thing I'm trying to write) give the path to the 
database to open, and which associative map to create a virtual table for.

If we think of 'items' as a pointer to the items associative array, for 
instance, then in our code we would use expressions like:

int inx = AA_inx(items, '0'); /* gives the index associated with 
upc 0 */
char *upc = AA_lbl(items, 123); /* gives the upc string associated with item 
inx 123 */

The table create string to use in xCreate/xConnect would be "CREATE TABLE x(inx 
INTEGER, upc VARCHAR(13));"I think that in my xBestIndex function that if the 
constraint involves equality then the cost is 1 lookup, but for any other type 
of constraint the cost is the number of items in the AA map, since this thing 
isn't sorted.

Could someone give me an idea of what a minimal xBestIndex/xFilter skeleton 
might look like? I walked though "ext/misc/amatch.c" from the 
www.sqlite.org/src/artifact tree, but I'm a little lost.

Thanks,

Mike Beddo

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Hick Gunter
Sent: Wednesday, November 05, 2014 6:16 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] SQLite as a meta database

Hi,

we have extensive experience with respect to the use of virtual tables in 
SQLite. In fact, the only native SQLite tables we use are in a configuration 
checking tool.

We have "providers" from in-memory indexed tables, CTree (r) files, Oracle 
tables (read only), structured disk files, in-memory structures, binary 
records, etc.

The trick is to be able to formulate your queries solely via comparison 
operators. This type of constraint gets passed to your xBestIndex function and 
can be processed there.

e.g. provide 2 virtual fields _function and _frame

SELECT * from VA, VB where VA._function='Intersect' and VA._frame=VB.geom;

When called for VA or VB with the constraints (_function,=) and (frame,=) your 
xBestIndex function should return
- a value proportional to the effort of locating a record via the internal 
index as "estimated cost"
- a number that signifies "use internal index"
- set the "omit" flag fort he contraints
- set the "argvIndex" values for the constraints

When called for VA or VB without constraints, your xBestIndex function should 
return
- a value proportional to the effort of a full table scan as "estimated cost"
- a number that signifies "full table scan"

This will make SQLite read VB via full table scan, and look up VA via the 
internal index.
For each row retrieved from VB, your xFilter function will be called with the 
parameter values "Intersect" and "VB.geom".
SQLite will expect to retrieve exactly those rows of VA the "Intersect" with 
"VB.geom".

Assuming that all _functions are commutative e.g. F(a,b) = F(b,a) you could 
provide a symmetrical solution:

SELECT * from VA, VB where VA._function='Intersect' and 
VB._function='Intersect' and VA._frame=VB.geom and VB._frame=VA.geom;

SQLite would then choose the smaller product of full table scan * lookup.

I think it should be possible to have SQLite omit all the checks; if not, 
_frame needs to return geom (best guess...).

-Ursprüngliche Nachricht-
Von: Hugo Mercier [mailto:hugo.merc...@oslandia.com]
Gesendet: Mittwoch, 05. November 2014 10:09
An: General Discussion of SQLite Database
Betreff: [sqlite] SQLite as a meta database

Hi all,

Following a first discussion on sqlite-dev that was probably not the right 
place to post, I've been invited to repost here for a broader audience :)

I am a developer on QGIS and I am investigating the possible use of SQLite / 
Spatialite to extend QGIS relational-oriented features.

For now, we have what we call "data providers" that allow to open / read / 
modify geographic data from 

Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

[Simon Slavin]

On 5 Nov 2014, at 10:05pm, nicolas riesch  wrote:

> Even if the user writes a Go program with only one logical thread, he has
> no control about which OS thread will process a function call.
> 
> This means that EACH SUCCESSIVE function in the sequence above can be
> processed on a DIFFERENT OS THREAD.
> 
> It means that to run safely, sqlite source code should not depend in any
> way on the identity of the threads, which must be fully interchangeable.
> So, the following conditions should be true. Are these sentences correct ?
> 
> 1) no local-thread-storage is used in sqlite code.
> 2) thread id (gettid()) are not used.
> 3) when a function of the API enters a mutex, it leaves it before the
> function returns.
>   Between two API function calls, no mutex should be locked (else, it
> would be impossible to ensure that the mutex is unlocked by the same thread
> that locked it).
> 4) all file locking information is attached to connections, and not to
> threads.

Since you don't already refer to it, can I ask that you read this page



and then ask any questions which remain, plus any new ones ?  You should 
probably tell us which threading mode you intend to use based on the needs you 
outline above.

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

Re: [sqlite] SQLite as a meta database

[Simon Slavin]

On 5 Nov 2014, at 9:28pm, Edward Lau  wrote:

> Maybe some time in the future a version 4 be started that incorporates many 
> new advancement in the industry.  Version 3 can still be continued for 
> backwards compatibility and version 4 will break some but set the stage for 
> the future.

Like this, you mean ?



You might be particularly interested in the fact that there will be two 
different types of storage engine, both pluggable:



I don't understand the details on that page, but one of those two looks like it 
may allow more flexibility for external data supply than the current virtual 
table implementation.  But I may have misunderstood that.

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

[sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?

[nicolas riesch]

I would like to use sqlite with "Go" (golang) language.
There are already several drivers available in this language. But before
using them, I would like to ensure it is really safe.

The normal sequence to access a database is:

  sqlite3_prepare()
  loop
 sqlite3_step()
 sqlite3_column()
  sqlite3_finalize()

Normally, all functions in this sequence are called from the same OS thread.

But Go is inherently a multithreaded language, with many threads running
inside.
Even if the user writes a Go program with only one logical thread, he has
no control about which OS thread will process a function call.

 This means that EACH SUCCESSIVE function in the sequence above can be
processed on a DIFFERENT OS THREAD.

It means that to run safely, sqlite source code should not depend in any
way on the identity of the threads, which must be fully interchangeable.
So, the following conditions should be true. Are these sentences correct ?

1) no local-thread-storage is used in sqlite code.
2) thread id (gettid()) are not used.
3) when a function of the API enters a mutex, it leaves it before the
function returns.
   Between two API function calls, no mutex should be locked (else, it
would be impossible to ensure that the mutex is unlocked by the same thread
that locked it).
4) all file locking information is attached to connections, and not to
threads.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to check if a record exists

[Drago, William @ CSG - NARDAEAST]

Cool website, James. I bookmarked it for future reference.

Thanks for your help, it will come in handy...

--
Bill Drago
Senior Engineer
L3 Communications / Narda Microwave East
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com



> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of James K. Lowden
> Sent: Tuesday, November 04, 2014 6:23 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How to check if a record exists
>
> On Tue, 4 Nov 2014 21:47:20 +
> "Drago, William @ CSG - NARDAEAST"  wrote:
>
> > I've been pulling my hair out trying to figure how to use EXISTS.
>
> I have several examples at
> http://www.schemamania.org/sql/#missing.table.
>
> > Is there a better/recommended way in SQLite to check if a record
> > exists?
> >
> > static bool IDisDuplicate(string dbFileName, int id)
>
> Yes.  Assert, don't check!
>
> Your function is checking for duplicates.  I could be wrong of course,
> but I suspect that you're checking for duplicates to do something about
> it in the database.  If so, between the time you check and the time you
> "do something" that duplicate could have gone away and another arisen.
> Cache coherency folks call that a write-after-read error.
>
> The way to avoid that is to put everything on the broad shoulders of
> SQLite, viz,
>
>   insert/update/delete T as t
>   -- ... --
>   where exists (
>   select 1 from T
>   where something = t.something
>   group by something
>   having count(*) > 1
>   );
>
> HTH.  Exists is confusing at first, and consequently under-appreciated.
>
> (BTW, I dis duplicates, too.  Despise them, in fact.)
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

[Vitaliy Filippov]

Can you please run ANALYZE then try the plans again ?


This was just after running ANALYZE :)

the fact that they produce the exact same answer is simply by virtue of  
your WHERE clause being specifically that


Of course, I understand, that's what I've meant - the plan shouldn't  
differ for this specific query with this specific condition...


Why would you use left join for that? Maybe this is just a really small  
extract from the real query you are doing and the real one would shed  
more light?


This is an autogenerated query, the conditions are user-specified, so I  
must analyze them to determine whether to use INNER or LEFT join in each  
case if SQLite does not do that. In fact that's what I've implemented at  
once in my fork of bugzilla (bugzilla4intranet) to optimise for SQLite,  
and in most cases it works.


But I think SQLite could do this kind of optimisation better inside  
itself, and most (if not all) client-server DBMSes (even MySQL) actually  
do it - their plans don't differ for the same query.


Even if NULLs are not specifically excluded, the column schema might  
include "NOT NULL" for that column which may also incur the optimisation


No-no, even if a column is NOT NULL in an outer-joined table, it may still  
be null in the result if no matching row is found.


So the condition is simple: turn outer join into an inner if the WHERE  
clause excludes NULLs.


--
With best regards,
  Vitaliy Filippov
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to check if a record exists

[Drago, William @ CSG - NARDAEAST]

This is exactly what I was looking for. It works perfectly.

Thanks,
--
Bill Drago
Senior Engineer
L3 Communications / Narda Microwave East
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com



> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Tuesday, November 04, 2014 5:49 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How to check if a record exists
>
> On 11/4/2014 4:47 PM, Drago, William @ CSG - NARDAEAST wrote:
> >  "SELECT count(1) DatasetID FROM UUT_Info
> > where DatasetID = " + id + ";";
>
> You could do
>
> select exists (select 1 from UUT_Info where DatasetID = :id);
>
> This returns 0 or 1 (record absent/present). May work faster than the
> variant using count() if there are many records satisfying the
> condition (EXISTS stops as soon as it finds the first matching record),
> but that may not be a consideration in your case (DatasetID sounds like
> primary key).
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a meta database

[Edward Lau]

Hi SQLite Team:


I second Nico's suggestion.  We are finding new usage with SQLite especially 
with virtual tables.  I understand the teams commitment to backwards 
compatibility.  Maybe some time in the future a version 4 be started that 
incorporates many new advancement in the industry.  Version 3 can still be 
continued for backwards compatibility and version 4 will break some but set the 
stage for the future.


I am interested in the VM to iterate through batches instead of single row at a 
time.  This opens up new frontier using GPUs  as suggested by Tom Gaill of 
linaro from his email:





One data point I'll add, this spring I worked on accelerating sqlite





using OpenCL. I was getting a 7x-25x perf improvement on ARM hardware





with a Mali 604T GPU.







and also from this link: 
http://www.cs.virginia.edu/~skadron/Papers/bakkum_sqlite_tr.pdf


Would higher concurrency produce between throughput in a low latency 
environment? For example, "union all" of several external tables.  Instead of 
sequentially doing one table at time, how about  scanning them in parallel?

Thanks.



-Original Message-
From: Nico Williams 
To: General Discussion of SQLite Database 
Sent: Wed, Nov 5, 2014 12:14 pm
Subject: Re: [sqlite] SQLite as a meta database


IIRC there's a GNOME interface to databases, libgda, that works this way.

The fundamental problem with using SQLite3 virtual tables to access
remote/high-latency resources is that the SQLite3 VM and the generated
bytecode are very serial, therefore the VM can't make progress on
other parts of a query while waiting for a virtual table operation to
complete.

In principle nothing stops one from adding support for using
co-routines in the VM to better exploit concurrency in a query whose
plan could evince concurrency.  But that doesn't exist yet in SQLite3.

IMO this would be a very good thing to add to SQLite3, and it would
make it a great meta-DB.  But I'm not sure that the SQLite3 developers
would welcome it.

Nico
--
___
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] How to check if a record exists

[Drago, William @ CSG - NARDAEAST]

Thanks for the reply, Darren. No need for parameters here. There's no risk of 
SQL injection attacks and speed is not a concern.

Thanks again,
--
Bill Drago
Senior Engineer
L3 Communications / Narda Microwave East
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com



> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Darren Duncan
> Sent: Tuesday, November 04, 2014 4:59 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How to check if a record exists
>
> If all you want to know is whether a record matching a particular field
> value exists, then what you did is appropriate.  The EXISTS construct
> is generally for filtering one table with another.  That being said,
> you should be using bind parameters rather than stitching id into the
> SQL string itself, as that practice generally leads to huge security
> problems / SQL injection (although if your language is strongly typed
> an int wouldn't do it, but a string would). -- Darren Duncan
>
> On 2014-11-04 1:47 PM, Drago, William @ CSG - NARDAEAST wrote:
> > All,
> >
> > I've been pulling my hair out trying to figure how to use EXISTS.
> I've had no luck with it (syntax errors) and I've resorted to this. Is
> there a better/recommended way in SQLite to check if a record exists?
> >
> >
> > static bool IDisDuplicate(string dbFileName, int id)
> >  {
> >  int count;
> >  string connectionString = String.Format("Data
> Source={0}", dbFileName);
> >  using (SQLiteConnection connection = new
> SQLiteConnection(connectionString))
> >  {
> >  using (SQLiteCommand command =
> connection.CreateCommand())
> >  {
> >  connection.Open();
> >  command.CommandText =
> >  "SELECT count(1) DatasetID FROM UUT_Info
> where DatasetID = " + id + ";";
> >  count =
> Convert.ToInt32(command.ExecuteScalar());
> >  }
> >  }
> >
> >  if (count > 0)
> >  {
> >  return true;
> >  }
> >  else
> >  {
> >  return false;
> >  }
> >  }
> >
> >
> > Thanks,
> > --
> > Bill Drago
> > Senior Engineer
> > L3 Communications / Narda Microwave
> > East
> > 435 Moreland Road
> > Hauppauge, NY 11788
> > 631-272-5947 /
> > william.dr...@l-3com.com
> >
> >
> > CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and
> any attachments are solely for the use of the addressee and may contain
> information that is privileged or confidential. Any disclosure, use or
> distribution of the information contained herein is prohibited. In the
> event this e-mail contains technical data within the definition of the
> International Traffic in Arms Regulations or Export Administration
> Regulations, it is subject to the export control laws of the
> U.S.Government. The recipient should check this e-mail and any
> attachments for the presence of viruses as L-3 does not accept any
> liability associated with the transmission of this e-mail. If you have
> received this communication in error, please notify the sender by reply
> e-mail and immediately delete this message and any attachments.
> > ___
> > 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
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a meta database

[Nico Williams]

IIRC there's a GNOME interface to databases, libgda, that works this way.

The fundamental problem with using SQLite3 virtual tables to access
remote/high-latency resources is that the SQLite3 VM and the generated
bytecode are very serial, therefore the VM can't make progress on
other parts of a query while waiting for a virtual table operation to
complete.

In principle nothing stops one from adding support for using
co-routines in the VM to better exploit concurrency in a query whose
plan could evince concurrency.  But that doesn't exist yet in SQLite3.

IMO this would be a very good thing to add to SQLite3, and it would
make it a great meta-DB.  But I'm not sure that the SQLite3 developers
would welcome it.

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

Re: [sqlite] SQLite as a meta database

[Don V Nielsen]

Wow!  Please keep this discussion up-to-date because it is absolutely
fascinating what all of you are doing.  Thanks, dvn

On Wed, Nov 5, 2014 at 8:36 AM, Hugo Mercier 
wrote:

> Hi,
>
> Le 05/11/2014 14:16, Hick Gunter a écrit :
> > Hi,
> >
> > we have extensive experience with respect to the use of virtual tables
> in SQLite. In fact, the only native SQLite tables we use are in a
> configuration checking tool.
> >
> > We have "providers" from in-memory indexed tables, CTree (r) files,
> Oracle tables (read only), structured disk files, in-memory structures,
> binary records, etc.
> >
> > The trick is to be able to formulate your queries solely via comparison
> operators. This type of constraint gets passed to your xBestIndex function
> and can be processed there.
> >
> > e.g. provide 2 virtual fields _function and _frame
> >
> > SELECT * from VA, VB where VA._function='Intersect' and
> VA._frame=VB.geom;
> >
> > When called for VA or VB with the constraints (_function,=) and
> (frame,=) your xBestIndex function should return
> > - a value proportional to the effort of locating a record via the
> internal index as "estimated cost"
> > - a number that signifies "use internal index"
> > - set the "omit" flag fort he contraints
> > - set the "argvIndex" values for the constraints
> >
> > When called for VA or VB without constraints, your xBestIndex function
> should return
> > - a value proportional to the effort of a full table scan as "estimated
> cost"
> > - a number that signifies "full table scan"
> >
> > This will make SQLite read VB via full table scan, and look up VA via
> the internal index.
> > For each row retrieved from VB, your xFilter function will be called
> with the parameter values "Intersect" and "VB.geom".
> > SQLite will expect to retrieve exactly those rows of VA the "Intersect"
> with "VB.geom".
> >
> > Assuming that all _functions are commutative e.g. F(a,b) = F(b,a) you
> could provide a symmetrical solution:
> >
> > SELECT * from VA, VB where VA._function='Intersect' and
> VB._function='Intersect' and VA._frame=VB.geom and VB._frame=VA.geom;
> >
> > SQLite would then choose the smaller product of full table scan * lookup.
> >
> > I think it should be possible to have SQLite omit all the checks; if
> not, _frame needs to return geom (best guess...).
> >
>
> This 'virtual field' trick is very clever, thanks !
> I still have to figure out the details, but it could allow us to use
> internal spatial indices, without copying them locally.
>
> It confirms however the query would not be very natural to write for the
> end user and that we would have to assist him or automate the query
> generation.
>
> ___
> 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] fixing time-warp

[Stephan Beal]

On Wed, Nov 5, 2014 at 6:24 PM, Andreas Kupries 
wrote:

> How about looking into
>
> fossil test-shortest-path
>
> and see how it follows the path of revisions. ?
>

Oh, but were's the fun in rolling a wheel someone else already made round
;).

(moments later...)

It turns out i already ported the whole shortest-path/PathNode bits to
libfossil, but it hasn't yet been used anywhere except test code. Good
thing Fossil remembers everything so well, because i only vaguely remember
writing it.

[stephan@host:~/cvs/fossil/libfossil/f-apps]$ ./f-sanity -1
fcli.appName=./f-sanity
Checkout dir=/home/stephan/cvs/fossil/libfossil/
Checkout db=/home/stephan/cvs/fossil/libfossil/_FOSSIL_
Repo db=/home/stephan/cvs/fossil/libfossil.fsl
test_path_1()...
directOnly=1, oneWayOnly=0
Versions d7927376fa9d (5534) to c10d7424ae4c (5525): 3 steps
#1: 5534
#2: 5532 begat 5534
#3: 5528 begat 5532
#4: 5525 begat 5528
checkout UUID=220da67a06ee577d4667718b2ffe2f94c48ca338 (RID 6660)
Cached statement count: 3
If you made it this far, no assertions were triggered. Now try again with
valgrind.
Total run time: 0.007919 seconds of CPU time


Interestingly, that test uses rid comparison for determining whether to say
"begat" or "derives from" (not seen above), but i've learned in the mean
time the rid comparison isn't strictly reliable because it's legal for
artifacts to get blobified (getting a blob.rid value) in an arbitrary order.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fixing time-warp

[Andreas Kupries]

How about looking into

fossil test-shortest-path

and see how it follows the path of revisions. ?



On Wed, Nov 5, 2014 at 9:04 AM, E. Timothy Uy  wrote:
> Thank you, that is helpful information.
>
> On Tue, Nov 4, 2014 at 11:52 PM, Stephan Beal  wrote:
>
>> On Wed, Nov 5, 2014 at 7:07 AM, E. Timothy Uy  wrote:
>>
>> > The problem is ultimately not time-warps. DRH can confirm - the problem
>> is
>> > actually inside fossil and sqlite.fossil. Very early on in sqlite.fossil
>> > there are entries in the plink table where the parent id (pid) is greater
>> > than the commit id (cid). There are over a thousand of these.
>>
>>
>> Those IDs are _transient_, not part of the historical record. The "child ID
>> comes before parent ID" behaviour also appears on completely benign repos
>> (i've seen it before in my own while testing libfossil).
>>
>>
>>
>> > If I had more brain cells, I could perhaps invent a way to efficiently
>> use
>> > the plink table to generate the proper export list where parents always
>> > come before children regardless of mtime.
>> >
>>
>> i've also attempted something similar in libfossil, but haven't been
>> successful. The RIDs cannot be used to figure it out, and neither can
>> mtime. The only 100% reliable way i know of traversing the history is to
>> read each manifest, as the P-cards give us that piece of context we need to
>> know the ordering.
>>
>> --
>> - stephan beal
>> http://wanderinghorse.net/home/stephan/
>> http://gplus.to/sgbeal
>> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
>> those who insist on a perfect world, freedom will have to do." -- Bigby
>> Wolf
>> ___
>> 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



-- 
Andreas Kupries
Senior Tcl Developer
Code to Cloud: Smarter, Safer, Faster™
F: 778.786.1133
andre...@activestate.com, http://www.activestate.com
Learn about Stackato for Private PaaS: http://www.activestate.com/stackato

21'st Tcl/Tk Conference: Nov 10-14, Portland, OR, USA --
http://www.tcl.tk/community/tcl2014/
Send mail to tclconfere...@googlegroups.com, by Sep 8
Registration is open.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fixing time-warp

[E. Timothy Uy]

Thank you, that is helpful information.

On Tue, Nov 4, 2014 at 11:52 PM, Stephan Beal  wrote:

> On Wed, Nov 5, 2014 at 7:07 AM, E. Timothy Uy  wrote:
>
> > The problem is ultimately not time-warps. DRH can confirm - the problem
> is
> > actually inside fossil and sqlite.fossil. Very early on in sqlite.fossil
> > there are entries in the plink table where the parent id (pid) is greater
> > than the commit id (cid). There are over a thousand of these.
>
>
> Those IDs are _transient_, not part of the historical record. The "child ID
> comes before parent ID" behaviour also appears on completely benign repos
> (i've seen it before in my own while testing libfossil).
>
>
>
> > If I had more brain cells, I could perhaps invent a way to efficiently
> use
> > the plink table to generate the proper export list where parents always
> > come before children regardless of mtime.
> >
>
> i've also attempted something similar in libfossil, but haven't been
> successful. The RIDs cannot be used to figure it out, and neither can
> mtime. The only 100% reliable way i know of traversing the history is to
> read each manifest, as the P-cards give us that piece of context we need to
> know the ordering.
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
> those who insist on a perfect world, freedom will have to do." -- Bigby
> Wolf
> ___
> 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] sqlite-3.8.7.1: create table as select with join creates corrupted results

[Richard Hipp]

On Wed, Nov 5, 2014 at 9:55 AM, Richard Hipp  wrote:

>
>
> On Wed, Nov 5, 2014 at 4:03 AM, Nissl Reinhard 
> wrote:
>
>> Hi,
>>
>> I'm just upgrading from 3.8.5 to 3.8.7.1 and experience the following
>> incorrect behavior (see commands below):
>>
>
> Tracking the problem here:
> https://www.sqlite.org/src/tktview/094d39a4c95ee4abbc417f04214617675ba15c63
>
>
>

The problem should now be fixed on trunk.  Reinhard, please confirm.

-- 
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] LEFT joins affect query plan for semantically equal queries

[Keith Medcalf]

On Wednesday, 5 November, 2014 05:14, vita...@yourcmc.ru said:

>After playing a little with SQLite as a DBMS for Bugzilla, I've
>discovered that LEFT/INNER join affects query plan in a bad way even for
>semantically equal queries:

>SELECT * FROM bugs b INNER JOIN profiles p ON p.userid=b.assigned_to
>WHERE p.login_name='vita...@yourcmc.ru'

>Query plan:
>SEARCH TABLE profiles AS p USING INDEX profiles_login_name_idx
>(login_name=?)
>   SEARCH TABLE bugs AS b USING INDEX bugs_assigned_to_idx
>(assigned_to=?)

>But

>SELECT * FROM bugs b LEFT JOIN profiles p ON p.userid=b.assigned_to
>WHERE p.login_name='vita...@yourcmc.ru'

>Query plan:
>SCAN TABLE bugs AS b
>   SEARCH TABLE profiles AS p USING INTEGER PRIMARY KEY (rowid=?)

>Which is of course very slow.

The two queries are different.  They may end up with the same result, but you 
are asking different questions.  In the first you are returning only matching 
rows.  In the later you are requesting a projection (outer join) then applying 
the conditional (where) to the projection.  An index on profiles.userid will 
speed it up, but you are still asking a different question.  You cannot change 
the table visitation order around a left outer join because it changes the 
question.

You ought to obtain the same result with 

SELECT * 
  FROM bugs b CROSS JOIN profiles p 
 WHERE p.userid=b.assigned_to
   AND p.login_name='vita...@yourcmc.ru'

as well, it is the same inner join.  However you have precluded the optimizer 
from re-ordering the tables in the join by using the cross join keyword, so the 
plan will be the same as the second (left outer join) rather than the first 
(equijoin) query.

>Maybe you'll fix it in later sqlite versions? :)

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.




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

Re: [sqlite] sqlite-3.8.7.1: create table as select with join creates corrupted results

[Richard Hipp]

On Wed, Nov 5, 2014 at 4:03 AM, Nissl Reinhard 
wrote:

> Hi,
>
> I'm just upgrading from 3.8.5 to 3.8.7.1 and experience the following
> incorrect behavior (see commands below):
>

Tracking the problem here:
https://www.sqlite.org/src/tktview/094d39a4c95ee4abbc417f04214617675ba15c63



-- 
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] LEFT joins affect query plan for semantically equal queries

[David Woodhouse]

On Wed, 2014-11-05 at 16:00 +0200, RSmith wrote:
> On 2014/11/05 15:26, David Woodhouse wrote:
> > On Wed, 2014-11-05 at 15:13 +0200, RSmith wrote:
> > I don't think it's anything to do with the table data being special,
> is it? Isn't it generically true that for any LEFT JOIN of 
> > a,b WHERE b.anything IS NOT NULL, the results are going to be equal
> with an INNER JOIN?
> 
> Yes, I was simply pointing to the fact that if indeed you had NULL
> values and omit the specific WHERE clause the results will depend 
> on your table.

Right. The proposed optimisation does fundamentally depend on having a
WHERE clause which discards all the additional records which the LEFT
JOIN includes, that the INNER JOIN would not.

Put simply, you can only change LEFT JOIN to INNER JOIN if you're going
to throw away the records from the output which make them different.

The optimiser's detection of such a WHERE clause could actually start
off being relatively simplistic (perhaps only notice that it can make
this optimisation if it sees an explicit IS NOT NULL on a column from
the right-hand table), and could grow to recognise more cases over time
as required. False negatives don't really hurt; they're just a missed
optimisation.

For example, I don't think anyone would be losing sleep if it initially
failed to spot that 'WHERE b.foo IS NOT NULL OR b.bar IS NOT NULL' also
means the optimisation could kick in. Or 'WHERE a.foo = b.bar' when
a.foo is actually a "NOT NULL" field.

> In fact this may allow much wider use of Left-joins which is always
> the preferred join method system-side because it doesn't hide 
> missing/unlinked items. Ok, I convinced myself, +1 to the request. :)

If this is going to end up in the tracker, could I also request that the
optimisations from here be tracked too, please:
 https://www.mail-archive.com/sqlite-users@sqlite.org/msg86350.html
 https://www.mail-archive.com/sqlite-users@sqlite.org/msg86643.html


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

Re: [sqlite] SQLite as a meta database

[Hugo Mercier]

Hi,

Le 05/11/2014 14:16, Hick Gunter a écrit :
> Hi,
> 
> we have extensive experience with respect to the use of virtual tables in 
> SQLite. In fact, the only native SQLite tables we use are in a configuration 
> checking tool.
> 
> We have "providers" from in-memory indexed tables, CTree (r) files, Oracle 
> tables (read only), structured disk files, in-memory structures, binary 
> records, etc.
> 
> The trick is to be able to formulate your queries solely via comparison 
> operators. This type of constraint gets passed to your xBestIndex function 
> and can be processed there.
> 
> e.g. provide 2 virtual fields _function and _frame
> 
> SELECT * from VA, VB where VA._function='Intersect' and VA._frame=VB.geom;
> 
> When called for VA or VB with the constraints (_function,=) and (frame,=) 
> your xBestIndex function should return
> - a value proportional to the effort of locating a record via the internal 
> index as "estimated cost"
> - a number that signifies "use internal index"
> - set the "omit" flag fort he contraints
> - set the "argvIndex" values for the constraints
> 
> When called for VA or VB without constraints, your xBestIndex function should 
> return
> - a value proportional to the effort of a full table scan as "estimated cost"
> - a number that signifies "full table scan"
> 
> This will make SQLite read VB via full table scan, and look up VA via the 
> internal index.
> For each row retrieved from VB, your xFilter function will be called with the 
> parameter values "Intersect" and "VB.geom".
> SQLite will expect to retrieve exactly those rows of VA the "Intersect" with 
> "VB.geom".
> 
> Assuming that all _functions are commutative e.g. F(a,b) = F(b,a) you could 
> provide a symmetrical solution:
> 
> SELECT * from VA, VB where VA._function='Intersect' and 
> VB._function='Intersect' and VA._frame=VB.geom and VB._frame=VA.geom;
> 
> SQLite would then choose the smaller product of full table scan * lookup.
> 
> I think it should be possible to have SQLite omit all the checks; if not, 
> _frame needs to return geom (best guess...).
> 

This 'virtual field' trick is very clever, thanks !
I still have to figure out the details, but it could allow us to use
internal spatial indices, without copying them locally.

It confirms however the query would not be very natural to write for the
end user and that we would have to assist him or automate the query
generation.

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

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

[RSmith]

On 2014/11/05 15:26, David Woodhouse wrote:

On Wed, 2014-11-05 at 15:13 +0200, RSmith wrote:
I don't think it's anything to do with the table data being special, is it? Isn't it generically true that for any LEFT JOIN of 
a,b WHERE b.anything IS NOT NULL, the results are going to be equal with an INNER JOIN?


Yes, I was simply pointing to the fact that if indeed you had NULL values and omit the specific WHERE clause the results will depend 
on your table. You also need to look through the entire A table in a Left join because NULLs in the B table does not disqualify 
them, unless of course there is specifically either a NOT NULL or ColVal='Someting Specific'. I am now not sure if these would be 
the only set of cases for the optimisation, but they are the only ones coming to mind currently.



Or we could hope that the SQL database has a query planner of its own which can 
do such optimisations... :)


Quite a reasonable request if the optimisation can be boiled down to a specific always working set of rules. As I mention above, the 
ones coming to mind is that the WHERE clause is simple and specifically excludes NULLs. Even if NULLs are not specifically excluded, 
the column schema might include "NOT NULL" for that column which may also incur the optimisation (if it doesn't already), or even 
have the sqlite_statx analyze point out column that do not contain any nulls, or rather not as that may change. I know there is an 
optimisation step in SQLite NGQP that promotes ON clauses to WHERE clause statements (except for Left Joins for this reason), though 
more complex queries might have some caveats which eludes me currently. Also if several left-joins exist in the same query, a light 
implementation of this optimisation might influence the truth of the result (and of course I am not implying that the implementation 
would be light, just that the optimisation might need a lot of work). It also doesn't affect people who know when to not use a left 
join, but that is never a reason not to implement an obvious optimisation (as your use case illustrates).


In fact this may allow much wider use of Left-joins which is always the preferred join method system-side because it doesn't hide 
missing/unlinked items. Ok, I convinced myself, +1 to the request. :)


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

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

[David Woodhouse]

On Wed, 2014-11-05 at 15:13 +0200, RSmith wrote:
> On 2014/11/05 14:13, vita...@yourcmc.ru wrote:
> > Hi!
> >
> > After playing a little with SQLite as a DBMS for Bugzilla, I've discovered 
> > that LEFT/INNER join affects query plan in a bad way 
> > even for semantically equal queries:
> >
> > SELECT * FROM bugs b INNER JOIN profiles p ON p.userid=b.assigned_to WHERE 
> > p.login_name='vita...@yourcmc.ru'
> >
> > Query plan:
> > SEARCH TABLE profiles AS p USING INDEX profiles_login_name_idx 
> > (login_name=?)
> >   SEARCH TABLE bugs AS b USING INDEX bugs_assigned_to_idx (assigned_to=?)
> >
> > But
> >
> > SELECT * FROM bugs b LEFT JOIN profiles p ON p.userid=b.assigned_to WHERE 
> > p.login_name='vita...@yourcmc.ru'
> >
> > Query plan:
> > SCAN TABLE bugs AS b
> >   SEARCH TABLE profiles AS p USING INTEGER PRIMARY KEY (rowid=?)
> >
> > Which is of course very slow.
> 
> These queries are very different, not equal in any way (semantically
> or otherwise), the fact that they produce the exact same answer 
> is simply by virtue of your WHERE clause being specifically that and
> your table data being being special. Drop the where clause and 
> they produce very different results for different table data.

I don't think it's anything to do with the table data being special, is
it?

Isn't it generically true that for any LEFT JOIN of a,b WHERE b.anything
IS NOT NULL, the results are going to be equal with an INNER JOIN?

> Why would you use left join for that? Maybe this is just a really
> small extract from the real query you are doing and the real one 
> would shed more light?

In some ways this looks very similar to the optimisation I was asking
about a few weeks ago. The query is coming from the user, and is being
"translated" into SQL from whatever the user puts into the actual UI. We
normally don't make users type SQL into a text box :)

In this specific query the filter criteria just *happen* to exclude
unmatched results from the profiles tables and thus it turns out we
*could* use an INNER JOIN and get the same results.

You are right, we *could* make a query planner of our own, and squeeze
it in between the simple UI->SQL translation and the SQL database. It
could spot these optimisations and automatically change the type of join
where it sees that it could.

Or we could hope that the SQL database has a query planner of its own
which can do such optimisations... :)

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

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

[Richard Hipp]

On Wed, Nov 5, 2014 at 8:12 AM, Clemens Ladisch  wrote:

>
> The WHERE expression then makes the outer join meaningless,
>

Thank you, Clemens - I missed that detail.

So the suggestion is that we should enhance the SQLite query planner to
detect when the WHERE clause requires some column in the right-hand table
of a LEFT JOIN to be non-NULL and then promote that join to a normal INNER
JOIN.  That seems like a reasonable enhancement request which we will take
under advisement.

-- 
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 as a meta database

[Hick Gunter]

Hi,

we have extensive experience with respect to the use of virtual tables in 
SQLite. In fact, the only native SQLite tables we use are in a configuration 
checking tool.

We have "providers" from in-memory indexed tables, CTree (r) files, Oracle 
tables (read only), structured disk files, in-memory structures, binary 
records, etc.

The trick is to be able to formulate your queries solely via comparison 
operators. This type of constraint gets passed to your xBestIndex function and 
can be processed there.

e.g. provide 2 virtual fields _function and _frame

SELECT * from VA, VB where VA._function='Intersect' and VA._frame=VB.geom;

When called for VA or VB with the constraints (_function,=) and (frame,=) your 
xBestIndex function should return
- a value proportional to the effort of locating a record via the internal 
index as "estimated cost"
- a number that signifies "use internal index"
- set the "omit" flag fort he contraints
- set the "argvIndex" values for the constraints

When called for VA or VB without constraints, your xBestIndex function should 
return
- a value proportional to the effort of a full table scan as "estimated cost"
- a number that signifies "full table scan"

This will make SQLite read VB via full table scan, and look up VA via the 
internal index.
For each row retrieved from VB, your xFilter function will be called with the 
parameter values "Intersect" and "VB.geom".
SQLite will expect to retrieve exactly those rows of VA the "Intersect" with 
"VB.geom".

Assuming that all _functions are commutative e.g. F(a,b) = F(b,a) you could 
provide a symmetrical solution:

SELECT * from VA, VB where VA._function='Intersect' and 
VB._function='Intersect' and VA._frame=VB.geom and VB._frame=VA.geom;

SQLite would then choose the smaller product of full table scan * lookup.

I think it should be possible to have SQLite omit all the checks; if not, 
_frame needs to return geom (best guess...).

-Ursprüngliche Nachricht-
Von: Hugo Mercier [mailto:hugo.merc...@oslandia.com]
Gesendet: Mittwoch, 05. November 2014 10:09
An: General Discussion of SQLite Database
Betreff: [sqlite] SQLite as a meta database

Hi all,

Following a first discussion on sqlite-dev that was probably not the right 
place to post, I've been invited to repost here for a broader audience :)

I am a developer on QGIS and I am investigating the possible use of SQLite / 
Spatialite to extend QGIS relational-oriented features.

For now, we have what we call "data providers" that allow to open / read / 
modify geographic data from different data sources, more or less structured 
data from regular files or from local or remote databases.

Some database concepts are little by little put into QGIS, but some of us feel 
this is not exactly the right place for that.

So I am considering the use of the virtual table mechanism of SQLite to embed a 
powerful SQL engine in QGIS.
The idea would be to expose each type of GIS layer as a virtual table in 
SQLite. Then the user could use them for advanced queries such as
(spatial) joins.

GIS layers can already be RDBMS, like Postgresql/Postgis, MSSQL, Oracle 
spatial, etc.

There have been discussions on QGIS ML about that, and we are concerned about 
the performances of such an approach [1] [2] [3]

The main concern is about how to "translate" a main query that must in the end 
be split into queries to different databases. And especially regarding the use 
of native indices of such databases.

From previous answers on sqlite-dev, using dedicated fields estimatedCost and 
estimatedRows in xBestIndex could be enough to orient the planner if native 
indices on regular columns are present (and if the virtual table knows that)

For geometry column(s) that might be more complicated if I am correct.
For a query such as:
SELECT * FROM VA, VB where Intersects(VA.geom, VB.geom) where VA are virtual 
tables of say a PostGIS table and a Shapefile respectively, there is no way to 
inform xBestIndex to use the native spatial indices of VA or VB during the 
query.
Native spatial indices must be locally copied and explicitly used with 
spatialite like :
SELECT * FROM VA, VB where Intersects(VA.geom, VB.geom) AND VA.ROWID IN (
SELECT ROWID
FROM SpatialIndex
WHERE f_table_name = 'VA'
AND search_frame = VB.geom
)
Avoiding such explicit syntax and index duplication would require something 
like the implementation of GIST [4] in Sqlite, and having more generic 
constraints passed to xBestIndex, I guess. Not very easy.
Are there other possibilies that I am missing ?

The other concern is about accessing the parsed SQL query before executing it. 
This could be used to process the query in order to :
- collect information on it : table names, column names and types, especially 
detecting geometry columns
- bypass SQLite execution if the query is actually to be made on tables of the 
same database
- possibly do SQL->SQL transformations

Apparently accessing this parse tree is often 

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

[RSmith]

On 2014/11/05 14:13, vita...@yourcmc.ru wrote:

Hi!

After playing a little with SQLite as a DBMS for Bugzilla, I've discovered that LEFT/INNER join affects query plan in a bad way 
even for semantically equal queries:


SELECT * FROM bugs b INNER JOIN profiles p ON p.userid=b.assigned_to WHERE 
p.login_name='vita...@yourcmc.ru'

Query plan:
SEARCH TABLE profiles AS p USING INDEX profiles_login_name_idx (login_name=?)
  SEARCH TABLE bugs AS b USING INDEX bugs_assigned_to_idx (assigned_to=?)

But

SELECT * FROM bugs b LEFT JOIN profiles p ON p.userid=b.assigned_to WHERE 
p.login_name='vita...@yourcmc.ru'

Query plan:
SCAN TABLE bugs AS b
  SEARCH TABLE profiles AS p USING INTEGER PRIMARY KEY (rowid=?)

Which is of course very slow.


These queries are very different, not equal in any way (semantically or otherwise), the fact that they produce the exact same answer 
is simply by virtue of your WHERE clause being specifically that and your table data being being special. Drop the where clause and 
they produce very different results for different table data.


To clarify - Producing the same results does not imply semantic equality, if I asked you to find me some people of a specific kind 
in a specific bus, and someone else asks you to find the same type of people in the New-York downtown traffic... it "may" end up 
that those just happen to be the exact same people at the end, but the methods which you employ to look for those people will be 
significantly different, the result coinciding does not make the syntax semantically equal.


Telling SQL (of any flavour) to "Left join" is asking for a very different method of computation than when asking it to "inner join" 
and it should use the best tools for the request, it cannot see into the future to know the result will be fine if it stealthily 
just does the other method. (Though Analyze might help it to see a bit clearer and take that decision differently).


Why would you use left join for that? Maybe this is just a really small extract from the real query you are doing and the real one 
would shed more light?


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

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

[Clemens Ladisch]

vita...@yourcmc.ru wrote:
> SELECT * FROM bugs b LEFT JOIN profiles p ON p.userid=b.assigned_to WHERE 
> p.login_name='vita...@yourcmc.ru'
>
> Query plan:
> SCAN TABLE bugs AS b
>   SEARCH TABLE profiles AS p USING INTEGER PRIMARY KEY (rowid=?)
>
> Which is of course very slow.
>
> Maybe you'll fix it in later sqlite versions?

To find bugs that do not have a matching profiles row, the database
always must go through the entire bugs table.

The WHERE expression then makes the outer join meaningless, but SQLite's
query optimizer does not protect you from the consequences of writing
inconsistent queries; if you don't want an outer join, omit the LEFT in
the first place.


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

Re: [sqlite] sqlite3.8.7 crashes when creating an index with WAL mode disabled

[RP McMurphy]

>Perhaps this could be caught at compile time.  The process that creates
>the amalgamation could add a preprocessor macro defining e.g.
>_SQLITE_AMALGAMATION_.  The SQLITE_OMIT_* macros could test for that
>definition and #pragma error if found.  

I think there is a much easier solution. Since those four API bindings are 
needed by more than just the WAL code then simply remove the four checks and 
have the APIs always bind.

This appears to be a simple oversight about the usage of the APIs. They are 
used by code other than WAL. Specifically the indexing code also uses the file 
mapping APIs.

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

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

[Richard Hipp]

On Wed, Nov 5, 2014 at 7:13 AM,  wrote:

> Hi!
>
> After playing a little with SQLite as a DBMS for Bugzilla, I've discovered
> that LEFT/INNER join affects query plan in a bad way even for semantically
> equal queries:
>

I'm not sure what you mean by "semantically equal", but INNER JOIN and LEFT
JOIN do compute different answers in many cases.  And in particular, the
first query plan below (the one for INNER JOIN) will compute the wrong
answer for a LEFT JOIN.

SQLite seems to be doing the right thing here.


>
> SELECT * FROM bugs b INNER JOIN profiles p ON p.userid=b.assigned_to WHERE
> p.login_name='vita...@yourcmc.ru'
>
> Query plan:
> SEARCH TABLE profiles AS p USING INDEX profiles_login_name_idx
> (login_name=?)
>   SEARCH TABLE bugs AS b USING INDEX bugs_assigned_to_idx (assigned_to=?)
>
> But
>
> SELECT * FROM bugs b LEFT JOIN profiles p ON p.userid=b.assigned_to WHERE
> p.login_name='vita...@yourcmc.ru'
>
> Query plan:
> SCAN TABLE bugs AS b
>   SEARCH TABLE profiles AS p USING INTEGER PRIMARY KEY (rowid=?)
>
> Which is of course very slow.
>
> Maybe you'll fix it in later sqlite versions? :)
>
> --
> With best regards,
>   Vitaliy Filippov
> ___
> 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] LEFT joins affect query plan for semantically equal queries

[Simon Slavin]

On 5 Nov 2014, at 12:13pm, vita...@yourcmc.ru wrote:

> Which is of course very slow.

Can you please run ANALYZE then try the plans again ?

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

[sqlite] LEFT joins affect query plan for semantically equal queries

[vitalif]

Hi!

After playing a little with SQLite as a DBMS for Bugzilla, I've 
discovered that LEFT/INNER join affects query plan in a bad way even for 
semantically equal queries:


SELECT * FROM bugs b INNER JOIN profiles p ON p.userid=b.assigned_to 
WHERE p.login_name='vita...@yourcmc.ru'


Query plan:
SEARCH TABLE profiles AS p USING INDEX profiles_login_name_idx 
(login_name=?)
  SEARCH TABLE bugs AS b USING INDEX bugs_assigned_to_idx 
(assigned_to=?)


But

SELECT * FROM bugs b LEFT JOIN profiles p ON p.userid=b.assigned_to 
WHERE p.login_name='vita...@yourcmc.ru'


Query plan:
SCAN TABLE bugs AS b
  SEARCH TABLE profiles AS p USING INTEGER PRIMARY KEY (rowid=?)

Which is of course very slow.

Maybe you'll fix it in later sqlite versions? :)

--
With best regards,
  Vitaliy Filippov
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comparing to NULL in prepared statment

[Clemens Ladisch]

Baruch Burstein wrote:
> "SELECT col1 FROM table1 WHERE col2=:val"
>
> col2 is a textual string, and may sometimes be NULL. If I bind a string to
> :val it works fine. If I bind a null-pointer the comparison fails since it
> should be using ISNULL. Is there a way to do this correctly with a single
> statement?

In SQLite, IS works correctly with non-NULL values:

  SELECT col1 FROM table1 WHERE col2 IS :val

However, this would not use an index.  Better use two SQL statements.


And shouldn't the value in the database be an emptry string instead?


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

Re: [sqlite] sqlite-3.8.7.1: create table as select with join creates corrupted results

[Richard Hipp]

On Wed, Nov 5, 2014 at 4:03 AM, Nissl Reinhard 
wrote:

>
> I can provide the 17 kB bug.db if required.
>

Please do provide the database.  Private email to me will be fine.


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

[sqlite] SQLite as a meta database

[Hugo Mercier]

Hi all,

Following a first discussion on sqlite-dev that was probably not the
right place to post, I've been invited to repost here for a broader
audience :)

I am a developer on QGIS and I am investigating the possible use of
SQLite / Spatialite to extend QGIS relational-oriented features.

For now, we have what we call "data providers" that allow to open / read
/ modify geographic data from different data sources, more or less
structured data from regular files or from local or remote databases.

Some database concepts are little by little put into QGIS, but some of
us feel this is not exactly the right place for that.

So I am considering the use of the virtual table mechanism of SQLite to
embed a powerful SQL engine in QGIS.
The idea would be to expose each type of GIS layer as a virtual table in
SQLite. Then the user could use them for advanced queries such as
(spatial) joins.

GIS layers can already be RDBMS, like Postgresql/Postgis, MSSQL, Oracle
spatial, etc.

There have been discussions on QGIS ML about that, and we are concerned
about the performances of such an approach [1] [2] [3]

The main concern is about how to "translate" a main query that must in
the end be split into queries to different databases. And especially
regarding the use of native indices of such databases.

>From previous answers on sqlite-dev, using dedicated fields
estimatedCost and estimatedRows in xBestIndex could be enough to orient
the planner if native indices on regular columns are present (and if the
virtual table knows that)

For geometry column(s) that might be more complicated if I am correct.
For a query such as:
SELECT * FROM VA, VB where Intersects(VA.geom, VB.geom)
where VA are virtual tables of say a PostGIS table and a Shapefile
respectively,
there is no way to inform xBestIndex to use the native spatial indices
of VA or VB during the query.
Native spatial indices must be locally copied and explicitly used with
spatialite like :
SELECT * FROM VA, VB where Intersects(VA.geom, VB.geom) AND
VA.ROWID IN (
SELECT ROWID
FROM SpatialIndex
WHERE f_table_name = 'VA'
AND search_frame = VB.geom
)
Avoiding such explicit syntax and index duplication would require
something like the implementation of GIST [4] in Sqlite, and having more
generic constraints passed to xBestIndex, I guess. Not very easy.
Are there other possibilies that I am missing ?

The other concern is about accessing the parsed SQL query before
executing it. This could be used to process the query in order to :
- collect information on it : table names, column names and types,
especially detecting geometry columns
- bypass SQLite execution if the query is actually to be made on tables
of the same database
- possibly do SQL->SQL transformations

Apparently accessing this parse tree is often asked for here, and some
said [5] it could be nice to have for sqlite 4. Is it still something
considered for next versions ?

[1]
https://github.com/mhugo/QGIS-Enhancement-Proposals/blob/master/QEP-3-virtual-layers.rst
[2]
http://osgeo-org.1560.x6.nabble.com/1-many-relation-enhancements-td5168023.html#a5168822
[3]
http://osgeo-org.1560.x6.nabble.com/QEP-RFC-sqlite-virtual-tables-tt5168850.html
[4] http://gist.cs.berkeley.edu/
[5] https://www.mail-archive.com/sqlite-users%40sqlite.org/msg43159.html


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

[sqlite] sqlite-3.8.7.1: create table as select with join creates corrupted results

[Nissl Reinhard]

Hi,

I'm just upgrading from 3.8.5 to 3.8.7.1 and experience the following incorrect 
behavior (see commands below):

-   The where clause A <> B is expected to return only the first row, but 
returns all rows.

-   String concatenation on column B doesn't sometimes work (e. g. see end 
of line on row 7).

I can provide the 17 kB bug.db if required.

I've compiled sqlite3.exe and sqlite3.dll myself, but didn't change any of 
settings I've used for 3.8.5 when compiling 3.8.7.1.

D:\Projects\sqlite3\Debug_Win32>sqlite3 d:\temp\bug.db
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
sqlite> drop table if exists x ; create table x as select [Rohmaterial.OID] as 
A, cast(coalesce(TranslateID.TOID, [Rohmaterial.OID]) as text) as B, 
TranslateID.TOID as C from FertAuftragRohmaterial left join TranslateID on 
FertAuftragRohmaterial.[Rohmaterial.OID] = TranslateID.OID;
sqlite> select A, B, A || '_', B || '_', C from x where A <> B;
(0:0-3209838#0, 402)|(0:0-193511#56473, 402)|(0:0-3209838#0, 
402)_|(0:0-193511#56473, 402)_|(0:0-193511#56473, 402)
(0:0-24992#5293, 402)|(0:0-24992#5293, 402)|(0:0-24992#5293, 
402)_|(0:0-24992#5293, 402)_|
(0:0-25012#5513, 402)|(0:0-25012#5513, 402)|(0:0-25012#5513, 
402)_|(0:0-25012#5513, 402)_|
(0:0-25032#5758, 402)|(0:0-25032#5758, 402)|(0:0-25032#5758, 
402)_|(0:0-25032#5758, 402)_|
(0:0-25051#5698, 402)|(0:0-25051#5698, 402)|(0:0-25051#5698, 
402)_|(0:0-25051#5698, 402)_|
(0:0-25069#5453, 402)|(0:0-25069#5453, 402)|(0:0-25069#5453, 
402)_|(0:0-25069#5453, 402)_|
(0:0-25078#5343, 402)|(0:0-25078#5343, 402)|(0:0-25078#5343, 
402)_|(0:0-25078#5343, 402)|
(0:0-25427#3656, 402)|(0:0-25427#3656, 402)|(0:0-25427#3656, 
402)_|(0:0-25427#3656, 402)_|
(0:0-25457#4076, 402)|(0:0-25457#4076, 402)|(0:0-25457#4076, 
402)_|(0:0-25457#4076, 402)_|
(0:0-25447#3966, 402)|(0:0-25447#3966, 402)|(0:0-25447#3966, 
402)_|(0:0-25447#3966, 402)_|
(0:0-25477#4296, 402)|(0:0-25477#4296, 402)|(0:0-25477#4296, 
402)_|(0:0-25477#4296, 402)_|
(0:0-25487#4406, 402)|(0:0-25487#4406, 402)|(0:0-25487#4406, 
402)_|(0:0-25487#4406, 402)_|
(0:0-25527#4846, 402)|(0:0-25527#4846, 402)|(0:0-25527#4846, 
402)_|(0:0-25527#4846, 402)_|
(0:0-25537#4956, 402)|(0:0-25537#4956, 402)|(0:0-25537#4956, 
402)_|(0:0-25537#4956, 402)_|
(0:0-25565#4786, 402)|(0:0-25565#4786, 402)|(0:0-25565#4786, 
402)_|(0:0-25565#4786, 402)_|
(0:0-25592#4456, 402)|(0:0-25592#4456, 402)|(0:0-25592#4456, 
402)_|(0:0-25592#4456, 402)_|
(0:0-25628#4016, 402)|(0:0-25628#4016, 402)|(0:0-25628#4016, 
402)_|(0:0-25628#4016, 402)_|
(0:0-25574#4676, 402)|(0:0-25574#4676, 402)|(0:0-25574#4676, 
402)_|(0:0-25574#4676, 402)_|
(0:0-25646#3796, 402)|(0:0-25646#3796, 402)|(0:0-25646#3796, 
402)_|(0:0-25646#3796, 402)_|
(0:0-26531#6800, 402)|(0:0-26531#6800, 402)|(0:0-26531#6800, 
402)_|(0:0-26531#6800, 402)_|
(0:0-26567#6956, 402)|(0:0-26567#6956, 402)|(0:0-26567#6956, 
402)_|(0:0-26567#6956, 402)_|
(0:0-25637#3906, 402)|(0:0-25637#3906, 402)|(0:0-25637#3906, 
402)_|(0:0-25637#3906, 402)_|
(0:0-26585#6744, 402)|(0:0-26585#6744, 402)|(0:0-26585#6744, 
402)_|(0:0-26585#6744, 402)_|
(0:0-26576#6850, 402)|(0:0-26576#6850, 402)|(0:0-26576#6850, 
402)_|(0:0-26576#6850, 402)_|
(0:0-574781#15625, 402)|(0:0-574781#15625, 402)|(0:0-574781#15625, 
402)_|(0:0-574781#15625, 402)_|
(0:0-221176#56809, 402)|(0:0-221176#56809, 402)|(0:0-221176#56809, 
402)_|(0:0-221176#56809, 402)_|
(0:0-221160#56781, 402)|(0:0-221160#56781, 402)|(0:0-221160#56781, 
402)_|(0:0-221160#56781, 402)|
(0:0-574785#15638, 402)|(0:0-574785#15638, 402)|(0:0-574785#15638, 
402)_|(0:0-574785#15638, 402)_|
(0:0-574764#18136, 402)|(0:0-574764#18136, 402)|(0:0-574764#18136, 
402)_|(0:0-574764#18136, 402)_|
(0:0-3118276#78573, 402)|(0:0-3118276#78573, 402|(0:0-3118276#78573, 
402)_|(0:0-3118276#78573, 402_|
(0:0-506625#57242, 402)|(0:0-506625#57242, 402)|(0:0-506625#57242, 
402)_|(0:0-506625#57242, 402)_|
(0:0-574767#18147, 402)|(0:0-574767#18147, 402)|(0:0-574767#18147, 
402)_|(0:0-574767#18147, 402)_|
(0:0-574717#18019, 402)|(0:0-574717#18019, 402)|(0:0-574717#18019, 
402)_|(0:0-574717#18019, 402)_|
(0:0-574797#15677, 402)|(0:0-574797#15677, 402)|(0:0-574797#15677, 
402)_|(0:0-574797#15677, 402)|
(0:0-506628#17302, 402)|(0:0-506628#17302, 402)|(0:0-506628#17302, 
402)_|(0:0-506628#17302, 402)_|
(0:0-574722#18034, 402)|(0:0-574722#18034, 402)|(0:0-574722#18034, 
402)_|(0:0-574722#18034, 402)_|
(0:0-506631#17313, 402)|(0:0-506631#17313, 402)|(0:0-506631#17313, 
402)_|(0:0-506631#17313, 402)|
(0:0-506636#17335, 402)|(0:0-506636#17335, 402)|(0:0-506636#17335, 
402)_|(0:0-506636#17335, 402)_|

Mit freundlichen Grüßen / Best regards

Reinhard Nißl
Softwareentwicklung

F.EE GmbH Informatik + Systeme
Industriestraße 6e, 92431 Neunburg  v. W.
Phone  +49 9672 506-198
Fax  +49 9672 506-10198
E-Mail: reinhard.ni...@fee.de
Internet: www.fee.de - 
www.fee-systemhaus.de - 
www.factwork.de

Geschäftsführung: J. u. G. 

Re: [sqlite] Comparing to NULL in prepared statment

[Baruch Burstein]

On Wed, Nov 5, 2014 at 10:52 AM, Martin Engelschalk <
engelsch...@codeswift.com> wrote:

> Hi Baruch,
>
> in such cases I use one of the following methods:
>
> SELECT col1 FROM table1 WHERE col2=:val or (col2 is null and :val is null)
>
> SELECT col1 FROM table1 WHERE coalesce(col2, '#') = coalesce(:val, '#')
>
> where '#' is a value that i know is never used in col2 (this is a drawback)


Thanks. Assuming col2 is indexed, any idea which is faster (I am guessing
the first, but didn't test)


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comparing to NULL in prepared statment

[Martin Engelschalk]

Hi Baruch,

in such cases I use one of the following methods:

SELECT col1 FROM table1 WHERE col2=:val or (col2 is null and :val is null)

SELECT col1 FROM table1 WHERE coalesce(col2, '#') = coalesce(:val, '#')

where '#' is a value that i know is never used in col2 (this is a drawback)

Yours
Martin

Am 05.11.2014 09:46, schrieb Baruch Burstein:

Hi all,

This is not really a sqlite specific question, but I was wondering if there
might be a sqlite specific answer.
I prepare the following statement:

"SELECT col1 FROM table1 WHERE col2=:val"

col2 is a textual string, and may sometimes be NULL. If I bind a string to
:val it works fine. If I bind a null-pointer the comparison fails since it
should be using ISNULL. Is there a way to do this correctly with a single
statement? I know I can have 2 statments and test the string pointer at
runtime to determine which one to run, but that seems awkward.



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

[sqlite] Comparing to NULL in prepared statment

[Baruch Burstein]

Hi all,

This is not really a sqlite specific question, but I was wondering if there
might be a sqlite specific answer.
I prepare the following statement:

"SELECT col1 FROM table1 WHERE col2=:val"

col2 is a textual string, and may sometimes be NULL. If I bind a string to
:val it works fine. If I bind a null-pointer the comparison fails since it
should be using ISNULL. Is there a way to do this correctly with a single
statement? I know I can have 2 statments and test the string pointer at
runtime to determine which one to run, but that seems awkward.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users