Other special values besides NULL?

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)

@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
;

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

..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)

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