COUNT(DISTINCT s_id) ergibt 0

[giordano]

Hallo,

Ich habe hier ein ganz seltsames verhalten. Ziel war, herauszufinden, viele Personen sowohl in der Tabelle s als auch m, nur in der Tabelle s und nur in der Tabelle m zu bestimmen. Meine Idee: erstelle Outer join und Zähle Werte.

OUTER JOIN:
s enthält id (PK) und mort_id (FK)
m enthält rec_num (PK)
mort_id = rec_num

CREATE TABLE test
SELECT s.id AS s_id, s.mort_id AS m_id
FROM s
LEFT OUTER JOIN m
ON s.mort_id = m.rec_num
UNION
SELECT s.id AS s_id, m.rec_num AS m_id
FROM s
RIGHT OUTER JOIN m
ON s.mort_id = m.rec_num
;

Nun möchte ich bilanzieren:

SELECT COUNT(*) n, COUNT(s_id), COUNT(DISTINCT s_id), COUNT(m_id), COUNT(DISTINCT m_id)
       , SUM(CASE WHEN s_id IS NOT NULL AND m_id IS NOT NULL THEN 1 ELSE 0 END) s1m1
       , SUM(CASE WHEN s_id IS NOT NULL AND m_id IS NULL THEN 1 ELSE 0 END) s1m0
       , SUM(CASE WHEN s_id IS NULL AND m_id IS NOT NULL THEN 1 ELSE 0 END) s0m1
       , SUM(CASE WHEN s_id IS NULL AND m_id IS NULL THEN 1 ELSE 0 END) s0m0
FROM test
;

+---------+-------------+----------------------+-------------+----------------------+---------+---------+---------+--------+
| n | COUNT(s_id) | COUNT(DISTINCT s_id) | COUNT(m_id) | COUNT(DISTINCT m_id) | s1m1 | s1m0 | s0m1 | s0m0 |
+---------+-------------+----------------------+-------------+----------------------+---------+---------+---------+--------+
| 9819354 | 8527786 | 0 | 2405789 | 2405789 | 1114221 | 7413565 | 1291568 | 0 |
+---------+-------------+----------------------+-------------+----------------------+---------+---------+---------+--------+

Alles geht schön auf:
n = s1m1 + s0m1 + s1m0
COUNT(s_id) entspricht Anzahl rows von s
COUNT(m_id) = COUNT(DISTINCT m_id) = s1m1 +s0m1 = Anzahl rows von m
etc.

Seltsam ist, dass COUNT(DISTINCT s_id) 0 ergibt !!

Wie ist das möglich?

Besten Dank für Hinweise.

Gruss
giordano

[dibo33]

Seltsam ist, dass COUNT(DISTINCT s_id) 0 ergibt !!

Wie ist das möglich?

Wenn s_id nur NULL enthält.

Was ergibt:
[highlight=sql]SELECT DISTINCT s_id FROM test[/highlight]

[giordano]

Hallo dibo33,

Danke für die Antwort. Ich habe gerade die DB nicht zur Verfügung, jedoch kann ich sagen, dass die Kolonne s_id NULL's und die Primary Key von s enthält. Seltsam ist ja, dass SELECT COUNT(s_id) die Anzahl der Primary Keys angibt, COUNT(DISTINCT s_id) jedoch 0.
Ich werde mal schauen, was Dein Vorschlage (SELECT DISTINCT s_id) ergibt und berichten.
Gruss
giordano

[giordano]

Hallo dibo33,

SELECT DISTINCT(s_id) 
FROM test
LIMIT 3

ergibt
+--------+
| s_id |
+--------+
| 485083 |
| 484437 |
| 485072 |
+--------+

Übrigens, der Datentyp von s_id ist INTEGER.

Gruss
giordano

[Falk Prüfer]

Hallo,

Wenn s_id nur NULL enthält.

... dann müsste auch COUNT(s_id) 0 sein!

Ist mir so auch unerklärlich.
Welche Ergebnisse bekommst du, wenn du jeden Ausdruck deines Selects mal einzeln abfragst?
[highlight=sql]
select COUNT(DISTINCT s_id)
from test
[/highlight]

Gruß Falk

[giordano]

Hall Falk,

Ich habe die Abfrage ausgeführt (einzeln) und habe tatsächlich die Anzahl erhalten, die ich erwartet habe (8527786). Nun habe ich die erste Abfrage ausgeführt:

SELECT COUNT(*) n, COUNT(s_id), COUNT(DISTINCT s_id), COUNT(m_id), COUNT(DISTINCT m_id)
       , SUM(CASE WHEN s_id IS NOT NULL AND m_id IS NOT NULL THEN 1 ELSE 0 END) s1m1
       , SUM(CASE WHEN s_id IS NOT NULL AND m_id IS NULL THEN 1 ELSE 0 END) s1m0
       , SUM(CASE WHEN s_id IS NULL AND m_id IS NOT NULL THEN 1 ELSE 0 END) s0m1
       , SUM(CASE WHEN s_id IS NULL AND m_id IS NULL THEN 1 ELSE 0 END) s0m0
FROM test
;

Output:
+---------+-------------+----------------------+-------------+----------------------+---------+---------+--------+--------+
| n | COUNT(s_id) | COUNT(DISTINCT s_id) | COUNT(m_id) | COUNT(DISTINCT m_id) | s1m1 | s1m0 | s0m1 | s0m0 |
+---------+-------------+----------------------+-------------+----------------------+---------+---------+--------+--------+
| 8527786 | 8527786 | 8527786 | 1114221 | 1114221 | 1114221 | 7413565 | 0 | 0 |
+---------+-------------+----------------------+-------------+----------------------+---------+---------+--------+--------+


Jedoch: COUNT(*) ergibt nicht mehr 9819354. Das ist alles seltsam, da ich die DB das Wochenende nicht berührt habe (und niemand zugriff hat auf mein C. Ich werde die Tabelle "test" nochmals erstellen.

Gruss
giordano

[giordano]

-- test if there are mortality id which are not in snc and vice versa
DROP TABLE test;
CREATE TABLE test
SELECT s.id AS s_id, s.mort_id AS m_id
FROM s
LEFT OUTER JOIN  m
ON s.mort_id = m.rec_num
UNION
SELECT s.id AS s_id, m.rec_num AS m_id
FROM s
RIGHT OUTER JOIN  m
ON s.mort_id = m.rec_num
;

-- A
SELECT COUNT(*) n, COUNT(s_id) nsid, COUNT(DISTINCT s_id) ndsid, COUNT(m_id) nmid, COUNT(DISTINCT m_id) mdmid
       , SUM(CASE WHEN s_id IS NOT NULL AND m_id IS NOT NULL THEN 1 ELSE 0 END) s1m1
       , SUM(CASE WHEN s_id IS NOT NULL AND m_id IS NULL THEN 1 ELSE 0 END) s1m0
       , SUM(CASE WHEN s_id IS NULL AND m_id IS NOT NULL THEN 1 ELSE 0 END) s0m1
       , SUM(CASE WHEN s_id IS NULL AND m_id IS NULL THEN 1 ELSE 0 END) s0m0
FROM test
;

-- B
SELECT DISTINCT s_id
FROM snc.testsmid_01
LIMIT 3;

-- C
SELECT COUNT(DISTINCT s_id)
FROM snc.testsmid_01
;

-- D
SELECT COUNT(*), COUNT(s_id), COUNT(DISTINCT s_id)
FROM snc.testsmid_01
;

Alles ergibt
A:
+---------+---------+-------+---------+---------+---------+---------+---------+--------+
| n | nsid | ndsid | nmid | mdmid | s1m1 | s1m0 | s0m1 | s0m0 |
+---------+---------+-------+---------+---------+---------+---------+---------+--------+
| 9819354 | 8527786 | 0 | 2405789 | 2405789 | 1114221 | 7413565 | 1291568 | 0 |
+---------+---------+-------+---------+---------+---------+---------+---------+--------+

B:
+--------+
| s_id |
+--------+
| 485083 |
| 484437 |
| 485072 |
+--------+

C:
0
D:
+----------+-------------+----------------------+
| COUNT(*) | COUNT(s_id) | COUNT(DISTINCT s_id) |
+----------+-------------+----------------------+
| 9819354 | 8527786 | 0 |
+----------+-------------+----------------------+

Das sind die Resultate, die ich anfangs geposted hatte. Es ist mir ein bisschen unheimlich, warum ich vorhin (mit SELECT COUNT(DISTINCT s_id)) die erwartete Zahl erhalten habe. Habe ich vielleicht zwei Probleme? Das eine ist eine falsche Berechnung von COUNT, dass andere ist das Verschwinden von Row mit s_id 0 NULL (nach einem Neustart?)?

[dibo33]

Allein schon die Abfragen B und C nicht nicht reproduzierbar, ich hab zwar noch nichts davon gehört aber evtl. ein BUG?
Welche genaue Version hast du denn?

SELECT @@version

Sonst ist das Verhalten absolut nicht erklärbar.

[giordano]

Ich habe die Version 5.5.11

Ich finde A, C, D seltsam. B listet einfach die ersten distincten s_id (wegen LIMIT 3) auf.

Ich habe noch folgendes seltsames Verhalten gefunden:

SELECT COUNT(t.id), COUNT(DISTINCT t.id)
FROM (
          SELECT DISTINCT(s_id) id
          FROM test
          ) t

+-------------+----------------------+
| COUNT(t.id) | COUNT(DISTINCT t.id) |
+-------------+----------------------+
| 8527786 | 0 |
+-------------+----------------------+

SELECT COUNT(t.id), COUNT(DISTINCT t.id)
FROM (
         SELECT DISTINCT(s_id) id
         FROM test
         LIMIT 3
          ) t

+-------------+----------------------+
| COUNT(t.id) | COUNT(DISTINCT t.id) |
+-------------+----------------------+
| 3 | 3 |
+-------------+----------------------+


Vieleicht kann man die Frage stellen: Welche Werte muss eine Kolonne x besitzen, damit SELECT COUNT(x) > 0 und SELECT COUNT(DISTINCT x) =0 ergibt?

CREATE TABLE test2.t3 (
x INTEGER DEFAULT NULL
);
INSERT INTO test2.t3 (x)
VALUES (0),(0),(NULL),(0)
;
SELECT COUNT(*), COUNT(x), COUNT(DISTINCT x)
FROM test2.t3

+----------+----------+-------------------+
| COUNT(*) | COUNT(x) | COUNT(DISTINCT x) |
+----------+----------+-------------------+
| 4 | 3 | 1 |
+----------+----------+-------------------+

[Falk Prüfer]

...Welche Werte muss eine Kolonne x besitzen, damit SELECT COUNT(x) > 0 und SELECT COUNT(DISTINCT x) =0 ergibt?

Diese Bedingung gibt es nicht! Wenn COUNT(x) > 0, dann gibt es mindestens einen DS der nicht NULL ist und damit ist auch IMMER COUNT(DISTINCT x) > 0

Deine Ergebnisse sehen komisch aus. Mach mal einen Check auf der Tabelle, vlt. ist ja was kaputt!?

[highlight=sql]
CHECK TABLE test
[/highlight]

Gruß Falk

[giordano]

CHECK TABLE test;

+-----------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------+--------+----------+----------+
| test | check | status | OK |
+-----------------+--------+----------+----------+

Scheint in Ordnung zu sein.
Gruss
giordano

[Falk Prüfer]

Dann weiß ich auch nicht weiter, zumal es sich mit ein paar Testdaten nicht nachvollziehen lässt.

Versuch mal ob du in der my.err evtl. irgendwelche Hinweise auf Abbrüche, Fehler etc. findest. Manchmal ist MySQL nach aussen sehr schweigsam was Fehlermeldungen angeht.

Gruß Falk

[giordano]

Hallo Falk,

Ist das ein log-file? Konnte my.err nicht finden. Ich werde nun die DB auf mein Notebook importieren und schauen, ob das verhalten identisch ist. Als sql-frontend habe ich mysqlcc verwendet. Ich versuche nun das mit der MySQL workbench durchzuspielen.

Gruss
giordano

[Falk Prüfer]

Ist das ein log-file?

Das ist das Error-Log von MySQL, liegt normalerweise im Datenverzeichnis (datadir) und heißt <servername>.err.

Gruß Falk

[giordano]

Besten Dank. Ich habe das .err-fuile gefunden und nichts Auffälliges entdeckt. Ich habe die Tabellen s und m als Dump-File exportiert, in mein Netbook-MySQL importiert und dieselben Joins durchgeführt. Dabei erhielt ich dieselben widersrpüchlichen Resultate:
+---------+---------+-------+---------+---------+---------+---------+---------+--------+
| n | nsid | ndsid | nmid | mdmid | s1m1 | s1m0 | s0m1 | s0m0 |
+---------+---------+-------+---------+---------+---------+---------+---------+--------+
| 9819354 | 8527786 | 0 | 2405789 | 2405789 | 1114221 | 7413565 | 1291568 | 0 |
+---------+---------+-------+---------+---------+---------+---------+---------+--------+

Ich schaue mal weiter und melde mich.

giordano