At 01:08 AM 13/07/2006, you 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"
>Error while trying to open file
>The system cannot find the file specified.

OK, the error is occurring because the engine is finding (or
calculating) an invalid path to your temporary directory (unless you
really DO have such a directory on your server, of course!)

So - check your TempDirectories entry in firebird.conf first.

On the other hand, you might be using the wrong message file for the
server you have installed, so that the error interpreter is sending
the wrong message for an invalid ORDER BY specification (see below).

>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")
>);

Here is your query:
SELECT *
FROM ITEM
ORDER BY ITMLEAFNAME;

Because of the quoted identifiers, the "safe" syntax should be:

SELECT *
FROM ITEM
ORDER BY "ITMLEAFNAME";

However, the parsing rules say that you can omit the double quotes if
the identifier is all upper case (as it is here).

So - if it is the case of wrong message interpretation, this could be
a parser bug. Still, if you are using an old Interbase client as
well as an old message file that is accessible by that old client,
the scrambling might well be due to that alone.

As a reality check, I'd suggest the following:-

1. Double check to discover what client IBO is loading and, if
necessary, correct it.

2. If there is an old firebird.msg or interbase.msg file in the
system path, move or rename it.

3. Check the TempDirectories configuration to make sure that it is a
valid path.

>The obvious thing is that in first case SELECT is ORDERED BY indexed
>column,
>while in second case there is no such index.

The underlying optimisation method is different for indexed and
non-indexed columns. It is remotely possible that some byway of code
is misbehaving under these conditions.

>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.

Don't use IBOConsole for testing Fb 2.0 as it will not support the
underlying changes in Firebird 2.0. For your continued
testing/verification you should use only the isql binary that is
installed in Fb 2.0 RC3.

It is not a workaround you need, but a solution. Firebird 2.0 RC3 is
beta software so, at this point, you are a field-tester.

Check and double check to verify what your application is accessing
when this problem occurs. If you don't resolve it, please post your
problem and a reproducible test case to the Tracker, or to
firebird-devel, high priority.

./heLen