Hello
I'm currently facing a problem with nested right hand joins. I've also reported
this to the mailing list over one month ago, but haven't received any reply. In
the meantime I've investigated the problem in the SQLite source and sorted some
things out.
Let's first start with an example which reproduces the problem:
CREATE TABLE t1(t1_id INTEGER PRIMARY KEY, t1_title TEXT);
CREATE TABLE t2(t2_id INTEGER PRIMARY KEY, t2_title TEXT);
CREATE TABLE t3(t3_id INTEGER PRIMARY KEY, t3_title TEXT);
INSERT INTO t1 (t1_id, t1_title) VALUES (888, 'data1');
INSERT INTO t2 (t2_id, t2_title) VALUES (999, 'data2');
INSERT INTO t3 (t3_id, t3_title) VALUES (999, 'data3');
Exeuting the following query works as expected and results : data1 | null | null
SELECT t1_title, t2_title, t3_title
FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2_id = t3_id) ON t1_id = t2_id;
If you now use the tablename t2 or t3 to access the columns like in the
following query :
SELECT t1_title, t2.t2_title, t3.t3_title
FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2_id = t3_id) ON t1_id = t2.t2_id;
You will receive SQL Error: no such column: t2.t2_title
I've also investigated the mailing list archive and I've seen one or two
messages regarding this problem. There the advice was given to rewrite the join
so you dont have a nested right handed join. I know this could be the solution
in some cases but it's not always possible to do that. And it's not always
possible to do the join without a table accessor on the columns, especially if
you always use the same column name for the primary key on all tables.
As I've stated, I've investigated the SQLite source and found the problem why
this doesn 't work. Right handed nested joins are currently implemented by
subqueries, because the joining is implemented as a list, where every table is
joined with the next one. The "lookupName" function which tries to search the
column in the used tables searches only through the source list. The subquery,
which represents the nested join, has an internal table name and there will
never be tried to take a look on the tables used in the subquery to resolve the
column. This is perfectly fine if it's a subquery but if the subquery
represents a nested join it has to be possible to access the tables used in the
subquery. At least it's possible with all the database systems I'm working with
in daily business.
Attached you will find a tcl test which shows the problem as well. In addition
you will find a patch which I've implemented to solve the problem. I know the
patch is not a perfect solution, but for the patch my main goal was to fix the
problem with at least changes to the original SQLite source as possible. The
patch currently breaks two of the authorizer tests, because for the patch to
work the resolving order of subqueries is changed in "resolveSelectStep" .
First subqueries in the FROM will be resolved and afterwards the result set.
The patch basically marks nested join subqueries during parsing and during
lookup it also searches in these subquries for the table.column. After a match,
this column will be remapped to correct column on the subqury result set. It
also works for TABLENAME.rowid as long as the rowid is designed in the table as
INTEGER PRIMARY KEY.
One problem still exists, it's not possible to execute a query with a selection
of all columns of a table, where table is defined in a nested join subquery,
e.g.
SELECT t2.*
FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2.t2_id = t3.t3_id) ON t1_id =
t2.t2_id;
I would really appreciate it if someone of the developers would reply to this
mail what they're opinion is regarding this problem, because I really think
this is a bug.
Kind Regards
Fabrizio
# 2011 December 09
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests to check if nested right hand joins work correctly.
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix "tkt-nested-joins"
do_execsql_test 1.1 {
CREATE TABLE t1(t1_id INTEGER PRIMARY KEY, t1_title TEXT);
CREATE TABLE t2(t2_id INTEGER PRIMARY KEY, t2_title TEXT);
CREATE TABLE t3(t3_id INTEGER PRIMARY KEY, t3_title TEXT);
INSERT INTO t1 (t1_id, t1_title) VALUES (888, 'data1');
INSERT INTO t2 (t2_id, t2_title) VALUES (999, 'data2');
INSERT INTO t3 (t3_id, t3_title) VALUES (999, 'data3');
}
do_execsql_test 1.2 {
SELECT t1_title, ifnull(t2_title, 'nil')
FROM t1 LEFT JOIN (t2) ON t1_id = t2_id;
} {data1 nil}
do_execsql_test 1.3 {
SELECT t1.t1_title, ifnull(t2.t2_title, 'nil')
FROM t1 LEFT JOIN (t2) ON t1.t1_id = t2.t2_id;
} {data1 nil}
do_execsql_test 1.4 {
SELECT t2_title, t3_title, ifnull(t1_title, 'nil')
FROM t2 INNER JOIN ( t3 LEFT JOIN t1 ON t3_id = t1_id) ON t2_id = t3_id;
} {data2 data3 nil}
do_execsql_test 1.5 {
SELECT t2.t2_title, t3.t3_title, ifnull(t1.t1_title, 'nil')
FROM t2 INNER JOIN ( t3 LEFT JOIN t1 ON t3.t3_id = t1.t1_id) ON t2.t2_id =
t3.t3_id;
} {data2 data3 nil}
do_execsql_test 1.6 {
SELECT t1_title, ifnull(t2_title, 'nil'), ifnull(t3_title, 'nil')
FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2_id = t3_id) ON t1_id = t2_id;
} {data1 nil nil}
do_execsql_test 1.7 {
SELECT t1.t1_title, ifnull(t2.t2_title, 'nil'), ifnull(t3.t3_title, 'nil')
FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2.t2_id = t3.t3_id) ON t1.t1_id =
t2.t2_id;
} {data1 nil nil}
do_execsql_test 1.8 {
SELECT t1.t1_title, ifnull(t1_inner.t1_title, 'nil'), ifnull(t3.t3_title,
'nil')
FROM t1 LEFT JOIN ( t1 AS t1_inner INNER JOIN t3 ON t1_inner.t1_id =
t3.t3_id) ON t1.t1_id = t1_inner.t1_id;
} {data1 nil nil}
finish_test
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
