Обсуждение: Understanding Aliases

Поиск
Список
Период
Сортировка

Understanding Aliases

От
"Stanislav Raskin"
Дата:

Hello everybody,

 

I did encounter a behaviour of aliases on my postgresql 8.1, which I don’t quite understand.

Consider this schema layout:

 

CREATE TABLE t1

(

  id serial NOT NULL,

  t2_id integer NOT NULL,

  t3_id integer NOT NULL,

  t1_name character varying(255),

  CONSTRAINT t1_pkey PRIMARY KEY (id),

  CONSTRAINT t2_id_fkey FOREIGN KEY (t2_id)

      REFERENCES t2 (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE CASCADE,

  CONSTRAINT t3_id_fkey FOREIGN KEY (t3_id)

      REFERENCES t3 (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE CASCADE

);

 

CREATE TABLE t2

(

  id serial NOT NULL,

  t2_name character varying(255),

  active boolean NOT NULL,

  CONSTRAINT t2_pkey PRIMARY KEY (id)

);

 

CREATE TABLE t3

(

  id serial NOT NULL,

  t3_name character varying(255),

  CONSTRAINT t3_pkey PRIMARY KEY (id)

);

 

CREATE TABLE t4

(

  id bigserial NOT NULL,

  t2_id integer NOT NULL,

  value integer NOT NULL,

  CONSTRAINT t4_pkey PRIMARY KEY (id),

  CONSTRAINT t4_t2_id_fkey FOREIGN KEY (t2_id)

      REFERENCES t2 (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE CASCADE

);

 

 

 

 

Now, I try two different queries. One must select all ids of rows in t2, who have t3 or t1 entries with names containing an ‘a’, and exactly three entries in t4 with a value of 10. This works quite fine:

 

SELECT

            t2.id AS got_t2_id

           

FROM

            t1 JOIN t2 ON (t1.t2_id = t2.id) JOIN t3 ON (t3.id = t1.t3_id)

WHERE

             t2.active

            AND (

                        (SELECT COUNT(id) FROM t4 WHERE t2_id = got_t2_id AND value=10) = 3

            )

            AND (

                                    t3.t3_name ILIKE '%a%'

                        OR       t1.t1_name ILIKE '%a%'

            )

ORDER BY t1.t1_name ASC

 

The second query does not need the “names containing ‘a’” - condition. It only needs to fetch those rows of t2, which have exactly three entries in t4 with a value of 10. Furthermore, no sorting is needed. But, when I try this query

 

SELECT

            t2.id AS got_t2_id

           

FROM

            t2

WHERE

             t2.active

            AND (

                        (SELECT COUNT(id) FROM t4 WHERE t2_id = got_t2_id AND value=10) = 3

            )

 

I always get an

 

ERROR: column "account_id" does not exist

 

What exactly goes wrong here? I simply do not understand, why the first, more complex query works with the alias, but the second one does not. Did I misunderstand the meaning and usage of such aliases as got_t2_id?

 

Thank you very much in advance.

 

Stanislav Raskin

 

Re: Understanding Aliases

От
"Stanislav Raskin"
Дата:

I messed up a little while reformatting, the error message is of course:

 

ERROR: column "got_t2_id" does not exist

 

 


Von: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] Im Auftrag von Stanislav Raskin
Gesendet: Dienstag, 11. Dezember 2007 12:33
An: pgsql-general@postgresql.org
Betreff: [GENERAL] Understanding Aliases

 

Hello everybody,

 

I did encounter a behaviour of aliases on my postgresql 8.1, which I don’t quite understand.

Consider this schema layout:

 

CREATE TABLE t1

(

  id serial NOT NULL,

  t2_id integer NOT NULL,

  t3_id integer NOT NULL,

  t1_name character varying(255),

  CONSTRAINT t1_pkey PRIMARY KEY (id),

  CONSTRAINT t2_id_fkey FOREIGN KEY (t2_id)

      REFERENCES t2 (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE CASCADE,

  CONSTRAINT t3_id_fkey FOREIGN KEY (t3_id)

      REFERENCES t3 (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE CASCADE

);

 

CREATE TABLE t2

(

  id serial NOT NULL,

  t2_name character varying(255),

  active boolean NOT NULL,

  CONSTRAINT t2_pkey PRIMARY KEY (id)

);

 

CREATE TABLE t3

(

  id serial NOT NULL,

  t3_name character varying(255),

  CONSTRAINT t3_pkey PRIMARY KEY (id)

);

 

CREATE TABLE t4

(

  id bigserial NOT NULL,

  t2_id integer NOT NULL,

  value integer NOT NULL,

  CONSTRAINT t4_pkey PRIMARY KEY (id),

  CONSTRAINT t4_t2_id_fkey FOREIGN KEY (t2_id)

      REFERENCES t2 (id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE CASCADE

);

 

 

 

 

Now, I try two different queries. One must select all ids of rows in t2, who have t3 or t1 entries with names containing an ‘a’, and exactly three entries in t4 with a value of 10. This works quite fine:

 

SELECT

            t2.id AS got_t2_id

           

FROM

            t1 JOIN t2 ON (t1.t2_id = t2.id) JOIN t3 ON (t3.id = t1.t3_id)

WHERE

             t2.active

            AND (

                        (SELECT COUNT(id) FROM t4 WHERE t2_id = got_t2_id AND value=10) = 3

            )

            AND (

                                    t3.t3_name ILIKE '%a%'

                        OR       t1.t1_name ILIKE '%a%'

            )

ORDER BY t1.t1_name ASC

 

The second query does not need the “names containing ‘a’” - condition. It only needs to fetch those rows of t2, which have exactly three entries in t4 with a value of 10. Furthermore, no sorting is needed. But, when I try this query

 

SELECT

            t2.id AS got_t2_id

           

FROM

            t2

WHERE

             t2.active

            AND (

                        (SELECT COUNT(id) FROM t4 WHERE t2_id = got_t2_id AND value=10) = 3

            )

 

I always get an

 

ERROR: column "account_id" does not exist

 

What exactly goes wrong here? I simply do not understand, why the first, more complex query works with the alias, but the second one does not. Did I misunderstand the meaning and usage of such aliases as got_t2_id?

 

Thank you very much in advance.

 

Stanislav Raskin

 

Re: Understanding Aliases

От
"Stanislav Raskin"
Дата:

Note to self: works if I directly reference outer query column name from the inner query.

 

Still no Idea why it does not work with the alias.

Re: Understanding Aliases

От
Tom Lane
Дата:
"Stanislav Raskin" <sr@brainswell.de> writes:
> Still no Idea why it does not work with the alias.

SELECT-list output aliases name the *output* columns of the SELECT.
Those output columns are not available to the SELECT's computation
clauses.  Otherwise you'd have a logical circularity --- by definition,
the output row(s) can't have been computed yet when you're evaluating
a WHERE condition.

People frequently think that because ORDER BY can refer to the
output-column aliases, other clauses should be able to do it too,
but this is not so.  Conceptually, ORDER BY happens after computation
of the output rows, so there's no logical inconsistency in allowing
it to do that ... but it's certainly confused enough people that
one wonders whether the SQL committee shouldn't have left that wart
out of the language.

            regards, tom lane

Re: Understanding Aliases

От
"Stanislav Raskin"
Дата:
> People frequently think that because ORDER BY can refer to the
> output-column aliases, other clauses should be able to do it too,
> but this is not so.

Thank you very much for clearing that one up.

Now I have a follow-up question. Let's assume that I want not only to
evaluate a subquery for the WHERE-statement, but also want to fetch the
result of this subquery. I think that this would be the correct way to do
so:

SELECT
            t2.id,
        (SELECT COUNT(id) FROM t4 WHERE t2_id = t2.id AND value=10)
AS t4_num
FROM
        t2
WHERE
        t2.active
            AND (
(SELECT COUNT(id) FROM t4 WHERE t2_id = t2.id AND value=10) <= 3
        )

Will the subquery be evaluated twice, or is postgres smart enough to somehow
cache the result from the first call of the subquery?

Kind Regards

Stanislav Raskin


Re: Understanding Aliases

От
"Harald Armin Massa"
Дата:
Stanislav,

SELECT
            t2.id,
               (SELECT COUNT(id) FROM t4 WHERE t2_id = t2.id AND value=10)
AS t4_num
FROM
               t2
WHERE
               t2.active
           AND (
(SELECT COUNT(id) FROM t4 WHERE t2_id = t2.id AND value=10) <= 3
               )


select
    t2.id, count( t4.id)
from
   t2 join t4 using on (t2.id=t4.t2_id)
where
   t2.active and t4.value < 10
group by t2.id
having count(t4.id) <= 3

should do the trick without double select, or?

Harald


--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

Re: Understanding Aliases

От
"Stanislav Raskin"
Дата:
> should do the trick without double select, or?

Indeed it seems to do so. The cost estimation is even about 10 times less
with the real application's queries.
I never really used group/having even though being aware of their existence.

I probably should do so more often.


Re: Understanding Aliases

От
"Stanislav Raskin"
Дата:

You are quite right, and I must admit that I messed up one more time on this one.

I chose an alias, which was already a column name in one of the relations, so the results were rubbish anyhow.

Took me some time to figure that out.

 


Von: Vyacheslav Kalinin [mailto:vka@mgcp.com]
Gesendet: Dienstag, 11. Dezember 2007 19:54
An: Stanislav Raskin
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Understanding Aliases

 

> SELECT-list output aliases name the *output* columns of the SELECT.
> Those output columns are not available to the SELECT's computation
> clauses

Then it's unclear how could the first query work

Re: Understanding Aliases

От
"Vyacheslav Kalinin"
Дата:
> SELECT-list output aliases name the *output* columns of the SELECT.
> Those output columns are not available to the SELECT's computation
> clauses

Then it's unclear how could the first query work