Error of sorting in ElevateDB 2.03 Build 18 when RequestSensitive is set.

/* script for table creation */

CREATE TABLE Table1
(
F1 INTEGER,
F2 INTEGER
)

CREATE INDEX Index1 ON Table1 (F1 DESC)

INSERT INTO Table1 (F1, F2)
VALUES(1, 9)

INSERT INTO Table1 (F1, F2)
VALUES(2, 8)

INSERT INTO Table1 (F1, F2)
VALUES(3, 7)

/* end of script for table creation */

SELECT * FROM Table1
ORDER BY F1

When Request Sensitive Result Set flag is not set then the following result is present:
F1 F2
1   9
2   8
3   7
It is all right.

When Request Sensitive Result Set flag is set then the following result is present:
F1 F2
3   7
2   8
1   9
It is incorrect.

In Build 18

SELECT * FROM Table1
ORDER BY F1 ASC

returns right result, but in Build 17 the result is incorrect always.

Sergey A. Kolotov wrote:

Error of sorting in ElevateDB 2.03 Build 18 when RequestSensitive is set.

/* script for table creation */

CREATE TABLE Table1
(
F1 INTEGER,
F2 INTEGER
)

CREATE INDEX Index1 ON Table1 (F1 DESC)

INSERT INTO Table1 (F1, F2)
VALUES(1, 9)

INSERT INTO Table1 (F1, F2)
VALUES(2, 8)

INSERT INTO Table1 (F1, F2)
VALUES(3, 7)

/* end of script for table creation */

SELECT * FROM Table1
ORDER BY F1

When Request Sensitive Result Set flag is not set then the following result is present:
F1 F2
1   9
2   8
3   7
It is all right.

When Request Sensitive Result Set flag is set then the following result is present:
F1 F2
3   7
2   8
1   9
It is incorrect.

In Build 18

SELECT * FROM Table1
ORDER BY F1 ASC

returns right result, but in Build 17 the result is incorrect always.

**************************************

XP SP3 - EDB 2.03b18

I have something similar:

A test table:

CREATE TABLE "TEST"
(
"ID" INTEGER NOT NULL,
"NOME" VARCHAR(25) COLLATE "ITA"
);

No indexes at all.

Populated  with 20 records by means of a sproc.

In my case:

'select * from test;' gives:

a) with Request Sensitive Result Set enabled -> records in natural order in which they were inserted (ID 1 through   20): as espected, IMO, due to the "live" nature of the result set.

b) with Request Sensitive Result Set disabled -> records in reverse order (ID 20 through 1): which I cannot understand why.

Tim, any clue about it?

Franco,

<< 'select * from test;' gives:

a) with Request Sensitive Result Set enabled -> records in natural order in
which they were inserted (ID 1 through   20): as espected, IMO, due to the
"live" nature of the result set.

b) with Request Sensitive Result Set disabled -> records in reverse order
(ID 20 through 1): which I cannot understand why. >>

This is normal - there is no guarantee that rows will be in any particular
order when you don't specify an ORDER BY, although with EDB the rule is:

1) If RequestSensitive=True, then the result set will be in natural order or
primary key order, if no ORDER BY is specified
2) If RequestSensitive=False, then the result set will be generated
according to the primary key, if possible based upon the SELECTed columns,
and then according to how the rows are selected physically from the table,
which can be virtually any order.

I can see about altering 2) so that it tries to put things in natural order
also, but I'll have to check it out first and see if it's possible.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

Franco,

<< 'select * from test;' gives:

a) with Request Sensitive Result Set enabled -> records in natural order in
which they were inserted (ID 1 through   20): as espected, IMO, due to the
"live" nature of the result set.

b) with Request Sensitive Result Set disabled -> records in reverse order
(ID 20 through 1): which I cannot understand why. >>

This is normal - there is no guarantee that rows will be in any particular
order when you don't specify an ORDER BY, although with EDB the rule is:

1) If RequestSensitive=True, then the result set will be in natural order or
primary key order, if no ORDER BY is specified
2) If RequestSensitive=False, then the result set will be generated
according to the primary key, if possible based upon the SELECTed columns,
and then according to how the rows are selected physically from the table,
which can be virtually any order.

I can see about altering 2) so that it tries to put things in natural order
also, but I'll have to check it out first and see if it's possible.

--
Tim Young
Elevate Software
www.elevatesoft.com

******************************************************************************************
Tim,
thanks for explanation.

<<I can see about altering 2) so that it tries to put things in natural order
also, but I'll have to check it out first and see if it's possible.>>

For my part, it is enough to clearly understand what is going on in the background (and thinking again, reasons appear quite obvious): IMO, you cannot always expect things to go the way *you* think they should be going.
More, as everything is a result of some trade-off, trying to get back a specific order anyway will imply some more overhead, at best .
(in the case of my TEST table, which has no indexes at all, I think it would need a full table scan with row sort (right?); think if it had say 100,000 rows! Is it worth?)

Franco


>think if it had say 100,000 rows! Is it worth?)

A 100,000 row table without an index - impressive but stupid

Roy Lambert

Roy Lambert wrote:

Franco


>think if it had say 100,000 rows! Is it worth?)

A 100,000 row table without an index - impressive but stupid

Roy Lambert

********

Roy,

Stupidity is everywhere...

Franco,

<< For my part, it is enough to clearly understand what is going on in the
background (and thinking again, reasons appear quite obvious): IMO, you
cannot always expect things to go the way *you* think they should be going.
More, as everything is a result of some trade-off, trying to get back a
specific order anyway will imply some more overhead, at best . >>

There really isn't any overhead, it's just a matter of making sure that the
driver table in the query is using a specific ordering during the result set
generation.

--
Tim Young
Elevate Software
www.elevatesoft.com

Franco

>Stupidity is everywhere...

Surely not amongst ElevateSoft customers?

Roy Lambert

Roy Lambert wrote:

Franco

>Stupidity is everywhere...

>>Surely not amongst ElevateSoft customers?

Roy Lambert

Stupidity, often, is like beauty: "is in the eye of the beholder".