Hello,
I still have problems with a query like the one described in ticket #806.
The solution to that ticket works only partially, as it handles the join,
but the conditions on tables bejond 32th are not enforced.
The attached scripts show this:
The script test000.sql seems to work correctly in 2.8.15, while it
produced no output in 2.8.14.
But as soon as you add more records to tbl32 (test001.sql) you see that
condition "LEFT OUTER JOIN tbl32 ON tbl00.id = tbl32.id" is not
enforced, and you get 2 rows as a result.
This doesn't depend on the join being a LEFT OUTER one, as shown
by test002.sql.
The same behaviour can be seen in release 3.0.3.
I didn't open a new ticked for this, should I?
Sorry to not have found it before release of 2.8.15, and also to not be
able to provide a patch, but I'm going to study a bit the sources and
maybe in the future I can give more help.
Regards,
Lorenzo
CREATE TABLE tbl00 (id integer, fld varchar(9));
CREATE TABLE tbl01 (id integer, fld varchar(9));
CREATE TABLE tbl02 (id integer, fld varchar(9));
CREATE TABLE tbl03 (id integer, fld varchar(9));
CREATE TABLE tbl04 (id integer, fld varchar(9));
CREATE TABLE tbl05 (id integer, fld varchar(9));
CREATE TABLE tbl06 (id integer, fld varchar(9));
CREATE TABLE tbl07 (id integer, fld varchar(9));
CREATE TABLE tbl08 (id integer, fld varchar(9));
CREATE TABLE tbl09 (id integer, fld varchar(9));
CREATE TABLE tbl10 (id integer, fld varchar(9));
CREATE TABLE tbl11 (id integer, fld varchar(9));
CREATE TABLE tbl12 (id integer, fld varchar(9));
CREATE TABLE tbl13 (id integer, fld varchar(9));
CREATE TABLE tbl14 (id integer, fld varchar(9));
CREATE TABLE tbl15 (id integer, fld varchar(9));
CREATE TABLE tbl16 (id integer, fld varchar(9));
CREATE TABLE tbl17 (id integer, fld varchar(9));
CREATE TABLE tbl18 (id integer, fld varchar(9));
CREATE TABLE tbl19 (id integer, fld varchar(9));
CREATE TABLE tbl20 (id integer, fld varchar(9));
CREATE TABLE tbl21 (id integer, fld varchar(9));
CREATE TABLE tbl22 (id integer, fld varchar(9));
CREATE TABLE tbl23 (id integer, fld varchar(9));
CREATE TABLE tbl24 (id integer, fld varchar(9));
CREATE TABLE tbl25 (id integer, fld varchar(9));
CREATE TABLE tbl26 (id integer, fld varchar(9));
CREATE TABLE tbl27 (id integer, fld varchar(9));
CREATE TABLE tbl28 (id integer, fld varchar(9));
CREATE TABLE tbl29 (id integer, fld varchar(9));
CREATE TABLE tbl30 (id integer, fld varchar(9));
CREATE TABLE tbl31 (id integer, fld varchar(9));
CREATE TABLE tbl32 (id integer, fld varchar(9));
INSERT INTO tbl00 VALUES (4, 'tbl00_4');
INSERT INTO tbl01 VALUES (4, 'tbl01_4');
INSERT INTO tbl02 VALUES (4, 'tbl02_4');
INSERT INTO tbl03 VALUES (4, 'tbl03_4');
INSERT INTO tbl04 VALUES (4, 'tbl04_4');
INSERT INTO tbl05 VALUES (4, 'tbl05_4');
INSERT INTO tbl06 VALUES (4, 'tbl06_4');
INSERT INTO tbl07 VALUES (4, 'tbl07_4');
INSERT INTO tbl08 VALUES (4, 'tbl08_4');
INSERT INTO tbl09 VALUES (4, 'tbl09_4');
INSERT INTO tbl10 VALUES (4, 'tbl10_4');
INSERT INTO tbl11 VALUES (4, 'tbl11_4');
INSERT INTO tbl12 VALUES (4, 'tbl12_4');
INSERT INTO tbl13 VALUES (4, 'tbl13_4');
INSERT INTO tbl14 VALUES (4, 'tbl14_4');
INSERT INTO tbl15 VALUES (4, 'tbl15_4');
INSERT INTO tbl16 VALUES (4, 'tbl16_4');
INSERT INTO tbl17 VALUES (4, 'tbl17_4');
INSERT INTO tbl18 VALUES (4, 'tbl18_4');
INSERT INTO tbl19 VALUES (4, 'tbl19_4');
INSERT INTO tbl20 VALUES (4, 'tbl20_4');
INSERT INTO tbl21 VALUES (4, 'tbl21_4');
INSERT INTO tbl22 VALUES (4, 'tbl22_4');
INSERT INTO tbl23 VALUES (4, 'tbl23_4');
INSERT INTO tbl24 VALUES (4, 'tbl24_4');
INSERT INTO tbl25 VALUES (4, 'tbl25_4');
INSERT INTO tbl26 VALUES (4, 'tbl26_4');
INSERT INTO tbl27 VALUES (4, 'tbl27_4');
INSERT INTO tbl28 VALUES (4, 'tbl28_4');
INSERT INTO tbl29 VALUES (4, 'tbl29_4');
INSERT INTO tbl30 VALUES (4, 'tbl30_4');
INSERT INTO tbl31 VALUES (4, 'tbl31_4');
INSERT INTO tbl32 VALUES (4, 'tbl32_4');
SELECT
tbl00.fld
,tbl01.fld
,tbl02.fld
,tbl03.fld
,tbl04.fld
,tbl05.fld
,tbl06.fld
,tbl07.fld
,tbl08.fld
,tbl09.fld
,tbl10.fld
,tbl11.fld
,tbl12.fld
,tbl13.fld
,tbl14.fld
,tbl15.fld
,tbl16.fld
,tbl17.fld
,tbl18.fld
,tbl19.fld
,tbl20.fld
,tbl21.fld
,tbl22.fld
,tbl23.fld
,tbl24.fld
,tbl25.fld
,tbl26.fld
,tbl27.fld
,tbl28.fld
,tbl29.fld
,tbl30.fld
,tbl31.fld
,tbl32.fld
FROM
tbl00
LEFT OUTER JOIN tbl01 ON tbl00.id = tbl01.id
LEFT OUTER JOIN tbl02 ON tbl00.id = tbl02.id
LEFT OUTER JOIN tbl03 ON tbl00.id = tbl03.id
LEFT OUTER JOIN tbl04 ON tbl00.id = tbl04.id
LEFT OUTER JOIN tbl05 ON tbl00.id = tbl05.id
LEFT OUTER JOIN tbl06 ON tbl00.id = tbl06.id
LEFT OUTER JOIN tbl07 ON tbl00.id = tbl07.id
LEFT OUTER JOIN tbl08 ON tbl00.id = tbl08.id
LEFT OUTER JOIN tbl09 ON tbl00.id = tbl09.id
LEFT OUTER JOIN tbl10 ON tbl00.id = tbl10.id
LEFT OUTER JOIN tbl11 ON tbl00.id = tbl11.id
LEFT OUTER JOIN tbl12 ON tbl00.id = tbl12.id
LEFT OUTER JOIN tbl13 ON tbl00.id = tbl13.id
LEFT OUTER JOIN tbl14 ON tbl00.id = tbl14.id
LEFT OUTER JOIN tbl15 ON tbl00.id = tbl15.id
LEFT OUTER JOIN tbl16 ON tbl00.id = tbl16.id
LEFT OUTER JOIN tbl17 ON tbl00.id = tbl17.id
LEFT OUTER JOIN tbl18 ON tbl00.id = tbl18.id
LEFT OUTER JOIN tbl19 ON tbl00.id = tbl19.id
LEFT OUTER JOIN tbl20 ON tbl00.id = tbl20.id
LEFT OUTER JOIN tbl21 ON tbl00.id = tbl21.id
LEFT OUTER JOIN tbl22 ON tbl00.id = tbl22.id
LEFT OUTER JOIN tbl23 ON tbl00.id = tbl23.id
LEFT OUTER JOIN tbl24 ON tbl00.id = tbl24.id
LEFT OUTER JOIN tbl25 ON tbl00.id = tbl25.id
LEFT OUTER JOIN tbl26 ON tbl00.id = tbl26.id
LEFT OUTER JOIN tbl27 ON tbl00.id = tbl27.id
LEFT OUTER JOIN tbl28 ON tbl00.id = tbl28.id
LEFT OUTER JOIN tbl29 ON tbl00.id = tbl29.id
LEFT OUTER JOIN tbl30 ON tbl00.id = tbl30.id
LEFT OUTER JOIN tbl31 ON tbl00.id = tbl31.id
LEFT OUTER JOIN tbl32 ON tbl00.id = tbl32.id
WHERE
tbl00.id = 4
;
CREATE TABLE tbl00 (id integer, fld varchar(9));
CREATE TABLE tbl01 (id integer, fld varchar(9));
CREATE TABLE tbl02 (id integer, fld varchar(9));
CREATE TABLE tbl03 (id integer, fld varchar(9));
CREATE TABLE tbl04 (id integer, fld varchar(9));
CREATE TABLE tbl05 (id integer, fld varchar(9));
CREATE TABLE tbl06 (id integer, fld varchar(9));
CREATE TABLE tbl07 (id integer, fld varchar(9));
CREATE TABLE tbl08 (id integer, fld varchar(9));
CREATE TABLE tbl09 (id integer, fld varchar(9));
CREATE TABLE tbl10 (id integer, fld varchar(9));
CREATE TABLE tbl11 (id integer, fld varchar(9));
CREATE TABLE tbl12 (id integer, fld varchar(9));
CREATE TABLE tbl13 (id integer, fld varchar(9));
CREATE TABLE tbl14 (id integer, fld varchar(9));
CREATE TABLE tbl15 (id integer, fld varchar(9));
CREATE TABLE tbl16 (id integer, fld varchar(9));
CREATE TABLE tbl17 (id integer, fld varchar(9));
CREATE TABLE tbl18 (id integer, fld varchar(9));
CREATE TABLE tbl19 (id integer, fld varchar(9));
CREATE TABLE tbl20 (id integer, fld varchar(9));
CREATE TABLE tbl21 (id integer, fld varchar(9));
CREATE TABLE tbl22 (id integer, fld varchar(9));
CREATE TABLE tbl23 (id integer, fld varchar(9));
CREATE TABLE tbl24 (id integer, fld varchar(9));
CREATE TABLE tbl25 (id integer, fld varchar(9));
CREATE TABLE tbl26 (id integer, fld varchar(9));
CREATE TABLE tbl27 (id integer, fld varchar(9));
CREATE TABLE tbl28 (id integer, fld varchar(9));
CREATE TABLE tbl29 (id integer, fld varchar(9));
CREATE TABLE tbl30 (id integer, fld varchar(9));
CREATE TABLE tbl31 (id integer, fld varchar(9));
CREATE TABLE tbl32 (id integer, fld varchar(9));
INSERT INTO tbl00 VALUES (4, 'tbl00_4');
INSERT INTO tbl01 VALUES (4, 'tbl01_4');
INSERT INTO tbl02 VALUES (4, 'tbl02_4');
INSERT INTO tbl03 VALUES (4, 'tbl03_4');
INSERT INTO tbl04 VALUES (4, 'tbl04_4');
INSERT INTO tbl05 VALUES (4, 'tbl05_4');
INSERT INTO tbl06 VALUES (4, 'tbl06_4');
INSERT INTO tbl07 VALUES (4, 'tbl07_4');
INSERT INTO tbl08 VALUES (4, 'tbl08_4');
INSERT INTO tbl09 VALUES (4, 'tbl09_4');
INSERT INTO tbl10 VALUES (4, 'tbl10_4');
INSERT INTO tbl11 VALUES (4, 'tbl11_4');
INSERT INTO tbl12 VALUES (4, 'tbl12_4');
INSERT INTO tbl13 VALUES (4, 'tbl13_4');
INSERT INTO tbl14 VALUES (4, 'tbl14_4');
INSERT INTO tbl15 VALUES (4, 'tbl15_4');
INSERT INTO tbl16 VALUES (4, 'tbl16_4');
INSERT INTO tbl17 VALUES (4, 'tbl17_4');
INSERT INTO tbl18 VALUES (4, 'tbl18_4');
INSERT INTO tbl19 VALUES (4, 'tbl19_4');
INSERT INTO tbl20 VALUES (4, 'tbl20_4');
INSERT INTO tbl21 VALUES (4, 'tbl21_4');
INSERT INTO tbl22 VALUES (4, 'tbl22_4');
INSERT INTO tbl23 VALUES (4, 'tbl23_4');
INSERT INTO tbl24 VALUES (4, 'tbl24_4');
INSERT INTO tbl25 VALUES (4, 'tbl25_4');
INSERT INTO tbl26 VALUES (4, 'tbl26_4');
INSERT INTO tbl27 VALUES (4, 'tbl27_4');
INSERT INTO tbl28 VALUES (4, 'tbl28_4');
INSERT INTO tbl29 VALUES (4, 'tbl29_4');
INSERT INTO tbl30 VALUES (4, 'tbl30_4');
INSERT INTO tbl31 VALUES (4, 'tbl31_4');
INSERT INTO tbl32 VALUES (4, 'tbl32_4');
INSERT INTO tbl32 VALUES (5, 'tbl32_5');
SELECT
tbl00.fld
,tbl01.fld
,tbl02.fld
,tbl03.fld
,tbl04.fld
,tbl05.fld
,tbl06.fld
,tbl07.fld
,tbl08.fld
,tbl09.fld
,tbl10.fld
,tbl11.fld
,tbl12.fld
,tbl13.fld
,tbl14.fld
,tbl15.fld
,tbl16.fld
,tbl17.fld
,tbl18.fld
,tbl19.fld
,tbl20.fld
,tbl21.fld
,tbl22.fld
,tbl23.fld
,tbl24.fld
,tbl25.fld
,tbl26.fld
,tbl27.fld
,tbl28.fld
,tbl29.fld
,tbl30.fld
,tbl31.fld
,tbl32.fld
FROM
tbl00
LEFT OUTER JOIN tbl01 ON tbl00.id = tbl01.id
LEFT OUTER JOIN tbl02 ON tbl00.id = tbl02.id
LEFT OUTER JOIN tbl03 ON tbl00.id = tbl03.id
LEFT OUTER JOIN tbl04 ON tbl00.id = tbl04.id
LEFT OUTER JOIN tbl05 ON tbl00.id = tbl05.id
LEFT OUTER JOIN tbl06 ON tbl00.id = tbl06.id
LEFT OUTER JOIN tbl07 ON tbl00.id = tbl07.id
LEFT OUTER JOIN tbl08 ON tbl00.id = tbl08.id
LEFT OUTER JOIN tbl09 ON tbl00.id = tbl09.id
LEFT OUTER JOIN tbl10 ON tbl00.id = tbl10.id
LEFT OUTER JOIN tbl11 ON tbl00.id = tbl11.id
LEFT OUTER JOIN tbl12 ON tbl00.id = tbl12.id
LEFT OUTER JOIN tbl13 ON tbl00.id = tbl13.id
LEFT OUTER JOIN tbl14 ON tbl00.id = tbl14.id
LEFT OUTER JOIN tbl15 ON tbl00.id = tbl15.id
LEFT OUTER JOIN tbl16 ON tbl00.id = tbl16.id
LEFT OUTER JOIN tbl17 ON tbl00.id = tbl17.id
LEFT OUTER JOIN tbl18 ON tbl00.id = tbl18.id
LEFT OUTER JOIN tbl19 ON tbl00.id = tbl19.id
LEFT OUTER JOIN tbl20 ON tbl00.id = tbl20.id
LEFT OUTER JOIN tbl21 ON tbl00.id = tbl21.id
LEFT OUTER JOIN tbl22 ON tbl00.id = tbl22.id
LEFT OUTER JOIN tbl23 ON tbl00.id = tbl23.id
LEFT OUTER JOIN tbl24 ON tbl00.id = tbl24.id
LEFT OUTER JOIN tbl25 ON tbl00.id = tbl25.id
LEFT OUTER JOIN tbl26 ON tbl00.id = tbl26.id
LEFT OUTER JOIN tbl27 ON tbl00.id = tbl27.id
LEFT OUTER JOIN tbl28 ON tbl00.id = tbl28.id
LEFT OUTER JOIN tbl29 ON tbl00.id = tbl29.id
LEFT OUTER JOIN tbl30 ON tbl00.id = tbl30.id
LEFT OUTER JOIN tbl31 ON tbl00.id = tbl31.id
LEFT OUTER JOIN tbl32 ON tbl00.id = tbl32.id
WHERE
tbl00.id = 4
;
CREATE TABLE tbl00 (id integer, fld varchar(9));
CREATE TABLE tbl01 (id integer, fld varchar(9));
CREATE TABLE tbl02 (id integer, fld varchar(9));
CREATE TABLE tbl03 (id integer, fld varchar(9));
CREATE TABLE tbl04 (id integer, fld varchar(9));
CREATE TABLE tbl05 (id integer, fld varchar(9));
CREATE TABLE tbl06 (id integer, fld varchar(9));
CREATE TABLE tbl07 (id integer, fld varchar(9));
CREATE TABLE tbl08 (id integer, fld varchar(9));
CREATE TABLE tbl09 (id integer, fld varchar(9));
CREATE TABLE tbl10 (id integer, fld varchar(9));
CREATE TABLE tbl11 (id integer, fld varchar(9));
CREATE TABLE tbl12 (id integer, fld varchar(9));
CREATE TABLE tbl13 (id integer, fld varchar(9));
CREATE TABLE tbl14 (id integer, fld varchar(9));
CREATE TABLE tbl15 (id integer, fld varchar(9));
CREATE TABLE tbl16 (id integer, fld varchar(9));
CREATE TABLE tbl17 (id integer, fld varchar(9));
CREATE TABLE tbl18 (id integer, fld varchar(9));
CREATE TABLE tbl19 (id integer, fld varchar(9));
CREATE TABLE tbl20 (id integer, fld varchar(9));
CREATE TABLE tbl21 (id integer, fld varchar(9));
CREATE TABLE tbl22 (id integer, fld varchar(9));
CREATE TABLE tbl23 (id integer, fld varchar(9));
CREATE TABLE tbl24 (id integer, fld varchar(9));
CREATE TABLE tbl25 (id integer, fld varchar(9));
CREATE TABLE tbl26 (id integer, fld varchar(9));
CREATE TABLE tbl27 (id integer, fld varchar(9));
CREATE TABLE tbl28 (id integer, fld varchar(9));
CREATE TABLE tbl29 (id integer, fld varchar(9));
CREATE TABLE tbl30 (id integer, fld varchar(9));
CREATE TABLE tbl31 (id integer, fld varchar(9));
CREATE TABLE tbl32 (id integer, fld varchar(9));
INSERT INTO tbl00 VALUES (4, 'tbl00_4');
INSERT INTO tbl01 VALUES (4, 'tbl01_4');
INSERT INTO tbl02 VALUES (4, 'tbl02_4');
INSERT INTO tbl03 VALUES (4, 'tbl03_4');
INSERT INTO tbl04 VALUES (4, 'tbl04_4');
INSERT INTO tbl05 VALUES (4, 'tbl05_4');
INSERT INTO tbl06 VALUES (4, 'tbl06_4');
INSERT INTO tbl07 VALUES (4, 'tbl07_4');
INSERT INTO tbl08 VALUES (4, 'tbl08_4');
INSERT INTO tbl09 VALUES (4, 'tbl09_4');
INSERT INTO tbl10 VALUES (4, 'tbl10_4');
INSERT INTO tbl11 VALUES (4, 'tbl11_4');
INSERT INTO tbl12 VALUES (4, 'tbl12_4');
INSERT INTO tbl13 VALUES (4, 'tbl13_4');
INSERT INTO tbl14 VALUES (4, 'tbl14_4');
INSERT INTO tbl15 VALUES (4, 'tbl15_4');
INSERT INTO tbl16 VALUES (4, 'tbl16_4');
INSERT INTO tbl17 VALUES (4, 'tbl17_4');
INSERT INTO tbl18 VALUES (4, 'tbl18_4');
INSERT INTO tbl19 VALUES (4, 'tbl19_4');
INSERT INTO tbl20 VALUES (4, 'tbl20_4');
INSERT INTO tbl21 VALUES (4, 'tbl21_4');
INSERT INTO tbl22 VALUES (4, 'tbl22_4');
INSERT INTO tbl23 VALUES (4, 'tbl23_4');
INSERT INTO tbl24 VALUES (4, 'tbl24_4');
INSERT INTO tbl25 VALUES (4, 'tbl25_4');
INSERT INTO tbl26 VALUES (4, 'tbl26_4');
INSERT INTO tbl27 VALUES (4, 'tbl27_4');
INSERT INTO tbl28 VALUES (4, 'tbl28_4');
INSERT INTO tbl29 VALUES (4, 'tbl29_4');
INSERT INTO tbl30 VALUES (4, 'tbl30_4');
INSERT INTO tbl31 VALUES (4, 'tbl31_4');
INSERT INTO tbl32 VALUES (4, 'tbl32_4');
INSERT INTO tbl32 VALUES (5, 'tbl32_5');
SELECT
tbl00.fld
,tbl01.fld
,tbl02.fld
,tbl03.fld
,tbl04.fld
,tbl05.fld
,tbl06.fld
,tbl07.fld
,tbl08.fld
,tbl09.fld
,tbl10.fld
,tbl11.fld
,tbl12.fld
,tbl13.fld
,tbl14.fld
,tbl15.fld
,tbl16.fld
,tbl17.fld
,tbl18.fld
,tbl19.fld
,tbl20.fld
,tbl21.fld
,tbl22.fld
,tbl23.fld
,tbl24.fld
,tbl25.fld
,tbl26.fld
,tbl27.fld
,tbl28.fld
,tbl29.fld
,tbl30.fld
,tbl31.fld
,tbl32.fld
FROM
tbl00
,tbl01
,tbl02
,tbl03
,tbl04
,tbl05
,tbl06
,tbl07
,tbl08
,tbl09
,tbl10
,tbl11
,tbl12
,tbl13
,tbl14
,tbl15
,tbl16
,tbl17
,tbl18
,tbl19
,tbl20
,tbl21
,tbl22
,tbl23
,tbl24
,tbl25
,tbl26
,tbl27
,tbl28
,tbl29
,tbl30
,tbl31
,tbl32
WHERE
tbl00.id = tbl01.id
AND tbl00.id = tbl02.id
AND tbl00.id = tbl03.id
AND tbl00.id = tbl04.id
AND tbl00.id = tbl05.id
AND tbl00.id = tbl06.id
AND tbl00.id = tbl07.id
AND tbl00.id = tbl08.id
AND tbl00.id = tbl09.id
AND tbl00.id = tbl10.id
AND tbl00.id = tbl11.id
AND tbl00.id = tbl12.id
AND tbl00.id = tbl13.id
AND tbl00.id = tbl14.id
AND tbl00.id = tbl15.id
AND tbl00.id = tbl16.id
AND tbl00.id = tbl17.id
AND tbl00.id = tbl18.id
AND tbl00.id = tbl19.id
AND tbl00.id = tbl20.id
AND tbl00.id = tbl21.id
AND tbl00.id = tbl22.id
AND tbl00.id = tbl23.id
AND tbl00.id = tbl24.id
AND tbl00.id = tbl25.id
AND tbl00.id = tbl26.id
AND tbl00.id = tbl27.id
AND tbl00.id = tbl28.id
AND tbl00.id = tbl29.id
AND tbl00.id = tbl30.id
AND tbl00.id = tbl31.id
AND tbl00.id = tbl32.id
AND tbl00.id = 4
;
