[GENERAL] order of clauses

[Patrick Welche]

create table vals (
  x float,
  y float
);
insert into vals values (2,4);
insert into vals values (2,2);
insert into vals values (2,1);
insert into vals values (2,0);
select x/y from vals where y0 and x/y1;

will give a divide by zero error as A=(y0) and B=(x/y1) can be evaluated in
any order (A and B = B and A). I obviously would like (y0) to happen first,
but I don't see how this can be achieved.. Any ideas?

Cheers,

Patrick

Re: [GENERAL] order of clauses

[Michael Fork]

You didn't mention what version of Postgres, but in 7.1beta, you could do
the following (pretty sure on the syntax):

SELECT a.x/b.y FROM vals a, (SELECT y FROM vals WHERE y  0) b WHERE (a.x
/ b.y)  1;

In anything else, you could try a view:

CREATE VIEW valid_vals  AS SELECT y FROM vals WHERE y  0;
SELECT a.x/b.y FROM vals a, valid_vals b WHERE (a.x 
/ b.y)  1

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Wed, 14 Feb 2001, Patrick Welche wrote:

 create table vals (
   x float,
   y float
 );
 insert into vals values (2,4);
 insert into vals values (2,2);
 insert into vals values (2,1);
 insert into vals values (2,0);
 select x/y from vals where y0 and x/y1;
 
 will give a divide by zero error as A=(y0) and B=(x/y1) can be evaluated in
 any order (A and B = B and A). I obviously would like (y0) to happen first,
 but I don't see how this can be achieved.. Any ideas?
 
 Cheers,
 
 Patrick
 

Re: [GENERAL] order of clauses

[Tom Lane]

Patrick Welche [EMAIL PROTECTED] writes:
 select x/y from vals where y0 and x/y1;

 will give a divide by zero error as A=(y0) and B=(x/y1) can be evaluated in
 any order (A and B = B and A). I obviously would like (y0) to happen first,
 but I don't see how this can be achieved.. Any ideas?

Of course you can rewrite this particular case to avoid the division,
but I suppose you are looking for a more general answer.
Consider something like

CASE WHEN y  0 THEN x/y  1 ELSE false END

I think that right now, the planner gratuitously reverses the order of
the WHERE clauses that it's unable to convert to index/join quals, thus
your failure.  So you could hack around the problem just by switching
the two conditions.  I've been meaning to try to figure out where the
reversal is happening and undo it, however, so this behavior should not
be considered to be documented/supported/guaranteed.

regards, tom lane

Re: [GENERAL] order of clauses

[Dan Wilson]

: SELECT a.x/b.y FROM vals a, (SELECT y FROM vals WHERE y  0) b WHERE (a.x
: / b.y)  1;

How much of a performance hit is there when using a select in the FROM
clause?  Is it even noticeable?  How much better is it to create a static
view?

-Dan

Re: [GENERAL] order of clauses

[Steve Wolfe]

  will give a divide by zero error as A=(y0) and B=(x/y1) can be
evaluated in
  any order (A and B = B and A). I obviously would like (y0) to happen
first,
  but I don't see how this can be achieved.. Any ideas?

  I have one idea that would be nifty to implement.  In some compilers, you
can turn off complete boolean checking.  As soon as any part of an
expression will invalidate the expression, it stops evaluating all of it.
That can help you avoid division by zero, and keeps you from evaluating
parts of the expression that don't matter.  It sounds like a good idea, at
least to an ignoramus like me. : )

steve