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 Section 12.2.8.1, “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 allow 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)

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

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

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)

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

It is no longer possible to issue FLUSH commands from within stored functions or triggers. See Section D.1, “Restrictions on Stored Routines and Triggers”, for details. (Bug#12280, Bug#12307)

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

Recursive triggers are detected and disallowed. Also, within a stored function or trigger, it is not allowable 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)

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

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

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 ENGINE INNODB STATUS now can display longer query strings. (Bug#7819)

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)

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 Disk is full condition occurred, ndbd exited without reporting this condition in the error log. (Bug#12716)

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 command caused loss of data stored prior to the issuing of the command. (Bug#12118)

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

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: Improved error messages related to file system issues. (Bug#11218)

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)

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)

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: 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)

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)

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

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

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

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

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

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)

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)

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)

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

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

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)

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

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

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

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

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

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

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

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)

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)

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)

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

CHECKSUM TABLE command 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)

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 -T option. (Bug#12235)

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)

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

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

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

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

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

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

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

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)

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

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

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

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

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)

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

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)

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

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)

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

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)

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

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

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

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

A view was allowed to depend on a function that referred to a temporary table. (Bug#10970)

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

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, allow conversion if one character set is a superset of the other. (Bug#10892)

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

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

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

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

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

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

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

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

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

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

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)

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)

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)