Re: [sqlite] LEFT JOIN: ON vs. WHERE

[James K. Lowden]

On Mon, 28 Oct 2019 12:10:38 -0600
"Keith Medcalf"  wrote:

> CREATE TABLE t1 (a, b);
> INSERT INTO t1 VALUES(1,2);
> INSERT INTO t1 VALUES(2,3);
> INSERT INTO t1 VALUES(1,3);
> CREATE TABLE t2 (b, c);
> INSERT INTO t2 VALUES(2,3);
> INSERT INTO t2 VALUES(3,4);
> select * from t1 left join t2 on t1.b == t2.b and t1.a == 1;
> 1|2|2|3
> 2|3||
> 1|3|3|4

Thank you, Keith.  What you're saying is that when ON applies to the
outer table, it still constrains the JOIN.  The tuple

2 3 3 4

would have been produced, but for the ON restriction 

t.a = 1

> This sort of thing is useful

I don't doubt it.  I can't remember ever having written a query like
that (not knowing that's what it would do).  I would probably have
expressed the giraffe-neck problem as 

select * from t1 
left join ( select * from t2 where b <> 1 ) as t2
on t1.b = t2.b

because usually, in domains I worked with, the constraint on the inner
table wasn't a function of the join criteria, but of some type or group
or catagory column, or date.  

--jkl

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

Re: [sqlite] Single or double quotes when defining alias?

[Thomas Kurz]

Ok, thanks for everone's answer.


- Original Message - 
From: James K. Lowden 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Monday, October 28, 2019, 18:27:06
Subject: [sqlite] Single or double quotes when defining alias?

On Fri, 25 Oct 2019 23:55:20 +0200
Thomas Kurz  wrote:

> SELECT column1 AS 'c'
> --or--
> SELECT column2 AS "d"

> On the one hand, the name refers to a column or table identifier. 

The SQL-92 standard refers to that kind of name as a
"correlation name", and its BNF grammar designates a correlation name
as a kind of indentifier.  Therefore, syntactically, "d" is correct
because double-quotes are used to quote identifiers.  

Which was news to me.  I've always used single-quotes for
correlation names (on creation, never reference).  Not because they need
quoting. I never choose a correlation name that needs to be quoted;
normally they're just 3 lower-case letters, at most.  I would quote
them only to make them stand out for the syntax highlighter.  And who
doesn't like pretty SQL?  

--jkl


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

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

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[Keith Medcalf]

On Monday, 28 October, 2019 11:19, James K. Lowden  
wrote:

>When wrestling with this topic, it's useful to remember that ON doesn't
>constrain the outer table:
>
>   FROM R LEFT OUTER JOIN S
>   ON R.key = S.key AND R.col = 'foo'

>Here,

>   AND R.col = 'foo'

>while valid syntax (unfortunately), has no effect.  R is the outer
>table, and all rows of the outer table are always produced, no matter
>what ON says about it.

Saying that it has no effect is incorrect.  It does constrain the inner loop 
candidates.  It has no effect on the outer loop candidate selection because we 
are already inside the inner loop before that condition is evaluated.  In other 
words, there can be no matching t2 tuple if the t1 tuple does not have t1.a == 
1.

CREATE TABLE t1 (a, b);
INSERT INTO t1 VALUES(1,2);
INSERT INTO t1 VALUES(2,3);
INSERT INTO t1 VALUES(1,3);
CREATE TABLE t2 (b, c);
INSERT INTO t2 VALUES(2,3);
INSERT INTO t2 VALUES(3,4);
select * from t1 left join t2 on t1.b == t2.b and t1.a == 1;
1|2|2|3
2|3||
1|3|3|4

This sort of thing is useful, for example, if t2 represents data about the neck 
length of giraffes, and t1.a tells the animal type (1 == giraffe).  Clearly 
things which are not giraffe's do not have a giraffe neck length so this 
constraint is properly part of the inner candidate selection.

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



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

Re: [sqlite] Single or double quotes when defining alias?

[James K. Lowden]

On Fri, 25 Oct 2019 23:55:20 +0200
Thomas Kurz  wrote:

> SELECT column1 AS 'c'
> --or--
> SELECT column2 AS "d"
> 
> On the one hand, the name refers to a column or table identifier. 

The SQL-92 standard refers to that kind of name as a
"correlation name", and its BNF grammar designates a correlation name
as a kind of indentifier.  Therefore, syntactically, "d" is correct
because double-quotes are used to quote identifiers.  

Which was news to me.  I've always used single-quotes for
correlation names (on creation, never reference).  Not because they need
quoting. I never choose a correlation name that needs to be quoted;
normally they're just 3 lower-case letters, at most.  I would quote
them only to make them stand out for the syntax highlighter.  And who
doesn't like pretty SQL?  

--jkl


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

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[James K. Lowden]

On Sun, 27 Oct 2019 15:09:03 -0700
Benjamin Asher  wrote:

> My colleagues and I are trying to understand the role of ON
> constraints vs WHERE clauses in JOINs. 

ON applies before JOIN.  WHERE applies after.  

That's a loose interpretation, but IMO it's nevertheless a useful way
to think about it.  

It *mostly* doesn't matter.  It was invented for outer joins.  

In SQL-89, there was only the inner join. There was no JOIN keyword.
You listed all the tables in FROM: 

FROM R, S
WHERE R.key = S.key

and all was good with the world.  

To support outer joins, vendors invented various syntaxes.  To unifiy
them, SQL-92 defined JOIN and ON: 

FROM R JOIN S
ON R.key = S.key

with that syntax, JOIN could be modifed to be LEFT or RIGHT outer, and
the inner table could be further restricted:  

FROM R LEFT OUTER JOIN S
ON R.key = S.key AND S.col = 'foo'

That means: all rows from R joined to rows in S for which col = 'foo'.
If no such S rows exist, every result row will be NULL for the S
columns.  Compare with 

FROM R LEFT OUTER JOIN S
ON R.key = S.key 
WHERE S.col = 'foo'

Here, R and S are joined, and the WHERE test is applied to the joined
result.  If no such S rows exist, there will be no result rows at all.

When wrestling with this topic, it's useful to remember that ON doesn't
constrain the outer table: 

FROM R LEFT OUTER JOIN S
ON R.key = S.key AND R.col = 'foo'

Here, 

AND R.col = 'foo'

while valid syntax (unfortunately), has no effect.  R is the outer
table, and all rows of the outer table are always produced, no matter
what ON says about it.  Perhaps the above should mean: 

FROM (select * from R WHERE R.col = 'foo') as R 
LEFT OUTER JOIN S 
ON R.key = S.key 

but it does not.  Perfection remains, as ever, elusive.  

--jkl


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

Re: [sqlite] Roadmap?

[Jens Alfke]

> On Oct 27, 2019, at 1:50 PM, Thomas Kurz  wrote:
> 
> But not compatible to standards from Open Geospatial Consortium, as far as I 
> know. 

That's the nice thing about standards: there are so many of them.

> Which requires additional conversions, and considering that geodata usually 
> have sizes of a GB or more, this is not an option at all.

Yes, you might have to buy one of those new 4GB hard disks, or upgrade your 486 
box to a Pentium… ;-)

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

Re: [sqlite] sqlite segfault bug report

[Simon Slavin]

On 28 Oct 2019, at 11:07am, Richard Hipp  wrote:

> Bisect:

Just to explain to the Korean students: you spotted a real bug, but very 
recently the bug was fixed.

However, your work is useful.  Please download the current version of SQLite 
from



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

Re: [sqlite] sqlite segfault bug report

[Richard Hipp]

On 10/28/19, Graham Holden  wrote:
> Monday, October 28, 2019, 5:46:55 AM, ???  wrote:
>
>> Code:
>
>> BEGIN;
>> CREATE TABLE t1(a);
>> ALTER TABLE t1 ADD c CHECK (b>c);
>> PRAGMA writable_schema=on;
>> INSERT INTO t1 VALUES(2,3);
>
>> Version: 3.29.0, 3.22.0
>
> On "SQLite version 3.30.1 2019-10-10 20:19:45" this returns an
> error after the third line:
>
> Error: no such column: b

Bisect:

https://sqlite.org/src/timeline?bid=ya1e1ba9145nfc82b73eaan596ac2a4ean361eb2f682yffd4c30620n32fba11ab7y75775c5ab4y238e083571y5d76dbc5b0y31e85fbbc4


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

Re: [sqlite] sqlite segfault bug report

[Graham Holden]

Monday, October 28, 2019, 5:46:55 AM, ???  wrote:

> Code:
 
> BEGIN;
> CREATE TABLE t1(a);
> ALTER TABLE t1 ADD c CHECK (b>c);
> PRAGMA writable_schema=on;
> INSERT INTO t1 VALUES(2,3);
 
> Version: 3.29.0, 3.22.0

On "SQLite version 3.30.1 2019-10-10 20:19:45" this returns an
error after the third line:

Error: no such column: b


Regards,
Graham Holden


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

[sqlite] sqlite segfault bug report

[우병수]

Code:
 
BEGIN;
CREATE TABLE t1(a);
ALTER TABLE t1 ADD c CHECK (b>c);
PRAGMA writable_schema=on;
INSERT INTO t1 VALUES(2,3);
 
Version: 3.29.0, 3.22.0 
 
Hello, we are students from Korea.
We learned about fuzzing and tried to fuzz on sqlite3, and we found a piece of 
code which makes crash. 
 
When we try to insert values, segmentation fault occured.
 
So, we tried to analyze the reason and we found that the program consider b as 
tk_id, not a column.
Thus, when we try to insert the value, the program try to access 
"pEList->nExpr"(sqlite3.c:101266) whose value is NULL.
 
If we change b>c into c>b in the third line, b is considered as column. So, we 
think there is a problem when unknown variable appears first in constraint 
statement.
 
Please check about this. Thank you! 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users