The NIST Tests Were Not Standard SQL

NIST (the National Institute for Standards and Testing) used to put out tests which "verified" whether a DBMS complied with SQL standard requirements. They are obsolete, but some commentators still refer to them today. Some have even suggested that SQL-92 is preferable to the current standard (SQL-99 aka SQL:1999), because SQL-92 conformance can be "verified" by the NIST tests and SQL-99 cannot be. I have written this article to dispute those suggestions. I have tested the NIST tests, and concluded:

Any DBMS which passes the NIST tests is violating the SQL standard.

How I tested

I downloaded the tests from the NIST site (they are publicly available nowadays). I looked only at SQL tests in \nist\schema (which create schemas and tables), and the SQL tests in \nist\sql (which run statements against the schemas and tables). There are many text files in these sections, mostly ending with the extension .SQL. Each file contains one or more tests. Typically a test consists of an SQL statement which a DBMS must execute. For example, file cts5sch2.sql contains a CREATE TABLE statement that looks like this (I have removed columns which are not relevant to the illustration):

CREATE TABLE DATA_TYPE (DEC DECIMAL);

But the statement is illegal! According to the SQL standard, DEC is a reserved word. Therefore a DBMS can pass NIST, or it can conform to the SQL standard. It can't do both. I know that's only one example, but I'll supply many more.

I should make these caveats:

• I downloaded years ago. The tests I looked at were dated "NIST SQL Test Suite 6.0" at the time. The files are dated 1997. I believe there has been no update since then, but I may be wrong.
• I work from memory. I have some qualifications to claim expertise in the SQL standard, since I work for an SQL vendor and I co-wrote the book SQL-99 Complete, Really. However, my recollections or understandings of SQL-92 might be incorrect. Since I take the word of the ANSI and ISO committees that SQL-92 is cancelled and superseded, I stopped working with SQL-92 years ago.
• I mean no deprecation of NIST. The tests are solid and obviously the product of a lot of hard effort by competent people. I have only gathered these examples because I want to dispute the suggestion that SQL-92 is better than SQL-99 because DBMSs had NIST to verify compliance.

The errors that I found

These are some of the things that I found. This is only a sampling, since NIST usually makes the same sort of error multiple times. I have only included one representative sample for each error. Where I say or imply that a feature is "required by NIST" I mean that the syntax of a NIST test, or a comment that accompanies it, makes it clear that NIST wants a conformant DBMS to accept the statement and/or behave the specified way. I have not troubled to cite the individual .sql file names, but will respond to anyone who cannot find them..

• Allow the word DEC as an identifier. (See the example above.)
• Assume that the statement "CREATE SCHEMA X" implies "SET SCHEMA X".
• Require INSERT privileges when testing a UNIQUE constraint, but not REFERENCES privileges.
• Allow "GRANT USAGE on (domain name)" as if the key word DOMAIN is optional.
• Assume that CREATE CHARACTER SET (unqualified name) puts a character set in the user's current schema.
• Assume that CREATE COLLATION (unqualified name) puts a collation in the user's current schema.
• Require CAST('.7E1' AS NUMERIC(5,3)) as if it's valid syntax.
• Require CAST('15:00' DAY TO HOUR) as if it's valid syntax.
• Require that '123456789' (notice the quotes which make this a CHAR literal) be assignable to a NUMERIC.
• Require that 722B be accepted as a constraint name.
• Require that SET SESSION AUTHORIZATION 'ILLEGALUSER' be rejected.
• Require PRIMARY KEY NOT NULL as if it's legal syntax (notice the incorrect clause order).
• Require that COALESCE combine CHAR with DECIMAL values.
• State that casting 3.000 to DECIMAL should result in 3.000, when the correct answer is 3.
• Require that ORDER BY with columns not in the select list, or qualified column names, be accepted (this is okay nowadays but wasn't okay in SQL-92).
• Require updating of views that contain the word DISTINCT.
• Require that a 2-digit number be inserted into a column defined as DECIMAL (note that precision wasn't specified during column creation).
• Require that "check" options be checked even if no rows are updated.
• Assume that if you create a view on a CTS1 tabe, information_schema . views view_schema should be 'CTS1' (at least I guess that's what they were thinking).
• Fail to require USAGE privilege when dealing with a character set.
• Assume that an information_schema list of constraints won't contain entries for every NOT NULL constraint.
• Expect that information_schema table view_table_usage will have a column named constraint_name.
• Truncate various names, for example information_schema becomes info_schem.

Therefore, any vendor whose DBMS "passed" the NIST tests does not have the right to claim compliance with any version of the SQL standard at any level. It's good that NIST had tests, and it's good to hear that another group is planning tests for SQL:2003 conformance, but there was no golden age when standard-SQL conformance was verifiable by tests. The only certain measure is a comparison of the vendor's actions with the ANSI/ISO 9075 standard documents.

I'll quote Hal Berenson here from an article he posted on microsoft.public.sqlserver.server in October 2002: "Many a vendor who started working on SQL92 before the test suite was final had to do some re-engineering once they discovered how NIST was interpreting the standard." I should note, though, that Mr Berenson used this datum to come to an entirely different conclusion than mine.