Louis Jean-Richard wrote: > I run into an unexpected result from a SELECT on a view > in one of my schema for which
... I have created the following minimal example: sqlite> create table t(x,y); sqlite> insert into t values(1,'a'); sqlite> insert into t values(2,'b'); sqlite> select min(x), y from t; 1|a sqlite> select y from (select min(x), y from t); b > It is quite odd that the content of a table should depend on which of > its columns are selected or not further down. The "min(x)" is not used, so it gets optimized away. And there is a reason why this is legal: When you are using a query with an aggregate function, the SQL standard requires that any column from the table appears either inside some aggregate function or in the GROUP BY clause; something like "SELECT min(x), y" is _not_ allowed. MySQL does not check for this error, and returns the contents of some random record for such a column. For compatibility with MySQL, SQLite behaves the same, and further guarantees that when using "select min(x), y", the value of y comes from the same record from which the final min() value was computed. However, this is a non-standard extension to a non-standard extension, so this feature is not fully specified; it works only if the min(x) value is actually computed. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
