Other special values besides NULL?

Pieter_Sheth-Vo

Are there other special values like NULL in Vertica SQL ?

NULL is nice in that data columns of any type can have NULL values.
We can interpret NULL as the concept of "N/A" (i.e. missing, not available, not asked, not answered, etc.).
I would also like to represent another concept, the idea of Overall, in the same result set.

Is there another special value that I can return in a query, other than NULL, but which like NULL:

• can be returned in any type of column integer, float, string, date, etc.
• doesn't match any actual finite value that might be in the data
• doesn't throw ERROR: For 'UNION', types varchar and numeric are inconsistent in this toy query:

select 1 as numeral, 'one' as name
union all 
select 2 as numeral, 'two' as name
union all 
select NULL as numeral, NULL as name
union all 
select ??? as numeral, ??? as name;

https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/LanguageElements/Literals/Number-typeLiterals.htm

mosheg

NaN (Not a Number) does not equal anything, not even another NaN.
You can query for them using the predicate … WHERE column != column
See: https://www.vertica.com/blog/vertica-quick-tip-query-nan-values

create table NaN_Test (c1 float);
insert into NaN_Test select 'NaN';
select * from NaN_Test;
c1
-----
NaN
(1 row)

select * from NaN_Test where c1 = 'NaN';
c1
----
(0 rows)

select * from NaN_Test where c1 != c1;
c1
-----
NaN
(1 row)

Pieter_Sheth-Vo

@mosheg

Circling back with new words from exploration, my actual goal was to calculate COUNT/GROUP BY with rollups and differentiate counts of actual NULL values from rollups across values. For this the Vertica ROLLUP(x1, x2, ...) feature is _almost_ perfect.

But there are three complications in the toy example below:

• the BY variables are of unknown possibly mixed types
• I'd like to differentiate true NULL values from rollups but they both return as NULL
• I'd like to merge the results back but you can't merge on NULL values

Where I'm netting out is that it's practical if not pretty to:

• cast all by columns to VARCHAR to avoid type conflicts
• coalesce all NULL values to a special string to differentiate rollups from true nulls
• use the vSQL syntax GROUP BY ROLLUP(1,2,3...)
• on return, interpret NULL as a rollup value and the special string as a NULL

WITH 
    base AS (
        SELECT 1 as numeral, 'one' AS name UNION ALL
        SELECT 2 as numeral, 'two' AS name UNION ALL
        SELECT NULL as numeral, NULL AS name
    ),
    summed AS (
        SELECT numeral, name, GROUPING_ID(),count(*) 
        FROM base
        GROUP BY ROLLUP(1,2)
    )
SELECT * FROM summed
ORDER BY grouping_id, numeral, name
;

numeral	name	grouping_id	count
NULL	NULL	0	1
1	one	0	1
2	two	0	1
NULL	NULL	1	1
1	NULL	1	1
2	NULL	1	1
NULL	NULL	3	3

VValdar

Hi Pieter_Sheth-Vo,

You're only lacking the knowledge of the grouping function that let you know if it's a base line row or a rolled up one.

with cte_base (numeral, name) as
(
select 1   , 'one' union all
select 2   , 'two' union all
select null, null
)
  select case grouping(numeral) when 1 then 999        else numeral end as numeral
       , case grouping(name)    when 1 then 'All name' else name    end as name
       , count(*)
    from cte_base
group by rollup(numeral, name)
order by grouping_id(), 1, 2;

numeral  name     count
-------- -------- -----
 [NULL]  [NULL]       1
      1  one          1
      2  two          1
 [NULL]  All name     1
      1  All name     1
      2  All name     1
    999  All name     3

mosheg

..and a VSQL count scale example:

\set SCALE_MAX_CHARS 40
with list1 (numeral, name) as
(
select 1   , 'one' union all
select 2   , 'two' union all
select 2   , 'two' union all
select 3   , 'three' union all
select 3   , 'three' union all
select 3   , 'three' union all
select null, null
),
list2 as (select case grouping(numeral) when 1 then 'TOTAL'    else ISNULL(TO_CHAR(numeral),'[NULL]') end as numeral
       , case grouping(name)    when 1 then '' else ISNULL(name,'[NULL]') end as name
       , grouping_id() as gid
       , count(*) as count
    from list1
group by rollup(numeral,name)
order by grouping_id(), 1, 2),
norm as (select max(count / :SCALE_MAX_CHARS)::INT as norm_factor from list2)
select numeral, name,
       case when gid > 0 then count end as count,
       case gid when 1 then REPEAT('*', ((count / (norm_factor +1))::INT)) else '' end as Count_Scale
from list2, norm
order by gid ,1,2;

 numeral |  name  | count | Count_Scale
---------+--------+-------+-------------
 1       | one    |       |
 2       | two    |       |
 3       | three  |       |
 [NULL]  | [NULL] |       |
 1       |        |     1 | *
 2       |        |     2 | **
 3       |        |     3 | ***
 [NULL]  |        |     1 | *
 TOTAL   |        |     7 |
(9 rows)

HKhan1

See this: https://www.vertica.com/blog/vertica-quick-tip-query-nan-values/