The person I was working with pointed me to a little used link on their site, and I believe that all of us here should take a look at it when we have time.
It's a link to get the draft copy of the SQL-200x standard, which, according to him as he is on the ISO SQL committee, is fairly near ready to be released. He also says there will be no more fixes to it, other than spelling/typo fixes.
Here is the link: http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/23933607697154bf852566160054bcf7/f...
You want the first PDF file.
Most notably in it, I find that some things that MySQL thinks are valid, are NOT part of the SQL standard. ESP how it does the valid identifier tokens that I had trouble with.
MySQL claims valid tokens are: ([A-Za-z$_0-9]*[a-zA-Z$_][A-Za-z$_0-9]*) But the standard says: ([a-zA-Z][a-zA-Z_0-9]*)
There are a number of other things, so such a point where I have this question:
Should we explictly support MySQL where it diverges from the standard, or only support the SQL standard, with minimal MySQL extensions?
Robin Johnson wrote:
The person I was working with pointed me to a little used link on their site, and I believe that all of us here should take a look at it when we have time.
It's a link to get the draft copy of the SQL-200x standard, which, according to him as he is on the ISO SQL committee, is fairly near ready to be released. He also says there will be no more fixes to it, other than spelling/typo fixes.
Here is the link: http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/23933607697154bf852566160054bcf7/f...
You want the first PDF file.
Most notably in it, I find that some things that MySQL thinks are valid, are NOT part of the SQL standard. ESP how it does the valid identifier tokens that I had trouble with.
MySQL claims valid tokens are: ([A-Za-z$_0-9]*[a-zA-Z$_][A-Za-z$_0-9]*) But the standard says: ([a-zA-Z][a-zA-Z_0-9]*)
There are a number of other things, so such a point where I have this question:
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.
Marc
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.
-
Marc Delisle -
Robin Johnson