Database SQL Compatibility

Question

I am deploying a Ruby on Rails application that I developed with Sqlite3 to a server with either MySQL or PostgreSQL. I quickly discovered that the "group by" and "strftime" functions that I am using heavily to produce by-month rollup reports are working differently or not compatible between the various databases.

I can refactor my code to do the grouping, summing and averaging - but the database does such a nice job of it and reduces the processing required by the server! Advanced applications go beyond simple select and join. ActiveRecord gives us :group, but the DATABASES are not CONSISTENT.

So my question is a matter of architecture - does anyone expect to create truly "database portable" applications in Ruby on Rails? Should I modify my codebase to work with MySQL only and forget about the other databases? Should I modify my codebase to do the advanced grouping, summing, and averaging?

cheers - Don

Answer 1

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 RTFM 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.

Answer 2

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