From: Tom Lane <tgl@sss.pgh.pa.us>
To: newsy@lewczuk.com
Cc: 'Karsten Hilbert' <Karsten.Hilbert@gmx.net>
Cc: 'Lista dyskusyjna pgsql-general' <pgsql-general@postgresql.org>
Cc: Lista dyskusyjna pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: [SQL] SQL query problem (many table in FROM statement and many LEFT JOIN's)
Date: Tue, 14 Oct 2003 10:35:06 -0400
Message-ID: <9190.1066142106@sss.pgh.pa.us> (raw)
In-Reply-To: <004001c38f03$8d8b8230$4701a8c0@marek>
References: <004001c38f03$8d8b8230$4701a8c0@marek>
"Marek Lewczuk" <newsy@lewczuk.com> writes:
> ... It also working fine. !!!! The question is, why my first query isn't
> working:
> SELECT
> _CON.con_id,
> _MOD.mod_ty,
> _VER.version,
> _YEA.year,
> _CON.dri_id,
> _CON.man_cod,
> _ENG.eng_pow
> FROM
> db_data.mda_mod _MOD,
> db_data.mda_mod_con _CON,
> db_data.mda_mak_eng _ENG,
> db_data.set_mda_fue _FUE
> LEFT JOIN db_data.mda_mod_ver _VER ON _VER.ver_id = _CON.ver_id
> LEFT JOIN db_data.mda_mod_yea _YEA ON _YEA.yea_id = _CON.yea_id
> WHERE
> _MOD.mod_id = '283' AND
> _CON.mod_id = _MOD.mod_id AND
> _CON.psd <= NOW() AND
> _CON.ped > NOW() AND
> _ENG.eng_id = _CON.eng_id AND
> _ENG.eng_fue = _FUE.fue_id
The reason that works in MySQL and fails in Postgres is that MySQL isn't
compliant with the SQL standard. The standard says that the above FROM
clause means that _FUE is left-joined to _VER, then that result is
left-joined to _YEA, then the _MOD, _CON, and _ENG tables are joined to
that result (in no particular order). You get the error because the
LEFT JOIN ON clauses refer to _CON which is not part of what they are
joining.
I believe that MySQL interprets the above statement as "join the tables
in the order listed in the FROM clause", that is they join
_MOD/_CON/_ENG/_FUE, then left-join _VER to that result, etc. This is a
hangover from days when they didn't actually have a query planner.
Unfortunately, it's not SQL, it's only something that looks like SQL.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, newsy@lewczuk.com, Karsten.Hilbert@gmx.net, pgsql-general@postgresql.org
Subject: Re: [SQL] SQL query problem (many table in FROM statement and many LEFT JOIN's)
In-Reply-To: <9190.1066142106@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox