Snowflake - produced extra row with null values after count operation

Question

I have two tables like:

create or replace table t1 (C_ID varchar, I_A varchar ) as select *  from values
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','j'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','l'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','m'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','d')
;

create or replace table t2 (C_ID varchar, I_ID varchar, I_N varchar, I_V varchar, UPDATED_DATE datetime, SOURCE_DELETED_DATE datetime, DELETED_DATE datetime, SYNC_DATE datetime ) as select *  from values
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','ac12000371951f7e81719520b4bd031f','d','E','2020-06-19 04:35:00.386000000',NULL,NULL,'2022-07-06 08:10:33.239000000 -07:00'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','ac12000371951f7e81719520b4bd031f','j','C','2020-06-19 04:35:00.386000000',NULL,NULL,'2022-07-06 08:10:33.239000000 -07:00'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','ac12000371951f7e81719520b7700321','d','M','2020-06-19 04:35:00.476000000',NULL,NULL,'2022-07-06 08:10:33.239000000 -07:00'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','ac12000371951f7e81719520b7700321','j','VM','2020-06-19 04:35:00.476000000',NULL,NULL,'2022-07-06 08:10:33.239000000 -07:00'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','ac12000371951f7e81719520b7700321','m','dd','2020-06-19 04:35:00.476000000',NULL,NULL,'2022-07-06 08:10:33.239000000 -07:00'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','ac12000371951f7e81719520b7dc0323','d','En','2020-06-19 04:35:00.501000000',NULL,NULL,'2022-07-06 08:10:33.239000000 -07:00'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','ac12000371951f7e81719520b7dc0323','j','VPE','2020-06-19 04:35:00.501000000',NULL,NULL,'2022-07-06 08:10:33.239000000 -07:00'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','ac12000371951f7e81719520b7dc0323','m','dd','2020-06-19 04:35:00.501000000',NULL,NULL,'2022-07-06 08:10:33.239000000 -07:00'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','ac12000371951f7e81719520b84c0325','d','En','2020-06-19 04:35:00.420000000',NULL,NULL,'2022-07-06 08:10:33.239000000 -07:00'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','ac12000371951f7e81719520b84c0325','j','EN','2020-06-19 04:35:00.420000000',NULL,NULL,'2022-07-06 08:10:33.239000000 -07:00'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','ac12000371951f7e81719520b84c0325','m','j','2020-06-19 04:35:00.420000000',NULL,NULL,'2022-07-06 08:10:33.239000000 -07:00'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','ac12000371951f7e81719520b8b40327','d','M','2020-06-19 04:35:00.548000000',NULL,NULL,'2022-07-06 08:10:33.239000000 -07:00'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','ac12000371951f7e81719520b8b40327','j','PM','2020-06-19 04:35:00.548000000',NULL,NULL,'2022-07-06 08:10:33.239000000 -07:00'),
    ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','ac12000371951f7e81719520b8b40327','m','a','2020-06-19 04:35:00.548000000',NULL,NULL,'2022-07-06 08:10:33.239000000 -07:00');


SELECT
    CONCAT(T2.C_ID, T2.I_ID, T2.I_V, T2.UPDATED_DATE)   AS PK,
    T2.I_N                                              AS I_N,
    T2.I_V                                              AS I_V,
    count(T2.I_ID)                                      AS I_ID,
    T2.UPDATED_DATE                                     AS UPDATED_DATE,
    T2.SYNC_DATE                                        AS SYNC_DATE
FROM T1 AS T1
         LEFT OUTER JOIN T2
                         ON T1.C_ID = T2.C_ID
                             AND T1.I_A = T2.I_N
GROUP BY T2.C_ID, T2.I_N, T2.I_ID, T2.I_V, T2.UPDATED_DATE, T2.SYNC_DATE;

Here I'm trying to get count of the different I_ID-s for the same pairs of C_ID + I_N. Am I missing something? I expect no null values row in such join. My results

Gokhan Atil · Answer

Please check your data and query:

create or replace table t1 (id number, col1 number ) as select *  from values
( 1    , 222  ),
( 2    , 333  ),
( 3    , 444  ),
( 4    , 555  );

create or replace table t2 (id number, id_t1 number, col1_ number, col2 number ) as select *  from values
( 1    , 1    , 222  ,   41 ),
( 2    , 1    , 222  ,   42 ),
( 3    , 2  , 333  ,   43 ),
( 4    , 2    , 333  ,   44 ),
( 5    , 2    , 333  ,   45 ),
( 6    , 3    , 444  ,  46 ),
( 7    , 4    , 555  ,  47 );


SELECT
    CONCAT(T1.ID, T1.COL1)   AS PK,
    T1.COL1                  AS COL1,
    count(DISTINCT T2.COL2)  AS COL2_COUNT
FROM T1
LEFT OUTER JOIN T2
ON T1.ID = T2.ID_T1
AND T1.COL1 = T2.COL1_
GROUP BY 1,2;

+------+------+------------+
|  PK  | COL1 | COL2_COUNT |
+------+------+------------+
| 1222 |  222 |          2 |
| 2333 |  333 |          3 |
| 4555 |  555 |          1 |
| 3444 |  444 |          1 |
+------+------+------------+

No NULLs in the result!

Lukasz Szozda · Answer

The table t1 contains row ('4335b37a-242d-4b42-a5bf-92b0fbbb50b6','l') that does not have a corresponding row on t2.

The SELECT statement:

SELECT CONCAT(T2.C_ID, T2.I_ID, T2.I_V, T2.UPDATED_DATE)   AS PK,

gets values from t2 which are all NULLs.

Removing the GROUP BY and providing output for T1 join columns:

SELECT T1.C_ID, T1.I_A,
    CONCAT(T2.C_ID, T2.I_ID, T2.I_V, T2.UPDATED_DATE)   AS PK,
    T2.I_N                                              AS I_N,
    T2.I_V                                              AS I_V,
    T2.UPDATED_DATE                                     AS UPDATED_DATE,
    T2.SYNC_DATE                                        AS SYNC_DATE
FROM T1 AS T1
LEFT OUTER JOIN T2
  ON T1.C_ID = T2.C_ID
  AND T1.I_A = T2.I_N;

Output:

Snowflake - produced extra row with null values after count operation

2 Answers