Skip to content

[#539] Implement COALESCE built-in function

Jynn Nelson requested to merge jyn514/YDBOcto:coalesce into master
  • Generates plans of the following structure:
LP_WHERE:
  LP_COALESCE_CALL:
    LP_COLUMN_LIST:
      LP_VALUE: '1'
      LP_COLUMN_LIST:
        LP_VALUE: '2'
        LP_COLUMN_LIST:
          LP_VALUE: '3'
          LP_COLUMN_LIST:
            LP_VALUE: ''
  • Generates M templates that look like the following:
;; This is a generated file; do not modify. Generated M code corresponds to below SQL query
;; ---------------------------------------------------------
;  select coalesce(1, 2, 3, NULL);
;; ---------------------------------------------------------

octoPlan0(cursorId)
    DO octoPlan1(cursorId)
    QUIT

octoPlan1(cursorId)
    NEW %ydboctoz KILL %ydboctocursor(cursorId,"keys",3,"","")
    SET %ydboctocursor(cursorId,"keys",1,"OCTOONEROWTABLE","ID")=""
    FOR  SET %ydboctocursor(cursorId,"keys",1,"OCTOONEROWTABLE","ID")=$ORDER(^%ydboctoocto("tables","octoOneRow",%ydboctocursor(cursorId,"keys",1,"OCTOONEROWTABLE","ID"))) QUIT:(%ydboctocursor(cursorId,"keys",1,"OCTOONEROWTABLE","ID")="")  DO
    . NEW %ydboctobool SET %ydboctobool=1
    . IF %ydboctobool DO
    . . SET:1&$INCREMENT(%ydboctocursor(cursorId,"keys",3,"","")) %ydboctoz=42
    . . SET %ydboctocursor(cursorId,"keys",3,"","",%ydboctocursor(cursorId,"keys",3,"",""))=$$str2mval^%ydboctoplanhelpers($SELECT('$ZYISSQLNULL((+$GET(%ydboctocursor(cursorId,"parameters",1)))):((+$GET(%ydboctocursor(cursorId,"parameters",1)))),'$ZYISSQLNULL((+$GET(%ydboctocursor(cursorId,"parameters",2)))):((+$GET(%ydboctocursor(cursorId,"parameters",2)))),'$ZYISSQLNULL((+$GET(%ydboctocursor(cursorId,"parameters",3)))):((+$GET(%ydboctocursor(cursorId,"parameters",3)))),'$ZYISSQLNULL($ZYSQLNULL):($ZYSQLNULL),1:$ZYSQLNULL))
    QUIT
  • Add tests for COALESCE
  • Add documentation for COALESCE
  • Fix outref files

The files were updated using the following command:

set reffilelist = `find bats-test.* -name "*.ref"`
foreach file ($reffilelist)
        set batsdir = $file:h:h
        set reffile = $file:t
        echo "cp $batsdir/clean_output.txt ../tests/outref/$reffile"
end

I verified the changes were correct using the following command:

git diff --name-only | while IFS= read -r file; do diff <(sort "$file") <(sort <(git show "HEAD:$file")) >/dev/null || echo "$file"; done
  • Add typecasts so that tests will run on CentOS 7
  • Do not handle COALESCE in functions that will not encounter it

{de,}compress_statment should never encounter a COALESCE statement, so it is fine to fall through and give an assertion error.

  • Group coalesce and case_expression into conditional_expression
  • Use consistent order for cases
  • Test NULL with mismatched types
  • Name tests conditional_expression_functions since more functions like this will be added in the future
  • Print COALESCE for column names
Edited by Jynn Nelson

Merge request reports