[Dbix-class] Creating custom resultset error and MySQL

[Matt S Trout]

On Tue, Jul 01, 2008 at 04:58:10AM -0700, Andreas Pronakis wrote:
> I've tried creating a custom resultset, following the instructions on DBIx::Class::Cookbook docs, but when calling the search() method on it it always returns empty, so I enabled DBIC_TRACE and the following sql was being executed:
> 
> 
> SELECT me.story_id, me.journalist_user_id, me.revision, me.story_category_id, me.story_status_id, me.distribution_flag, me.date, me.created_timestamp, me.last_edit_timestamp, me.true_timestamp, me.order_id, me.delete_flag, me.publish_revision, me.creator_user_id, me.published_timestamp, me.external_code, me.external_sequence, me.type_id, me.expiry_datetime, me.source_id, me.headline, me.body, me.introduction, me.abstract, me.author_notes, me.by_line FROM (SELECT se.story_id, se.journalist_user_id, se.revision, se.story_category_id, se.story_status_id, se.distribution_flag, se.date, se.created_timestamp, se.last_edit_timestamp, se.true_timestamp, se.order_id, se.delete_flag, se.publish_revision, se.creator_user_id, se.published_timestamp, se.external_code, se.external_sequence, se.type_id, se.expiry_datetime, se.source_id, sb.revision, sb.headline, sb.body, sb.introduction, sb.abstract, sb.author_notes, sb.by_line FROM story_edit AS se INNER JOIN
>  story_body AS sb ON se.story_id = sb.story_id WHERE se.story_id = ? AND se.delete_flag = 0)
> me: '2134660'
 
> By copying and pasting the SQL directly into the DB (MySQL 4.1) the following error is being returned - after substituting ? for 2134660:
> ERROR 1248 (42000): Every derived table must have its own alias

And if you look, the last bit of the query is ' me' which sets an alias.

Evidently MySQL is being retarded. Unless I'm wrong about that ' me' then
this is something you need to report against MySQL ...

(didn't we used to do AS for FROM clauses and then find things broke?)

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/servers/