Commented by: @sim1984

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

Commented by: @asfernandes

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.

Commented by: @hvlad

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 ?

Commented by: verleon (verleon)

Hi!
After all - should I expect any activities about this issue or it considered closed and we have to live with it?

Commented by: Sean Leyne (seanleyne)

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.

Commented by: verleon (verleon)

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.

Commented by: Evgeniy Shubenkov (e.shubenkov)

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

Commented by: @hvlad

Adriano,

what do you think about following attempt to fix (regression, at least)

diff --git a/src/dsql/pass1.cpp b/src/dsql/pass1.cpp
index 23d5162547..a5ce442a34 100644
--- a/src/dsql/pass1.cpp
+++ b/src/dsql/pass1.cpp
@@ -2911,7 +2911,7 @@ bool PASS1_set_parameter_type(DsqlCompilerScratch* dsqlScratch, ValueExprNode* i
if (!inNode)
return false;

- MAKE_desc(dsqlScratch, &node->nodDesc, node);
return inNode->setParameterType(dsqlScratch, &node->nodDesc, force_varchar);
}

v2.5 have no MAKE_desc() call in this place, AFAIU

See quick comparison below:

a) with no fix

SQL> set sqlda_display on;
SQL>
SQL>
SQL>
SQL> select 1 from rdb$database where rdb$relation_id between ? + 3*? and ?
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-expression evaluation not supported
-Invalid data type for multiplication in dialect 3
SQL>
SQL>
SQL> select 1 from rdb$database where rdb$relation_id = ? * -1;
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-expression evaluation not supported
-Invalid data type for multiplication in dialect 3
SQL>
SQL>
SQL>
SQL> select 1 from rdb$database where rdb$relation_id = (?) * -1;
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-expression evaluation not supported
-Invalid data type for multiplication in dialect 3
SQL>
SQL>
SQL>
SQL> select 1 from rdb$database where rdb$relation_id = ? || 'n';

INPUT message field count: 1
01: sqltype: 500 SHORT Nullable scale: 0 subtype: 0 len: 2
: name: alias:
: table: owner:

OUTPUT message field count: 1
01: sqltype: 496 LONG scale: 0 subtype: 0 len: 4
: name: CONSTANT alias: CONSTANT
: table: owner:
Statement failed, SQLSTATE = 07002
Dynamic SQL Error
-SQLDA error
-No SQLDA for input values provided

b) with a fix

SQL> set sqlda_display on;
SQL>
SQL> select 1 from rdb$database where rdb$relation_id between ? + 3*? and ?;

INPUT message field count: 3
01: sqltype: 500 SHORT Nullable scale: 0 subtype: 0 len: 2
: name: alias:
: table: owner:
02: sqltype: 500 SHORT Nullable scale: 0 subtype: 0 len: 2
: name: alias:
: table: owner:
03: sqltype: 500 SHORT Nullable scale: 0 subtype: 0 len: 2
: name: alias:
: table: owner:

OUTPUT message field count: 1
01: sqltype: 496 LONG scale: 0 subtype: 0 len: 4
: name: CONSTANT alias: CONSTANT
: table: owner:
Statement failed, SQLSTATE = 07002
Dynamic SQL Error
-SQLDA error
-No SQLDA for input values provided
SQL>
SQL>
SQL>
SQL> select 1 from rdb$database where rdb$relation_id = ? * -1;

INPUT message field count: 1
01: sqltype: 500 SHORT Nullable scale: 0 subtype: 0 len: 2
: name: alias:
: table: owner:

OUTPUT message field count: 1
01: sqltype: 496 LONG scale: 0 subtype: 0 len: 4
: name: CONSTANT alias: CONSTANT
: table: owner:
Statement failed, SQLSTATE = 07002
Dynamic SQL Error
-SQLDA error
-No SQLDA for input values provided
SQL>
SQL>
SQL>
SQL> select 1 from rdb$database where rdb$relation_id = (?) * -1;

INPUT message field count: 1
01: sqltype: 500 SHORT Nullable scale: 0 subtype: 0 len: 2
: name: alias:
: table: owner:

OUTPUT message field count: 1
01: sqltype: 496 LONG scale: 0 subtype: 0 len: 4
: name: CONSTANT alias: CONSTANT
: table: owner:
Statement failed, SQLSTATE = 07002
Dynamic SQL Error
-SQLDA error
-No SQLDA for input values provided
SQL>
SQL>
SQL>
SQL> select 1 from rdb$database where rdb$relation_id = ? || 'n';

INPUT message field count: 1
01: sqltype: 500 SHORT Nullable scale: 0 subtype: 0 len: 2
: name: alias:
: table: owner:

OUTPUT message field count: 1
01: sqltype: 496 LONG scale: 0 subtype: 0 len: 4
: name: CONSTANT alias: CONSTANT
: table: owner:
Statement failed, SQLSTATE = 07002
Dynamic SQL Error
-SQLDA error
-No SQLDA for input values provided

I.e. it fixed all issues with arithmetic operations and not changed data type for concatenation

Commented by: @dyemanov

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

Commented by: @hvlad

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

Do you speak about this code

v2.5
---------
static bool set_parameter_type(CompiledStatement* statement, dsql_nod* in_node,
dsql_nod* node, bool force_varchar)
{
...
case nod_parameter:
{
if (!node)
in_node->nod_desc.makeNullString();
else
{
MAKE_desc(statement, &in_node->nod_desc, node, NULL);

				if \(att\-\>att\_charset \!= CS\_NONE && att\-\>att\_charset \!= CS\_BINARY\)
				\{

---------

v3
---------
bool ParameterNode::setParameterType(DsqlCompilerScratch* dsqlScratch,
const dsc* desc, bool forceVarChar)
{
...
if (!desc)
dsqlParameter->par_desc.makeNullString();
else
{
dsqlParameter->par_desc = *desc;

	if \(tdbb\-\>getCharSet\(\) \!= CS\_NONE && tdbb\-\>getCharSet\(\) \!= CS\_BINARY\)
	\{

---------

If yes, i think it is more or less the same. Note, "desc" in ParameterNode::setParameterType is the node->nodDesc passed at

bool PASS1_set_parameter_type(DsqlCompilerScratch* dsqlScratch, ValueExprNode* inNode,
ValueExprNode* node, bool force_varchar)
{
if (!inNode)
return false;

return inNode\-\>setParameterType\(dsqlScratch, &node\-\>nodDesc, force\_varchar\);

}

and your patch just assign the same value to it.

Am I wrong ?

Commented by: @dyemanov

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.

Commented by: @hvlad

Ok, i think we need Adriano's opinion to go forward

Commented by: @asfernandes

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.

Commented by: @dyemanov

Fix is committed into v3.0.5, please test the next (tomorrow's) snapshot build and report back. Patch for v4 will follow shortly.

Commented by: Evgeniy Shubenkov (e.shubenkov)

Tested successfully on Firebird-3.0.5.33083-0_x64.
Now the transition to Firebird 3 is complete for us.
Thanks so much.