You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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;
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.
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" ?
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".
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
The text was updated successfully, but these errors were encountered: