Question Re: database variable same as procedure name in Technology Q&A

database variable same as procedure name

Baron — Sun, 06 Feb 2022 17:24:22 GMT

Is this a bug in SQL Anywhere 17.0.9

create or replace database variable testvar bit default 0;
create or replace function testvar()
returns bit
begin
    if testvar = 1 then
        return(1);
    else
        return(0);
    end if;
end;
set testvar = 1;
select testvar;
select testvar();--why this delivers 0?

Re: database variable same as procedure name

VolkerBarth — Mon, 07 Feb 2022 03:05:09 GMT

Odd, seems to be a bug with 17.0.11.6800, too.

The behaviour is as expected when the database variable is qualified within the function:

create or replace database variable dba.testvar bit default 0;
create or replace function dba.testvar()
returns bit
begin
    if dba.testvar = 1 then
        return(1);
    else
        return(0);
    end if;
end;
set testvar = 1;
select testvar;
select testvar(); return 1 as expected

Re: database variable same as procedure name

Breck_Carter — Mon, 07 Feb 2022 08:35:37 GMT

It seems to affect a connection-level variable in version 16 as well.

create or replace variable testvar bit default 0;
create or replace function testvar()
returns bit 
begin
    if testvar = 1 then
        return(1);
    else
        return(0);
    end if;
end;
set testvar = 1;
select @@version, testvar, testvar();
@@version,testvar,testvar()
'16.0.0.2512',true,false
@@version,testvar,testvar()
'17.0.9.4882',true,false