From: Richard Huxton <dev@archonet.com>
To: newsy@lewczuk.com
To: 'Karsten Hilbert' <Karsten.Hilbert@gmx.net>
To: 'Lista dyskusyjna pgsql-general' <pgsql-general@postgresql.org>
To: 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: Fri, 10 Oct 2003 09:13:50 +0100
Message-ID: <200310100913.50259.dev@archonet.com> (raw)
In-Reply-To: <004001c38f03$8d8b8230$4701a8c0@marek>
References: <004001c38f03$8d8b8230$4701a8c0@marek>
On Friday 10 October 2003 08:53, Marek Lewczuk wrote:
> > > SELECT
> > > _CON.con_id,
> >
> > Please make sure you get the quoting right regarding table
> > names. PostgreSQL will fold _CON into _con unless quoted
> > "_CON". So, it may be that you created the table with quotes
> > ("_CON"). Now, in your query you don't use quotes and thusly
> > it is looking for a _con table. The simple rule of thumb is
> > to either always or never use quotes.
>
> I don't think that this is the solution, becouse the query:
>
> SELECT
> _CON.con_id,
> _MOD.mod_ty,
> _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
> 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
>
> ...is working fine. I belive that this some problem with LEFT JOIN and
> FROM statement. If I will rewrite this query:
I think what's happening here is the fact that if you use explicit joins in a
query that forces the order of the joins. You originally had:
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
which was being parsed as something like:
( (_FUE LEFT JOIN _VER)
LEFT JOIN _YEA ),
_MOD, _CON, ENG
Of course, if it tries to evaluate in this order it can't see _CON from the
innermost brackets.
I believe the "force planner order" is configurable in the forthcoming 7.4
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, 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: <200310100913.50259.dev@archonet.com>
* 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