Set the fixed and documented check order for WHERE clause and other conditional sentences [CORE1971]

[firebird-automations]

Submitted by: Dmitry Gogol (dmitrygogol)

Is related to QA350

CREATE TABLE T_LINKS (
LINK_TYPE INTEGER,
RIGHT_ID INTEGER,
PROP_VALUE VARCHAR(1024)
);

INSERT INTO T_LINKS (LINK_TYPE,RIGHT_ID,PROP_VALUE) VALUES(2,161,'2001');
INSERT INTO T_LINKS (LINK_TYPE,RIGHT_ID,PROP_VALUE) VALUES(2,161,'2002');
INSERT INTO T_LINKS (LINK_TYPE,RIGHT_ID,PROP_VALUE) VALUES(2,161,'2003');
INSERT INTO T_LINKS (LINK_TYPE,RIGHT_ID,PROP_VALUE) VALUES(10,161,'ANY STRING');

Fisrt query:
SELECT * FROM T_LINKS
WHERE (RIGHT_ID=161 AND LINK_TYPE=2) AND CAST(PROP_VALUE AS INTEGER)<>2001

Second query:
SELECT * FROM T_LINKS
WHERE CAST(PROP_VALUE AS INTEGER)<>2001 AND (RIGHT_ID=161 AND LINK_TYPE=2)

On Firebird SS build 17798 for Win32 (source Firebird-2.1.0.17798_0_Win32.exe) both queries returns the same result.

On Firebird SS build 17798 for Linux (source FirebirdSS-2.1.0.17798-0.nptl.i686.rpm) second query returns correct result, but first query returns the error:
Overflow occurred during data type conversion.
conversion error from string "Any string"

Error presents even adds index, F.E.:
CREATE INDEX LINKS_RIGHT_ID
ON T_LINKS (RIGHT_ID);

Commits: 1d817af 9b90f01 99a17e9

[firebird-automations]

Commented by: Smirnoff Serg (wildsery)

Translate from english to english :)
"Set the fixed and documented check order for WHERE clause and other conditional sentences"

[firebird-automations]

Commented by: Dmitry Gogol (dmitrygogol)

>> Translate from english to english
:-)

I have said at gmane.comp.db.firebird.russian : "I'll go to wake up my russian/non-russian speller". It has not woken up. ;-)

[firebird-automations]

Modified by: Dmitry Gogol (dmitrygogol)

summary: Order of check of conditions in query depends on the build version => Set the fixed and documented check order for WHERE clause and other conditional sentences

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Modified by: @dyemanov

status: Open [ 1 ] => Open [ 1 ]

Fix Version: 2.5 Beta 1 [ 10251 ]

[firebird-automations]

Commented by: @dyemanov

I've tried your sample, but the truth is a bit different. On Windows, the second query raises the conversion error.

[firebird-automations]

Commented by: @pmakowski

Under MacOsX :
ISQL Version: UI-V2.1.1.17910 Firebird 2.1
Server version:
Firebird/Darwin/Intel64 (access method), version "UI-V2.1.1.17910 Firebird 2.1"
Firebird/Darwin/Intel64 (remote server), version "UI-V2.1.1.17910 Firebird 2.1/tcp (callandor)/P11"
Firebird/Darwin/Intel64 (remote interface), version "UI-V2.1.1.17910 Firebird 2.1/tcp (callandor)/P11"
on disk structure version 11.1
SQL> CREATE TABLE T_LINKS (
CON> LINK_TYPE INTEGER,
CON> RIGHT_ID INTEGER,
CON> PROP_VALUE VARCHAR(1024)
CON> );
SQL> INSERT INTO T_LINKS (LINK_TYPE,RIGHT_ID,PROP_VALUE) VALUES(2,161,'2001');
SQL> INSERT INTO T_LINKS (LINK_TYPE,RIGHT_ID,PROP_VALUE) VALUES(2,161,'2002');
SQL> INSERT INTO T_LINKS (LINK_TYPE,RIGHT_ID,PROP_VALUE) VALUES(2,161,'2003');
SQL> INSERT INTO T_LINKS (LINK_TYPE,RIGHT_ID,PROP_VALUE) VALUES(10,161,'ANY STRING');
SQL> commit;
SQL> SELECT * FROM T_LINKS
CON> WHERE (RIGHT_ID=161 AND LINK_TYPE=2) AND CAST(PROP_VALUE AS INTEGER)<>2001;

LINK_TYPE RIGHT_ID PROP_VALUE
============ ============ ===============================================================================
2 161 2002
2 161 2003
Statement failed, SQLCODE = -413
conversion error from string "ANY STRING"
SQL> SELECT * FROM T_LINKS
CON> WHERE CAST(PROP_VALUE AS INTEGER)<>2001 AND (RIGHT_ID=161 AND LINK_TYPE=2)
CON> ;

LINK_TYPE RIGHT_ID PROP_VALUE
============ ============ ===============================================================================
2 161 2002
2 161 2003

SQL>

[firebird-automations]

Commented by: Dmitry Gogol (dmitrygogol)

To Dmitry Yemanov:
>I've tried your sample, but the truth is a bit different

You are right. I didn't check my sample. Just wrote it. When I checked my real database I have not made full fetch. Sorry. Confused.

[firebird-automations]

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

[firebird-automations]

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

[firebird-automations]

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

[firebird-automations]

Modified by: @dyemanov

Fix Version: 2.5 RC1 [ 10300 ]

Fix Version: 2.5 Beta 1 [ 10251 ] =>

[firebird-automations]

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

[firebird-automations]

Commented by: @dyemanov

The new predicate evaluation order is always from left to right.

[firebird-automations]

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA350 [ QA350 ]

[firebird-automations]

Commented by: @pcisar

QA test added.

[firebird-automations]

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test => Done successfully