[Phpmyadmin-devel] SQL Standards

[Robin Johnson]

>From Marc's previous message about the SQL Validator
> That's good news.  Did you check what the Validator says with valid
> MySQL statements that are not exactly ANSI-compliant?
It fails them on validity. However, a great many general (SELECT, INSERT,
UPDATE,DELETE) MySQL statements are perfectly valid ANSI SQL. 'CREATE
TABLE' on the other hand fails more often than not, as do a few other
generic things. Most notably amongst them is the MySQL '#' style comments.
(Which is part of the reason I requested we change to use /* */ style
comments instead, or even the core ANSI '-- ').

On Fri, 12 Jul 2002, Marc Delisle wrote:
> >Should we explictly support MySQL where it diverges from the standard, or
> >only support the SQL standard, with minimal MySQL extensions?
> Yes, I think we should explicitely support MySQL, as phpMyAdmin is
> strictly MySQL-oriented,
> and we can expect our users to use whatever syntax MySQL offers.
> Of course we can only hope that MySQL converges towards the official
> standard.
Ok, for now, here is what I will do then.

There are going to be two versions of the query tokenizer. The existing
one is MySQL compliant, and my new one will require strict adherence to
SQL identifer token rules (as I indictacted about the identifier names
before).

The ANSI SQL tokenizer will be considerably faster than the totally MySQL
compliant one, as there are a large number of strange cases in the MySQL
system that require a lot of work to deal with.

A fair number of the strange cases in MySQL are documented to behave one
way, but then do NOT behave the same way within MySQL itself, so believe
using them is looking for trouble anyway.

The most irksome rules amongst the totally MySQL compliant ones are:
Invalid hex numbers (eg. 0xffg) become identifiers (this is REALLY buggy
in MySQL vs the documentation0.
'.' is a VALID character in the name of an SQL variable
Identifers can start with a digit (leads to strange cases like 34r10,
where you bumped r instead of e, leading to it being an identifier instead
of a number).

My base parser is now publically accesible on:
http://robbat2.matchbox.research.techbc.ca/sqlparse.php

I'm tweaking the table/database/column/alias code now.

-- 
Robin Hugh Johnson
E-Mail     : robbat2 at orbis-terrarum.net
Home Page  : http://www.orbis-terrarum.net/?l=people.robbat2
ICQ#       : 30269588 or 41961639