Hi All,
First post, think I have found a bug!
Given the SELECT query:
select l2, substr(l2,1,1), substr(l2,2,1)
FROM
(select substr(a,((abs(random()) % 13)*2)+1,2) AS l2
FROM
(select 'abcdefghijklmnopqrstuvwxyz' as a) alpha) l2;
Basically the job of the query is to grab 2 characters from a random point in
the string 'a'.
Running the query, you will get results such as:
lm|z|w
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.
Checked my understanding of SQL by trying it in Postgresql 8.X - bit more fussy
about type conversion so the query becomes:
select l2, substring(l2 FROM 1 FOR 1), substring(l2 FROM 2 FOR 1)
FROM
(select substring(a FROM cast(((random() * 12)*2)+1 AS INTEGER) FOR 2) AS l2
FROM
(select CAST('abcdefghijklmnopqrstuvwxyz' AS VARCHAR(26)) as a) alpha) l2;
The above query gives a result such as:
l2 | substring | substring
----+-----------+-----------
rs | r | s
You can see that the 1st and 2nd character when analysed by substring return the
values you would expect when looking at the l2 column result.
I believe SQLITE3 (version 3.6.23 is the version I am testing against) handles
above case wrong.
Thanks all for your excellent database.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
