Quoth "smiths...@essess.org.uk" <smiths...@essess.org.uk>, on 2010-12-29 20:11:34 +0000: > In this instance above clearly random() function has been called more than > once, > as character 1 and character 2 are not part of the 'l2' string being analyzed.
Confirmed behavior in SQLite 3.7.4 from Debian unstable. (I'm not completely sure whether it's a bug or not because I'd have to look at the definition of subselect sources more first.) Here's a minimal pair: sqlite> select a, a+1 from (select random() & 65535 as a); a a+1 39692 39693 sqlite> select a, a+1 from (select random() & 65535 as a from (select 'foo' as x)); a a+1 32219 10986 It looks like the latter makes the query optimizer handle the impure/nondeterministic function call differently: sqlite> explain select a, a+1 from (select random() & 65535 as a); addr opcode p1 p2 p3 p4 p5 comment 0 Trace 0 0 0 00 1 OpenEphemeral 0 1 0 00 2 Goto 0 16 0 00 3 Function 0 0 2 random(0) 00 4 Integer 65535 3 0 00 5 BitAnd 3 2 1 00 6 MakeRecord 1 1 3 00 7 NewRowid 0 2 0 00 8 Insert 0 3 2 08 9 Rewind 0 15 0 00 10 Column 0 0 4 00 11 Integer 1 3 0 00 12 Add 3 4 5 00 13 ResultRow 4 2 0 00 14 Next 0 10 0 01 15 Halt 0 0 0 00 16 Goto 0 3 0 00 sqlite> explain select a, a+1 from (select random() & 65535 as a from (select 'foo' as x)); addr opcode p1 p2 p3 p4 p5 comment 0 Trace 0 0 0 00 1 OpenEphemeral 1 1 0 00 2 Goto 0 19 0 00 3 String8 0 1 0 foo 00 4 MakeRecord 1 1 2 00 5 NewRowid 1 3 0 00 6 Insert 1 2 3 08 7 Rewind 1 18 0 00 8 Function 0 0 2 random(0) 00 9 Integer 65535 3 0 00 10 BitAnd 3 2 4 00 11 Function 0 0 2 random(0) 00 12 Integer 65535 6 0 00 13 BitAnd 6 2 3 00 14 Integer 1 6 0 00 15 Add 6 3 5 00 16 ResultRow 4 2 0 00 17 Next 1 8 0 01 18 Halt 0 0 0 00 19 Goto 0 3 0 00 ---> Drake Wilson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
