Thread: SQL query problem

SQL query problem

From

"Marek Lewczuk"

Date:

09 October 2003, 18:53:46

Hello,
I'm in the middle of the migration process from MySQL to PostgreSQL and
I cannot understand why this query isn't working (in MySQL it's working
fine). PG returns: ERROR:  Relation "_con" does not exist

This is my 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,
  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


I will be appreciated for you help.

ML

Re: [SQL] SQL query problem

From

Bruno Wolff III

Date:

09 October 2003, 18:58:52

On Thu, Oct 09, 2003 at 20:52:58 +0100,
  Marek Lewczuk <newsy@lewczuk.com> wrote:
> Hello,
> I'm in the middle of the migration process from MySQL to PostgreSQL and
> I cannot understand why this query isn't working (in MySQL it's working
> fine). PG returns: ERROR:  Relation "_con" does not exist

Probably you quoted the table name when you created it so that it is named
_CON. However when you don't quote the name in later references _CON
is converted to _con which is a different name that doesn't match any
existing table.

This behavior is contrary to the spec (lowercasing instead of uppercasing),
but isn't going to change since uppercase names look ugly.

A good general rule to follow is to either never quote names or always
quote names.

>
> This is my 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,
>   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
>
>
> I will be appreciated for you help.
>
> ML
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

Re: SQL query problem

From

Karsten Hilbert

Date:

09 October 2003, 21:39:54

> fine). PG returns: ERROR:  Relation "_con" does not exist
>
> This is my query:
>
> 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.

HTH,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: SQL query problem (many table in FROM statement and many LEFT JOIN's)

From

"Marek Lewczuk"

Date:

10 October 2003, 06:53:53

> > 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: [SQL] SQL query problem (many table in FROM statement and many LEFT JOIN's)

From

Richard Huxton

Date:

10 October 2003, 08:14:58

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

Re: [SQL] SQL query problem (many table in FROM statement and many LEFT JOIN's)

From

Tom Lane

Date:

14 October 2003, 14:35:48

"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