--- In firebird-support@yahoogroups.com, "Plamen Krastev"
<plamenkrastev@...> wrote:

>
> Hi,
>
> When I try to do SELECT like this:
>
> SELECT *
> FROM ITEM
> ORDER BY ITMFULLNAME;
>
> it is successful, but when I try to do this:
>
> SELECT *
> FROM ITEM
> ORDER BY ITMLEAFNAME;
>
> I receive error message:
>
> ISC ERROR CODE:335544344
>
> ISC ERROR MESSAGE:
> I/O error for file "C:\WINDOWS\system32\WINDOWS\TEMP\fb_sort_x2l31t"

That is a strange Temp folder location. Have you set this in
Firebird.conf?

How much free space is on C drive?

Does the firebird user have file system permissions to work in that
folder?

> Error while trying to open file
> The system cannot find the file specified.

Is there some other process 'cleaning out' that folder?

>
> STATEMENT:
> TIBOInternalDataset: "<TApplication>.frmMain.d
>
> Here is the table definition:
>
> /* Table: ITEM, Owner: SYSDBA */
>
> CREATE TABLE "ITEM"
> (
> "IDITEM" INTEGER NOT NULL,
> "ITMPARENT" INTEGER,
> "ITMLEAFNAME" VARCHAR(60),
> "ITMFULLNAME" VARCHAR(120),
> "ITMTYPE" VARCHAR(10),
> "ITMISGROUP" CHAR(1),
> "ITMMEASURE" VARCHAR(15),
> "IDACCITEM" INTEGER,
> CONSTRAINT "ITEM_PRIMARY" PRIMARY KEY ("IDITEM")
> );
>
> /* Index definitions for ITEM */
>
> CREATE INDEX "IDACCITEM" ON "ITEM"("IDACCITEM");
> CREATE INDEX "IDX_ITEM_1" ON "ITEM"("ITMFULLNAME");
> CREATE UNIQUE INDEX "ITMLEAFLEVELIDX"
> ON "ITEM"("ITMPARENT", "ITMLEAFNAME");
> CREATE INDEX "ITMPARENT" ON "ITEM"("ITMPARENT");
> ALTER TABLE "ITEM" ADD CONSTRAINT "RIHIERARCHY" FOREIGN KEY
> ("ITMPARENT")
> REFERENCES "ITEM" ("IDITEM");
>
> The obvious thing is that in first case SELECT is ORDERED BY indexed
> column,
> while in second case there is no such index.

In the first case, it is doing an indexed walk through the results. In
the second case, it can't because no such index. Note that an indexed
walk is often slower to return ALL the records, although it is MUCH
quicker to return the first record.

>
> Is that the reason for the error, and is there any workaround
> different than
> indexing on columns I want to use in ORDER BY clause ?
>
> Additional information:
>
> I made the test with IBOConsole, operating system Windows XP Home
> Edition
> SP2, Firebird 2.0 RC3, table contains 934 records.

Unless something has been announced that I am not aware, RC3 specific
questions are not yet appropriate for this group. Run your test on FB
1.5.3, if you have the same problem, then feel free to ask more. If it
is apparently a bug in FB2, then follow the instructions in the
release notes for reporting it!

Adam