Home » Database » SQL_Parser » Bug #495

[2003-12-25 23:50 UTC] coil at strangebuzz dot com

Description:
------------
A query that is ok is not validated by SQL_Parser

Reproduce code:
---------------
$sql = 'SELECT * FROM user';

$parseRes = _SQLParser->parse($sql);

Expected result:
----------------
Note that when using USERS table it works !!!!!!!!!

???

I think that it worked on previous release.

Actual result:
--------------
Parse error: Unexpected clause on line 1
SELECT * FROM user
^ found: "user")

[2003-12-28 05:32 UTC] busterb at php dot net

Hmm, 'user' is marked in Dialect_ANSI.php as a reserved
word for SQL, and the parser currently expects that table
names are not reserved words.

Is there a standard way of handling this? I know that MS
SQL uses [] around conflicting table names. Perhaps we
could just allow it, or remove user from the list of
reserved words (I copied it from MSQL's list I think)

[2005-04-01 23:49 UTC] epte at ruffdogs dot com

The problem, I think, is that different dialects have different standard ways of handling this:

All of them that I know if are fine if delimiterized, though the delimiters differ between dialects (backtick for MySQL, double quote for Informix, [] for MSSQL, etc...). This implies that the delimiters used must be select based on dialect.

And of course the specific reserved words differ between them. I think this implies that the "Correct Behavior (TM)" is that the set of reserved words be selected based on dialect.

Now, the real question is, "Is the policy the same between them all?" All the SQL dialects I know (not that many unfortunately) will error if a reserved word (for that dialect) is used as a table/column name, but not if delimiterized. This is true at least in MySQL, MSSQL, and I think in Informix and pgSQL.

[2005-04-02 00:00 UTC] epte at ruffdogs dot com

Regarding this specific problem, I don't think "user" should be in the reserved array for the MySQL dialect. "user" isn't reserved in MySQL. See http://www.htmlite.com/mysql002a.php or some similar site.

Assuming that the above site is correct, then the reserved word array in Dialect_MySQL.php should have this instead:

'reserved'=array('add','all','alter','analyze','and','as','asc','asensitive','auto_increment','bdb','before','berkeley
db','between','bigint','binary','blob','both','by','call','cascade','case','change','char','character','check','collat
e','column','columns','condition','connection','constraint','continue','create','cross','current_date','current_time',
'current_timestamp','cursor','database','databases','day_hour','day_microsecond','day_minute','day_second','dec','deci
mal','declare','default','delayed','delete','desc','describe','deterministic','distinct','distinctrow','div','double',
'drop','else','elseif','enclosed','escaped','exists','exit','explain','false','fetch','fields','float','for','force','
foreign','found','frac_second','from','fulltext','grant','group','having','high_priority','hour_microsecond','hour_min
ute','hour_second','if','ignore','in','index','infile','inner','innodb','inout','insensitive','insert','int','integer'
,'interval','into','io_thread','is','iterate','join','key','keys','kill','leading','leave','left','like','limit','line
s','load','localtime','localtimestamp','lock','long','longblob','longtext','loop','low_priority','master_server_id','m
atch','mediumblob','mediumint','mediumtext','middleint','minute_microsecond','minute_second','mod','natural','not','no
_write_to_binlog','null','numeric','on','optimize','option','optionally','or','order','out','outer','outfile','precisi
on','primary','privileges','procedure','purge','read','real','references','regexp','rename','repeat','replace','requir
e','restrict','return','revoke','right','rlike','second_microsecond','select','sensitive','separator','set','show','sm
allint','some','soname','spatial','specific','sql','sqlexception','sqlstate','sqlwarning','sql_big_result','sql_calc_f
ound_rows','sql_small_result','sql_tsi_day','sql_tsi_frac_second','sql_tsi_hour','sql_tsi_minute','sql_tsi_month','sql
_tsi_quarter','sql_tsi_second','sql_tsi_week','sql_tsi_year','ssl','starting','straight_join','striped','table','table
s','terminated','then','timestampadd','timestampdiff','tinyblob','tinyint','tinytext','to','trailing','true','undo','u
nion','unique','unlock','unsigned','update','usage','use','user_resources','using','utc_date','utc_time','utc_timestam
p','values','varbinary','varchar','varcharacter','varying','when','where','while','with','write','xor','year_month','z
erofill'),

[2005-04-02 00:26 UTC] epte at ruffdogs dot com

That works for me, once I got my include path right :)

Except that 'reserved'=blah needs to be changed to 'reserved'=>blah in my version I included earlier. The complete patch follows:

? diff
Index: Dialect_MySQL.php
===================================================================
RCS file: /repository/pear/SQL_Parser/Dialect_MySQL.php,v
retrieving revision 1.4
diff -u -r1.4 Dialect_MySQL.php
--- Dialect_MySQL.php 3 Jul 2004 04:49:44 -0000 1.4
+++ Dialect_MySQL.php 2 Apr 2005 00:24:17 -0000
@@ -36,7 +36,7 @@

'functions'=>array('avg','count','max','min','sum','nextval','currval','concat','date_format'),

-'reserved'=>array('absolute','action','add','all','allocate','and','any','are','asc','ascending','assertion','at','authorization','begin','bit_length','both','cascade','cascaded','case','cast','catalog','char_length','character_length','check','close','coalesce','collate','collation','column','commit','connect','connection','constraint','constraints','continue','convert','corresponding','cross','current','current_date','current_time','current_timestamp','current_user','cursor','day','deallocate','declare','default','deferrable','deferred','desc','descending','describe','descriptor','diagnostics','disconnect','distinct','domain','else','end','end-exec','escape','except','exception','exec','execute','exists','external','extract','false','fetch','first','for','foreign','found','full','get','global','go','goto','grant','group','having','hour','identity','immediate','indicator','initially','inner','input','insensitive','intersect','isolation','join','key','language','last','leading','left','level','limit','local','lower','match','minute','module','month','names','national','natural','next','no','null','nullif','octet_length','of','only','open','option','or','order','outer','output','overlaps','pad','partial','position','precision','prepare','preserve','primary','prior','privileges','procedure','public','read','references','relative','restrict','revoke','right','rollback','rows','schema','scroll','second','section','session','session_user','size','some','space','sql','sqlcode','sqlerror','sqlstate','substring','system_user','table','temporary','then','timezone_hour','timezone_minute','to','trailing','transaction','translate','translation','trim','true','union','unique','unknown','upper','usage','user','using','value','values','varying','view','when','whenever','work','write','year','zone','eoc'),
+'reserved'=>array('add','all','alter','analyze','and','as','asc','asensitive','auto_increment','bdb','before','berkeleydb','between','bigint','binary','blob','both','by','call','cascade','case','change','char','character','check','collate','column','columns','condition','connection','constraint','continue','create','cross','current_date','current_time','current_timestamp','cursor','database','databases','day_hour','day_microsecond','day_minute','day_second','dec','decimal','declare','default','delayed','delete','desc','describe','deterministic','distinct','distinctrow','div','double','drop','else','elseif','enclosed','escaped','exists','exit','explain','false','fetch','fields','float','for','force','foreign','found','frac_second','from','fulltext','grant','group','having','high_priority','hour_microsecond','hour_minute','hour_second','if','ignore','in','index','infile','inner','innodb','inout','insensitive','insert','int','integer','interval','into','io_thread','is','iterate','join','key','keys','kill','leading','leave','left','like','limit','lines','load','localtime','localtimestamp','lock','long','longblob','longtext','loop','low_priority','master_server_id','match','mediumblob','mediumint','mediumtext','middleint','minute_microsecond','minute_second','mod','natural','not','no_write_to_binlog','null','numeric','on','optimize','option','optionally','or','order','out','outer','outfile','precision','primary','privileges','procedure','purge','read','real','references','regexp','rename','repeat','replace','require','restrict','return','revoke','right','rlike','second_microsecond','select','sensitive','separator','set','show','smallint','some','soname','spatial','specific','sql','sqlexception','sqlstate','sqlwarning','sql_big_result','sql_calc_found_rows','sql_small_result','sql_tsi_day','sql_tsi_frac_second','sql_tsi_hour','sql_tsi_minute','sql_tsi_month','sql_tsi_quarter','sql_tsi_second','sql_tsi_week','sql_tsi_year','ssl','starting','straight_join','striped','table','tables','terminated','then','timestampadd','timestampdiff','tinyblob','tinyint','tinytext','to','trailing','true','undo','union','unique','unlock','unsigned','update','usage','use','user_resources','using','utc_date','utc_time','utc_timestamp','values','varbinary','varchar','varcharacter','varying','when','where','while','with','write','xor','year_month','zerofill'),

'synonyms'=>array('decimal'=>'numeric','dec'=>'numeric','numeric'=>'numeric','float'=>'float','real'=>'real','double'=>'real','int'=>'int','integer'=>'int','interval'=>'interval','smallint'=>'smallint','timestamp'=>'timestamp','bool'=>'bool','boolean'=>'bool','set'=>'set','enum'=>'enum','text'=>'text','char'=>'char','character'=>'char','varchar'=>'varchar','ascending'=>'asc','asc'=>'asc','descending'=>'desc','desc'=>'desc','date'=>'date','time'=>'time'),