Bug 46 : table alias "AS" causes SQL error...
Priority 
High
Reported Version 
 
Logged By 
Rob
Status 
Fixed
Fixed Version 
0.9.8
Assigned To 
 
Product 
Ebean - core
Duplicate Of 
 
Created 
19/10/2008
Updated 
19/10/2008
Type 
Bug
 
Attachments 
No attachments

Bug: Using mysql keyword as

I just had an issue with the following generated sql:
select e.id, e.startdate, e.comment, e.minpeople, e.canceled, e.reason, e.enddate, e.deleted, e.cdate, e.archived
, ce.id, ce.name
, as.id, as.login
, le.id, le.name, le.address, le.use_coordinates, le.route, le.description, le.longitude, le.latitude
, pp.id, pp.doesparticipate, pp.externalname
, pus.id, pus.login
from event e
join eventcategory ce ON e.category_id = ce.id
join systemuser as ON e.creator_id = as.id
left outer join eventlocation le ON e.location_id = le.id
left outer join participation pp ON e.id = pp.event_id
left outer join systemuser pus ON pp.user_id = pus.id
where e.deleted=0
order by e.startdate ASC;

i think the problem is the alias from systemuser named 'as', which is a mysql keyword.

 
Rob 19 Oct 09:28
From forum...

http://www.avaje.org/topic-74.html

I have had a look at this... not resolved yet. Will get back to it after putting out 0.9.8.

Rob 20 Oct 11:41
Fixed in 0.9.8

Ok, fixed this. In the process refactored how the SQL alias' work... removed some of the complexity that was there.

In terms of this bug, I have a set of all the SQL keywords including "AS" ... that's 339 keywords currently. All derived table alias are checked to make sure they are not SQL keywords, if they are then they get an underscore appended.

So "as" would be "as_"

Rob 21 Oct 08:01
Also note: you can add extra reserved words...

In the hopefully unlikely case where you need to add a reserved word you can specify extra reserved words in the properties file.

In the properties file:

ebean.sqlreservedwords=cb,banana,apple

Will add 3 extra reserved words "CB", "BANANA" and "APPLE". Also note that reserved word checking is NOT case sensitive.

woResponse

Upload a file