MySQL Bugs: #12943: bogus "Unknown column 'a.b' in 'on clause'" error

Bug #12943	bogus "Unknown column 'a.b' in 'on clause'" error
Submitted:	2 Sep 2005 4:45	Modified:	29 Sep 2005 7:45
Reporter:	Timothy Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.12	OS:	Any (any)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:

Tested with MyISAM and InnoDB tables; I just pulled & built new code to verify this.

Running the test script shows:

16:41 ~/m/csc/6423$ mysql test < bug.sql
ERROR 1054 (42S22) at line 21: Unknown column 'cost.costid' in 'on clause'

However, when I run it against a 5.0.11 release version, it works:

16:43 ~/m/csc/6423$ mysql test < bug.sql
count(*)
1

How to repeat:

drop table if exists users;
drop table if exists cost;
drop table if exists usergroups;

create table users (
  usergroupid int(11) not null
);
insert into users values (1);

create table cost (
  costid int(11) not null
);
insert into cost values (1);

create table usergroups (
  usergroupid int(11) not null,
  costid int(11) not null
);
insert into usergroups values (1,1);

select
    count(*)
from
    cost
    join users
    join usergroups
        on users.usergroupid = usergroups.usergroupid
        and cost.costid = usergroups.costid
    ;

Suggested fix:

I have not yet looked at all at why this is happening.

Just a note: I tested this with the 5.0.12 release binary, and the bug exists there.  I think this is possibly a critical priority bug - it *definitely* should be fixed before the next release.

mysql> create table users (
    ->   usergroupid int(11) not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into users values (1);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> create table cost (
    ->   costid int(11) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into cost values (1);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> create table usergroups (
    ->   usergroupid int(11) not null,
    ->   costid int(11) not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into usergroups values (1,1);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> select
    ->     count(*)
    -> from
    ->     cost
    ->     join users
    ->     join usergroups
    ->         on users.usergroupid = usergroups.usergroupid
    ->         and cost.costid = usergroups.costid
    ->     ;
ERROR 1054 (42S22): Unknown column 'cost.costid' in 'on clause'
mysql>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29598

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29676

Fixed in 5.0.13.

Timour,

Hmmm, looks like I simplified the test case too much.  :-(

The original customer test case that forced this bug report still fails, even though the simplified test case now works fine.  I will include the full file in a following private comment.

Regards,

Timothy

Please notice that from version 5.0.12 on there is a change in
the syntax of JOIN ... ON (currently being documented) that
makes the syntax SQL:2003 compliant. The essense of the
change is that the ON condition of a JOIN ... ON clause may
reference only tables that are in one of the operands of the
JOIN. For example, given the tables t1(a), t2(b), t3(c),
the query:
  select * from t1 join t2 join t3 on t1.a = t3.a;
is incorrect because the operands of the second join are
't2' and 't3', but not 't1', thus referring to 't1.a' is an error.
The correct query is:
  select * from (t1 join t2) join t3 on t1.a = t3.a;
Here the first operand of the second join is a virtual table
  t1t2 = (t1 join t2), such that 't1t2' contains the column t1.a.

If we apply this rule to the database attached to the support
issue, then the view definition for view 'vm_getallvmdn' is
incorrect, because the join that contains the condition
  'cost.COSTID = usergroups.COSTID'
does not contain table 'cost' among its operands. Indeed, the
left operand is a join of the tables:
{dn, dnfollowdestinations, mailboxes, users, callhandlingmodes},
and the right operand of the join is table 'usergroups'.

For reference below I provide the most simplified part of the
query that shows the problem:

select *
from
cosvm
  join
(cost
  join
(((((((((dn left join dnfollowdestinations
                 on (dnfollowdestinations.UserDN = dn.DN))
  join
mailboxes on (dn.DN = mailboxes.UserDN))
  join
users on (mailboxes.UserDN = users.UserDN))
  join
callhandlingmodes on (users.CurrentCHMTypeID = callhandlingmodes.CHMTypeID and
                      users.UserDN = callhandlingmodes.UserDN))
  join
usergroups on (users.UserGroupID = usergroups.UserGroupID and
               cost.COSTID = usergroups.COSTID and
               cosvm.COSVMID = usergroups.COSVMID))
  left join
tabaddresses on (users.AddressID = tabaddresses.AddressID))
  left join
workgroupagents on (users.UserDN = workgroupagents.UserDN))
  left join
trunkgroups on (users.DefaultTrunkGroupID = trunkgroups.TrunkGroupID))
  left join
vmnotifications on((dn.DN = vmnotifications.VMNotificationDN))));

Sorry, in my previous example above there is an error - the query
stated to be illegal is actually a legal one.

The corrected example is:

Given the tables t1(a), t2(b), t3(c),
the query:
  select * from t1 join (t2 join t3 on t1.a = t3.c);
is incorrect because the operands of the second join are
 't2' and 't3', but not 't1', thus referring to 't1.a' is an error.
The correct query is:
  select * from (t1 join t2) join t3 on t1.a = t3.c;
Here the first operand of the second join is a virtual table
  t1t2 = (t1 join t2), such that 't1t2' contains the column t1.a.

Notice that in the correct query above one may skip the braces and
rely on the left grouping of joins. Thus the correct query is equivalent
to simply
  select * from t1 join t2 join t3 on t1.a = t3.c;

Noted in 5.0.13 changelog.

Hi, I need to open this bug back up, because it's not fixed completely.  Now there is a problem with RIGHT JOIN.  Here is a full test case, which I tested with the most recent
code from BitKeeper (5.0.14-rc):

drop table if exists a, b, c;
create table a (
  id int(11) not null default '0'
) engine=myisam default charset=latin1;

insert into a values (123),(191),(192);

create table b (
  id char(16) character set utf8 not null default ''
) engine=myisam default charset=latin1;

insert into b values ('58013'),('58014'),('58015'),('58016');

create table c (
  a_id int(11) not null default '0',
  b_id char(16) character set utf8 default null
) engine=myisam default charset=latin1;

insert into c values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');

-- With LEFT JOIN, it works OK
select count(*) from a inner join (c left join b on b.id = c.b_id) on a.id = c.a_id;

-- With RIGHT JOIN, it gives an error
select count(*) from a inner join (b right join c on b.id = c.b_id) on a.id = c.a_id;

When I run this test case, I get:

[tsmith@production m]$ mysql test < bug.sql 
count(*)
6
ERROR 1054 (42S22) at line 22: Unknown column 'c.a_id' in 'on clause'

This bug seems happen on mysql Ver 14.12 Distrib 5.0.22 again