SQLite Forum

how DISTINCT remove the duplicate rows?
> Does this mean that the final output row depends on the order of scanning the projection candidates?


> Forget this example. By changing the order of scanning origin table, just like adding and using an index, the order of projection candidates will change with it. In that case, will the result change from 1 to 1.0? Isn't it confusing?

Yes, the result may change as you suggest. Perhaps that will confuse some people whose notion of what "DISTINCT" means differs from what the implementation does. However, avoiding that confusion would likely confuse other people with yet another expectation. People are often confused by their query results. They are best served, IMHO, by an implementation with well documented and human-comprehensible rules. I do not see anything in this thread to suggest a different resolution would be less confusing.