> >
> > The errors all come from bad SQL being generated. For example this
> code (from a Catalyst app)
> >
> > $items = $c->model('BirdWebDB::SurveyorsSurveySites')->search(
> > {survey_site_id => $parms->{id} },
> > {order_by => [{'-'. $sort_order => $sort_column},
> > {'-asc' => 'name'}],
> > prefetch => ['surveyor', 'site'],
> > page => $page,
> > rows => $limit}
> > );
> >
> > Results in this error/sql under version 0.08115:
> >
> > [error] DBI Exception: DBD::Sybase::st execute failed: Server message
> number=8156 severity=16 state=1 line=1 server=SQLB29 text=The column
> 'id' was specified multiple times for 'me'.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "surveyor.id" could not be bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "surveyor.name" could not be bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "surveyor.email" could not be bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "surveyor.phone" could not be bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "surveyor.login" could not be bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "surveyor.password" could not be bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "surveyor.is_active" could not be bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "surveyor.is_verifier" could not be
> bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "surveyor.arm_length" could not be
> bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "surveyor.eye_height" could not be
> bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "surveyor.year_joined" could not be
> bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "site.id" could not be bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "site.site_code" could not be bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "site.site_name" could not be bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "site.county" could not be bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "site.location" could not be bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "site.position" could not be bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "site.elevation" could not be bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "site.comments" could not be bound.
> > Server message number=4104 severity=16 state=1 line=1 server=SQLB29
> text=The multi-part identifier "site.active" could not be bound.
> > Server message number=8156 severity=16 state=1 line=1 server=SQLB29
> text=The column 'id' was specified multiple times for 'orig_query'.
> > Server message number=8156 severity=16 state=1 line=1 server=SQLB29
> text=The column 'id' was specified multiple times for 'rno_subq'.
> > [for Statement " SELECT * FROM ( SELECT orig_query.*, ROW_NUMBER()
> OVER( ORDER BY (SELECT(1)) ) AS rno__row__index FROM (SELECT me.id,
> me.surveyor_id, me.survey_site_id, me.year, surveyor.id, surveyor.name,
> surveyor.email, surveyor.phone, surveyor.login, surveyor.password,
> surveyor.is_active, surveyor.is_verifier, surveyor.arm_length,
> surveyor.eye_height, surveyor.year_joined, site.id, site.site_code,
> site.site_name, site.county, site.location, site.position,
> site.elevation, site.comments, site.active FROM (SELECT TOP 100 PERCENT
> me.id, me.surveyor_id, me.survey_site_id, me.year, surveyor.id,
> surveyor.name, surveyor.email, surveyor.phone, surveyor.login,
> surveyor.password, surveyor.is_active, surveyor.is_verifier,
> surveyor.arm_length, surveyor.eye_height, surveyor.year_joined,
> site.id, site.site_code, site.site_name, site.county, site.location,
> site.position, site.elevation, site.comments, site.active FROM
> surveyors_survey_sites me JOIN surveyors surveyor ON surveyor.id =
> me.surveyor_id JOIN survey_sites site ON site.id = me.survey_site_id
> WHERE ( survey_site_id = '2' ) ORDER BY year DESC, name ASC) me)
> orig_query ) rno_subq WHERE rno__row__index BETWEEN 1 AND 5 "] at
> /usr/local/share/perl/5.10.0/DBIx/Class/Schema.pm line 1026
> >
> > Under the mssql_limit_regression branch we get this (which works):
> >
> > SELECT me.id, me.surveyor_id, me.survey_site_id, me.year,
> surveyor.id, surveyor.name, surveyor.email, surveyor.phone,
> surveyor.login, surveyor.password, surveyor.is_active,
> surveyor.is_verifier, surveyor.arm_length, surveyor.eye_height,
> surveyor.year_joined, site.id, site.site_code, site.site_name,
> site.county, site.location, site.position, site.elevation,
> site.comments, site.active FROM ( SELECT * FROM ( SELECT me.*,
> ROW_NUMBER() OVER( ORDER BY (SELECT(1)) ) AS rno__row__index FROM
> (SELECT me.id, me.surveyor_id, me.survey_site_id, me.year FROM (SELECT
> TOP 4294967296 me.id, me.surveyor_id, me.survey_site_id, me.year FROM
> surveyors_survey_sites me JOIN surveyors surveyor ON surveyor.id =
> me.surveyor_id JOIN survey_sites site ON site.id = me.survey_site_id
> WHERE ( survey_site_id = '2' ) ORDER BY year DESC, name ASC) me) me )
> rno_subq WHERE rno__row__index BETWEEN 1 AND 5 ) me JOIN surveyors
> surveyor ON surveyor.id = me.surveyor_id JOIN survey_sites site ON
> site.id = me.survey
> _site_id WHERE ( survey_site_id = '2' ) ORDER BY year DESC, name ASC:
> >
> > I'll be patient.
>
> No need to be patient. Checkout the trunk and report your findings
> (you'll be
> surprised by some exceptions - read the documentation they point you
> to).
I've installed the trunk code and, for my purposes, the unsafe_subselect_ok
attribute is probably going to be sufficient. Thank you for your thorough
documentation.
But (and you knew there was going to be a "but", right?) the trunk code still
breaks on the following (same place as above):
$items = $c->model('BirdWebDB::Birds')->search(
{},
{order_by => {'-'. $sort_order => $sort_column},
unsafe_subselect_ok => 1,
join => ['shadebirds'],
prefetch => [qw/genus species/],
page => $page,
rows => $limit}
);
[error] DBI Exception: DBD::Sybase::st execute failed: Server message
number=306 severity=16 state=2 line=1 server=SQLB29 text=The text, ntext, and
image data types cannot be compared or sorted, except when using IS NULL or
LIKE operator.
[for Statement "SELECT me.id, me.genus_id, me.species_name_id, me.image_id,
me.north_america_map_id, me.washington_map_id, me.default_sound_id,
me.is_washington_bird, me.common_name, me.taxonomic_order, me.identification,
me.voice, me.habitat, me.behavior, me.diet, me.nesting, me.migration,
me.where_found, me.conservation_status, me.breeding, me.notes, genus.id,
genus.family_id, genus.genus_scientific_name, genus.genus_common_name,
genus.genus_description, species.id, species.species_scientific_name,
species.species_name_notes FROM ( SELECT me.id, me.genus_id,
me.species_name_id, me.image_id, me.north_america_map_id, me.washington_map_id,
me.default_sound_id, me.is_washington_bird, me.common_name, me.taxonomic_order,
me.identification, me.voice, me.habitat, me.behavior, me.diet, me.nesting,
me.migration, me.where_found, me.conservation_status, me.breeding, me.notes
FROM ( SELECT me.*, ROW_NUMBER() OVER( ORDER BY (SELECT(1)) ) AS
rno__row__index FROM ( SELECT me.id, me.genus_id, me.species_name_id,
me.image_id, me.north_america_map_id, me.washington_map_id,
me.default_sound_id, me.is_washington_bird, me.common_name, me.taxonomic_order,
me.identification, me.voice, me.habitat, me.behavior, me.diet, me.nesting,
me.migration, me.where_found, me.conservation_status, me.breeding, me.notes
FROM (SELECT TOP 4294967296 me.id, me.genus_id, me.species_name_id,
me.image_id, me.north_america_map_id, me.washington_map_id,
me.default_sound_id, me.is_washington_bird, me.common_name, me.taxonomic_order,
me.identification, me.voice, me.habitat, me.behavior, me.diet, me.nesting,
me.migration, me.where_found, me.conservation_status, me.breeding, me.notes
FROM birds me JOIN shadecoffee_bird_info shadebirds ON shadebirds.bird_id =
me.id JOIN genera genus ON genus.id = me.genus_id JOIN species_names species ON
species.id = me.species_name_id ORDER BY taxonomic_order ASC) me ) me ) me
WHERE rno__row__index BETWEEN 1 AND 10 ) me JOIN shadecoffee_bird_info
shadebirds ON shadebirds.bird_id = me.id JOIN genera genus ON genus.id =
me.genus_id JOIN species_names species ON species.id = me.species_name_id GROUP
BY me.id, me.genus_id, me.species_name_id, me.image_id,
me.north_america_map_id, me.washington_map_id, me.default_sound_id,
me.is_washington_bird, me.common_name, me.taxonomic_order, me.identification,
me.voice, me.habitat, me.behavior, me.diet, me.nesting, me.migration,
me.where_found, me.conservation_status, me.breeding, me.notes, genus.id,
genus.family_id, genus.genus_scientific_name, genus.genus_common_name,
genus.genus_description, species.id, species.species_scientific_name,
species.species_name_notes ORDER BY taxonomic_order ASC"]
After playing with the generated SQL it turns out the problem is in the GROUP
BY statement which has a bunch of text columns.
Let me know if there's anything I can do.
- Alan
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
