« Open Source's Role in Transforming Society | Main | InnoDB Multiple Tablespaces and Compressed Tables »

April 15, 2004

MySQL and the ANSI SQL Standards

First MySQL 2004 session of the morning is Peter Gulutzan, going over MySQL and the compliance with ANSI SQL Standards.

Last year Peter preannounced some of the things that were going to be done, this year announcing some of them again, now they are completed.

(Peter has some mannerisms like Eugene Levy, think Waiting for Guffman).

Peter has been studying the SQL standards for years, was co-author of SQL-99 Complete, Really. Makes a good point, MySQL having a standards person on the staff demonstrates MySQL's commitment to getting standardized. ISO and ANSI publish the same spec. Microsoft and Oracle prefer to say seequel, but the standards docs always prounounce it Ess Queue Ell.

Are many (half-dozen) versions of the SQL standard, many of which claim can be "the" standard. SQL-92, SQL-99, SQL:2003 are all used, SQL:2003 became the official in December 2003. SQL:2003 is upward compatible with SQL-99, which is upward compatible with SQL-92. Each standard version makes clear distinction between mandatory and optional. Core compliance is doing all the mandatory. DB2 and Oracle claim core SQL-99, MS-SQL claims entry-level SQL-92. MySQL "continues to work toward" the ANSI SQL standard.

More info on db compliance.

How to check compliance of MySQL? Run NIST test, which is old and only looks at SQL-92. MySQL AB took SQL:2003 feature list and created a list of functions, added scripts for checking to the crashme script.

MySQL is compliant for data types specificed in standards except for a few instances. INTERVAL (partial), which is optional. Some difference in the date and time arithmatic. MySQL has a workaround for CLOB/NCLOB, NCHAR and NCHAR VARYING, converts them to CHAR and VARCHAR, but these aren't core items. MySQL, along with some other databases trim trailing spaces. Boolean is not supported, but not part of the core SQL. UDT is is non-core.

Character sets and collation features are well beyond the core spec, and far beyond most other DBMS.

SQL standard says that "table1" should not be equal to Table1 (ie "select column1 from Table1"). MySQL doesn't respect this

MySQL supports all operators in the core.

Subqueries are requred for core SQL compliance, important that it is available in 4.1.

All core joins are supported and some additional. Only join not supported is FULL JOIN, which is optional.

All constraints supported except CHECK, which is core (the only core feature that MySQL doesn't support). FOREIGN KEY has support, but have to be careful to create using index and specific column in primary key table. It works, but the syntax to create isn't exactly what the standard specifies.

Transaction control is supported, but want to keep MyISAM tables for speed in the cases where transactions arent necessary.

Set (or aggregate) functions work as specified. There is one case where a warning isn't delivered. MySQL is good at errors and OK, not so good at warnings. SQL standard requires warning very infrequently.

MySQL still needs to work on these issues to accomplish core compliance:
- schemas (container for tables) - very close
- select * from INFORMATION_SCHEMA.tables
- views
- grant and revoke - filled intent of standard, not necessarily exact - the security needs of MySQL demand some changes, but the core
- CHECK clause
- SQLSTATE - done in 4.1

The great MySQL Caveat - things we won't do
1. Change C-like syntax additiona
2. Add something that makes MySQL slower in default install
3. Change case sensitivity for identifiers
4. Add errors for illegal assignments

Features that are extensions of core SQL (all non-core SQL:2003):
- IDENTITY
- FLOOR, CEILING
- CASE
- CAST
- PROCEDURE

Magor missing non-core features:
- triggers
- UDTs
- XML

Final remarks:
- Much improved since 3.23
- Supports all but 4 Major non-Core Features
- Cathing up with upper classmates

Peter's script.

Posted by mike at April 15, 2004 10:11 AM

Hard Drive Recovery Group offers hard disk data recovery services for RAID, laptops and servers. Complete clean room and hard drive repair service.

Trackback Pings

TrackBack URL for this entry:
http://mike.kruckenberg.com/mt/mt-tb.cgi/615

Comments

can u please send the recent SQL standards

Posted by: pratima at July 15, 2004 11:47 PM