Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Invalid data type for negation (minus operator) [CORE5395] #5668

Closed
firebird-issue-importer opened this issue Nov 15, 2016 · 21 comments
Closed

Comments

@firebird-issue-importer

Submitted by: verleon (verleon)

Attachments:
core-5395.diff

Votes: 7

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

Commits: 4ce50c0 93da6eb

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 15, 2016

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 15, 2016

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.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 15, 2016

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 ?

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 17, 2017

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?

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 17, 2017

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.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 12, 2017

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 29, 2017

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.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Oct 26, 2018

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 12, 2018

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 13, 2018

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.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 13, 2018

Modified by: @dyemanov

Attachment: core-5395.diff [ 13310 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 13, 2018

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 ?

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 13, 2018

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.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 13, 2018

Commented by: @hvlad

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 13, 2018

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.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 22, 2018

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.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 22, 2018

Modified by: @dyemanov

Fix Version: 3.0.5 [ 10885 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 23, 2018

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.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 25, 2018

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 9, 2018

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 9, 2018

Modified by: @pavel-zotov

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants