My personal and professional life

2020-10-20

MySQL 8.0.22 released

Yesterday, Oracle released MySQL 8.0.22, so it came as a kind of birthday present to me :-) This is of course the next purely maintenance release in the 8.0 series, so there are no big new features, but I normally checked the release notes and the official blog post. There are many small changes and bug fixes, but the list below are the ones that caught my attention:

  • Derived condition pushdown optimization for eligible queries - it could improve the performance of some queries, by rewriting the following query
    • SELECT * FROM (SELECT i, j FROM t1) as dt WHERE i > 10; into
    • SELECT * FROM (SELECT i, j FROM t1 WHERE i > 10) as dt;
  • Parenthesized Query Expressions enables you writing statements like ( query_expression ) [order_by_clause] [limit_clause] [into_clause]. Just a few examples from the manual to get the idea:
    • (SELECT 1 AS result UNION SELECT 2);
    • (SELECT 1 AS result UNION SELECT 2) LIMIT 1;
    • (SELECT 1 AS result UNION SELECT 2) LIMIT 1 OFFSET 1;
    • (SELECT 1 AS result UNION SELECT 2) ORDER BY result DESC LIMIT 1;
    • (SELECT * FROM t1 ORDER BY a) UNION (SELECT * FROM t2 ORDER BY b) ORDER BY z;
  • CAST() or CONVERT() to YEAR - e.g. SELECT CAST('1945ad' AS YEAR) yr; or SELECT CONVERT('1945ad', YEAR) yr; both produce 1945.
  • Retrieve a stored TIMESTAMP value in UTC - please, note "stored" here, because you cannot convert CURRENT_TIMESTAMP to another time zone like this (i.e the statement SELECT CAST(CURRENT_TIMESTAMP() AT TIME ZONE 'UTC' AS DATETIME) utc_tz; results into Error Code: 3998. Cannot cast value to TIMESTAMP WITH TIME ZONE). Given that chapter The DATE, DATETIME, and TIMESTAMP Types in the manual stipulates that "MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval." it should mean the value is retrieved as it's stored, which may be what you want if you'd like to offset the conversion out of the MySQL server in case its time zone support is not suitable for your needs.
  • Read only option for schema provides a way to completely prohibit writes to entities in a schema. It's done with ALTER DATABASE statement (e.g. ALTER DATABASE test READ ONLY = 0) with the purpose to simplify online data migrations.
  • Support for SRV DNS records in the client (see Connecting to the Server Using DNS SRV Records).

I had problems building the new version on Slackware -current with Protobuf library 3.2.1, because apparently the minimum requirement on the library version was changed without notice (see bug 101230) and furthermore is not properly detected and reported by the build system leading to compilation errors afterwards (see bug 101231).