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

Boolean 'IS' operator can raise 'Token unknown' error in execute statement (when immediately followed by named parameter) [CORE6041] #6291

Closed
firebird-automations opened this issue Apr 2, 2019 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

Following script:

select 1 as static_sql_result from rdb$database where true union all
select 2 as static_sql_result from rdb$database where true = true union all
select 3 as static_sql_result from rdb$database where true is true union all
select 4 as static_sql_result from rdb$database where true is distinct from false
;

set heading on;
set list on;
set echo on;
set term ^;
execute block returns( dynamic_sql_result int) as
begin
for execute statement ( 'select 1 from rdb$database where :param_b' ) ( param_b := true ) into dynamic_sql_result
do
suspend;
end
^

execute block returns( dynamic_sql_result int) as
begin
for execute statement ( 'select 2 from rdb$database where true = :param_b' ) ( param_b := true ) into dynamic_sql_result
do
suspend;
end
^

execute block returns( dynamic_sql_result int) as
begin
for execute statement ( 'select 3 from rdb$database where true IS :param_b' ) ( param_b := true ) into dynamic_sql_result -- <<<<<<< { NB }
do
suspend;

end
^

execute block returns( dynamic_sql_result int) as
begin
for execute statement ( 'select 4 from rdb$database where true IS distinct from :param_b' ) ( param_b := false ) into dynamic_sql_result
do
suspend;

end
^
set term ;^
commit;

-- will issue:

execute block returns( dynamic_sql_result int) as
begin
for execute statement ( 'select 3 from rdb$database where true IS :param_b' ) ( param_b := true ) into dynamic_sql_result
do
suspend;

end
^

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 42
-?
-At block line: 3, col: 5

Checked on: WI-V3.0.5.33115, WI-T4.0.0.1479

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

IIRC, the allowed predicates are: <value> IS TRUE, <value> IS FALSE and <value> IS UNKNOWN (and <value> IS NULL, of course). There's no such predicate as <value> IS <value>, thus parameters (as well as any value expressions) are not allowed for the right side.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> There's no such predicate as <value> IS <value>

execute statement ( 'select 4 from rdb$database where true IS distinct from :param_b' ) ( param_b := false )
-- passes OK.
Why "IS distinct from" is `better` that just "IS" ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Because it compares two values: <value> IS [NOT] DISTINCT FROM <value>. But you cannot compare with NULL using "IS :param", explicit IS [NOT] NULL is required. The same for TRUE | FALSE | UNKNOWN, they are kinda predefined "constants".

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

1 participant