Watch, Follow, &
Connect with Us
Public Report
Report From: Delphi-BCB/Database/DBExpress    [ Add a report in this area ]  
Report #:  121795   Status: Resolved
DBCommon.GetIndexForOrderBy may cause "List Index Out of Bounds (-1)" error
Project:  Delphi Build #:  XE, ... XE5
Version:    19.0 Submitted By:   Eden WU
Report Type:  Issue Date Reported:  1/16/2014 7:18:28 AM
Severity:    Infrequently encountered problem Last Updated: 8/5/2015 8:22:17 AM
Platform:    All versions Internal Tracking #:   65224
Resolution: Fixed (Resolution Comments) Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: None
Description
My SQLQuery.CommandText is :
-------
SELECT * FROM book WHERE 1=1 ORDER BY book.ISBN LIMIT 0
-------
But, I get the Error msg: List Index Out of Bounds (-1) when ClientDataSet1.Open.

Because this problem is due to the standard DBCommon.GetIndexForOrderBy method, and the parser doesn't take into account the necessity of comma between columns in the OrderBy section, since everything, that is after OrderBy in the parser is defined as fields, and when attempting to find a field by the 0 index, when calling

LastField := DataSet.Fields[FieldIndex - 1].FieldName

(in this case, FieldIndex = 0), you get a corresponding error.

Delphi XE 15.0.3953.35171
dbExpress for PostgreSQL V3.3.4.0
Steps to Reproduce:
-------
  create table book (
   ISBN character varying(12) primary key
  )
-------

1. Drop TSQLConnection
2. Drop TSQLQuery, set SQLConnection property to SQLConnection1
3. Drop TDataSetProvider, set DataSet property to SQLQuery1
4. Drop TClientDataSet, set Provider property to DataSetProvider1
5. SQLQuery.CommandText := 'SELECT * FROM book WHERE 1=1 ORDER BY book.ISBN LIMIT 0'
6.
------------
procedure TForm1.Button1Click(Sender: TObject);
begin
  ClientDataSet1.Close();
  ClientDataSet1.Open();
end;
------------

You can get the error: "List Index Out of Bounds (-1)"
Workarounds
None
Attachment
ERR_DEMOs.zip
Comments

Eden WU at 1/16/2014 4:56:15 PM -
BEFORE:
function GetIndexForOrderBy(const SQL: WideString; DataSet: TDataSet): TIndexDef;
begin
...
            stNumber:
            begin
              FieldIndex := StrToInt(Token);
              if DataSet.FieldCount >= FieldIndex then
                LastField := DataSet.Fields[FieldIndex - 1].FieldName else
...
end

AFTER:
function GetIndexForOrderBy(const SQL: WideString; DataSet: TDataSet): TIndexDef;
begin
...
            stNumber:
            begin
              FieldIndex := StrToInt(Token);
              if FieldIndex = 0 then FieldIndex := 1;  // <- Add the code.
              if DataSet.FieldCount >= FieldIndex then
...
end

Fix the code can work, but I don't know it is correct...

Tomohiro Takahashi at 1/16/2014 5:58:11 PM -
Could you please attach sample proejct to reproduce/confirm your issue?

> dbExpress for PostgreSQL V3.3.4.0
Is it a 3rd-party driver for Delphi XE?

Eden WU at 1/16/2014 7:01:30 PM -
Demo Project:
https://db.tt/V7pbNpXy

dbExpress for PostgreSQL
http://www.devart.com/dbx/postgresql/download.html

Tomohiro Takahashi at 1/16/2014 8:06:56 PM -
I attached the .zip file to this report instead of you.

So, does your issue occur only with PostgreSQL w/ the driver?
Does your issue occur with other RDBMSs(e.g., MySQL, InterBase) w/ Embarcadero's dbExpress driver?

Eden WU at 1/16/2014 9:43:03 PM -
Yes, I try the mysql-5.1.73-win32.msi and Embarcadero's dbExpress driver.

http://dev.mysql.com/downloads/file.php?id=450448

Get the same Error Msg!!!

The MySQL DEMO:
https://db.tt/RzqRzpVY

Tomohiro Takahashi at 1/17/2014 12:01:56 AM -
ok, so could you please re-attach sample project for MySQL?

Eden WU at 1/17/2014 12:28:58 AM -
The MySQL DEMO:
https://db.tt/RzqRzpVY

Eden WU at 1/17/2014 4:19:49 PM -
This problem occurs only in dbExpress when using the LIMIT construct (or any other, that follows ORDER BY).

"SELECT TOP 0 * FROM book ORDER BY ISBN"

Since in the SQL Server query is a "clean" ORDER BY, so the problem doesn't occur.

Eden WU at 1/16/2014 9:57:54 PM -
I to try ADODataSet(PostgreSQL connection with ODBC) + DataSetProvider + ClientDataSet

It's work, don't show any error message.

But, I need dbExpress!

Tomohiro Takahashi at 1/18/2014 9:28:23 PM -
Does your issue occur only when using 'LIMIT 0' for PostgreSQL, 'TOP 0' for MySQL ?

Eden WU at 1/18/2014 9:35:46 PM -
No!
SQL "LIMIT" Statement is only for MySQL and PostgreSQL.

Could you not testing this problem?

Tomohiro Takahashi at 1/19/2014 5:28:36 PM -
With empty table, I can reproduce the error with InterBase using ROWS 0 clause like this.
"SELECT * FROM book ORDER BY ISBN ROWS 0".

But, with the same empty table, if I use ROWS 1 as below, I do not get the error.
"SELECT * FROM book ORDER BY ISBN ROWS 1".

So, as a possible workaround, could you try LIMIT 1 or TOP 1 or so?

Eden WU at 1/19/2014 7:59:27 PM -
SELECT * FROM book WHERE 1=1 ORDER BY book.isbn LIMIT 0 <-- ERROR

SELECT * FROM book WHERE 1=0 ORDER BY book.isbn LIMIT 0 <-- OK

Tomohiro Takahashi at 1/19/2014 8:24:54 PM -
What about this one?
  SELECT * FROM book WHERE 1=1 ORDER BY book.isbn LIMIT 1

Eden WU at 1/19/2014 8:35:07 PM -
This is correct.
But, I don't used the SQL Statements, my table is not empty.

Server Response from: ETNACODE01