wrong generated SQL

[GoogleCodeExporter]

What steps will reproduce the problem?
1.create 3 tables

CREATE TABLE `table1` (
  `tb1_id_a` int(11) NOT NULL auto_increment,
  `Name` varchar(50) default NULL,
  PRIMARY KEY  (`tb1_id_a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `table2` (
  `tb2_ID_b` int(11) NOT NULL auto_increment,
  `tb2_ID_c` int(11) default NULL,
  `Exporta` int(11) default '0',
  PRIMARY KEY  (`tb2_ID_b`),
  CONSTRAINT `fk_table1` FOREIGN KEY (`tb2_ID_c`) REFERENCES `table1` (`tb1_id_a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `table3` (
  `tb3_ID_c` int(11) NOT NULL,
  `tb3_ID_d` int(11) NOT NULL,

  PRIMARY KEY  (`tb3_ID_c`,`tb3_ID_d`),
  CONSTRAINT `fk_table2` FOREIGN KEY (`tb3_ID_d`) REFERENCES `table2` (`tb2_ID_b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2.generate the classes with dbmetal

3.var b = (from a in db1.table3
                 where a.table2.Exporta == 0
                 select new {
                     a.table2.table1.Name,
                     a.table2.table1.tb1ida
                 });

What is the expected output? What do you see instead?

you get a query like this

SELECT t2$.`Name`, t2$.`tb1_id_a`
FROM `alf_sitio`.`table2` t1$
 LEFT JOIN `alf_sitio`.`table1` t2$ ON t2$.`tb1_id_a` = t1$.`tb2_ID_c`
WHERE (t1$.`tb2_ID_b` = a$.`tb3_ID_d`) AND (t1$.`Exporta` = 0)

the reference to table3 is missing so the query won't work with error "Unknown 
column 'a$.tb3_ID_d' in 'where clause'"

the right query should be at least

SELECT t2$.`Name`, t2$.`tb1_id_a`
FROM 
 `bd`.`table3` a$,
 `bd`.`table2` t1$
 LEFT JOIN `bd`.`table1` t2$ ON t2$.`tb1_id_a` = t1$.`tb2_ID_c`
WHERE (t1$.`tb2_ID_b` = a$.`tb3_ID_d`) AND (t1$.`Exporta` = 0)

What version of the product are you using? On what operating system?

I am using windows XP, Visual Studio 2010, DbLinq-0.20.1

if I create this same structure on SQLserver and use the native Linq provider 
the query comes back nicely.

Please provide any additional information below.

I'll try to figure more data today, but I am no .NET expert so don't expect too 
much ;)

Original issue reported on code.google.com by alfredo....@gmail.com on 8 Oct 2010 at 12:19