Bug #429 NATURAL LEFT OUTER JOIN
Submitted: 13 May 2003 18:25 Modified: 6 Jun 2003 9:22
Reporter: Sebastian Flores Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.12 OS:Microsoft Windows (Windows 2000)
Assigned to: CPU Architecture:Any

[13 May 2003 18:25] Sebastian Flores
Description:
My english is poor, but i need an answer

NATURAL LEFT OUTER JOIN is wrong when i have more of 3 tables.

Help - ME!

How to repeat:
[13 May 2003 18:30] Sebastian Flores
My english is poor, but i need an answer

NATURAL LEFT OUTER JOIN is wrong when i have more of 2 tables.
[20 May 2003 4:52] Alexander Keremidarski
Sebastian,

Please send repeatable test case. Without How-to-repeat we can't help you.

Send us what query you use, what results you expect and why do you think result is wrong.
[20 May 2003 5:38] Sebastian Flores
the structure of db is:

#
# Table structure for table 'eess'
#

CREATE TABLE /*!32312 IF NOT EXISTS*/ eess (
  id_eess int(5) unsigned NOT NULL auto_increment,
  nombre_eess varchar(255) NOT NULL DEFAULT '' ,
  responsable varchar(255) NOT NULL DEFAULT '' ,
  telefono varchar(100) DEFAULT '0' ,
  direccion varchar(255) DEFAULT '0' ,
  id_provincia tinyint(2) unsigned NOT NULL DEFAULT '0' ,
  id_marca tinyint(3) unsigned NOT NULL DEFAULT '1' ,
  localidad varchar(255) DEFAULT '' ,
  id_boca tinyint(3) unsigned NOT NULL DEFAULT '1' ,
  id_zona tinyint(3) unsigned NOT NULL DEFAULT '1' ,
  PRIMARY KEY (id_eess),
  KEY id_provincia (id_provincia),
  KEY id_eess (id_eess),
  KEY nombre_eess (nombre_eess)
);

#
# Table structure for table 'eess_encuesta'
#

CREATE TABLE /*!32312 IF NOT EXISTS*/ eess_encuesta (
  id_eess int(5) unsigned NOT NULL DEFAULT '0' ,
  id_encuesta int(6) unsigned NOT NULL DEFAULT '0' ,
  fecha_inicio date ,
  votos int(10) unsigned NOT NULL DEFAULT '0' ,
  PRIMARY KEY (id_eess,id_encuesta)
);

#
# Table structure for table 'encuestas'
#

CREATE TABLE /*!32312 IF NOT EXISTS*/ encuestas (
  id_encuesta int(6) unsigned NOT NULL auto_increment,
  descripcion varchar(255) NOT NULL DEFAULT '' ,
  propietario int(3) ,
  encabezado text ,
  activo char(1) DEFAULT '1' ,
  PRIMARY KEY (id_encuesta),
  KEY id_encuesta (id_encuesta)
);

and the query is:

SELECT * FROM encuestas NATURAL LEFT OUTER JOIN eess_encuesta NATURAL LEFT OUTER JOIN eess WHERE eess.id_eess=? ORDER BY fecha_inicio DESC

where '?' is a id_esss in the three tables

in linux version i found the same error.
[31 May 2003 11:05] Alexander Keremidarski
Sebastian,
What error? What is wrong with result? It's good that you sent tables structure, but it is not enough. Send also some rows for them. INSERT ... and result of query which you say is wrong.

And what exactly do you mean by:

"where '?' is a id_esss in the three tables"
[6 Jun 2003 9:22] Alexander Keremidarski
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Open". Thank you.
[1 Mar 2007 16:35] Gulf E-Solutions
I have 3 tables i.e. t1, t2 and t3. Now t1.field1 referances t2.field1 and t1.field2 referances t3.field2. Tables t2 and t3 do not have any relation. If I do left outer join on these tables, I get thousands of records, where as expected number of records are two only.

The query is as under which gives thousands of records.

SELECT t1.field1, t2.field2 FROM t1 left outer join (t2, t3) on (t1.field1=t2.field1 and t1.field2=t3.field2)

Table t1 has only 2 records, table t2 has 7376 records and table t3 has 141 records. After execution of this query, the number of records that are return is 14752.
I do not know as what could be wrong, but it seems some problem in such type of query. If any one can assist me as where am I doing wrong, I will appreciate.

http://www.gulfesolutions.com