Databases
StackOverflow2013
Postsdbo
PODBIx::Class query fails, yet dump from DBIC_TRACE works
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODBIx::Class query fails, yet dump from DBIC_TRACE works
    text
    copied!<p>I cannot rewrite the current DB schema right now BTW, but that's beside the point to the issue I've hit, so please ignore the table structure :D</p> <p>I'm running this DB query:</p> <pre><code>my $rs = $dbx-&gt;resultset('Result')-&gt;search( { 'result_hnd' =&gt; 16078055, 'seasons.outdoor' =&gt; 'venue.outdoors', 'seasons.start_date' =&gt; { '&lt;=' =&gt; 'meet.date_end' }, 'seasons.end_date' =&gt; { '&gt;=' =&gt; 'meet.date_begin' }, }, { 'join' =&gt; [ { 'team' =&gt; { 'league_teams' =&gt; { 'league' =&gt; 'seasons', }, }, }, { 'meet' =&gt; 'venue' }, ], '+select' =&gt; ['seasons.season_hnd','seasons.name','seasons.start_date','seasons.end_date','meet.date_begin','meet.date_end'], '+as' =&gt; ['season_hnd','season_name','s_start','s_end','m_start','m_end'], columns =&gt; ['result_hnd'], group_by =&gt; ['seasons.season_hnd'], } ); </code></pre> <p>When I run this, I get no results. With DBIC_TRACE on, I see the generated SQL as:</p> <pre><code>SELECT me.result_hnd, seasons.season_hnd, seasons.name, seasons.start_date, seasons.end_date, meet.date_begin, meet.date_end FROM track.result me JOIN track.team team ON team.team_hnd = me.team_hnd LEFT JOIN track.league_team league_teams ON league_teams.team_hnd = team.team_hnd LEFT JOIN track.league league ON league.league_hnd = league_teams.league_hnd LEFT JOIN track.season seasons ON seasons.league_hnd = league.league_hnd OR seasons.league_hnd = league.parent_league_hnd JOIN track.meet meet ON meet.meet_hnd = me.meet_hnd JOIN track.venue venue ON venue.venue_hnd = meet.venue_hnd WHERE ( ( result_hnd = ? AND seasons.end_date &gt;= ? AND seasons.outdoor = ? AND seasons.start_date &lt;= ? ) ) GROUP BY seasons.season_hnd: '16078055', 'meet.date_begin', 'venue.outdoors', 'meet.date_end' </code></pre> <p>When I copy and paste this statement into my MYSQL client (and interpolate the placeholders), like this:</p> <pre><code>SELECT me.result_hnd, seasons.season_hnd, seasons.name, seasons.start_date, seasons.end_date, meet.date_begin, meet.date_end FROM track.result me JOIN track.team team ON team.team_hnd = me.team_hnd LEFT JOIN track.league_team league_teams ON league_teams.team_hnd = team.team_hnd LEFT JOIN track.league league ON league.league_hnd = league_teams.league_hnd LEFT JOIN track.season seasons ON seasons.league_hnd = league.league_hnd OR seasons.league_hnd = league.parent_league_hnd JOIN track.meet meet ON meet.meet_hnd = me.meet_hnd JOIN track.venue venue ON venue.venue_hnd = meet.venue_hnd WHERE ( ( result_hnd = 16078055 AND seasons.end_date &gt;= meet.date_begin AND seasons.outdoor = venue.outdoors AND seasons.start_date &lt;= meet.date_end ) ) GROUP BY season_hnd; </code></pre> <p>I get the exact result I expect (7 records).</p> <p>This is really bizarre. To all intents and purposes, isn't that exactly the same query? Am I missing something in my debugging? Or is something else happening at the DBIx::Class::ResultSet layer that isn't being dumped?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload