Hey Frans,

I'm struggling with this. My dynamic list fetches, which support paging, are not returning the correct data. I tend to get the same data for the first several pages of my resultset. The wrong results are 100% consistant.

The query JOINs to a view, but there are no text, ntext, blob, etc. fields in the query.

If I run the LLBL-generated query in QA, I get the same results. However, if I remove the "TOP xx" clause from the query, the results come back as expected.

Using the latest runtime libraries for 1.0.2004.2 (have not installed any hotfixes--are the hotfixes included in the latest full version?).

On this particular project, I haven't had time to upgrade to 1.0.2005.1 (I will invest the time if that's what's required to fix this, though).

Thanks for any insight.

Do you think you could paste some of the code and perhaps the query that is being generated?

Also please check the following threads:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3844 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=4021

Good Luck

Thanks for your responses.

I unfortunately can't post code, since this is a client project. I realize this limits the level of support you can give.

I think I figured out what is causing this, but I'm not sure how to solve it.

I pasted the query in QA and tinkered around with it. As I said before, removing the TOP clause made the query work correctly. So I added a SELECT * FROM #TempTable into the executed text, then ran the query again with and without the TOP statement.

Essentially, the data is getting into the temp table in a different order when TOP is used vs. when it's not used.

The ORDER BY uses field1 (we'll call it). The values for field1 are the same for 70 of 78 records returned from the query. Field1 is the only field used in the ORDER BY clause.

When using TOP, the records get inserted into the temp table in this order: field1 ASC **field2 DESC **

When TOP is not used, the records get into the temp table in this order: field1 ASC field2 ASC

So since TOP happens AFTER the ORDER BY clause is applied, does the fact that the sorting happens "in-memory" somehow cause the additional sorting (meaning sorting NOT specified in ORDER BY) to be reversed?

What is causing field2 to affect the sort order when it's not part of the ORDER BY anyway? Any sources on how SQL orders records outside of ORDER BY clauses?

More info: Field1 corresponds to a lookup, which is returned as name1. Name1 is the first field in the GROUP BY clause. Since it corresponds exactly to field1, it is already sorted. Field2 is the second field in the GROUP BY clause--can group by affect sorting? **EDIT: This is what causes the sorting after ORDER BY. I guess this was easy to test. **

More info #2: field2 is the PK of the first table named in the FROM clause. Does a table's position in the FROM clause affect sorting?

I think I have a workaround I can use for now, but I'd still like to get to the bottom of why this is happening and how to fix it (if it can be fixed).

Thanks for any insight.

Maybe a sample code snippet, a sample table structure and a sample SQL query would be helpfull, we don't need the real production code.

You can provide snippets and samples with dummy names and data.

Thanks

Please check if the view has an orderby. If so, remove it. It's a known issue with sqlserver 2000.

Otis wrote:

Please check if the view has an orderby. If so, remove it. It's a known issue with sqlserver 2000.

Nope, no ORDER BY in the views.

I'm not sure how (or if) I can duplicate it without sharing DDL and code. If I were able to duplicate it with SQL via Northwind (and I'm not sure that I can), would that be good enough?

I am leaving this afternoon and will not be in the office until Monday, so I may not have time to even attempt in until then.

psandler wrote:

Otis wrote:

Please check if the view has an orderby. If so, remove it. It's a known issue with sqlserver 2000.

Nope, no ORDER BY in the views.

I'm not sure how (or if) I can duplicate it without sharing DDL and code. If I were able to duplicate it with SQL via Northwind (and I'm not sure that I can), would that be good enough?

Northwind would be fine. If nothing else works but your original DDL, you could mail the necessary tables/view to support AT llblgen.com. We always destroy 3rd party test material once the issue has been resolved.

Ok, here is a Northwind Query that demonstrates the effect TOP has on the paging queries. This was not emitted by LLBL (I simply don't have the time today), but I constructed it to mirror the way paging queries get generated from LLBL. This exactly duplicates the problem I am having.

--vars to simulate paging
DECLARE 
    @start int,
    @end int

--create temp table
CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT NULL, [Orderdate] [DateTime] NOT NULL)

SELECT @start = 0  /*****CHANGE ME*****/
SELECT @end = 12 /*****CHANGE ME*****/

--insert rows
INSERT INTO #TempTable (CustomerID, ShipVia, ShipName, ShipAddress,Orderdate)
SELECT DISTINCT
TOP 12 /*****CHANGE ME*****/
    CustomerID,
    ShipVia,
    ShipName,
    ShipAddress,
    Orderdate
FROM
    ORDERS
WHERE 
    CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
ORDER BY 
    CustomerId

--select the page of data
SELECT * FROM #TempTable WHERE __rowcnt > @start AND __rowcnt <= @end

--select to see the whole temp table
--SELECT * FROM #TempTable

DROP TABLE #TempTable

1. Run the query as is. Notice that there are 3 rows returned where ShipVia = 2.

2. Change @start to 12, @end to 24, and the TOP statement to be "TOP 24". Notice that although the __rowcnt selection correctly returns 13-24, the rows are the same.

3. Change @start to 24, @end to 36, and the TOP statement to be "TOP 36". The page of data now changes (I believe because the customerID changes on this page).

4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages of data returned are now "correct"--the data returned is different for each page.

You can uncomment the final select statement to see what is actually going into the temp table.

Again, I'm not sure what the root cause of this is, but it seems that the TOP statement is somehow causing the rows to be added to the temp table in reverse order. It's more than possible that something is going on here that I am just not seeing.

Hopefully this is helpful in tracking down the problem (if it is a problem). It seems to be a SQL Server issue, but I believe that leaving TOP off the generated query would immediately solve the problem.

Thanks,

Phil

I did as you said, I copied the SQL script into the QA and I runned the query as is and here is the result:

1   ERNSH   1   Ernst Handel    Kirchgasse 6    1996-07-17 00:00:00.000
2   ERNSH   1   Ernst Handel    Kirchgasse 6    1996-11-11 00:00:00.000
3   ERNSH   1   Ernst Handel    Kirchgasse 6    1996-12-13 00:00:00.000
4   ERNSH   1   Ernst Handel    Kirchgasse 6    1996-12-23 00:00:00.000
5   ERNSH   1   Ernst Handel    Kirchgasse 6    1997-01-30 00:00:00.000
6   ERNSH   1   Ernst Handel    Kirchgasse 6    1997-07-10 00:00:00.000
7   ERNSH   1   Ernst Handel    Kirchgasse 6    1997-09-12 00:00:00.000
8   ERNSH   1   Ernst Handel    Kirchgasse 6    1997-10-09 00:00:00.000
9   ERNSH   1   Ernst Handel    Kirchgasse 6    1998-01-16 00:00:00.000
10  ERNSH   1   Ernst Handel    Kirchgasse 6    1998-02-18 00:00:00.000
11  ERNSH   2   Ernst Handel    Kirchgasse 6    1996-11-29 00:00:00.000
12  ERNSH   2   Ernst Handel    Kirchgasse 6    1997-01-02 00:00:00.000

1. Run the query as is. Notice that there are 3 rows returned where ShipVia = 2.

I only have 2 rows of ShipVia = 2

Then I changed the parameters to: (@start = 12, @end = 24 & TOP 24) and the result is:

13  ERNSH   2   Ernst Handel    Kirchgasse 6    1997-02-11 00:00:00.000
14  ERNSH   2   Ernst Handel    Kirchgasse 6    1997-04-22 00:00:00.000
15  ERNSH   2   Ernst Handel    Kirchgasse 6    1997-12-10 00:00:00.000
16  ERNSH   2   Ernst Handel    Kirchgasse 6    1997-12-24 00:00:00.000
17  ERNSH   2   Ernst Handel    Kirchgasse 6    1998-01-27 00:00:00.000
18  ERNSH   2   Ernst Handel    Kirchgasse 6    1998-03-26 00:00:00.000
19  ERNSH   2   Ernst Handel    Kirchgasse 6    1998-04-13 00:00:00.000
20  ERNSH   2   Ernst Handel    Kirchgasse 6    1998-05-05 00:00:00.000
21  ERNSH   3   Ernst Handel    Kirchgasse 6    1996-07-23 00:00:00.000
22  ERNSH   3   Ernst Handel    Kirchgasse 6    1997-01-03 00:00:00.000
23  ERNSH   3   Ernst Handel    Kirchgasse 6    1997-06-17 00:00:00.000
24  ERNSH   3   Ernst Handel    Kirchgasse 6    1997-08-15 00:00:00.000

1. Change @start to 12, @end to 24, and the TOP statement to be "TOP 24". Notice that although the __rowcnt selection correctly returns 13-24, the rows are the same

The rows showed are different than before

Then I changed the parameters to: (@start = 24, @end =36 & TOP 36) and the result is:

25  ERNSH   3   Ernst Handel    Kirchgasse 6    1997-12-03 00:00:00.000
26  ERNSH   3   Ernst Handel    Kirchgasse 6    1997-12-11 00:00:00.000
27  ERNSH   3   Ernst Handel    Kirchgasse 6    1997-12-15 00:00:00.000
28  ERNSH   3   Ernst Handel    Kirchgasse 6    1998-03-23 00:00:00.000
29  ERNSH   3   Ernst Handel    Kirchgasse 6    1998-04-01 00:00:00.000
30  ERNSH   3   Ernst Handel    Kirchgasse 6    1998-04-08 00:00:00.000
31  QUICK   1   QUICK-Stop  Taucherstra?e 10    1997-01-17 00:00:00.000
32  QUICK   1   QUICK-Stop  Taucherstra?e 10    1997-04-23 00:00:00.000
33  QUICK   1   QUICK-Stop  Taucherstra?e 10    1997-05-05 00:00:00.000
34  QUICK   1   QUICK-Stop  Taucherstra?e 10    1997-05-27 00:00:00.000
35  QUICK   1   QUICK-Stop  Taucherstra?e 10    1997-09-05 00:00:00.000
36  QUICK   1   QUICK-Stop  Taucherstra?e 10    1997-11-18 00:00:00.000

Conclusion: on my side everything is working fine with the Top query!!

Phil, what's the version of sqlserver? (SELECT @@VERSION ) It could be you need to apply a service pack. This order by issue has haunted me before and I recall a service pack did miracles. But before that, could you post your SELECT @@VERSION result? (Walaa, could you do that too? I'll now also perform a test here to see if I can reproduce it here)

SELECT @@VERSION: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

760 is Service pack 3.

first query result:

1   ERNSH   2   Ernst Handel    Kirchgasse 6    1997-02-11 00:00:00.000
2   ERNSH   2   Ernst Handel    Kirchgasse 6    1997-01-02 00:00:00.000
3   ERNSH   2   Ernst Handel    Kirchgasse 6    1996-11-29 00:00:00.000
4   ERNSH   1   Ernst Handel    Kirchgasse 6    1998-02-18 00:00:00.000
5   ERNSH   1   Ernst Handel    Kirchgasse 6    1998-01-16 00:00:00.000
6   ERNSH   1   Ernst Handel    Kirchgasse 6    1997-10-09 00:00:00.000
7   ERNSH   1   Ernst Handel    Kirchgasse 6    1997-09-12 00:00:00.000
8   ERNSH   1   Ernst Handel    Kirchgasse 6    1997-07-10 00:00:00.000
9   ERNSH   1   Ernst Handel    Kirchgasse 6    1997-01-30 00:00:00.000
10  ERNSH   1   Ernst Handel    Kirchgasse 6    1996-12-23 00:00:00.000
11  ERNSH   1   Ernst Handel    Kirchgasse 6    1996-12-13 00:00:00.000
12  ERNSH   1   Ernst Handel    Kirchgasse 6    1996-11-11 00:00:00.000

second query:

13  ERNSH   2   Ernst Handel    Kirchgasse 6    1997-02-11 00:00:00.000
14  ERNSH   2   Ernst Handel    Kirchgasse 6    1997-01-02 00:00:00.000
15  ERNSH   2   Ernst Handel    Kirchgasse 6    1996-11-29 00:00:00.000
16  ERNSH   1   Ernst Handel    Kirchgasse 6    1998-02-18 00:00:00.000
17  ERNSH   1   Ernst Handel    Kirchgasse 6    1998-01-16 00:00:00.000
18  ERNSH   1   Ernst Handel    Kirchgasse 6    1997-10-09 00:00:00.000
19  ERNSH   1   Ernst Handel    Kirchgasse 6    1997-09-12 00:00:00.000
20  ERNSH   1   Ernst Handel    Kirchgasse 6    1997-07-10 00:00:00.000
21  ERNSH   1   Ernst Handel    Kirchgasse 6    1997-01-30 00:00:00.000
22  ERNSH   1   Ernst Handel    Kirchgasse 6    1996-12-23 00:00:00.000
23  ERNSH   1   Ernst Handel    Kirchgasse 6    1996-12-13 00:00:00.000
24  ERNSH   1   Ernst Handel    Kirchgasse 6    1996-11-11 00:00:00.000

Same rows indeed.

third query:

25  ERNSH   1   Ernst Handel    Kirchgasse 6    1997-07-10 00:00:00.000
26  ERNSH   1   Ernst Handel    Kirchgasse 6    1997-01-30 00:00:00.000
27  ERNSH   1   Ernst Handel    Kirchgasse 6    1996-12-23 00:00:00.000
28  ERNSH   1   Ernst Handel    Kirchgasse 6    1996-12-13 00:00:00.000
29  ERNSH   1   Ernst Handel    Kirchgasse 6    1996-11-11 00:00:00.000
30  ERNSH   1   Ernst Handel    Kirchgasse 6    1996-07-17 00:00:00.000
31  QUICK   1   QUICK-Stop  Taucherstraße 10    1998-01-21 00:00:00.000
32  QUICK   1   QUICK-Stop  Taucherstraße 10    1997-11-18 00:00:00.000
33  QUICK   1   QUICK-Stop  Taucherstraße 10    1997-09-05 00:00:00.000
34  QUICK   1   QUICK-Stop  Taucherstraße 10    1997-05-27 00:00:00.000
35  QUICK   1   QUICK-Stop  Taucherstraße 10    1997-05-05 00:00:00.000
36  QUICK   1   QUICK-Stop  Taucherstraße 10    1997-04-23 00:00:00.000

I get the same results on my desktop engine sp3 as well. I'll now upgrade that one to SP4 and see what happens.

(edit): upgrading to SP4 didn't help. Although the sp4 installation did report an error. Amazing how hard it apparently is to update some dlls for these microsoft guys.

Looking into it. It's weird what this query dares to report as results...

It's weird. If I remove the TOP statement, the query works. If I add the TOP statement, the query Fails.

If you look at the temptable completely, and remove the TOP statement, the temptable looks as expected. If you add the TOP statement, it doesn't. If you add SET ROWCOUNT 24 for the second query right above the INSERT (and comment out TOP), it also works.

Which is strange, because it shouldn't work, as ROWCOUNT simply chops off the data before sorting, TOP should do that AFTER sorting, so it's very strange TOP gives a different result than when you omit top.

Though there's something odd about the query: you order on customerid, but there are multiple values for the same customerid. It's therefore random (e.g. 'undefined') what the result will be, as all rows are equal according to the orderby statement. If I add Shipvia to the orderby, it works WITH the top statement.

I think that's the explanation why the query actually fails while you would expect it to work: for the orderby it has sorted the rows as requested, but as there are a lot of rows with the same customerid, teh order in which THOSE rows are stored in the temp table is 'undefined' by default.

For the records:

My results were based on the following SQL Server version:

SELECT @@VERSION -> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

SELECT SERVERPROPERTY('ProductLevel') -> RTM

My version on the server:

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: )

My version on the desktop:

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Both have the same result.

Otis wrote:

It's weird. If I remove the TOP statement, the query works. If I add the TOP statement, the query Fails.

Yep, very strange.

Otis wrote:

If you look at the temptable completely, and remove the TOP statement, the temptable looks as expected. If you add the TOP statement, it doesn't. If you add SET ROWCOUNT 24 for the second query right above the INSERT (and comment out TOP), it also works.

Would replacing TOP with ROWCOUNT be an option for the LLBL query generator?

Otis wrote:

Though there's something odd about the query: you order on customerid, but there are multiple values for the same customerid. It's therefore random (e.g. 'undefined') what the result will be, as all rows are equal according to the orderby statement. If I add Shipvia to the orderby, it works WITH the top statement.

I think that's the explanation why the query actually fails while you would expect it to work: for the orderby it has sorted the rows as requested, but as there are a lot of rows with the same customerid, teh order in which THOSE rows are stored in the temp table is 'undefined' by default.

Yep, the query is odd as it stands. And I know that SQL Server does not "guarantee" that records will be returned in any particular order if the order isn't specified.

However, paging doesn't work without the sort order being consistent. I need to be able to give users the flexibility of sorting by any field they want (or not sorted at all) and still seeing data one page at a time. I'm not sure how to work around this in the long term.

Do you have a contact with SQL Server at Miscrosoft who you could ask? Or do you have a particular forum you post these kinds of issues to? (I would post the question on Usenet, but I don't want my hands slapped by Celko: "Tables are not files, rows are not records, etc." ).

psandler wrote:

Otis wrote:

If you look at the temptable completely, and remove the TOP statement, the temptable looks as expected. If you add the TOP statement, it doesn't. If you add SET ROWCOUNT 24 for the second query right above the INSERT (and comment out TOP), it also works.

Would replacing TOP with ROWCOUNT be an option for the LLBL query generator?

No, as that would give wrong results as well, because Order By isn't applied to all rows which COULD end up in the resultset.

Otis wrote:

Though there's something odd about the query: you order on customerid, but there are multiple values for the same customerid. It's therefore random (e.g. 'undefined') what the result will be, as all rows are equal according to the orderby statement. If I add Shipvia to the orderby, it works WITH the top statement.

I think that's the explanation why the query actually fails while you would expect it to work: for the orderby it has sorted the rows as requested, but as there are a lot of rows with the same customerid, teh order in which THOSE rows are stored in the temp table is 'undefined' by default.

Yep, the query is odd as it stands. And I know that SQL Server does not "guarantee" that records will be returned in any particular order if the order isn't specified.

However, paging doesn't work without the sort order being consistent. I need to be able to give users the flexibility of sorting by any field they want (or not sorted at all) and still seeing data one page at a time. I'm not sure how to work around this in the long term.

That's indeed a problematic situation, but it's understandable, as the rows aren't guaranteed to be in a given order if you don't specify the order. I know paging can then be problematic, but I don't see another way to solve this than to recommend more order options, or add them under the hood (like an order by on a PK field, if it's specified).

Do you have a contact with SQL Server at Miscrosoft who you could ask? Or do you have a particular forum you post these kinds of issues to? (I would post the question on Usenet, but I don't want my hands slapped by Celko: "Tables are not files, rows are not records, etc." ).

No, I don't have such a contact, I always post these kind of questions (if I have them) in teh MVP newsgroup for sqlserver. Celko knows a lot but goes too far in a lot of discussions. Don't worry about him, he bashed me for days in a row because I said that BL in the database with procedures was a bad idea (which resulted in the reply from Celko that I didn't understand a single bit of Codd's theory. Uhuh... Right, Joe ).

I could post the query you have in the Sqlserver mvp newsgroup, see what they have to say. But I have the strong feeling you already know the answer: add another sort column as rows aren't guaranteed ...

I understand.

But this issue is bound to come up repeatedly for your users, is it not? Ultimately, if someone implements a search with paging, there WILL be cases where a field is the same across multiple pages. Although this particular search is odd in that respect, it is hardly unique.

This can also be fixed fairly easily by removing TOP from the query. Is it your feeling that this will affect efficiency to the point where it's not worth it? It seems to me that it wouldn't make that much difference, as the entire recordset has to be processed and THEN sorted. So is TOP really saving that much? (Disclaimer: that's just my "SQL Sense" talking--I have no real knowledge of how the optimizer works under the hood).

Also, I wonder why it works for Walaa and not for us?

(Edit: P.S. My favorite is when Celko insists that you post DDL, then trashes your DDL when you do post it. )

(Edit #2: Doh! Of course TOP is more efficient, as it limits the number of rows inserted into the temp table. My SQL Sense failed me. )

psandler wrote:

I understand.

But this issue is bound to come up repeatedly for your users, is it not? Ultimately, if someone implements a search with paging, there WILL be cases where a field is the same across multiple pages. Although this particular search is odd in that respect, it is hardly unique.

This can also be fixed fairly easily by removing TOP from the query. Is it your feeling that this will affect efficiency to the point where it's not worth it? It seems to me that it wouldn't make that much difference, as the entire recordset has to be processed and THEN sorted. So is TOP really saving that much? (Disclaimer: that's just my "SQL Sense" talking--I have no real knowledge of how the optimizer works under the hood).

(Edit #2: Doh! Of course TOP is more efficient, as it limits the number of rows inserted into the temp table. My SQL Sense failed me. )

Bingo

It comes down to the simple fact that for the order by specified there are rows which are 'the same' and therefore the order in which they're listed is 'not defined', how sucky it might be.

Because of the temptable requirement TOP is required which apparently messes up the resultset.

Also, I wonder why it works for Walaa and not for us?

He runs an unpatched sqlserver, no service pack So a bugfix somewhere 'fixed' this

[quote](Edit: P.S. My favorite is when Celko insists that you post DDL, then trashes your DDL when you do post it. ) Hehe He clearly should simply retire, or stay in comp.databases.theory.

I went ahead and posted it on microsoft.public.sqlserver.programming, simply because I'm curious by nature.

psandler wrote:

I went ahead and posted it on microsoft.public.sqlserver.programming, simply because I'm curious by nature.

cool I'll subscribe to that newsgroup and monitor the thread