Issue Details (XML | Word | Printable)

Key: CORE-5395
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: verleon
Votes: 7
Watchers: 12
Operations

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

Invalid data type for negation (minus operator)

Created: 15/Nov/16 09:04 AM   Updated: 09/Dec/18 12:58 PM
Component/s: Engine
Affects Version/s: 3.0.1
Fix Version/s: 4.0 Beta 1, 3.0.5

File Attachments: 1. File core-5395.diff (2 kB)

Environment:
FB 3.0.1 (WI-V6.3.1.32609 Firebird 3.0) x64
Windows 7

QA Status: Done successfully


 Description  « Hide
meta:

CREATE TABLE TABLE1 (
    ID INTEGER
);

query:
 
select 1 from TABLE1 where id = - :param
 
result:
 
Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
Dynamic SQL Error.
expression evaluation not supported.
Invalid data type for negation (minus operator).
 
This problem is gone when using - (cast :param as integer) instead of - :param, but with FB 2.5 it was not necessary


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Simonov Denis added a comment - 15/Nov/16 09:10 AM
This regression is reproduced the same way and using the execute statement

execute block
returns (i int)
as
begin
  for execute statement ('select 1 from rdb$database where 1 = - :id')
      (id := -1)
      into :i
  do suspend;
end

Adriano dos Santos Fernandes added a comment - 15/Nov/16 03:20 PM
Saying 2.5 is right is half words. Would you consider this right?

SQL> select 1 from TABLE1 where id = 'a' || ?;

INPUT SQLDA version: 1 sqln: 10 sqld: 1
01: sqltype: 497 LONG Nullable sqlscale: 0 sqlsubtype: 0 sqllen: 4
  : name: (0) alias: (0)
  : table: (0) owner: (0)

Of course it's not.

It's very weird the way it infers a parameter datatype.

Vlad Khorsun added a comment - 15/Nov/16 07:32 PM
Adriano,


SQL> execute block returns (i int)
CON> as
CON> begin
CON> for execute statement ('select 1 from rdb$database where 1 = - :id')
CON> (id := -1)
CON> into :i
CON> do suspend;
CON> end
CON> ^

           I
============
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-expression evaluation not supported
-Invalid data type for negation (minus operator)
-At block line: 4, col: 3
SQL>
SQL> execute block returns (i int)
CON> as
CON> begin
CON> for execute statement ('select 1 from rdb$database where - :id = 1')
CON> (id := -1)
CON> into :i
CON> do suspend;
CON> end
CON> ^

           I
============
           1

do you consider it correct ?

verleon added a comment - 17/Mar/17 03:04 AM - edited
Hi!
After all - should I expect any activities about this issue or it considered closed and we have to live with it?

Sean Leyne added a comment - 17/Mar/17 01:26 PM
Verleon,

While it is an issue, there is a workaround...

select 1 from TABLE1 where id = (:param) * -1


As the other examples show, there is no simple fix for your issue. So, you may need to help yourself by using workarounds.

verleon added a comment - 29/Sep/17 04:08 AM
Sean Leyne,

In version 3.0.2., database in dialect 1, this query:
 
select 1 from TABLE1 where id = (:param) * -1

also results a bug:
 
Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
Dynamic SQL Error.
expression evaluation not supported.
Invalid data type for multiplication in dialect 1.

Evgeniy Shubenkov added a comment - 26/Oct/18 09:34 AM - edited
We have a similar problem that makes it very difficult to switch to Firebird 3.0.4 due to the large number of similar requests.

An example of a working query (Firebird 2.5.7):

CREATE TABLE PERERASHETCASE (
    LSHET INTEGER,
    FMONTH INTEGER,
    FYEAR INTEGER
);

select LSHET from PERERASHETCASE
where FMONTH + 12 * FYEAR
  between :BEGMONTH + 12 * :BEGYEAR and :ENDMONTH + 12 * :ENDYEAR

result error on Firebird 3.0.4:

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
Dynamic SQL Error.
expression evaluation not supported.
Invalid data type for multiplication in dialect 3.


But adding "cast (: BEGYEAR as integer)" for only ONE parameter solves the problem:

select LSHET from PERERASHETCASE
where FMONTH + 12 * FYEAR
  between :BEGMONTH + 12 * cast(:BEGYEAR as integer) and :ENDMONTH + 12 * :ENDYEAR

Dmitry Yemanov added a comment - 13/Nov/18 04:18 AM
> v2.5 have no MAKE_desc() call in this place, AFAIU

I had the same idea, but v2.5 has MAKE_desc() inside PASS1_set_parameter_type() for nod_parameter, so IMO removing it completely is not absolutely correct. As it's hard to pass the node through the setParameterType() call stack (now it passes only the descriptor), I tried a different approach, see the attached patch. It also fixes all known regressions, although looks somewhat overcomplicated if compared to yours ;-) Maybe Adriano could say whether it's suitable or not, in his opinion.

Dmitry Yemanov added a comment - 13/Nov/18 08:25 AM
Yep, I speak about that piece of code. MAKE_desc() should be called *only* for the node we're deriving the parameter datatype from. In v3, it's called for all nodes (both parts of comparison), including the part we haven't guessed yet, hence the errors reported in this ticket.

v2.5 was passing the node through the call stack to make deferred MAKE_desc() possible. v3 passes only the descriptor, so it has to call MAKE_desc() before that (otherwise node->nodDesc has unknown datatype) and we have the issue. Deferred evaluation of this descriptor is impossible without the node which is not accessible when ParameterNode is processed.

My patch stacks the current node until the parameter is processed, thus deferring MAKE_desc() call to its original location. In other words, if no parameters are found in one part of comparison (left or right), MAKE_desc() is not called for the opposite part (unlike what happens now). It's a simpler alternative to restoring the original logic which would mean to duplicate the setParameterType() method through all the ExprNode classes.

I don't insist it's 100% correct, it was a just quick attempt to fix, now subject for discussion.

Vlad Khorsun added a comment - 13/Nov/18 10:24 AM
Ok, i think we need Adriano's opinion to go forward

Adriano dos Santos Fernandes added a comment - 13/Nov/18 11:43 AM
I'd go with Dmitry's solution. I think a question remains, if we need to adjust the "if (!desc)" to also check for "!dsqlScratch->paramTypeNodes.hasData()", as it seems it's what the original code does.

Dmitry Yemanov added a comment - 22/Nov/18 10:46 AM
Fix is committed into v3.0.5, please test the next (tomorrow's) snapshot build and report back. Patch for v4 will follow shortly.

Evgeniy Shubenkov added a comment - 23/Nov/18 02:46 PM
Tested successfully on Firebird-3.0.5.33083-0_x64.
Now the transition to Firebird 3 is complete for us.
Thanks so much.