Обсуждение: Rewritten queries

Hi All,

I wrote a query that created a view and when I looked at it in the database
the SELECT statement was very different.  In part

SELECT items.id,
       COALESCE(sales.sum, 0) AS committed,
       COALESCE(purchases.sum, 0) AS on_order,
       COALESCE(stock.quantity, 0) AS on_hand
  FROM peachtree.tbl_item AS items...

was changed to

CREATE OR REPLACE VIEW interface.view_inventory AS

 SELECT items.id,
        CASE
            WHEN (sales.sum IS NOT NULL ) THEN sales.sum
            WHEN (0 IS NOT NULL ) THEN (0 )::real
            ELSE NULL::real
        END  AS "committed",
        CASE
            WHEN (purchases.sum IS NOT NULL ) THEN purchases.sum
            WHEN (0 IS NOT NULL ) THEN (0 )::real
            ELSE NULL::real
        END  AS on_order,
        CASE
            WHEN (stock.quantity IS NOT NULL ) THEN stock.quantity
            WHEN (0 IS NOT NULL ) THEN (0 )::real
            ELSE NULL::real
        END  AS on_hand
   FROM (((peachtree.tbl_item items

Is this expected behavior?  Should I be concerned?  Is the modified query
faster?  Better in some way?  Should I be writing my queries to conform with
the modified syntax?

Kind Regards,
Keith

______________________________________________
99main Internet Services http://www.99main.com

 --- Keith Worthington <keithw@narrowpathinc.com>
escribió:
> Hi All,
>
> I wrote a query that created a view and when I
> looked at it in the database
> the SELECT statement was very different.
> ...
> Is this expected behavior?

Yes. Sometimes postgres will rewrite your queries.

> Should I be concerned?

Actually, no. If a query has to be rewritten Postgres
always will rewrite the query before execution. In a
view it doesn't want to do that every time so it
create the view with the modified query.

> Is the modified query faster?

Sometimes, but not necesarilly.

> Better in some way?

I think is just a way to manage different sintaxis
that do the same. But i can be wrong.

For example queries with field1 IN (value1, value2)
will be rewritten like
(field1 = value1 or field1 = value2)

> Should I be writing my queries to conform with
> the modified syntax?
>
If u want. I do not if u can gain some performance
when the query will be no rewritten. I do not.

regards,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

 --- Keith Worthington <keithw@narrowpathinc.com>
escribió:
> Hi All,
>
> I wrote a query that created a view and when I
> looked at it in the database
> the SELECT statement was very different.  In part
>
> SELECT items.id,
>        COALESCE(sales.sum, 0) AS committed,
>        COALESCE(purchases.sum, 0) AS on_order,
>        COALESCE(stock.quantity, 0) AS on_hand
>   FROM peachtree.tbl_item AS items...
>
> was changed to
>
> CREATE OR REPLACE VIEW interface.view_inventory AS
>
>  SELECT items.id,
>         CASE
>             WHEN (sales.sum IS NOT NULL ) THEN
> sales.sum
>             WHEN (0 IS NOT NULL ) THEN (0 )::real
>             ELSE NULL::real
>         END  AS "committed",
>         CASE
>             WHEN (purchases.sum IS NOT NULL ) THEN
> purchases.sum
>             WHEN (0 IS NOT NULL ) THEN (0 )::real
>             ELSE NULL::real
>         END  AS on_order,
>         CASE
>             WHEN (stock.quantity IS NOT NULL ) THEN
> stock.quantity
>             WHEN (0 IS NOT NULL ) THEN (0 )::real
>             ELSE NULL::real
>         END  AS on_hand
>    FROM (((peachtree.tbl_item items
>
What version of postgres are u using?
I don't see that particular behavior in coalesce.

someone knows if this is the behavior in any older
version of pg?

regards,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

Jaime Casanova <systemguards@yahoo.com> writes:
> What version of postgres are u using?
> I don't see that particular behavior in coalesce.

> someone knows if this is the behavior in any older
> version of pg?

Yes.  Since about 7.4 COALESCE is a first-class expression node type,
but before that the parser expanded it into a CASE construct.

The CASE implementation is lacking in that it may evaluate the arguments
more than once, which could be wrong if they are volatile values.

            regards, tom lane

 --- Tom Lane <tgl@sss.pgh.pa.us> escribió:
> Jaime Casanova <systemguards@yahoo.com> writes:
> > What version of postgres are u using?
> > I don't see that particular behavior in coalesce.
>
> > someone knows if this is the behavior in any older
> > version of pg?
>
> Yes.  Since about 7.4 COALESCE is a first-class
> expression node type,
> but before that the parser expanded it into a CASE
> construct.
>
> The CASE implementation is lacking in that it may
> evaluate the arguments
> more than once, which could be wrong if they are
> volatile values.
>
>             regards, tom lane
>

So, i think the recommended suggestion will be to
upgrade to a newer version. Maybe you want to wait
until 8 is out.

regards,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com