Incompatible Change: Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The changes include elimination of redundant output columns for NATURAL joins and joins specified with a USING clause and proper ordering of output columns. The precedence of the comma operator also now is lower compared to JOIN.

In addition, a Duplicate column name error no longer occurs when selecting from a view defined as SELECT * from a join that uses a USING clause on tables that have a common column name.

These changes make MySQL more compliant with standard SQL. However, they can result in different output columns for some joins. Also, some queries that appeared to work correctly prior to 5.0.12 must be rewritten to comply with the standard. For details about the scope of the changes and examples that show what query rewrites are necessary, see JOIN Syntax. (Bug #6495, Bug #6136, Bug #10972, Bug #9978, Bug #10428, Bug #10646, Bug #6276, Bug #6489, Bug #6558, Bug #9067, Bug #4789, Bug #12065, Bug #13551)

MySQL Cluster: The parsing of the CLUSTERLOG command by ndb_mgm was corrected to permit multiple items. (Bug #12833)

Replication: Interleaved execution of stored procedures and functions could be written to the binary log incorrectly, causing replication slaves to get out of sync. (Bug #12335)

Replication: Calls to stored procedures were written to the binary log even within transactions that were rolled back, causing them to be executed on replication slaves. (Bug #12334)

INFORMATION_SCHEMA objects are now reported as a SYSTEM VIEW table type. (Bug #11711)

Using DESCRIBE on a view after renaming a column in one of the view's base tables caused the server to crash. (Bug #12533)

A query of the form SHOW TABLE STATUS FROM db_name WHERE name IN (select_query) would crash the server. (Bug #12636)

SHOW ENGINE INNODB STATUS now can display longer query strings. (Bug #7819)

Trying to drop the default keycache by setting @@global.key_buffer_size to zero now returns a warning that the default keycache cannot be dropped. (Bug #10473)

SHOW TABLE STATUS FROM INFORMATION_SCHEMA now sorts output by table name the same as it does for other databases. (Bug #12315)

If a thread (connection) has tables locked, the query cache is switched off for that thread. This prevents invalid results where the locking thread inserts values between a second thread connecting and selecting from the table. (Bug #12385)

Recursive triggers are detected and not permitted. Also, within a stored function or trigger, it is not permissible to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. (Bug #11896, Bug #12644)

Added the SLEEP() function, which pauses for the number of seconds given by its argument. (Bug #6760)

SHOW TABLE STATUS for a view now shows VIEW in uppercase, consistent with SHOW TABLES and INFORMATION_SCHEMA. (Bug #5501)

The stability of cursors when used with InnoDB tables was greatly improved. (Bug #11309, Bug #11832, Bug #12243)

SHOW OPEN TABLES now supports FROM and LIKE clauses. (Bug #12183)

It is no longer possible to issue FLUSH statements from within stored functions or triggers. See Restrictions on Stored Programs, for details. (Bug #12280, Bug #12307)

MySQL Cluster: Improved error messages related to file system issues. (Bug #11218)

MySQL Cluster: When it could not copy a fragment, ndbd exited without printing a message about the condition to the error log. Now the message is written. (Bug #12900)

MySQL Cluster: When a schema was detected to be corrupt, ndb neglected to close it, resulting in a file already open error if the schema was opened again later. written. (Bug #12027)

MySQL Cluster: The wrong error message was displayed when the cluster management server port was closed while a mysqld process was trying to connect. (Bug #10950)

MySQL Cluster: Invalid values in config.ini caused ndb_mgmd to crash. (Bug #12043)

MySQL Cluster: Cluster failed to take character set data into account when recomputing hashes (and thus could not locate records for updating or deletion) following a configuration change and node restart. (Bug #12220)

MySQL Cluster: An ALTER TABLE statement caused loss of data stored prior to the issuing of the command. (Bug #12118)

MySQL Cluster: When a Disk is full condition occurred, ndbd exited without reporting this condition in the error log. (Bug #12716)

Replication: If a DROP DATABASE fails on a master server due to the presence of a nondatabase file in the database directory, the master have the database tables deleted, but not the slaves. To deal with failed database drops, we now write DROP TABLE statements to the binary log for the tables so that they are dropped on slaves. (Bug #4680)

Replication: Slave I/O threads were considered to be in the running state when launched (rather than after successfully connecting to the master server), resulting in incorrect SHOW SLAVE STATUS output. (Bug #10780)

Replication: Some statements executed on a master server caused the SQL thread on a slave to run out of memory. (Bug #12532)

Replication: Trigger and stored procedure execution could break replication. (Bug #12482)

Replication: NOW(), CURRENT_TIME() and values generated by timestamp columns are now constant for the duration of a stored function or trigger. This prevents the breaking of statement-based replication. (Bug #12481)

Columns defined as TINYINT(1) were redefined as TINYINT(4) when incorporated into a VIEW. (Bug #11335)

Issuing FLUSH INSTANCES followed by STOP INSTANCE caused instance manager to crash. (Bug #10957)

A SELECT DISTINCT query with a constant value for one of the columns would return only a single row. (Bug #12625)

Views with multiple UNION and UNION ALL produced incorrect results. (Bug #10624)

Duplicate instructions in stored procedures resulted in incorrect execution when the optimizer optimized the duplicate code away. (Bug #12168)

DATE_ADD() and DATE_SUB() were converting invalid dates to NULL in TRADITIONAL SQL mode rather than rejecting them with an error. (Bug #10627)

Renamed the rest() macro in my_list.h to list_rest() to avoid name clashes with user code. (Bug #12327)

The ROW() contructor returned an incorrect result when comparison involved NULL values. (Bug #12509)

Some subqueries of the form SELECT ... WHERE ROW(...) IN (subquery) were being handled incorrectly. (Bug #11867)

Improper use of loose index scan in InnoDB sometimes caused incorrect query results. (Bug #12672)

The only valid values for the PACK_KEYS table option are 0 and 1, but other values were being accepted. (Bug #10056)

A UNION query with FULLTEXT could cause server crash. (Bug #11869)

ISO-8601 formatted dates were not being parsed correctly. (Bug #7308)

The CHECKSUM TABLE statement returned incorrect results for tables with deleted rows. After upgrading, users who used stored checksum information to detect table changes should rebuild their checksum data. (Bug #12296)

SHOW TABLE STATUS sometimes reported a Row_format value of Dynamic for MEMORY tables, though such tables always have a format of Fixed. (Bug #3094)

The NUMERIC_SCALE column of the INFORMATION_SCHEMA.COLUMNS table should be returned as 0 for integer columns. It was being returned as NULL. (Bug #12301)

Mishandling of comparison for rows containing NULL values against rows produced by an IN subquery could cause a server crash. (Bug #12392)

Built-in commands for the mysql client, such as delimiter and \d, are now always parsed within files that are read using the \. and source commands. (Bug #11523)

A race condition between server threads could cause a crash if one thread deleted a stored routine while another thread was executing a stored routine. (Bug #12228)

STRCMP() was not handled correctly in views. (Bug #12489)

Concatenating USER() or DATABASE() with a column produced invalid results. (Bug #12351)

A trigger that included a SELECT statement could cause a server crash. (Bug #11587)

DELETE or UPDATE for an indexed MyISAM table could fail. This was due to a change in end-space comparison behavior from 4.0 to 4.1. (Bug #12565)

MEMORY tables using B-Tree index on 64-bit platforms could produce false table is full errors. (Bug #12460)

On Windows when the --innodb_buffer_pool_awe_mem_mb option has been given, the server detects whether AWE support is available and has been compiled into the server, and displays an appropriate error message if not. (Bug #6581)

ALTER TABLE db_name.t RENAME t did not move the table to default database unless the new name was qualified with the database name. (Bug #11493)

The COLUMN_DEFAULT column of the INFORMATION_SCHEMA.COLUMNS table should be returned as NULL if a column has no default value. An empty string was being returned if the column was defined as NOT NULL. (Bug #12518)

Pathame values for options such as --basedir or --datadir didn't work on Japanese Windows machines for directory names containing multi-byte characters having a second byte of 0x5C (“\”). (Bug #5439)

Inserting NULL into a GEOMETRY column for a table that has a trigger could result in a server crash if the table was subsequently dropped. (Bug #12281)

myisampack failed to delete .TMD temporary files when run with the -T option. (Bug #12235)

Stored procedures with particularly long loops could crash the server due to a memory leak. (Bug #11247, Bug #12297)

INSERT ... SELECT ... ON DUPLICATE KEY UPDATE could fail with an erroneous “Column 'col_name' specified twice” error. (Bug #10109)

A data type of CHAR BINARY was not recognized as valid for stored routine parameters. (Bug #9048)

An incorrect conversion from double to ulonglong caused indexes not to be used for BDB tables on HP-UX. (Bug #10802)

When restoring INFORMATION_SCHEMA as the default database after failing to execute a stored procedure in an inaccessible database, the server returned a spurious ERROR 42000: Unknown database 'information_schema' message. (Bug #12318)

A server crash could result from an update of a view defined as a join, even though the update updated only a single table. (Bug #12569)

TRUNCATE TABLE did not work with TEMPORARY InnoDB tables. (Bug #11816)

An optimizer estimate of zero rows for a nonempty InnoDB table used in a left or right join could cause incomplete rollback for the table. (Bug #12779)

A failure to obtain a lock for a LOCK IN SHARE MODE query could result in a server crash. (Bug #12082)

The CREATE_OPTIONS column of INFORMATION_SCHEMA.TABLES showed incorrect options for tables in INFORMATION_SCHEMA. (Bug #12397)

It was not possible to create a stored function with a spatial return value data type. (Bug #10499)

FLUSH TABLES WITH READ LOCK combined with LOCK TABLE .. WRITE caused deadlock. (Bug #9459)

SHOW TABLES FROM returned the wrong error message if the schema specified did not exist. (Bug #12591)

Using a stored procedure that referenced tables in the INFORMATION_SCHEMA database would return an empty result set. (Bug #10055, Bug #12278)

XA permitted two active transactions to be started with the same XID. (Bug #12162)

Users created using an IP address or other alias rather than a host name listed in /etc/hosts could not set their own passwords. (Bug #12302)

SET GLOBAL TRANSACTION ISOLATION LEVEL was not working. (Bug #11207)

The mysql_info() C API function could return incorrect data when executed as part of a multi-statement that included a mix of statements that do and do not return information. (Bug #11688)

Comparison of InnoDB multi-part primary keys that include VARCHAR columns can result in incorrect results. (Bug #12340)

Column names in subqueries must be unique, but were not being checked for uniqueness. (Bug #11864)

Added portability check for Intel compiler to address a problem compiling InnoDB code. (Bug #11510)

For PKG installs on Mac OS X, the preinstallation and postinstallation scripts were being run only for new installations and not for upgrade installations, resulting in an incomplete installation process. (Bug #11380)

Creating a view that included the TIMESTAMPDIFF() function resulted in a invalid view. (Bug #12298)

mysql_fix_privilege_tables.sql was missing a comma, causing a syntax error when executed. (Bug #12705)

Prepared statement parameters could cause errors in the binary log if the character set was cp932. (Bug #11338)

Joins on VARCHAR columns of different lengths could produce incorrect results. (Bug #11398)

It was permitted for a view to depend on a function that referred to a temporary table. (Bug #10970)

Invocations of the SLEEP() function incorrectly could be optimized away for statements in which it occurs. Statements containing SLEEP() incorrectly could be stored in the query cache. (Bug #12689)

SELECT ... INTO var_name within a trigger could cause a server crash. (Bug #11973)

NULL column definitions were read incorrectly for inner tables of nested outer joins. (Bug #12154)

GROUP_CONCAT ignored the DISTINCT modifier when used in a query joining multiple tables where one of the tables had a single row. (Bug #12095)

Using cursors and nested queries for the same table, corrupted results were returned for the outer query. (Bug #11909)

Selecting from a view defined as a join over many tables could result in a server crash due to miscalculation of the number of conditions in the WHERE clause. (Bug #12470)

Selecting from a view after INSERT statements for the view's underlying table yielded different results than subsequent selects. (Bug #12382)

User variables were not automatically cast for comparisons, causing queries to fail if the column and connection character sets differed. Now when mixing strings with different character sets but the same coercibility, permit conversion if one character set is a superset of the other. (Bug #10892)

A query using a LEFT JOIN, an IN subquery on the outer table, and an ORDER BY clause, caused the server to crash when cursors were enabled. (Bug #11901)