[Dbix-class] Creating custom resultset error and MySQL

[Andreas Pronakis]

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

When I change the SQL on the manual run to include at the end '...) AS me;' (excluding the quotes) it returns the correct results.


The actual SQL that $new_source->name(\<<SQL) is:
(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)
SQL



Has anyone else come across this error?
Can anyone confirm if this is a bug with DBIx::Class::ResultSet?
Is there a fix for it?


I have also tried the following (which is basically the same only without the explicit JOIN, with exactly the same results.

(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, story_body AS sb WHERE se.story_id = ? AND se.delete_flag = 0 AND se.story_id = sb.story_id)


Even the simpler SQL:

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.*, sb.* FROM story_edit se INNER JOIN story_body sb ON se.story_id = sb.story_id WHERE se.story_id = 2134660 AND se.delete_flag = 0);

Deriving from the \<<SQL);
(SELECT se.*, sb.* FROM story_edit se INNER JOIN story_body sb ON se.story_id = sb.story_id WHERE se.story_id = ? AND se.delete_flag = 0)
SQL

Returns the same MySQL error, adding 'AS me' at the end solves the problem

Any suggestions/recommendations would be greatly appreciated.

Thank you in advance
Andreas