Issue Details (XML | Word | Printable)

Key: CORE-1971
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Dmitry Gogol
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 01/Jul/08 02:13 AM   Updated: 25/Apr/11 02:31 PM
Component/s: Build Issues / Porting
Affects Version/s: 2.1.0
Fix Version/s: 2.5 Beta 2

Time Tracking:
Not Specified

Environment:
WinXP Sp2, CentOS 5.1
Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
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);


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Smirnoff Serg added a comment - 01/Jul/08 05:11 AM
Translate from english to english :)
"Set the fixed and documented check order for WHERE clause and other conditional sentences"

Dmitry Gogol added a comment - 01/Jul/08 05:52 AM
>> 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. ;-)

Dmitry Yemanov added a comment - 02/Jul/08 07:58 AM - edited
I've tried your sample, but the truth is a bit different. On Windows, the second query raises the conversion error.

Philippe Makowski added a comment - 02/Jul/08 08:15 AM
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>

Dmitry Gogol added a comment - 03/Jul/08 01:25 AM - edited
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.

Dmitry Yemanov added a comment - 12/Jul/09 06:32 AM
The new predicate evaluation order is always from left to right.

Pavel Cisar added a comment - 25/Apr/11 02:31 PM
QA test added.