MariaDB: is COUNT DISTINCT bugged?

user_2511957 0
Viewed 265

I have 3 requests :

SELECT COUNT(DISTINCT origine_client_id,  annee_imputation)
         FROM dossier d1;

34438

SELECT COUNT(DISTINCT d2.origine_client_id,  d2.annee_imputation)
         FROM (SELECT origine_client_id,  annee_imputation
                  FROM dossier) as d2;

34438

SELECT COUNT(*)
         FROM (SELECT DISTINCT origine_client_id,  annee_imputation
                 FROM dossier) as d3;

34478

But i haven't the same result, why ? (I am using Mariadb)

EDIT :

@jarlh

SELECT COUNT(DISTINCT origine_client_id) FROM dossier; => 19 488

SELECT COUNT(DISTINCT annee_imputation) FROM dossier; => 42

@a_horse_with_no_name

yes, there is null value

SELECT COUNT(id) FROM dossier WHERE annee_imputation IS NULL; => 1

SELECT COUNT(id) FROM dossier WHERE origine_client_id IS NULL; => 289 711

SELECT COUNT(id) FROM dossier WHERE origine_client_id IS NULL AND annee_imputation IS NULL; => 1

mariadb count distinct sql
2 Answers

Based on doc:

COUNT(DISTINCT expr,[expr...])

Returns a count of the number of different non-NULL values.

This syntax is non-standard MySQL/MariaDB extension. It seems to treat "non-NULL" to be all not NULL to be counted.

Demo:

CREATE TABLE dossier
AS
SELECT 1 origine_client_id, 2 annee_imputation  UNION ALL  -- both values provided
SELECT NULL, NULL UNION ALL
SELECT NULL origine_client_id, 1 annee_imputation UNION ALL
SELECT 1 origine_client_id, NULL annee_imputation;

Queries:

SELECT COUNT(DISTINCT origine_client_id,  annee_imputation) FROM dossier d1;
-- 1

SELECT COUNT(DISTINCT d2.origine_client_id,  d2.annee_imputation) 
FROM (SELECT origine_client_id,  annee_imputation FROM dossier) as d2;
-- 1

SELECT COUNT(*) 
FROM (SELECT DISTINCT origine_client_id,  annee_imputation FROM dossier) as d3;
-- 4

db<>fiddle demo

user_50708790

I think a_horse_with_no_name asked a relevant question. This is simpler and might be analogous:

CREATE TABLE t (s1 INT PRIMARY KEY, s2 INT);
INSERT INTO t VALUES (1, 1), (2, NULL), (3, NULL);
SELECT COUNT(DISTINCT s2) FROM t;
SELECT COUNT(*) FROM (SELECT DISTINCT s2 FROM t) AS x;

In the first case, nulls are eliminated before the function is done so the answer is 1. In the second case, nulls are not eliminated before the function is done so the answer is 2. Update added later: sorry for not saying it clearly: this example shows expected behaviour which does not contradict the SQL standard, except that there is no warning about nulls being eliminated.

user_62533400
Related