[Dbix-class] Prefetch problem

[Jesse Sheidlower]

I'm working on something using prefetch, and though I thought
I was using it correctly, the SQL that it's generating is
dying with a syntax error, and since I can't even see the
error in the SQL, I figured I'd ask here, perhaps so that the
mere act of typing it will reveal what I'm doing wrong.

I have a table _lessons_ that had previously had columns
"group" and "performer" in it, but I normalized the database
by putting these columns into separate tables. So now part
of my _lesson_ schema looks like this:

__PACKAGE__->belongs_to( group => 'MyDB::Schema::Main::LessonGroup', 'group_id' );
__PACKAGE__->belongs_to( performer => 'MyDB::Schema::Main::Performer', 'performer_id' );
__PACKAGE__->resultset_attributes({ order_by => 'me.title', prefetch => [qw/performer group/ ]} );

When I run a query on this table from a Catalyst app like so:

  my $lesson = $c->model->find($id);

I get the following generated SQL according to DBIC_TRACE
(reformatted and with the id value filled in so I can paste it
into the MySQL commandline):

SELECT me.id, me.title, me.performer_id, me.number, me.capo, me.tuning, me.tab_id, me.audio_id, me.group_id, performer.id, performer.name, group.id, group.name 
FROM lesson me  
JOIN performer performer ON ( performer.id = me.performer_id )  
JOIN lesson_group group ON ( group.id = me.group_id ) 
WHERE ( ( me.id = 35 ) ) 
ORDER BY me.title;

This gives me the error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON ( group.id = me.group_id ) 
WHERE ( ( me.id = 35 ) ) 
ORDER BY me.title' at line 4

Since I can't even see the error in the SQL, let alone in the
DBIC stuff, I must assume that I'm doing something
horrendously wrong. Can someone please tell me what it is?

BTW dropping the prefetch from the attributes, so I just have 

__PACKAGE__->resultset_attributes({ order_by => 'me.title'});

works fine, except that I have two extra queries.

Thanks.

Jesse Sheidlower