1

It seems the "NATURAL JOIN" syntax does not work terribly well in my PHP5.3 environment. I use PDO to access the SQLite3 engine.

See the example below, where I want to store various access rights for users :

CREATE TABLE user (
    id_u  integer PRIMARY KEY,
    name  text);

CREATE TABLE access (
    id_a    integer PRIMARY KEY,
    id_u    integer,
    area    text,
    granted text,
    FOREIGN KEY(id_u) REFERENCES user(id_u));

After some instertions, the tables contain this:

id_u  name
---- ------
1    Igor
2    Rebecca  

id_a id_u area    granted
---- ---- ------- -------
1    1    kitchen full
2    2    kitchen restricted
3    1    lab     forbidden

Now if I perform this query :

SELECT granted FROM user U 
    INNER JOIN access A USING (id_u) 
    WHERE U.name='Igor' and A.area='kitchen';

I get the expected result (full)

However, this query:

SELECT granted FROM user U 
    NATURAL INNER JOIN access A
    WHERE U.name='Igor' and A.area='kitchen';

yields no result.

Is there something wrong with the second query, or are PDO and/or SQLite3 acting funny here?

EDIT: as requested, I added an SQLfiddle to demonstrate the problem

It works under the two variants of SQLite, but nevertheless it fails in my own environment. Whether there is something wrong with the specific PDO interface, the SQLite version or the way I send my requests, I have no idea. That's where a bit of help would be appreciated.

kuroi neko
  • 8,479
  • 1
  • 19
  • 43
  • And if you try: SELECT right FROM user U NATURAL JOIN access A WHERE U.name='Igor' and A.area='kitchen'; – Hackerman Dec 28 '13 at 01:45
  • The area attribute for the access table was specified as integer in the DDL but used as string in the query – D. Rattansingh Dec 28 '13 at 01:58
  • Sorry, it was an integer in my actual app, I just changed it to a string in the example for a bit more clarity. I edited the question to remove this typo. As for using 'NATURAL JOIN' instead of 'NATURAL INNER JOIN', it makes no difference. – kuroi neko Dec 28 '13 at 03:13
  • You have answer here: http://stackoverflow.com/questions/13545476/sqlite-natural-join-broken – Awlad Liton Dec 28 '13 at 03:21
  • Well I saw this question before asking, but I am not sure it is the same issue. In my case there is only one join, so operator precedence does not come into play. – kuroi neko Dec 28 '13 at 04:06
  • Please create an example on [SQLFiddle](http:// sqlfiddle.com). – CL. Dec 28 '13 at 12:37

1 Answers1

1

Tried this with MySql (had to use rright since its a keyword):

CREATE TABLE user (
id_u  integer PRIMARY KEY,
name  varchar(255) UNIQUE NOT NULL);

CREATE TABLE access (
id_a  integer,
id_u  integer,
area  varchar(55),
rright varchar(55),
primary key(id_a),
foreign key(id_u) references user(id_u)
);

This is the equivalent for your second query in standard SQL:

select rright
from user u, access a
where u.id_u=a.id_u and u.name='Igor' and a.area='kitchen';

The result was:

+--------+
| rright |
+--------+
| full   |
+--------+
1 row in set (0.00 sec)
D. Rattansingh
  • 1,569
  • 3
  • 19
  • 30
  • This syntax works fine but, as far as I know, INNER JOIN and NATURAL JOIN are also standard SQL, right? – kuroi neko Dec 28 '13 at 03:16
  • I'm not sure if it's standard but it should work the same. I normally apply the natural join as a restriction on the cartesian product (DB terminology). Didn't realise your question was focusing on getting the natural join to work – D. Rattansingh Dec 28 '13 at 03:21
  • Well according to various second sources (since the actual doc has to be bought in Geneva), NATURAL dates back from ISO SQL-92. I have little or no problem using another syntactical variant, but I am surprised to see no mention of this apparently broken keyword (possibly only in the PHP/PDO context) while at the same time SQLite's own tutorial claims it works like a charm. – kuroi neko Dec 28 '13 at 04:28
  • Reminds me of someone about to penalize a student for not putting domain check constraints in the MySql DDL code just to hear: but sir MySql doesn't enforce check constraints so why bother put it? – D. Rattansingh Dec 28 '13 at 06:05
  • And after the student added the checks : "Congrats young man, your code does not work but according to the books it should" :). Now seriously folks... – kuroi neko Dec 28 '13 at 11:13