Обсуждение: Re: [GENERAL] SQL query problem (many table in FROM statement and many LEFT JOIN's)
Re: [GENERAL] SQL query problem (many table in FROM statement and many LEFT JOIN's)
От
"Marek Lewczuk"
Дата:
> > 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: 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 JOIN db_data.mda_mod_con _CON ON _CON.mod_id = _MOD.mod_id AND _CON.psd <= NOW() AND _CON.ped > NOW() JOIN db_data.mda_mak_eng _ENG ON _ENG.eng_id = _CON.eng_id JOIN db_data.set_mda_fue _FUE ON _ENG.eng_fue = _FUE.fue_id 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' ... 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
Re: [GENERAL] SQL query problem (many table in FROM statement and many LEFT JOIN's)
От
Richard Huxton
Дата:
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
"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