Обсуждение: BUG #1854: SQL Bug

The following bug has been logged online:

Bug reference:      1854
Logged by:          Christian Almeida
Email address:      cbalmeida@gmail.com
PostgreSQL version: 8.0
Operating system:   Windows XP SP2
Description:        SQL Bug
Details:

Should this sql work?

I just picked 3 tables from pg_catalog and build this query. It really
should not work, because there's a reference to a table
(PG_CATALOG.PG_CLASS) wich is not listed in any from clauses.

Try it yourself:

SELECT
  *
FROM
  PG_CATALOG.PG_AM
WHERE
  NOT EXISTS
  (
  SELECT
    1
  FROM
    PG_CATALOG.PG_CAST
  WHERE
    PG_CATALOG.PG_CLASS.RELOWNER = PG_CATALOG.PG_AM.AMOWNER
  )

Cheers,

Christian Almeida.

SELECT
  *
FROM
  PG_CATALOG.PG_AM
WHERE
  NOT EXISTS
  (
  SELECT
    1
  FROM
    PG_CATALOG.PG_CAST
  WHERE
    PG_CATALOG.PG_CLASS.RELOWNER = PG_CATALOG.PG_AM.AMOWNER
  )

On Mon, 29 Aug 2005, Christian Almeida wrote:

> Should this sql work?

Yes and no. ;)  Whether this errors or adds the reference is controlled by
a configuration variable, add_missing_from.  For backwards compatibility
reasons (and for allowing outside table references in delete) the variable
currently defaults to on IIRC.  At some point in the future the default
will likely change to off.

On Mon, Aug 29, 2005 at 09:11:06 -0700,
  Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
>
> On Mon, 29 Aug 2005, Christian Almeida wrote:
>
> > Should this sql work?
>
> Yes and no. ;)  Whether this errors or adds the reference is controlled by
> a configuration variable, add_missing_from.  For backwards compatibility
> reasons (and for allowing outside table references in delete) the variable
> currently defaults to on IIRC.  At some point in the future the default
> will likely change to off.

Unless things change it will be off by default in 8.1. This is mentioned
in the release notes. There is now a USING clause for DELETE which covers
the case that couldn't previously be handled without the missing from
feature.

On Mon, Aug 29, 2005 at 09:11:06AM -0700, Stephan Szabo wrote:
> On Mon, 29 Aug 2005, Christian Almeida wrote:
> > Should this sql work?
>
> Yes and no. ;)  Whether this errors or adds the reference is controlled by
> a configuration variable, add_missing_from.  For backwards compatibility
> reasons (and for allowing outside table references in delete) the variable
> currently defaults to on IIRC.  At some point in the future the default
> will likely change to off.

add_missing_from will be off by default in 8.1.

--
Michael Fuhr