Database SQL Compatibility

Several comments:

• Develop and test with the same RDBMS brand and version that you're going to deploy to.

• Writing portable SQL code is hard because vendors have all these non-standard extra functions and features. For example, strftime() is not part of the ANSI SQL standard. The only way to resolve this is to RTM for each database you use, and learn what functions they have in common. Sometimes they have a function of a different name that you can use in a similar way. There's no short-cut around this -- you have to study the manuals.

• All the databases support GROUP BY, but SQLite and MySQL are kind of more permissive about certain usage than standard ANSI SQL (and all other brands of database which do follow the standard). Specifically, in your GROUP BY clause you must name every column in your select-list that isn't part of a grouping function.

  The following two examples are right:

  SELECT A, B, COUNT(C) FROM MyTable GROUP BY A, B;SELECT A, COUNT(C) FROM MyTable GROUP BY A;

  But the next one is wrong, because B has multiple values per group, and it's ambiguous which value it should return in a given row:

  SELECT A, B, COUNT(C) FROM MyTable GROUP BY A;

• No framework writes truly portable SQL. Rails' ActiveRecord solves this only in very trivial cases. In fact, ActiveRecord helps solve neither of the examples you give, of functions that are brand-specific, and non-standard GROUP BY clauses.

The problem is that especially with GROUP BY MySQL does it wrong. If you leave out columns from the group by MySQL simply returns "something" accepting that the results may be indeterminate.

You can (should) use the ONLY_FULL_GROUP_BY parameter to make MySQL throw an error if the result of your GROUP BY would not be clearly defined.

Actually there are a lot more settings that should be changed in MySQL to make it behave more sanely

You might be interested in reading this:

http://www.slideshare.net/ronaldbradford/mysql-idiosyncrasies-that-bite-201007