least/greatest with 'in' fails

ftobinftobin Vertica Customer

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

Tagged:

Best Answers

  • SruthiASruthiA Vertica Employee Administrator
    Answer ✓

    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=>

  • SruthiASruthiA Vertica Employee Administrator
    Answer ✓

    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

Answers

  • SruthiASruthiA Vertica Employee Administrator

    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=>

  • ftobinftobin Vertica Customer

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file