I agree with you here. It is a temporary table that should "FIX" the
values.
Interestingly
select name, RNDValue
from (
select 'name', random() as RNDValue
)
where RNDValue > 0;
only calls random once and works as expected.
0|Trace|0|0|0|explain select name, RNDValuefrom ( select 'name',
random() as RNDValue)where RNDValue > 0;|00|
1|OpenEphemeral|0|2|0||00|
2|Goto|0|17|0||00|
3|String8|0|1|0|name|00|
4|Function|0|0|2|random(-1)|00|
5|MakeRecord|1|2|3||00|
6|NewRowid|0|4|0||00|
7|Insert|0|3|4||08|
8|Integer|0|5|0||00|
9|Rewind|0|16|0||00|
10|Column|0|1|3||00|
11|Le|5|15|3|collseq(BINARY)|6a|
12|Column|0|0|6||00|
13|Column|0|1|7||00|
14|ResultRow|6|2|0||00|
15|Next|0|10|0||00|
16|Halt|0|0|0||00|
17|Goto|0|3|0||00|
Noah
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik
Sent: Friday, August 29, 2008 9:39 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] problem using random() in queries
Noah Hart <[EMAIL PROTECTED]> wrote:
> I would expect that multiple calls to random always return different
> values, even if in the same line.
>
> The fact that we are aliasing random by a column name makes no
> difference to me.
What about this:
select name, RNDValue
from (
select name, random() as RNDValue from names
)
where RNDValue > 0;
Conceptually, the inner select produces a temporary table, and the outer
works on that table. Personally, I found it surprising that random() was
still called twice per row in this case.
Igor Tandetnik
CONFIDENTIALITY NOTICE:
This message may contain confidential and/or privileged information. If you are
not the addressee or authorized to receive this for the addressee, you must not
use, copy, disclose, or take any action based on this message or any
information herein. If you have received this message in error, please advise
the sender immediately by reply e-mail and delete this message. Thank you for
your cooperation.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
