D.2.1 Changes in release 4.1.1 (not released yet)

Functionality added or changed:

• Added new functions DATE(), TIME(), TIMESTAMP(), WEEKOFYEAR(), MICROSECOND(), ADDTIME(), SUBTIME(), DATEDIFF(), TIMEDIFF(), MAKEDATE(), MAKETIME(), and TIMEDIFF().
• Added new syntax for ADDDATE() and SUBDATE(). They now allow the second argument to be specified in days, to be added to or subtracted to the first date argument.
• Added new type values DAY_MICROSECOND, HOUR_MICROSECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND, and MICROSECOND for DATE_ADD(), DATE_SUB(), and EXTRACT().
• Added new %f microseconds format specifier for DATE_FORMAT() and TIME_FORMAT().
• Enabled that all queries in which at least one SELECT does not use indices properly get into slow query log when long log format is used.
• One can now use MERGE tables with tables in different databases.
• Added new functions COMPRESS(), UNCOMPRESS(), and UNCOMPRESSED_LENGTH().
• When doing SET sql_mode=mode for a complex mode (like ANSI), we now update the sql_mode variable to include all options the mode requires.
• Added OLAP (Online Analytical Processing) function ROLLUP, which gives you summary rows for each GROUP BY level.
• Added SQLSTATE codes for all server errors.
• Added client API function mysql_sqlstate() and mysql_stmt_sqlstate() that returns the SQLSTATE error code for the last error.
• --lower-case-table-names=1 now also makes aliases case insensitive. (Bug #534)
• TIME columns with hours > 24 (days) were returned incorrectly to the client.
• ANALYZE, OPTIMIZE, REPAIR, FLUSH (and its equivalents invoked from mysqladmin) commands are now stored in the binary log (hence are replicated to the slave), except FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, FLUSH TABLES WITH READ LOCK, and unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) was used (for a syntax example, see section 4.5.3 FLUSH Syntax).
• New global variable RELAY_LOG_PURGE to enable/disable automatic relay log purging.
• LOAD DATA now produces warnings that can be fetched with SHOW WARNINGS.
• Added support for syntax CREATE TABLE table_name (LIKE table_name2).
• CREATE TABLE table_name (...) TYPE=storage_engine will give a warning if storage engine is not honored.
• Most subqueries are now much faster than before.
• Added alias PURGE BINARY LOGS to PURGE MASTER LOGS.
• PURGE LOGS syntax is disabled (it had been added in version 4.1.0). It is now PURGE MASTER LOGS or PURGE BINARY LOGS.
• Added alias SHOW BDB LOGS for SHOW LOGS.
• Added alias SHOW MASTER LOGS (which had been deleted in version 4.1.0) to SHOW BINARY LOGS.

Bugs fixed:

• Fixed a bug in UNION which prohibited that NULL values are inserted in result set where first SELECT contains NOT NULL columns
• Fixed name resolution of fields of reduced subqueries in unions (bug #745).
• Fixed memory overrun in subqueries in select list with WHERE clause bigger then outer query WHERE clause (bug #726).
• Fixed a bug that made MyISAM tables with FULLTEXT indexes created in 4.0.x unreadable in 4.1.x.
• Fixed a data loss bug in REPAIR TABLE ... USE_FRM when used with tables that contained TIMESTAMP fields and were created in 4.0.x.
• Fixed reduced subselect processing in ORDER BY/GROUP BY clauses (bug #442).
• Fixed name resolving of outer fields of subselect in INSERT/REPLACE statements (bug #446).
• Fixed bug in marking fields of reduced subselects (bug #679).
• Fixed a bug that made CREATE FULLTEXT INDEX syntax illegal.
• Fixed a crash when a SELECT that required a temporary table (marked by Using temporary in EXPLAIN output) was used as a derived table in EXPLAIN command (bug #251).
• Fixed a rare table corruption bug in DELETE from a big table with a new (created by MySQL-4.1) fulltext index.
• LAST_INSERT_ID() will now return 0 if the last INSERT didn't insert any rows.
• Fixed missing last character in function output (bug #447)
• Fixed a rare replication bug when a transaction spanned on two or more relay logs, and the slave was stopped while he was executing the part of the transaction which was in the second or further relay log. Then replication would resume at the beginning of the second or further relay log, which was wrong (it should resume at BEGIN, in the first relay log). (Bug #53)
• CONNECTION_ID() is now properly replicated (bug #177).
• The new PASSWORD() function in 4.1 is now properly replicated (bug #344).
• Fixed bug with doubly freed memory.
• Fixed crashing bug with temporary tables in UNION operations.
• Fixed a crashing bug in DERIVED TABLES when EXPLAIN is used on a DERIVED TABLES with a join.
• Fixed a crashing bug in DELETE with ORDER BY and LIMIT caused by non initiated array of reference pointers.
• Fixed a bug in USER() function caused by the error in the size of the allocated string.
• Fixed a crashing bug when attempting to create a table with GEOMETRY column type with a storage engine that does not support it.
• Fixed a crashing bug in UNION caused by the empty select list and a non-existent field being used in some of the sub-selects.
• Fixed a replication bug when the master is 3.23 and the slave 4.0: the slave lost the replicated temporary tables if FLUSH LOGS was issued on the master. (Bug #254)
• Fixed a security bug: A server compiled without SSL support still allowed connections by users that had the REQUIRE SSL option specified for their accounts.

User Comments

Something like the following with UNION would be cool - enabling union sets to be used as derived tables in other select statements. Not sure if this is already supported... can't seem to get it to work in 4.1 and there are no suggestions in the documentation to the contrary...

select * FROM ( (SELECT * FROM table1) AS t1 UNION ALL ( SELECT * FROM table2 ) AS t2 ) AS t3

Add your own comment.