Commented by: @jasonwharton

I was able to see this error in my regression testing app for IB Objects.

Here is my setup:

CREATE TABLE COUNT_TEST (
ID INTEGER NOT NULL,
COL1 VARCHAR(10) NOT NULL,
COL2 CHAR(1),
CONSTRAINT PK_COUNT_TEST
PRIMARY KEY (ID));

// Populate with 1001 rows from ID 0 to 1000.

Q\.SQL\.Text := 'SELECT \* FROM COUNT\_TEST';
Q\.Open;
for ii := 0 to 1000 do
begin
  Q\.Insert;
  Q\.Fields\[0\]\.AsInteger := ii;
  Q\.Fields\[1\]\.AsString := IntToStr\( ii \);
  Q\.Post;
end;
Q\.Close;

// Executing this query seems fine:

SELECT ID, COL1, COL2
FROM COUNT_TEST
ROWS 2 TO 1

// Executing this query reliably reproduces this undesirable message:

SELECT ID, COL1, COL2
FROM COUNT_TEST
ROWS 200 TO 100

This is an excerpt from the IBO SQL trace showing the error when the query is executed:

/*---
PREPARE STATEMENT
TR_HANDLE = 221
STMT_HANDLE = 220
Dialect = 3

SELECT ID, COL1, COL2 FROM COUNT_TEST ROWS 200 TO 100

PLAN (COUNT_TEST NATURAL)

FIELDS = [ Version 1 SQLd 3 SQLn 30
COUNT_TEST.ID = <NIL> < LONG[!null] SubType: 0 Len: 4 Scale: 0 Data: <nil> >
COUNT_TEST.COL1 = <NIL> < VARYING[!null] SubType: 2 Len: 10 Scale: 0 Data: <nil> >
COUNT_TEST.COL2 = <NIL> < TEXT SubType: 2 Len: 1 Scale: 0 Data: <nil> > ]
----*/
/*===
//>>> STATEMENT PREPARED <<<//
TIB_Statement.API_Prepare()
TIB_Query: "Q" stHandle=220
====*/
//>>> STATEMENT PREPARED <<<//
TIB_Statement.SysAfterPrepare()
TIB_Query: "Q" stHandle=220
CharSet Information:
Field Name:COUNT_TEST.COL1 CharSet:ASCII|ASCII Collate:ASCII CharLen:10 ByteLen:10
Field Name:COUNT_TEST.COL2 CharSet:ASCII|ASCII Collate:ASCII CharLen:1 ByteLen:1
====*/
/*===
Execute Statement: <no name>.Q(TIB_Query)
Tran: <no name>.<no name>(TIB_Transaction)
====*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 105
STMT_HANDLE = 220
Dialect = 3
PARAMS = [ ]

ERRCODE = 335544817
----*/
/*---
INTERPRET BUFFER = Invalid parameter to FIRST. Only integers >= 0 are allowed.
MSG_LEN = 60
----*/
/*---
INTERPRET BUFFER =
MSG_LEN = 0
----*/
/*===
//>>> ERROR <<<//
ISC ERROR CODE:335544817

ISC ERROR MESSAGE:
Invalid parameter to FIRST. Only integers >= 0 are allowed.

STATEMENT:
<no name>.Q(TIB_Query)
stHandle=220 (ERROR)

//>>> SQL MESSAGE <<<//
SQL ERROR CODE:-204

SQL ERROR MESSAGE:
Undefined name
====*/

Commented by: @jasonwharton

Also, it seems this bug entry is addressing the confusing nature of the error message.
Please note, what I am addressing is far more serious. This is a failure to process a valid SQL.
There shouldn't be an error message at all, it should just return zero records.
I recommend either elevating the status and nature of this bug entry or that we make a new one of a much higher priority.

I'd like to hear from someone to acknowledge this and let me know what can/should be done about it.

Commented by: @dyemanov

I respectfully disagree regarding the "valid SQL". ROWS 200 TO 100 is a confusing construct. You expect it to return zero rows, someone else might expect it to return 100 rows in the reverse order starting with 200th row. The one who knows that it's just a syntax sugar for FIRST/SKIP understands it as -100 rows to be returned and both Firebird and the SQL standard treats negative values there as an error condition.

ROWS 2 TO 1 works incidentally, because it's transformed to FIRST 0 SKIP 1 and zero rows filter is allowed. This is just an implementation artefact that should not be treated as a common rule.

Commented by: @jasonwharton

Of course a person isn't going to write such a query adhoc, but that isn't the only situation to be dealt with.

For example, in code I rely upon the fact that such a condition will result in zero iterations in a for loop.

It should simply evaluate what is there and return zero records just as it does with the other example that doesn't complain.

What you are saying is that C++ or Delphi should raise a runtime exception if someone passes in such a situation to a for loop and that simply is not practical. Code becomes really messy to have to guard against such a situation when the simplest and most logical thing to do is to simply move on quietly with no action.

Commented by: @dyemanov

Unlike C++/Delphi, SQL is a declarative language, and SELECT is not a loop. They cannot be compared directly.

Commented by: @jasonwharton

I respectfully disagree with your ultimate conclusion to do nothing about this issue. I would much prefer a practical and common sense implementation than a complicated mess such as now exists. Quietly returning zero records is the best answer and thankfully that is what InterBase does. They got this one right.

Commented by: @dyemanov

I don't ultimately object to change the current behavior, but I don't agree in treating this issue as a showstopper. Your InterBase argument sounds important though. One of the reasons behind the ROWS syntax was the InterBase compatibility, so complying with their semantics might be a good idea.

Commented by: @jasonwharton

Apologies if I assumed you intended to do nothing about it.

I also agree this doesn't merit "showstopper" status. Perhaps Minor importance is fine.

Thus, we are back to my original question. Do we associate further with this misleading error message issue or should I go ahead and post a new issue that focuses on this more specific issue of not having the same behavior as exists with the InterBase handling of the ROWS clause?

Commented by: @dyemanov

I'd prefer to have both issues in the same ticket, as both are about ROWS not being just a syntax sugar for FIRST/SKIP but handling the arguments a bit differently.

Commented by: @jasonwharton

Ok, thanks for your attention.