4 ms·

It really depends how 'carefully crafted' your queries are. If there are things like index hints, you can just strip them out. If you are using non-standard m

It really depends how 'carefully crafted' your queries are. If there are things like index hints, you can just strip them out. If you are using non-standard mysql specific syntax, that will be a bit of a pain, but it won't take more than a day or two to fix a few hundred queries. Where you are going to experience the most pain is that MySQL lets you write idiotic queries like this: SELECT id, last_name FROM some_table GROUP BY last_name; That is not valid SQL, for good reason. Other things that will bite you: http://andreas.scherbaum.la/blog/archives/657-PostgreSQL-9.0-Includes-the-new-MySQL-Emulation-Layer.html http://andreas.scherbaum.la/blog/archives/657-PostgreSQL-9.0... God, looking at that link makes me so sorry for anyone using MySQL. Life is too short for that.

rudiger 15y ago
Why isn't that valid SQL?
- ericflo 15y ago
  'id' doesn't appear in the group by list and it isn't being used in an aggregate function. I'm actually curious: what id would a MySQL user expect to be displayed for this query?
  - rimantas 15y ago
    > I'm actually curious: what id would a MySQL user expect to be displayed > for this query? MySQL discourages using this feature if columns not included in GROUP BY are not constant in the group: > Do not use this feature if the columns you omit from the GROUP BY part > are not constant in the group. The server is free to return any value > from the group, so the results are indeterminate unless all values are > the same. Their example in documentation: SELECT order.custid, customer.name, MAX(payments) FROM order,customer WHERE order.custid = customer.custid GROUP BY order.custid;
    - jeffdavis 15y ago
      "MySQL discourages using this feature if columns not included in GROUP BY are not constant in the group" That's what errors are for, not documentation. It is pretty easy to forget something in the group by, and documentation won't help with that. The dangerous thing is that the result returned from such a nonsense query looks valid in many cases, while being wrong in subtle ways. PostgreSQL detects when the query is valid, and executes it if so. So, if you do a GROUP BY customer_id (a key column), you can also see customer_name without adding it to the GROUP BY list. But if you group by customer_zipcode (not a key), and try to select the customer_name, it will throw an error.
- mey 15y ago
  Ponder: Which id is returned when you have more then one last name that is the same?
- justin_vanw 15y ago
  Well, lets say your rows are: id|score|last_name 1|11|smith 2|22|jones 3|33|smith 4|44|jones Query we are pretending is valid is: SELECT id, last_name FROM table GROUP BY last_name; What rows are returned? I expect to see something like: ?|?|smith ?|?|jones However, what ? is isn't clear. Could we get a row like 1|33|smith ? In SQL, all selected columns must be part of the group by or inside an aggregate.
rimantas 15y ago
And what stops you from using SQL standard syntax? You can disable MySQL extensions for that if you wish so.
sjwright 15y ago
> If you are using non-standard mysql specific syntax, that will be a bit of a pain, but it won't take more than a day or two to fix a few hundred queries. One issue is with non-standard MySQL functions (such as inet_aton/inet_ntoa) and aggregate functions (such as group_concat). While most have great and superior pg implementations, it's a lot of work to cross-reference each one, grep the source code, and hope the pg implementation is sufficiently identical. Another issue is that MySQL considers the 'as' keyword to be optional. I'm sure pg has good reason for requiring it, but there's probably about a thousand 'as'es to be added. And that's about where I gave up last time, if I recall.

rudiger 15y ago

Why isn't that valid SQL?

ericflo 15y ago
'id' doesn't appear in the group by list and it isn't being used in an aggregate function. I'm actually curious: what id would a MySQL user expect to be displayed for this query?
- rimantas 15y ago
  > I'm actually curious: what id would a MySQL user expect to be displayed > for this query? MySQL discourages using this feature if columns not included in GROUP BY are not constant in the group: > Do not use this feature if the columns you omit from the GROUP BY part > are not constant in the group. The server is free to return any value > from the group, so the results are indeterminate unless all values are > the same. Their example in documentation: SELECT order.custid, customer.name, MAX(payments) FROM order,customer WHERE order.custid = customer.custid GROUP BY order.custid;
  - jeffdavis 15y ago
    "MySQL discourages using this feature if columns not included in GROUP BY are not constant in the group" That's what errors are for, not documentation. It is pretty easy to forget something in the group by, and documentation won't help with that. The dangerous thing is that the result returned from such a nonsense query looks valid in many cases, while being wrong in subtle ways. PostgreSQL detects when the query is valid, and executes it if so. So, if you do a GROUP BY customer_id (a key column), you can also see customer_name without adding it to the GROUP BY list. But if you group by customer_zipcode (not a key), and try to select the customer_name, it will throw an error.
mey 15y ago
Ponder: Which id is returned when you have more then one last name that is the same?
justin_vanw 15y ago
Well, lets say your rows are: id|score|last_name 1|11|smith 2|22|jones 3|33|smith 4|44|jones Query we are pretending is valid is: SELECT id, last_name FROM table GROUP BY last_name; What rows are returned? I expect to see something like: ?|?|smith ?|?|jones However, what ? is isn't clear. Could we get a row like 1|33|smith ? In SQL, all selected columns must be part of the group by or inside an aggregate.

rimantas 15y ago

And what stops you from using SQL standard syntax? You can disable MySQL extensions for that if you wish so.

sjwright 15y ago

> If you are using non-standard mysql specific syntax, that will be a bit of a pain, but it won't take more than a day or two to fix a few hundred queries. One issue is with non-standard MySQL functions (such as inet_aton/inet_ntoa) and aggregate functions (such as group_concat). While most have great and superior pg implementations, it's a lot of work to cross-reference each one, grep the source code, and hope the pg implementation is sufficiently identical. Another issue is that MySQL considers the 'as' keyword to be optional. I'm sure pg has good reason for requiring it, but there's probably about a thousand 'as'es to be added. And that's about where I gave up last time, if I recall.