least/greatest with 'in' fails

ftobin

The use of least or greatest appears to have issues when combined with in and used in a function. The following function should always return true, but returns false if there is a second argument in the in.

create or replace function bizint.test(a int) return boolean
as begin
    return a in (least(a), 9);
end;

select bizint.test(5);
[ ]   (false)

The same happens with the use of greatest.

Interestingly, there s no issue if I switch to chars from ints. Even if I force everything to be an int inside the in, it still fails.

I'm on Vertica 10.0.1

SruthiA

I just tried it in 10.1.1-9 as well.. it is working fine.. Please find the repro below

[dbadmin@sru3 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

dbadmin=> select version();

version

Vertica Analytic Database v10.1.1-9
(1 row)

dbadmin=> create or replace function test(a int) return boolean
dbadmin-> as begin
dbadmin-> return a in (least(a), 9);
dbadmin-> end;
CREATE FUNCTION
dbadmin=> select test(5);

test

t
(1 row)

dbadmin=>

SruthiA

you are welcome. 10.0 support for bug fixes has ended on 31 May 2022. So for now, you can use workaround of casting to char until upgrade is completed.

https://www.microfocus.com/productlifecycle/?term=Vertica

SruthiA

I just tried it on 12.0 and it is working fine.. 10.0 version is out of support. could you please try it on 10.1.1 and above once to see if the issue reproduces?

dbadmin=> create or replace function test(a int) return boolean
dbadmin-> as begin
dbadmin-> return a in (least(a), 9);
dbadmin-> end;
CREATE FUNCTION
dbadmin=> select test(5);

test

t
(1 row)

dbadmin=> select version();

version

Vertica Analytic Database v12.0.0-0
(1 row)

dbadmin=>

ftobin

Ugh, thank you for testing with 10.1.1. Unfortunately upgrading is not a small task.