MySQL Bugs: #436: Cross-depency: sql-query is depending on sequence of outer joins

Bug #436	Cross-depency: sql-query is depending on sequence of outer joins
Submitted:	16 May 2003 4:09	Modified:	16 May 2003 5:08
Reporter:	Christian Metzler	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	3.23.xx	OS:	Windows (Win XP, Linux)
Assigned to:		CPU Architecture:	Any

Description:
#Query-1: does not work
#Error Code : 1120
#Cross dependency found in OUTER JOIN.  Examine your ON conditions
# outer join tb_person,tb_contact is third line

SELECT count(*)
from tb_contact c , tb_favorite f
LEFT OUTER JOIN tb_ctry ctry ON c.ctry_id = ctry.id
LEFT OUTER JOIN tb_ctry ctry2 ON p.ctry_id = ctry2.id
LEFT OUTER JOIN tb_person p ON p.contact_id = c.id
LEFT OUTER JOIN tb_x x1 ON x1.x_id = c.x_category
LEFT OUTER JOIN tb_x x2 ON x2.x_id = c.x_business1
LEFT OUTER JOIN tb_x x3 ON x3.x_id = c.region
LEFT OUTER JOIN tb_x x4 ON x4.x_id = c.business2
LEFT OUTER JOIN tb_x x5 ON x5.x_id = c.category2
LEFT OUTER JOIN tb_x x6 ON x6.x_id = p.x_mrmrs
LEFT OUTER JOIN tb_acl acl ON (acl.objid = c.id and acl.obj = 'con')
WHERE c.id = f.objid
AND f.obj = 'con'
AND f.usr_id = 10
AND c.client_id = 1

# Query-2: does work correctly
# the only difference is the ordering of outer joins
# outer join tb_person,tb_contact is first line
SELECT count(*)
from tb_contact c , tb_favorite f
LEFT OUTER JOIN tb_person p ON p.contact_id = c.id
LEFT OUTER JOIN tb_ctry ctry ON c.ctry_id = ctry.id
LEFT OUTER JOIN tb_ctry ctry2 ON p.ctry_id = ctry2.id
LEFT OUTER JOIN tb_x x1 ON x1.x_id = c.x_category
LEFT OUTER JOIN tb_x x2 ON x2.x_id = c.x_business1
LEFT OUTER JOIN tb_x x3 ON x3.x_id = c.region
LEFT OUTER JOIN tb_x x4 ON x4.x_id = c.business2
LEFT OUTER JOIN tb_x x5 ON x5.x_id = c.category2
LEFT OUTER JOIN tb_x x6 ON x6.x_id = p.x_mrmrs
LEFT OUTER JOIN tb_acl acl ON (acl.objid = c.id and acl.obj = 'con')
WHERE c.id = f.objid
AND f.obj = 'con'
AND f.usr_id = 10
AND c.client_id = 1

How to repeat:
# create tables an run the queries
# 
create table tb_contact
(
    id                 INTEGER AUTO_INCREMENT not null,
    parent_id          INTEGER                       ,
    name               VARCHAR(70)           not null,
    name2              VARCHAR(70)                   ,
    namenorm           VARCHAR(100)                  ,
    nr                 VARCHAR(30)                   ,
    nameshort          VARCHAR(70)                   ,
    nameshortnorm      VARCHAR(100)          not null,
    street1            VARCHAR(100)                  ,
    street2            VARCHAR(100)                  ,
    zip                VARCHAR(30)                   ,
    city               VARCHAR(30)                   ,
    state              VARCHAR(30)                   ,
    region             VARCHAR(30)                   ,
    ctry_id            INTEGER                       ,
    phone              VARCHAR(30)                   ,
    phone1             VARCHAR(30)                   ,
    fax                VARCHAR(30)                   ,
    x_business1        VARCHAR(30)                   ,
    business2          VARCHAR(30)                   ,
    business3          VARCHAR(30)                   ,
    x_category         VARCHAR(30)                   ,
    category2          VARCHAR(30)                   ,
    www                VARCHAR(50)                   ,
    mail               VARCHAR(50)                   ,
    comment            TEXT                          ,
    archive            CHAR(1)               not null,
    field1             VARCHAR(30)                   ,
    field2             VARCHAR(30)                   ,
    field3             VARCHAR(30)                   ,
    field4             VARCHAR(30)                   ,
    field5             VARCHAR(30)                   ,
    x1                 CHAR(1)                       ,
    x2                 CHAR(1)                       ,
    x3                 CHAR(1)                       ,
    x4                 CHAR(1)                       ,
    x5                 CHAR(1)                       ,
    crea               VARCHAR(50)           not null,
    crea_usr           INTEGER               not null,
    upd                VARCHAR(50)                   ,
    upc                SMALLINT              not null,
    client_id          INTEGER               not null,
    primary key (id)
);

create table tb_favorite
(
    id                 INTEGER AUTO_INCREMENT not null,
    usr_id             INTEGER               not null,
    obj                VARCHAR(15)           not null,
    objid              INTEGER               not null,
    primary key (id)
);

create table tb_person
(
    id                 INTEGER AUTO_INCREMENT not null,
    contact_id         INTEGER                       ,
    x_mrmrs            VARCHAR(30)                   ,
    fname              VARCHAR(30)                   ,
    lname              VARCHAR(30)           not null,
    lnamenorm          VARCHAR(100)                  ,
    title              VARCHAR(30)                   ,
    department         VARCHAR(30)                   ,
    jobtitle           VARCHAR(30)                   ,
    decision           CHAR(1)                       ,
    phone              VARCHAR(30)                   ,
    phone1             VARCHAR(30)                   ,
    phone2             VARCHAR(30)                   ,
    mail               VARCHAR(50)                   ,
    mail1              VARCHAR(50)                   ,
    zip                VARCHAR(30)                   ,
    city               VARCHAR(30)                   ,
    street1            VARCHAR(100)                  ,
    state              VARCHAR(30)                   ,
    region             VARCHAR(30)                   ,
    ctry_id            INTEGER                       ,
    x_source           VARCHAR(30)                   ,
    birthday           VARCHAR(8)                    ,
    archive            CHAR(1)               not null,
    comment            TEXT                          ,
    field1             VARCHAR(30)                   ,
    field2             VARCHAR(30)                   ,
    field3             VARCHAR(30)                   ,
    field4             VARCHAR(30)                   ,
    field5             VARCHAR(30)                   ,
    x1                 CHAR(1)                       ,
    x2                 CHAR(1)                       ,
    x3                 CHAR(1)                       ,
    x4                 CHAR(1)                       ,
    x5                 CHAR(1)                       ,
    crea               VARCHAR(50)           not null,
    crea_usr           INTEGER               not null,
    upc                SMALLINT              not null,
    upd                VARCHAR(50)                   ,
    client_id          INTEGER               not null,
    primary key (id)
);

create table tb_ctry
(
    id                 INTEGER AUTO_INCREMENT not null,
    code               CHAR(3)                       ,
    name               VARCHAR(30)                   ,
    timeoffset_utc     TINYINT                       ,
    zip_prefix         VARCHAR(30)                   ,
    upc                SMALLINT              not null,
    client_id          INTEGER               not null,
    primary key (id)
);

create table tb_x
(
    x_id               VARCHAR(30)           not null,
    list_text          VARCHAR(30)           not null,
    list_sort          SMALLINT                      ,
    parent_id          VARCHAR(30)                   ,
    edit               CHAR(1)               not null,
    is_default         CHAR(1)               not null,
    crea               VARCHAR(50)           not null,
    crea_usr           INTEGER               not null,
    upc                SMALLINT              not null,
    upd                VARCHAR(50)                   ,
    client_id          INTEGER               not null,
    primary key (x_id)
);

create table tb_acl
(
    objid              INTEGER               not null,
    obj                VARCHAR(15)           not null,
    usr                TEXT                          ,
    crea               VARCHAR(50)           not null,
    crea_usr           INTEGER               not null,
    upd                VARCHAR(50)                   ,
    upc                SMALLINT              not null,
    client_id          INTEGER               not null,
    primary key (objid, obj)
);

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

This is required by standard behaviour of LEFT JOIN
By it's nature LEFT JOINs are order dependant and must be processed from left to right. 

The problem with Query 1 is that in ON clause it refers to table which is on the right side of next join which is not allowed.

In Query 2 there is no such reference.

Simplest example (does not make sence but ilustrates the problem):

# Correct 

select * from a 
  left join b on (a.id = b.id) left join c on 1;

# Wrong

select * from a 
  left join b on (a.id = c.id) left join c on 1;

ERROR 1120: Cross dependency found in OUTER JOIN.  Examine your ON conditions

Thank you again for the time you spent writing this report.