Re: [GENERAL] order of clauses
> > will give a divide by zero error as A=(y>0) and B=(x/y>1) can be evaluated in > > any order (A and B = B and A). I obviously would like (y>0) 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
Re: [GENERAL] order of clauses
: 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
Patrick Welche <[EMAIL PROTECTED]> writes: > select x/y from vals where y>0 and x/y>1; > will give a divide by zero error as A=(y>0) and B=(x/y>1) can be evaluated in > any order (A and B = B and A). I obviously would like (y>0) 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
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 y>0 and x/y>1; > > will give a divide by zero error as A=(y>0) and B=(x/y>1) can be evaluated in > any order (A and B = B and A). I obviously would like (y>0) to happen first, > but I don't see how this can be achieved.. Any ideas? > > Cheers, > > Patrick >
Re: [GENERAL] order of clauses
Well, it doesn't solve the ordering question, but you could use a where something like this I guess: where y>0 and (x/(case when y=0 then 1 else y end))>1 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 y>0 and x/y>1; > > will give a divide by zero error as A=(y>0) and B=(x/y>1) can be evaluated in > any order (A and B = B and A). I obviously would like (y>0) to happen first, > but I don't see how this can be achieved.. Any ideas?
[GENERAL] order of clauses
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 y>0 and x/y>1; will give a divide by zero error as A=(y>0) and B=(x/y>1) can be evaluated in any order (A and B = B and A). I obviously would like (y>0) to happen first, but I don't see how this can be achieved.. Any ideas? Cheers, Patrick