Cannot getting empty result in case

mgvertica

I cannot get empty result in case and it returns NULL result:

SELECT CASE WHEN ('test' IS NULL) THEN (SELECT 'null_result') ELSE (SELECT 'empty_result' WHERE 1<>1) END;

swalkaus

The literal string value 'empty_result' won't be returned because the corresponding predicate ("WHERE 1<>1") evaluates to false so I assume you're asking why one row is returned instead of zero rows.

If you ask the optimizer to explain your query ("EXPLAIN SELECT CASE ...") you'll see that the query is re-written as a cross join between two paths that both scan a "special" system table named 'dual'. You can find more information about that table here - https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/DUAL.htm.

The path of the cross join that corresponds to the predicate "WHERE 1<>1" returns zero rows - since 1 is always equal to 1. The other path returns that one special row from dual. The result of the join is that one row is emitted that has a column value which is NULL.

mgvertica

https://stackoverflow.com/questions/56372462/how-to-get-empty-result-set-with-a-case-statement