Historical - SQL changes for MySQL

This is a post from circa 2008 that describes many of the changes we made at Google to the parser. It was first shared at code.google.com which has since shutdown. It describes work done by my team at Google.

Introduction

This describes changes to SQL parsed by MySQL.

New tokens:

• CLIENT_STATISTICS
• TABLE_STATISTICS
• USER_STATISTICS
• INDEX_STATISTICS
• IF_IDLE
• MAKE
• MAPPED
• MAX_QUERIES_PER_MINUTE
• NEW_PASSWORD
• ROLE
• SLOW
• TCMALLOC
• IEEE754_TO_STRING
• LAST_VALUE
• ORDERED_CHECKSUM
• UNORDERED_CHECKSUM

New SQL functions

New SQL functions include:

• ORDERED_CHECKSUM - This is a SQL aggregate function that accepts one or more arguments. It returns the hash of its input arguments per group. The function is order dependent. The output of this from the first row in a group is used as the seed for the hash on the next row.
• UNORDERED_CHECKSUM - This is a SQL aggregate function that accepts one or more arguments. It returns the hash of its input arguments per group. The function is order independent. The result from each row in a group is combined by XOR.
• LAST_VALUE - This is a SQL aggregate function. It returns the last value read per group. Thus this depends on the input order to aggregation. See OnlineDataDrift for a use case (TODO - repost that)
• HASH - This is a SQL function. It returns the hash of its input argument. It is not an aggregate function and produces one value per row.
• IEEE754_TO_STRING - Converts a float or double decimal value with type string. This generates 17 digits of precision so that conversion of the string back to a double does not lose precision (the original double should be equal to the final double for all but a few special cases.
• NEW_PASSWORD - Computes the new-style password hash regardless of the value for the my.cnf parameter old_passwords.

An example for UNORDERED_CHECKSUM is:

select unordered_checksum(c1) from foo group by c2;
select unordered_checksum(c1, c2) from foo group by c3;

An example for ORDERED_CHECKSUM is:

select ordered_checksum(c1) from foo group by c2;
select ordered_checksum(c1, c2) from foo group by c3;

An example for HASH is:

select hash(column) from foo

New options for existing statements

KILL <id> IF_IDLE can be used to kill a connection but only if it is idle.

MAX_QUERIES_PER_MINUTE can be used in place of MAX_QUERIES_PER_HOUR. This version of MySQL enforces query limits per minute rather than per hour and the value stored in the MySQL privilege table is the rate per minute.

CREATE MAPPED USER '' ROLE 'bar' and DROP MAPPED USER 'foo' support mapped users. See MysqlRoles for more details (TODO - repost Roles)

SHOW PROCESSLIST WITH ROLES and SHOW USER_STATISTICS WITH ROLES use the role name rather than the user name in results.

New statements

See the monitoring post for more details:

• SHOW USER_STATISTICS
• SHOW TABLE_STATISTICS
• SHOW INDEX_STATISTICS
• SHOW CLIENT_STATISTICS
• FLUSH TABLE_STATISTICS
• FLUSH INDEX_STATISTICS
• FLUSH CLIENT_STATISTICS*

See the post on delayed users for more details:

• MAKE USER 'foo' DELAYED 1000
• MAKE CLIENT '10.0.0.1' DELAYED 2000
• SHOW DELAYED USER
• SHOW DELAYED CLIENT
• SHOW TCMALLOC STATUS displays the status of tcmalloc when MySQL hash been linked with it and compiled with -DUSE_TCMALLOC. This displays the output from MallocExtension::GetStats.
• CAST supports cast to DOUBLE
• SHOW INNODB LOCKS provides more details on InnoDB lock holders and waiters
• FLUSH SLOW QUERY LOGS rotates the slow query log.
• MAKE MASTER REVOKE SESSION disconnects all sessions but the current one and prevents future connections from all users unless they have SUPER, REPL_CLIENT or REPL_SLAVE privileges. MAKE MASTER GRANT SESSION undoes this.