Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

least/greatest with 'in' fails

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 Employee
    Answer ✓

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

    [dbad[email protected] ~]$ 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 Employee
    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

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

  • 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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.