Hello Florian, It seems dangerous for a cast to modify global state such a MU. The > evaluation > order of functions during query execute isn't always easy to guess, and may > change depending on the execution plan.
I supposed that fuzzy2bool is called just before the terminal evaluation of the WHERE clause which needs a Boolean. My first tests showed that this hypothesis is right but it might be wrong in the case of alternative execution plans. > > With this implicit cast, the query > > SELECT age, young(age) FROM set_alpha(0.1), employees WHERE young(age); > > is equivalent to > > SELECT age, young(age) FROM set_alpha(0.1), employees WHERE > > fuzzy2bool(young(age)); > > Those set_alpha() calls seem equally dangerous. If this alpha is supposed > to be a global parameter, why not set it *before* issuing the query? Alternatively, we could also set the alpha value before the query : SELECT set_alpha(0.1); SELECT age, young(age) FROM employees WHERE young(age); I would be very interested to know if there is smarter way to set global variables. > I can sort the results in the view 'sorted_employees' according to > > value MU of a fuzzy predicate thanks to fuzzy2bool cast function. > > > > CREATE OR REPLACE VIEW sorted_employees AS > > SELECT *, get_mu() as mu > > FROM employees > > ORDER BY mu DESC; > > Are you aware that an ORDER BY clause in a VIEW is only going to work > if you do "SELECT .. .FROM view". I It really the first time I am using views, I didn't know that ! > If the outer query is more complex > than that, I wouldn't bet on the results coming back in the expected order. > I don't mind if the ordering is wrong : it is just a way to process filtering according to K and ALPHA. Usually, you'd attach ORDER BY to the outermost query (or to subqueries > containing a LIMIT clause). > > > The following query > > SELECT age, mu FROM set_k(5), set_alpha(0.1), filtered_employees > > Again, you seem to rely on these set_k(), set_alpha() calls being > done before the query is executed. But postgres doesn't guarantee > that. > > > WHERE young(age); > > gives the results : > > age mu > > 24 1 > > 16 1 > > instead of : > > age mu > > 16 1 > > 21 0.899999976158142 > > 24 0.600000023841858 > > 26 0.400000005960464 > > 26 0.400000005960464 > > > > It seems that the 'LIMIT K' instruction have side effects on the MU > value. > > The execution plan may very well vary depending on the limit. Off-hand, > I'd guess that with a small K, one of these set_whatever() calls in one > of your FROM clauses gets executed after the computation it'd supposed > to affect has already happened. > > > Why is it not working ? How to fix this issue ? > > Don't rely on the execution order of function calls in a SELECT statement. > Divide your functions into two classes. > > The ones which have side-effects (i.e. change global state). These should > *never* be called from SQL statements, except in the trivial case of > "SELECT my_func(...);". Also, they should be marked with VOLATILE > > And the ones without side effects. Those should be marked with IMMUTABLE, > STABLE of VOLATILE, depending on how they're influenced by global state > changes. Read the documentation on these flags. > > Finally, don't assume that ORDER BY inside a view influences the output > order of queries using the view. (Except in the trivial case of > "SELECT * FROM view"). As a rule of thumb, ORDER BY in a view only makes > sense if there's also a LIMIT clause. You are then guaranteed that the > view returns the first <limit> rows according to the specified order. > Don't assume they're necessarily returned in ascending order, though. > > best regards, > Florian Pflug > Thank you very much ! I will try to follow your advices. Thomas
