[Dbix-class] MSSQL/Page/Prefetch problem

[Alan Humphrey]

> -----Original Message-----
> From: Marc Mims [mailto:marc at questright.com]
> Sent: Friday, May 29, 2009 4:16 PM
> To: DBIx::Class user and developer list
> Cc: DBIx:
> Subject: Re: [Dbix-class] MSSQL/Page/Prefetch problem
> 
> * Peter Rabbitson <rabbit+dbic at rabbit.us> [090529 15:07]:
> > Alan Humphrey wrote:
> > > Hi all -
> > >
> > > I just upgraded to version 0.08103 and have encountered a problem
> using page
> > > in conjunction with prefetch against a MSSQL database.
> > >
> > > Given:
> > >
> > >     my $result = $schema->resultset('Surveys')->search(
> > >         {},
> > >         {   page     => 1,
> > >             rows     => 25,
> > >             order_by => ['survey_site_id', 'survey_date'],
> > >             prefetch => 'site'
> > >         }
> > >     )->first();
> > >
> > > Executing the code against MSSQL generates this error:
> > >
> > > DBI Exception: DBD::Sybase::st execute failed: Server message
> number=8156
> > > severity=16 state=1 line=1 server=SQLA4 text=The column 'comments'
> was
> > > specified multiple times for 'foo'. [for Statement "  SELECT * FROM
> > >   (
> > >     SELECT TOP 25 * FROM
> > >     (
> > >         SELECT TOP 25  me.id, me.survey_site_id, me.start_time,
> me.end_time,
> > > me.zero_ref_point, me.survey_date, me.weather, me.precipitation,
> > > me.sea_state, me.tide_movement, me.visibility_distance,
> > > me.poor_visibility_reason, me.poor_visibility_reason_other,
> me.equipment,
> > > me.binocular_magnification, me.scope_magnification,
> me.walker_count,
> > > me.dog_count, me.power_boat_count, me.unpowered_boat_count,
> > > me.other_activities_name, me.other_activities_count, me.comments,
> > > me.is_complete, me.raptor1_bird_id, me.raptor1_count,
> me.raptor1_affect,
> > > me.raptor2_bird_id, me.raptor2_count, me.raptor2_affect,
> me.raptor3_bird_id,
> > > me.raptor3_count, me.raptor3_affect, site.site_code,
> site.site_name,
> > > site.position, site.elevation, site.comments FROM surveys me  JOIN
> > > survey_sites site ON site.site_code = me.survey_site_id  ORDER BY
> > > survey_site_id ASC, survey_date ASC
> > >     ) AS foo
> > >     ORDER BY survey_site_id DESC, survey_date DESC
> > >   ) AS bar
> > >    ORDER BY survey_site_id, survey_date
> > >
> > >
> > > This used to work.  Any ideas?  Thanks!
> > >
> > > - Alan
> > >
> >
> > Can you debug the SQL for us, and figure out why it complains about
> > a column duplicate when there cleare isn't one?
> 
> There are indeed 2: me.comments and sites.comments. In the outer
> selects, they conflict.
> 
> 	-Marc

Right.  I don't understand why the outer selects are there.  The inner
select works fine on its own.

- Alan