SQL script with "unusual" results

Question

SQL script with "unusual" results

grahamc

SSCertifiable

Points: 6762
More actions
June 7, 2006 at 3:53 am

#113315

Good day all,
Not exactly sure what to call this, but hopefully you can help.
One of my developers wrote a script, rather simple, but with some strange results.
If the script is run as below, then I get 527 records returned. If I comment out the ORDER BY statement at the end, I 249 records returned.
Script:
Declare
            @dFromDate datetime,
            @dToDate datetime

set @dFromDate = 'Jun 1 2006 12:00:00:000AM'
set @dToDate = 'Jun 10 2006 12:00:00:000AM'

SELECT
            *
FROM tbl_QuotePAOption Me
            INNER JOIN dbo.tbl_Quotation ON qot_lid = osa_lQuotationId
            INNER JOIN dbo.tbl_QuotePASource ON qss_lid = osa_lSASourceId
            INNER JOIN dbo.tbl_QuoteCommon ON qps_lSourceId = Me.osa_lSASourceId and qps_iProdid = 1
            INNER JOIN dbo.tbl_CommissionTaken ON ct_lid = qss_iCommissionTakenId
            LEFT OUTER JOIN dbo.tbl_QuoteUpliftDetail on osa_lUpliftDetailId = opp_lId
WHERE            osa_dRateDate >= @dFromDate
            and osa_dRateDate <= @dToDate
order by osa_lUpliftDetailId
I am trying to figure out why this is happening? Surely an ORDER BY cannot increase/decrease the number of records returned from a SELECT statement?
Corrupt index? Corrupt execution plan? I have tried a DBCC FREEPROCCACHE - next I am planning and index re-build.
Any other ideas?
Many thanks,
Graham

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Marshall Smith SSCrazy Points: 2041 More actions · Answer 1

You might try explicitly qualifying your columns with table names.

My guess, looking at this, is that you are ordering on a column from the left joined table. That could be affecting the join behavior. Where does osa_dRateDate come from? Which table does opp_lId come from?

My first suggestion would be to remove the other joins, and see what kind of behavior you see.

grahamc SSCertifiable Points: 6762 More actions · Answer 2

Declare

@dFromDate datetime,

@dToDate datetime

set @dFromDate = 'Jun 1 2006 12:00:00:000AM'

set @dToDate = 'Jun 10 2006 12:00:00:000AM'

SELECT

opp_lId,

opp_mOriginalAnnuity,

opp_fUpliftAmount,

/*opp_fMaxUpliftAmount,*/

opp_bSuperUsered,

opp_lCompetitorId,

opp_mCompetitionAnnuity,

opp_mRTPPremium

FROM tbl_QuotePAOption Me --osa_ Fields

LEFT OUTER JOIN dbo.tbl_QuoteUpliftDetail --opp_ Fields

on osa_lUpliftDetailId = opp_lId

WHERE osa_dRateDate >= @dFromDate

and osa_dRateDate <= @dToDate

New query...

* removed...

commented field - with comments no problem, if the comments are removed, causes the problem.

I have rebuilt this table on my local machine, and the same queries are not an issue.

Thanks,

Graham

stax68 SSChampion Points: 11711 More actions · Answer 3

post execution plans for the simplest version of the two queries.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant