[#CORE-1971] Set the fixed and documented check order for WHERE clause and other conditional sentences

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:

« Hide

WinXP Sp2, CentOS 5.1

Issue Links:

Relate

This issue is related to:

~~QA-350~~ Test for CORE-1971

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:

[ Permalink | « Hide ]

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"

[ Permalink | « Hide ]

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

[ Permalink | « Hide ]

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.

[ Permalink | « Hide ]

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>

[ Show » ]

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>

[ Permalink | « Hide ]

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.