[#539] Implement COALESCE built-in function
- 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