MySQL Bugs: #429: NATURAL LEFT OUTER JOIN

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:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

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:

My english is poor, but i need an answer

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

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.

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.

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"

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.

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