On Fri, Jun 11, 2010 at 11:30:43AM +0200, Benoit Mortgat scratched on the wall:
> select col1, col2 from foo group by col1; > > As you can see, that last query does not result in any error, however > col2 should not be selectable if not in the group by clause?A > > Is this a feature, a known bug or an unknown one? If this is a > feature, is there a pragma to forbid such a syntax? While the results are not all that useful, there is nothing that says you can't do this. You can't outright prohibit non-GROUP BY column references in the select header, since they can be passed to aggregate functions. So to address you concern, the short answer is, "don't be stupid." It is always possible to write bad SQL that returns non-sense results. For any non-grouped column, the grouped value is from the "last" row of the group. Of course, the definition of "last" is somewhat undefined. This is actually true of grouped columns as well. If you GROUP BY using a non-unique collation (such as NOCASE), you'll see the same behavior-- the returned value will simply be the value from the "last" row. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
