[sqlite] Why SQLite does not use a web-based forum?

[Nicolas Jäger]

hi,

> > Why SQLite does not utilize a web-based forum for all users
> > discuss problems? I think that will be more convenient and can help
> > more people.  

not agree! mailing lists are much more easier to use. Open your email in 
whatever client and you
can see the whole discussions open in all mailing list you suscribe. No more 
tons of accounts with
tons of pass and tons of usernames etc.

the only things I miss it's the code balises, but the best solution is to use 
some pastebin anyway.

forums links break much more often than mailing list for whatever reason, 
updated, deleted, domain
closed...

last thing, (still pro), even if it's not an obligation and verified, people 
tend more to use their
real name.

regards
Nicolas

[sqlite] Correlated subquery throwing an error

[Gary Briggs]

On Sun, Feb 14, 2016 at 07:31:43PM -0500, Richard Hipp wrote:
> On 2/14/16, Gary Briggs  wrote:
> >
> > "For every row in that table, I want the entire row in that same table
> > within a certain distance [eg 25], with the lowest "val". For rows
> > with the same "val", I want to use lowest distance as a tie breaker."
> >
> 
> Untested code follows.

I appreciate all the help I can get, at this point :-)

> I'l build up the answer in stages.  First, a query that finds the
> entry with 25 of @x, at y that as the minimum "val" with distance as a
> tie-breaker.
> 
> SELECT id
> FROM foo
> WHERE distance(@x, at y,x,y)<=25
> ORDER BY val, distance(@x, at y,x,y)
> LIMIT 1;

Yeah, this is what my current "solution" converged on [where I do n+1
queries in code, instead of having it all in a single query].

> For every row, find the ID of the row in the same table within 25 of
> the row and with the minumum val, distance.  (This is almost what you
> want, but only returns the IDs of the rows, not the full row.)
> 
> SELECT
>   a.id AS a_id,
>   (SELECT b.id
>FROM foo AS b
>WHERE b.id!=a.id
>  AND distance(a.x,a.y,b.x,b.y)<=25
>ORDER BY b.val, distance(a.x,a.y,b.x,b.y))
> FROM foo AS a

This is the bit that doesn't seem to work; having
distance(a.x,a.y,b.x,b.y) in the ORDER BY clause on the inner query is
what appears to cause the error that it can't find a.x, from the outer query.

It's not tied to it being a custom function; I can replace it with
something else silly and get the same problem:

SELECT
   a.id AS a_id,
   (SELECT b.id
FROM foo AS b
WHERE b.id!=a.id
  AND distance(a.x,a.y,b.x,b.y)<=25
ORDER BY b.val, (b.x-a.x)
 FROM foo AS a

no such column: foo.x

Thank-you!
Gary

[sqlite] Correlated subquery throwing an error

[Gary Briggs]

On Mon, Feb 15, 2016 at 11:11:26AM +1100, david at andl.org wrote:
> Why not
> 
> SELECT foo.*,
>   (SELECT id, DIST(foo.x, foo.y, nearest.x, nearest.y) AS d
>FROM foo AS nearest
>WHERE d < 25
>ORDER BY val, d
>LIMIT 1) AS id2
> FROM foo

That specific SQL gives the error:
"only a single result allowed for a SELECT that is part of an expression"
But when I removed the "id" column from the inner select, it worked. It's
when I replace "d" with the full DISTANCE() in just the ORDER BY clause
that it stops working.

Works:
 SELECT foo.*,
   (SELECT DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) AS d
FROM foo AS nearest
WHERE d < 25
ORDER BY val, d
LIMIT 1) AS id2
 FROM foo
LIMIT 5

Works:
 SELECT foo.*,
   (SELECT DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) AS d
FROM foo AS nearest
WHERE DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0)  < 25
ORDER BY val, d
LIMIT 1) AS id2
 FROM foo
LIMIT 5

Doesn't work ["no such column: foo.x"]:
 SELECT foo.*,
   (SELECT DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0) AS d
FROM foo AS nearest
WHERE d  < 25
ORDER BY val, DISTANCE(foo.x, foo.y, nearest.x, nearest.y,0)
LIMIT 1) AS id2
 FROM foo
LIMIT 5


Thanks,
Gary

> Regards
> David M Bennett FACS
> 
> Andl - A New Database Language - andl.org
> 
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Gary
> Briggs
> Sent: Monday, 15 February 2016 10:43 AM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Correlated subquery throwing an error
> 
> I posted a question on stackoverflow, here:
> http://stackoverflow.com/questions/35382897/implementing-a-sql-query-without
> -window-functions
> 
> In short, I have a table that I'm trying to query:
> CREATE TABLE foo (
>id INTEGER PRIMARY KEY,
>x REAL NOT NULL,
>y REAL NOT NULL,
>val REAL NOT NULL,
>UNIQUE(x,y));
> 
> I have a helper function, DISTANCE(x1,y1,x2,y2).
> The results I'm looking for are:
> 
> "For every row in that table, I want the entire row in that same table
> within a certain distance [eg 25], with the lowest "val". For rows with the
> same "val", I want to use lowest distance as a tie breaker."
> 
> The answer I got on stackoverflow included this correlated subquery, but
> it's not working:
> SELECT foo.*,
>   (SELECT id
>FROM foo AS nearest
>WHERE DIST(foo.x, foo.y, nearest.x, nearest.y) < 25
>ORDER BY val, DIST(foo.x, foo.y, nearest.x, nearest.y)
>LIMIT 1) AS id2
> FROM foo
> 
> I get the error "no such column: foo.x"
> 
> I tried rewriting it to have the subquery in a WHERE clause instead [not
> quite the same query, but I think the problem I have is the same]
> 
> SELECT outerfoo.*
>  FROM foo outerfoo
>  WHERE outerfoo.id=(SELECT id
>   FROM foo AS nearest
>   WHERE DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y) <= 25
>   ORDER BY val, DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y)
>   LIMIT 1)
> 
> And now I get the error "no such column: outerfoo.x"
> 
> Can anyone advise on what I might do?
> 
> Thank-you very much,
> Gary
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 

[sqlite] Correlated subquery throwing an error

[Richard Hipp]

On 2/14/16, Gary Briggs  wrote:
>
> "For every row in that table, I want the entire row in that same table
> within a certain distance [eg 25], with the lowest "val". For rows
> with the same "val", I want to use lowest distance as a tie breaker."
>

Untested code follows.

I'l build up the answer in stages.  First, a query that finds the
entry with 25 of @x, at y that as the minimum "val" with distance as a
tie-breaker.

SELECT id
FROM foo
WHERE distance(@x, at y,x,y)<=25
ORDER BY val, distance(@x, at y,x,y)
LIMIT 1;

For every row, find the ID of the row in the same table within 25 of
the row and with the minumum val, distance.  (This is almost what you
want, but only returns the IDs of the rows, not the full row.)

SELECT
  a.id AS a_id,
  (SELECT b.id
   FROM foo AS b
   WHERE b.id!=a.id
 AND distance(a.x,a.y,b.x,b.y)<=25
   ORDER BY b.val, distance(a.x,a.y,b.x,b.y))
FROM foo AS a

Use the output of the previous query to find the full rows:

SELECT a_id, c.*
  FROM foo AS c,
   (SELECT
 a.id AS a_id,
 (SELECT b.id
FROM foo AS b
WHERE b.id!=a.id
  AND distance(a.x,a.y,b.x,b.y)<=25
ORDER BY b.val, distance(a.x,a.y,b.x,b.y))
FROM foo AS a)
WHERE c.id=b_id;

I repeat:  The above is untested.  It is also, clearly, an O(N*N)
algorithm at best.  If performance is a secondary goal, an R-Tree
index may be useful here.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

[Clemens Ladisch]

Dave Blake wrote:
> Is there anything I can do to get the optimiser to perform 1) with the same
> efficiency as 2)?

See point 3 of ; the
optimizer has problems when you use a subquery (i.e., a view) at the
right side of a left join.

If you really want to keep the view, you could replace the outer join
with an inner join, and add the non-matching rows separately:

SELECT A.*, view.* FROM A JOIN view ON ... WHERE ...
UNION ALL
SELECT A.*, NULL FROM A WHERE ... AND id NOT IN (SELECT A_id FROM view);

Whether this is better than breaking up the view is something you have
to decide yourself.


Regards,
Clemens

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

[Dave Blake]

Thank you for your help. I now understand why the optimiser has
difficulties and 1) is slower than 2)

Thank you also for the union suggestion, although not sure in this case
that it makes anything more readable than breaking the view into tables.

In my real world application my solution is to avoid the left join by
ensuring that all songs have at least one artist. This is a better data
design in the end.

On 14 February 2016 at 18:00, Clemens Ladisch  wrote:

> Dave Blake wrote:
> > Is there anything I can do to get the optimiser to perform 1) with the
> same
> > efficiency as 2)?
>
> See point 3 of ; the
> optimizer has problems when you use a subquery (i.e., a view) at the
> right side of a left join.
>
> If you really want to keep the view, you could replace the outer join
> with an inner join, and add the non-matching rows separately:
>
> SELECT A.*, view.* FROM A JOIN view ON ... WHERE ...
> UNION ALL
> SELECT A.*, NULL FROM A WHERE ... AND id NOT IN (SELECT A_id FROM view);
>
> Whether this is better than breaking up the view is something you have
> to decide yourself.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Correlated subquery throwing an error

[Gary Briggs]

I posted a question on stackoverflow, here:
http://stackoverflow.com/questions/35382897/implementing-a-sql-query-without-window-functions

In short, I have a table that I'm trying to query:
CREATE TABLE foo (
   id INTEGER PRIMARY KEY,
   x REAL NOT NULL,
   y REAL NOT NULL,
   val REAL NOT NULL,
   UNIQUE(x,y));

I have a helper function, DISTANCE(x1,y1,x2,y2).
The results I'm looking for are:

"For every row in that table, I want the entire row in that same table
within a certain distance [eg 25], with the lowest "val". For rows
with the same "val", I want to use lowest distance as a tie breaker."

The answer I got on stackoverflow included this correlated subquery,
but it's not working:
SELECT foo.*,
  (SELECT id
   FROM foo AS nearest
   WHERE DIST(foo.x, foo.y, nearest.x, nearest.y) < 25
   ORDER BY val, DIST(foo.x, foo.y, nearest.x, nearest.y)
   LIMIT 1) AS id2
FROM foo

I get the error "no such column: foo.x"

I tried rewriting it to have the subquery in a WHERE clause instead
[not quite the same query, but I think the problem I have is the same]

SELECT outerfoo.*
 FROM foo outerfoo
 WHERE outerfoo.id=(SELECT id
  FROM foo AS nearest
  WHERE DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y) <= 25
  ORDER BY val, DISTANCE(outerfoo.x, outerfoo.y, nearest.x, nearest.y)
  LIMIT 1)

And now I get the error "no such column: outerfoo.x"

Can anyone advise on what I might do?

Thank-you very much,
Gary

[sqlite] Why SQLite does not use a web-based forum?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 14/02/16 17:19, admin at shuling.net wrote:
> Why SQLite does not utilize a web-based forum for all users
> discuss problems? I think that will be more convenient and can help
> more people.

Here you go in several different formats:

  http://dir.gmane.org/gmane.comp.db.sqlite.general

Note that is still using the mailing list, so all users can still
participate.

BTW web based forums tend to have terrible usability around discussion
of technical topics due to poor handling of threading.  Pick whatever
works for you.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iEYEARECAAYFAlbBNi4ACgkQmOOfHg372QSYjACfbCloKBmKZVYdHpVdgHVOcZHP
bssAn1rodsnBCUiBsS0sC39g3j3EdaGY
=f5Ig
-END PGP SIGNATURE-

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

[Dave Blake]

>You need to sort the output of EXPLAIN QUERY PLAN.

Apologies for not spoting the need to sort the query plan! A fundamental
error on my part.

However, in real world application 1) is significantly slower than 2)
despite returing the same number of rows. If I correctly read the plans
this time, it is because 2) searches the song_artist table using an index
but 1) scans the entire table.

Is there anything I can do to get the optimiser to perform 1) with the same
efficiency as 2)?

In real world application I use a "songartistview" as a means to list and
alias name the fields wanted from joins over several tables. This was fine
until I needed to use it in a left join, and the speed plumited. For
readablity and management I would like to continute to use a view, but it
is so slow. Is there any alternate to having to use tables like 2) does?

On 14 February 2016 at 15:15, Clemens Ladisch  wrote:

> Dave Blake wrote:
> > What I see as wrong is that in 1) (and 4) ) we have a query of the form
> > A LEFT JOIN B WHERE clause involving index fields on A
> >
> > yet the optimiser does not search A, the outer table, first using the
> index.
>
> EXPLAIN QUERY PLAN SELECT song.*, songartistview.* FROM song LEFT JOIN
> songartistview ON song.idSong = songartistview.idSong WHERE song.idSong =1;
> 1|0|0|SCAN TABLE song_artist
> 1|1|1|SEARCH TABLE artist USING INTEGER PRIMARY KEY (rowid=?)
> 0|0|0|SEARCH TABLE song USING INTEGER PRIMARY KEY (rowid=?)
> 0|1|1|SCAN SUBQUERY 1
>
> You need to sort the output of EXPLAIN QUERY PLAN.
> The song table is searched first.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

[Clemens Ladisch]

Dave Blake wrote:
> What I see as wrong is that in 1) (and 4) ) we have a query of the form
> A LEFT JOIN B WHERE clause involving index fields on A
>
> yet the optimiser does not search A, the outer table, first using the index.

EXPLAIN QUERY PLAN SELECT song.*, songartistview.* FROM song LEFT JOIN 
songartistview ON song.idSong = songartistview.idSong WHERE song.idSong =1;
1|0|0|SCAN TABLE song_artist
1|1|1|SEARCH TABLE artist USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|SEARCH TABLE song USING INTEGER PRIMARY KEY (rowid=?)
0|1|1|SCAN SUBQUERY 1

You need to sort the output of EXPLAIN QUERY PLAN.
The song table is searched first.


Regards,
Clemens

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

[Dave Blake]

>1) has "song LEFT JOIN (song_artist JOIN artist)".
>3) has "(song LEFT JOIN song_artist) JOIN artist".
>2) has "(song LEFT JOIN song_artist) LEFT JOIN artist".
>4) has "song LEFT JOIN (song_artist LEFT JOIN artist)".

OK, I see how you are saying the queries differ.

What I see as wrong is that in 1) (and 4) ) we have a query of the form
A LEFT JOIN B WHERE clause involving index fields on A

yet the optimiser does not search A, the outer table, first using the index.

You rightly said
> A left join forces SQLite to scan the outer table first.  This is
> necessary for the join to work correctly.

But it is not scanning song, the outer table, first. It is not optimising
correctly.

Is there a way with 1) to get it to scan song first? Can you see why that
would be the optimal plan?

On 14 February 2016 at 10:49, Clemens Ladisch  wrote:

> Dave Blake wrote:
> > 3) SELECT song.*, song_artist.*, artist.*
> > FROM song LEFT JOIN song_artist ON song.idSong = song_artist.idSong
> > JOIN artist ON song_artist.idArtist = artist.idArtist
> > WHERE song.idSong =1
> >
> > This ... has the same join combination as 1).
>
> No.
> 1) has "song LEFT JOIN (song_artist JOIN artist)".
> 3) has "(song LEFT JOIN song_artist) JOIN artist".
>
> > Similarly trying a view using outer join
> >
> > CREATE VIEW songartistleftview AS SELECT song_artist.idSong AS idSong,
> > song_artist.idArtist AS idArtist,
> > artist.strArtist AS strArtist
> > FROM song_artist LEFT JOIN artist ON song_artist.idArtist =
> artist.idArtist
> >
> > New query
> > 4) SELECT song.*, songartistleftview.* FROM song
> > LEFT JOIN songartistleftview ON song.idSong = songartistleftview.idSong
> > WHERE song.idSong =1
> >
> > 4) has same slow query plan as 1) despite having all left joins like 2).
>
> There are still differences:
> 2) has "(song LEFT JOIN song_artist) LEFT JOIN artist".
> 4) has "song LEFT JOIN (song_artist LEFT JOIN artist)".
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

[Clemens Ladisch]

Dave Blake wrote:
> 3) SELECT song.*, song_artist.*, artist.*
> FROM song LEFT JOIN song_artist ON song.idSong = song_artist.idSong
> JOIN artist ON song_artist.idArtist = artist.idArtist
> WHERE song.idSong =1
>
> This ... has the same join combination as 1).

No.
1) has "song LEFT JOIN (song_artist JOIN artist)".
3) has "(song LEFT JOIN song_artist) JOIN artist".

> Similarly trying a view using outer join
>
> CREATE VIEW songartistleftview AS SELECT song_artist.idSong AS idSong,
> song_artist.idArtist AS idArtist,
> artist.strArtist AS strArtist
> FROM song_artist LEFT JOIN artist ON song_artist.idArtist = artist.idArtist
>
> New query
> 4) SELECT song.*, songartistleftview.* FROM song
> LEFT JOIN songartistleftview ON song.idSong = songartistleftview.idSong
> WHERE song.idSong =1
>
> 4) has same slow query plan as 1) despite having all left joins like 2).

There are still differences:
2) has "(song LEFT JOIN song_artist) LEFT JOIN artist".
4) has "song LEFT JOIN (song_artist LEFT JOIN artist)".


Regards,
Clemens

[sqlite] Temporary table in SQLite

[ad...@shuling.net]

Hi,

Is it possible to create a temporary table in SQLite so that it will only be
visible to the current session and be destroyed after the session is closed?
Just like SQL Server
http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in
-SQL-Server ?

Thanks

[sqlite] Temporary table in SQLite

[R Smith]

Yes, you can do:

CREATE TEMP TABLE t (a, b, etc);

and it will only persist the duration of the current connection. i.e. 
Once you Close the DB connection, that table is no more, and even while 
the connection is open, it is only visible to the connection which 
created it.

There are even ways to make the temp table be in memory only and other 
advanced options, but you need to read the documents for more 
information. You can start here:
http://sqlite.org/lang_createtable.html

You will definitely need to read up on Type conventions in SQLite, since 
it might not work as expected in MSSQL - for instance, a column declared 
as "aCol VARCHAR(50)" will work, but it won't limit the field to only 50 
characters, it will happily store up to 2 Gigabytes in there. Read here:
https://www.sqlite.org/datatype3.html

I assume you are somewhat new to SQLite coming from an MSSQL background, 
so allow me to mention that, apa`r`t from Temp tables, you can also 
create Views and use Common Table Expressions (just like in MSSQL). 
Documentation is easy to find from the site, but if you have any 
specific questions, please ask here.

Some good free DB Admin programs out there will make learning it easy 
with SQL examples and the like. You could try DB Browser for SQLite 
http://sqlitebrowser.org/ with multi-platform support or, if you use 
Windows, SQLitespeed http://www.sqlc.rifin.co.za/

Good luck,
Ryan


On 2016/02/14 5:13 AM, admin at shuling.net wrote:
> Hi,
>
> Is it possible to create a temporary table in SQLite so that it will only be
> visible to the current session and be destroyed after the session is closed?
> Just like SQL Server
> http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in
> -SQL-Server ?
>
> Thanks
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users