When I run a positioned update that uses a SELECT statement with an
ORDER BY clause, only the last record in the order is affected. This
happens with or without a transaction. When I remove the ORDER BY
clause from the cursor declaration statement, the update works as
expected. Am I doing something wrong or is this a Pervasive defect?


Using this table with these indexes (Pervasive.SQL V 8.3 server):

CREATE TABLE T_SearchResults
(
ResultSetID CHAR(38) NOT NULL,
ImageID CHAR(16) NOT NULL,
ImageDate DOUBLE NOT NULL,
Ordinal UINT ,
Random DOUBLE
);

CREATE UNIQUE NOT MODIFIABLE INDEX I_SResults_ImageID ON
T_SearchResults
(
ResultSetID ASC,
ImageID ASC
);

CREATE INDEX I_SResults_Random ON T_SearchResults
(
Random ASC
);


The following statements in a stored procedure are supposed to
randomize the results and then order them:

-- Insert random values to the result set records
UPDATE T_SearchResults SET Random = RAND() WHERE ResultSetID =
:ResultSetID;

-- Insert ordinals to the search results (for paging)
DECLARE rd UINT = 1;
DECLARE :randVal DOUBLE;
DECLARE ordCur CURSOR FOR SELECT Random FROM T_SearchResults WHERE
ResultSetID = :ResultSetID ORDER BY Random FOR UPDATE;
OPEN ordCur;
START TRANSACTION;

-- Loop through the ordered results and insert an ordinal
OrdLoop:
LOOP
FETCH NEXT FROM ordCur INTO :randVal;
IF SQLSTATE = '02000' THEN
LEAVE OrdLoop;
END IF;
UPDATE T_SearchResults SET Random = rd WHERE CURRENT OF ordCur;
SET rd = rd + 1;
END LOOP;

COMMIT;

CLOSE ordCur;


The result is that only the last record gets affected, all the previous
records return their random value (generated by the first UPDATE):

ResultSetID ImageID ImageDate Ordinal Random

originalResults ZBIS134000053-01 38293 150 0.468
....
originalResults ZBIS134000055-01 38282 170 0.879
originalResults ZBIS134000057-01 37992 578 0.11
originalResults ZBIS134000058-01 38002 562 584


(584 row(s) affected)

If I understand it correctly, you are selecting data, but then you are
updating the data in the selection and also used for the sorting
algorithm. From what I understand about the Microkernel, this would
cause a problem.

However, it DOES look like a valid SQL statement, so I would recommend
contacting Pervasive Software directly to log a bug report on this one.
You'll also want to do it soon -- the PSQLV8 database will enter
transition support soon (no new patches), so time is of the essence in
getting this reported and a possible fix out.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive.SQL Service & Support - March, 2006 ***
*** Chicago: Pervasive DataExchange Class - March, 2006 ***


Doron A wrote: