Really weird problem

Question

Really weird problem

Felipe Saavedra

SSC-Addicted

Points: 477
More actions
February 2, 2005 at 8:10 am

#66488

Hi, i just notice that my query have a problem, I mean, I join tables into a temp tables, then i work with those temp tables and I join it again.
Then in that join (inner) I do a count and the results are not right, I mean, in the result set appears values that doesn't have to appear, this is some part of the procedure
SELECT rfq.rfqName, rfq.rfqExternalID AS rfq,
p.ptnExternalID AS ptn INTO #temp1
FROM dbo.qstRfq rfq INNER JOIN dbo.qstBid bid
ON rfq.rfqExternalID = bid.bidRfq_LNK INNER JOIN dbo.qstPartner p
ON bid.bidPartner_LNK = p.ptnExternalID
WHERE (rfq.rfqStartDateYear > @Year-1 ) AND (rfq.rfqStartDateYear < @Year+1)
AND (rfq.rfqStartDateMonth > @StartMonth-1 )AND (rfq.rfqStartDateMonth < @EndMonth+1 )
AND (rfq.rfqName LIKE '6000%')
GROUP BY rfq.rfqExternalID, p.ptnExternalID, rfq.rfqName
--Joins Providers and Categories
--Entrega tabla de Proveedores con Familias de Categorías
SELECT p.ptnExternalID AS ptn , c.catCategoryName AS Family INTO #temp2
FROM dbo.qstCategory c INNER JOIN dbo.qstPartnerCategory pc
ON c.catExternalCode = pc.ptcCategory_LNK
INNER JOIN dbo.qstPartner p ON pc.ptcPartner_LNK = p.ptnExternalID
WHERE (pc.ptcCategory_LNK LIKE '___.00.00')
GROUP BY p.ptnExternalID, c.catCategoryName
--Creates an initial table. Join the needed values to work with.
SELECT rfqName, rfq , Family,
COUNT(1) AS Freq
INTO #temporal
FROM #temp1 AS RfqPtn INNER JOIN #temp2 AS CatPtn
ON RfqPtn.ptn = CatPtn.ptn
GROUP BY rfqName, rfq, Family
ORDER BY rfqName
whell with these code I'm having 2 problems, the first problem is the result set like I said. Appears rfq (Id) joined with families that doesnt belong to #temp2, I mean searching for values in the result I saw in #temp2 there is no 7500 value on ptn (ID) but in #temp1 there is a rfq with 7500 ptn, with this query should not appear that rfq because 7500 value can't be joined with #temp2, but it appears!!!!
The other problem It's that isn't ordering correctly.
Well however, I change the query, and didn't use the count and it works correctly.
Well after all this, my question it's, the query corrupts with large set of values?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I cannot seem to replicate this problem. Here is the simple script I used, can you correct anything I have misinterpretted?

SELECT 'TEST ' AS rfqName, 1 AS rfq, 7500 AS ptn INTO #temp1

INSERT INTO #temp1 SELECT 'MATCH1', 2, 8400

INSERT INTO #temp1 SELECT 'MATCH2', 3, 8300

SELECT 7400 AS ptn INTO #temp2

INSERT INTO #temp2 SELECT 8400

INSERT INTO #temp2 SELECT 8300

SELECT rfqName, rfq, COUNT(1) AS Freq

FROM #temp1 AS RfqPtn

INNER JOIN #temp2 AS CatPtn ON RfqPtn.ptn = CatPtn.ptn

GROUP BY rfqName, rfq

ORDER BY rfqName

DROP TABLE #temp1

DROP TABLE #temp2

I wasn't born stupid - I had to study.

Felipe Saavedra SSC-Addicted Points: 477 More actions · Answer 2

Yep, that's the Idea, and I prove that and it works perfectly.. So my conclusion it's that the query fails with a large size of data.

Because i don see anything wrong in my query it's the same as yours, but with a large amount of data per table

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

I have difficulty buying that answer.

Hopefully, some of the true gurus will see this post and give us a better review and explaination.

I wasn't born stupid - I had to study.

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 4

Hi there,

I don't consider myself as one of the Farrell's true gurus :-), but I still cannot accept the fact that M$$SQL's query execution engine has an undiscovered bug that only appears when dealing with large ammounts of data. I suggest Felipe to did deeper in the data, to isolate those rows with 7500 values. Then, post the table structure (or at least the necessary part of it) and those isolated rows that cause the problem. I assume that there is something wired with the data and the way rows are JOINed. About the wrong ordering, consider using normal tables first. Temporary tables are stored in tempdb, and it's collation may affect results. I had the same problem when JOINing data with Macedonian collation with data from temporary table (with English collation).

Regards,

Goce.

Felipe Saavedra SSC-Addicted Points: 477 More actions · Answer 5

Well like a said the first and the second join are correct, I already verify that.

the table estructure in #temp1 is

rfqName varchar(255), rfq int, ptn int

#temp2

ptn int, Family varchar(255)

then I join with ptn.

My first query was one big query, then I saw the information wasn't correct, and I separate in 3 with temp table #temp1 and 2,

then I didn't use the COUNT(1) AS Freq in the same third query, and only then the results were correct.

I didn't think myself that is a bug, in fact I was hoping an answer like the server configuration could be wrong.. or something like that. I'm not working with the server.

I don't think the joins are incorrect, because I'm joining only the keys, so It couldn't be wrong.

I'm thinking that the count generates a strange problem, because I didn't use it and it works perfectly.

Well that it is, and if I'm the only one it doesn't mean that is a bug, in fact it means that probably the server isn't working at all well.

Anyway thx

Antares686 SSC Guru Points: 125444 More actions · Answer 6

I think there is a lot of confusion here.

First you say

"in #temp1 there is a rfq with 7500 ptn"

And go on to say

"With this query should not appear that rfq because 7500 value

can't be joined with #temp2, but it appears"

Which I assume is because of this statement

"I saw in #temp2 there is no 7500 value on ptn (ID)"

Now when you say that do you mean that #temp1 has a results of 7500 in it or that when completed #temporal contains a value that seemingly comes from pyn 7500 involved. What exists in #temp1 doesn't have to match with #temp2 as there is no defining element that I can see guarantees this fact. If however it is from the results of #temporal then I would suggest adding ptn to your #temporal data and load to verify that is where it came from. Also based on all I see you really should be able to convert to a single set based solution after you understand the reason for your perceived error.

Lastly, if would you please post and example of the data issue you are seeing as it exists in all 3 temp tables so we can judge what other details will help?

Felipe Saavedra SSC-Addicted Points: 477 More actions · Answer 7

Hi again, ..... I don't know what to say, I was trying to get the results from yesterday to post them, but now all works fine, In fact there is no ptn 7500 in none of the tables :S.

Well sorry to worry you, it's really weird, because yesterday I verify the data and it was wrong, now is fine... grrr. This really makes me headeck.

Well sorry again, I will be more carefull after post something like this

Antares686 SSC Guru Points: 125444 More actions · Answer 8

That's fine, but when you have and next time you see the issue if you can post example of the details of the problem it will help us to better help you so a permanent solution can be found if it is reoccurring.

rhunt SSCertifiable Points: 5868 More actions · Answer 9

Felipe, I have had a similar problem before. Make a note of the current query path as it is now. The next time it is not working, run it again. I think you will find that the query plan is wrong.

We have alwasy cleared the cache and it began to run properly again. Although, the question becomes: What if in the future it starts to run incorrectly and the optimizer continually chooses the wrong path.

Maybe someone else out there knows how to get past that one. I have been lucky with the optimizer fixing the problem thus far.

Ryan