With the response being public, it’s likely I’ll get something better than “Thanks for your inquiry, we’ll respond to you within 48 hours.”.
In the year 2000, I was working on migrating a web application from MySQL to Oracle. One of the issues we had was a number of queries which took advantage of slackness in MySQLs handling of the GROUP BY clause. Oracle took exception to our poor SQL and forced the developers to rewrite queries to be a higher standard. To illustrate the issue, have a look at the following example.
mysql> create table `test1` (`a` int(11), `b` int(11) ); Query OK, 0 rows affected (0.14 sec) mysql> insert into test1 values (1,2), (1,3); Query OK, 2 rows affected (0.09 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test1; +------+------+ | a | b | +------+------+ | 1 | 2 | | 1 | 3 | +------+------+ 2 rows in set (0.00 sec) mysql> select * from test1 group by a; +------+------+ | a | b | +------+------+ | 1 | 2 | +------+------+ 1 row in set (0.00 sec)
The issue at hand, is the value for the b column. You’ll note that we’ve grouped by a, so we correctly have a single row result. If you’re not sure what the issue is, let’s repopulate the table in a different order and retry.
mysql> truncate test1; Query OK, 0 rows affected (0.10 sec) mysql> insert into test1 values (1,3), (1,2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test1; +------+------+ | a | b | +------+------+ | 1 | 3 | | 1 | 2 | +------+------+ 2 rows in set (0.00 sec) mysql> select * from test1 group by a; +------+------+ | a | b | +------+------+ | 1 | 3 | +------+------+ 1 row in set (0.00 sec)
So, the value for b was 2 in the first example, and is now 3, with the same data in the table and the same SQL. The problem is that there is no aggregation or GROUP BY clause for b. There’s only one possible value for a, but b is not aggregated – there are two possible values: 2 or 3. MySQL just picks one of the two possible values. Let’s see what PostgreSQL does.
database=# create temporary table test1 (a integer, b integer); CREATE TABLE database=# insert into test1 values (1,2), (1,3); INSERT 0 2 database=# select * from test1; a | b ---+--- 1 | 2 1 | 3 (2 rows) database=# select * from test1 group by a; ERROR: column "test1.b" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select * from test1 group by a;
PostgreSQL decides you don’t want undefined / random data in your result set and lets you know. If you’re using GROUP BY queries in MySQL, and rely on the result being, you know, correct, it may be time for a quick audit of your SQL.
A quick search of Stack Overflow questions shows many solutions which include this flaw. The solutions work fine on MySQL, but fail on PostgreSQL.
I’m surprised that 11 years after first learning of MySQL’s handling of the GROUP BY clause, it has the same behaviour enabled by default. MySQL’s behaviour can be modified to behave in a stricter manner. Worth doing if you want to ensure you receive results without including randomly selected data.
New post on Daemons over on the Booko blog.
If you had the idea that books were the only thing Australians over paid for (I know, none of you believe that) check this out from Nintendo. The new Super Mario Bros. has an RRP in the US of $49 USD which is $50.235 AUD. You can buy it on Amazon. According to EBGames, the RRP for the Australian version is $98 AUD, for a markup of almost 100% of the US price.
Via The Age
Check it out: http://blog.booko.com.au/?p=319
Over on the Booko Blog.
If so, a friend of mine is running an art prize. Check it out: http://www.therealartprize.com/
Entry is free and there’s a cash prize. Nothing to loose!
Just posted on the Booko Blogo an article about implementing Users and Passwords.
I’m selling my last one on Ebay right now!
Get yourself a bargain!