[Dbix-class] MSSQL/Page/Prefetch problem

Peter Rabbitson rabbit+dbic at rabbit.us
Sat May 30 05:52:58 GMT 2009


Alan Humphrey wrote:
>> -----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.
> 

And what did a previous version generate as far as SQL goes (the one that
used to work)?



More information about the DBIx-Class mailing list