Tom,

> I don't think there's any very clean way to fix this sort of problem in
> general.  We could make this particular example work if

Frankly, I don't think there *is* any safe way to use volatile functions in 
subqueries -- I certainly avoid it, except now() and random() which as 
discussed are special cases.    Perhaps a WARNING is in order? 

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Mon, 4 Oct 2004 10:22:39 -0700
Josh Berkus <josh@agliodbs.com>
Re: is it a known issue or just a bug?
Josh Berkus wrote:
> Tom,
> 
> 
>>I don't think there's any very clean way to fix this sort of problem in
>>general.  We could make this particular example work if
> 
> 
> Frankly, I don't think there *is* any safe way to use volatile functions in 
> subqueries -- I certainly avoid it, except now() and random() which as 
> discussed are special cases.    Perhaps a WARNING is in order? 
> 

Personally I like Josh's idea. A warning would be a nice thing.

I have just looked at Oracle and SQL Server to find out what those 
systems are doing ...

Oracle has a very interesting concept *g*:

ORA-02287 sequence number not allowed here:
The usage of a sequence is limited and it can be used only in few areas 
of PL/SQL and SQL coding.

The following are the cases where you can't use a sequence:
For a SELECT Statement:
   1. In a WHERE clause
   2. In a GROUP BY or ORDER BY clause
   3. In a DISTINCT clause
   4. Along with a UNION or INTERSECT or MINUS
   5. In a sub-query

--------------------------

SQL Server does not support sequences the way we know it so it is hard 
to compare.

I did not have time to test DB2 yet.

	Thanks a lot,

		Hans


-- 
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/720/10 1234567 or +43/660/816 40 77
www.cybertec.at, www.postgresql.at, kernel.cybertec.at



Mon, 04 Oct 2004 20:34:40 +0200
=?UTF-8?B?SGFucy1Kw7xyZ2VuIFNjaMO2bmln?= <postgres@cybertec.at>
Re: is it a known issue or just a bug?
=?UTF-8?B?SGFucy1Kw7xyZ2VuIFNjaMO2bmln?= <postgres@cybertec.at> writes:
> Josh Berkus wrote:
>> Frankly, I don't think there *is* any safe way to use volatile functions in 
>> subqueries -- I certainly avoid it, except now() and random() which as 
>> discussed are special cases.    Perhaps a WARNING is in order? 

> Personally I like Josh's idea. A warning would be a nice thing.

 From the planner's perspective, it would have to warn about any volatile
function, which would probably be overly chatty --- remember that the
default marking for user-defined functions is "volatile".

This default may also be a good reason not to put in the anti-flattening
defenses I suggested before, because it would mean that even slight
sloppiness in the definition of a user function could cripple subquery
optimization.  I'm not sure that that's a strong argument, but it's
something to think about.

It'd be easy enough to put in the anti-flattening defenses (checks (1)
and (2) in my prior message) but I've got mixed emotions about whether
this is really a good thing to do.  Any opinions out there?

			regards, tom lane

Mon, 04 Oct 2004 15:25:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: is it a known issue or just a bug? 
Tom,

> It'd be easy enough to put in the anti-flattening defenses (checks (1)
> and (2) in my prior message) but I've got mixed emotions about whether
> this is really a good thing to do.  Any opinions out there?

If my opinion wasn't clear, I was suggesting adding a WARNING and not doing 
anything about flattening.   I can't say that, in 5 years of developing 
applications in Postgres, that this has ever been a problem for me personally 
-- from my perspective the persons reporting the issue needs to re-code their 
query, it's not what sequences were meant for.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Mon, 4 Oct 2004 12:54:23 -0700
Josh Berkus <josh@agliodbs.com>
Re: is it a known issue or just a bug?