DISTINCT malfunctions for VIEW
|User & Date:||drh 2019-09-10 19:46:38|
- Change icomment to:
0 is not distinct from 0.0 since (0 IS 0.0) IS TRUE. So the query
SELECT DISTINCT * FROM (VALUES(0),(0.0));
Is free to return either 0 or 0.0. The choice is arbitrary. It is analogous to a SELECT with a "LIMIT 1" and without an ORDER BY clause in that the query is free to return any single row that it wants.
If the query decides to return 0, then 0||0.1 is a string '00.1' which evaluates to TRUE. If the query decides to return 0.0, then 0.0||0.1 is a string '0.00.1' which evaluates to false. So the other queries in the original problem statement are free to return either result depending on which value comes out of the ambiguous view.
- Change login to "drh"
- Change mimetype to "text/x-fossil-wiki"
- Change priority to "Immediate"
- Change resolution to "Not_A_Bug"
- Change status to "Closed"
- Change subsystem to "Unknown"