(MySQL) GROUP BY soll NULL-Felder nicht gruppieren

[Hopsel]

Hallo liebe Retter!

Ich habe mal wieder ein eher einfaches Problem.

Nehmen wir an, ich habe eine sehr einfache Tabelle test:

id (autoincrement, primary)   |   wert (integer, NULL erlaubt)
——————————————————————————————————————————————————————————————
1                                 9
2                                 83
3                                 NULL
4                                 92
5                                 NULL
6                                 NULL
7                                 92
8                                 92

Ich möchte jetzt alle Werte so haben, wie sie folgende Abfrage ausgibt:

(SELECT id,wert         -- gib mir "id" und "wert"  
FROM test               -- aus der Tabelle test  
WHERE wert IS NOT NULL  -- wo "wert" nicht NULL ist  
GROUP BY wert           -- und gruppiere nach "wert"  
HAVING MIN(id))         -- wobei ich immer den wert haben möchte,  
                        -- der die kleinste id hat  
UNION  
(SELECT id,wert         -- außerdem will ich "id" und "wert"  
FROM test               -- aus der Tabelle test  
WHERE wert IS NULL)     -- wo "wert" NULL ist  
  
ORDER BY id ASC         -- sortiere dann alle Zeilen nach der id

Das sind also alle Datensätze, wobei "wert" nicht doppelt vorkommen darf, es sei denn er ist NULL.
Haben mehrere Datensätze den gleichen Wert, wird von diesen Datensätzen derjenige ausgegeben, der die niedrigste id hat.

Kann ich diese Abfrage so ändern, dass "GROUP BY" NULL-Werte gar nicht erst gruppiert?
Macht das überhaupt Sinn?

Verwendete MySQL-Version ist >= 5.0.
In der Tabelle "test" stehen nie mehr als 20000 Datensätze. Durchscnittlich sind es viel weniger; eher so 500-1000 Datensätze.

MfG H☼psel

[Vinzenz Mai]

Hallo Hopsel,

Ich habe mal wieder ein eher einfaches Problem.

Nehmen wir an, ich habe eine sehr einfache Tabelle test:
Ich möchte jetzt alle Werte so haben, wie sie folgende Abfrage ausgibt:

(SELECT id,wert         -- gib mir "id" und "wert"

FROM test               -- aus der Tabelle test
WHERE wert IS NOT NULL  -- wo "wert" nicht NULL ist
GROUP BY wert           -- und gruppiere nach "wert"
HAVING MIN(id))         -- wobei ich immer den wert haben möchte,
                        -- der die kleinste id hat
Macht das überhaupt Sinn?

nein.

Ich hege die Befürchtung, dass Du überhaupt nicht verstanden hast, was eine Gruppierung machen soll. Du hast auch nicht verstanden, was die HAVING-Klausel bewirkt.

id (autoincrement, primary)   |   wert (integer, NULL erlaubt)
——————————————————————————————————————————————————————————————
1                                 9
2                                 83
3                                 NULL
4                                 92
5                                 NULL
6                                 NULL
7                                 92
8                                 92

Welches Ergebnis möchtest Du bei Deinen Beispieldaten haben?

Möchtest Du

id | wert

1  |  9
2  | 83
4  | 92
3  | NULL

haben? Das bekämst Du mit

[code lang=sql]SELECT
    MIN(id),
    wert
FROM
    tabelle
GROUP BY
    wert
ORDER BY
    MIN(id)

  
Möchtest Du ein anderes Ergebnis, so gib es an und begründe, warum Du genau diese Daten haben möchtest.  
  
Möchtest Du hingegen \*alle\* ids mit NULL-werten, so wäre eine UNION angebracht. Auf jeden Fall ist es eine gute Idee, die erste Abfrage Deiner UNION entsprechend meinem Vorschlag umzuschreiben.  
  
Wenn Dein Statement die von Dir gewünschten Daten liefert, dann ist dies einfacher Zufall. Beachte, dass bei Deiner Abfrage  
  
id | wert  
\----------  
1  |  9  
2  | 83  
7  | 92  
  
selbstverständlich ein erlaubtes Ergebnis von  
  
~~~sql
» SELECT id,wert         -- gib mir "id" und "wert"  

> FROM test               -- aus der Tabelle test  
> WHERE wert IS NOT NULL  -- wo "wert" nicht NULL ist  
> GROUP BY wert           -- und gruppiere nach "wert"  
> HAVING MIN(id)          -- wobei ich immer den wert haben möchte,  
>                         -- der die kleinste id hat  

ist, denn MIN(7) ist wahr. Was in Deinem Kommentar steht, ist Wunschdenken, keine Anweisung an die DB. Wenn Du den kleinsten Wert haben willst, wende die entsprechende Aggregatsfunktion auf die Spalte an, aus der Du den kleinsten Wert je Gruppe benötigst. Benötigst Du noch weitere Informationen aus dem Datensatz mit diesem kleinsten Wert, so benötigst Du eine korrelierte Unterabfrage.

Von allen DBMS, die ich kenne, ist nur MySQL so schlampig, solche fehlerhaften Statements auszuführen. Da in vielen Fällen zufällig(!) das gewünschte Ergebnis rauskommt, glaubt der SQL-Novize (der nur MySQL kennt), sein Statement sei in Ordnung - und wundert sich, wenn mal was anderes rauskommt.

Freundliche Grüße

Vinzenz

[Hopsel]

Hi Vinzenz!

Ich hege die Befürchtung, dass Du überhaupt nicht verstanden hast, was eine Gruppierung machen soll. Du hast auch nicht verstanden, was die HAVING-Klausel bewirkt.

Ohje! Ohja! Du hast Recht mit deiner Befürchtung.

id (autoincrement, primary)   |   wert (integer, NULL erlaubt)
——————————————————————————————————————————————————————————————
1                                 9
2                                 83
3                                 NULL
4                                 92
5                                 NULL
6                                 NULL
7                                 92
8                                 92

Welches Ergebnis möchtest Du bei Deinen Beispieldaten haben?

Hab´ ich doch glatt vergessen, das gewünschte Ergebnis zu posten:
id | wert
----------
1  |  9
2  | 83
3  | NULL
4  | 92
5  | NULL
6  | NULL

Möchtest Du ein anderes Ergebnis, so gib es an und begründe, warum Du genau diese Daten haben möchtest.

Ich brauche diese NULL-Werte, weil ich keinen Einfluss darauf habe, ob der Wert belegt ist oder nicht.
Ist er aber belegt und es gibt weitere Datensätze mit dem gleichen Wert, so brauche ich den Datensatz mit der niedrigsten ID.
Alle Datensätze ohne einen Wert - also wert IS NULL - müssen zusätzlich in die Ergebnismenge

Möchtest Du hingegen *alle* ids mit NULL-werten, so wäre eine UNION angebracht. Auf jeden Fall ist es eine gute Idee, die erste Abfrage Deiner UNION entsprechend meinem Vorschlag umzuschreiben.

Ja, das habe ich auch getan.

Benötigst Du noch weitere Informationen aus dem Datensatz mit diesem kleinsten Wert, so benötigst Du eine korrelierte Unterabfrage.

Glücklicherweise hast du diesen Fall gleich mit einbezogen.

Demzufolge hätte ich dann diese Abfrage:

(SELECT id,wert /* weitere Spalten */  
FROM test t1                -- id und wert aus Tabelle test,  
WHERE wert IS NOT NULL      -- wobei wert nicht NULL  
AND id = (  
  SELECT MIN(id)            -- und nur den Datensatz  
  FROM test t2              -- mit der niedrigsten id  
  WHERE t1.wert = t2.wert  
  )  
)  
  
UNION                       -- vereinige mit  
  
(SELECT id,wert             -- id und wert aus Tabelle test,  
FROM test                   -- wobei wert NULL  
WHERE wert IS NULL)  
  
ORDER BY id ASC             -- sortiere die vereinigte Ergebnismenge

Ich hoffe, die Kommentare passen diesmal zum Code. ;-)

Von allen DBMS, die ich kenne, ist nur MySQL so schlampig, solche fehlerhaften Statements auszuführen. Da in vielen Fällen zufällig(!) das gewünschte Ergebnis rauskommt, glaubt der SQL-Novize (der nur MySQL kennt), sein Statement sei in Ordnung - und wundert sich, wenn mal was anderes rauskommt.

Heißt das, ich darf GROUP BY ausschließlich in Verbindung mit Aggregatfunktionen nutzen?

MfG H☼psel

[Vinzenz Mai]

Hallo,

Heißt das, ich darf GROUP BY ausschließlich in Verbindung mit Aggregatfunktionen nutzen?

nach allen Spalten, die *keine* Aggregatsfunktion enthalten, musst Du gemäß SQL-Standard gruppieren.

MySQL weicht dies dokumentiert so auf, dass es auf die Spalten, auf die keine Aggregatsfunktion angewendet wird und die auch nicht in der Spaltenliste der GROUP-BY-Klausel enthalten sind die Aggregatsfunktion GIB_ZUFÄLLIGEN_WERT_AUS_GRUPPE() anwendet.

Sind innerhalb der Gruppe alle Werte gleich, so stellt dies eine Optimierung da, weil man ein paar Bytes in der GROUP-BY-Klausel einspart. Sind innerhalb der Gruppe die Werte nicht gleich, so erhält man - ja, das steht so in der Doku - unvorhersagbare Ergebnisse. Du wirst nicht glauben, wieviele "Progger" sich auf solch unvorhersagbare Ergebnisse verlassen, weil sie das von ihnen gewünschte Ergebnis erhalten. Das hiesige Archiv weist genügend dieser Spezies auf.

Genau aus diesem Grund gibt es meiner Meinung nach kein DBMS, das sich schlechter dazu eignet, den Umgang mit GROUP BY zu lernen, als MySQL, es sei denn MySQL läuft im ONLY_FULL_GROUP_BY SQL mode.

Freundliche Grüße

Vinzenz

[Hopsel]

Hi Vinzenz!

Ich habe mich noch gar nicht für deine ausführlichen Antworten bedankt: Danke!

Heißt das, ich darf GROUP BY ausschließlich in Verbindung mit Aggregatfunktionen nutzen?

nach allen Spalten, die *keine* Aggregatsfunktion enthalten, musst Du gemäß SQL-Standard gruppieren.

Das war eigentlich nicht meine Frage. Ich habe in meiner Spaltenliste ja gar keine Aggregatsfunktion und brauche auch keine.
Diese Spalten müssen mit GROUP BY natürlich auch nicht noch einmal aufgelistet werden.

Meine Frage war jetzt, ob es aber möglich ist, GROUP BY _ohne_ Aggregatsfunktion in der Projektion zu nutzen.

Um bei dem bisherigen Beispiel zu bleiben:

SELECT id,wert  
FROM test  
GROUP BY wert

Wie ist das Verhalten dieser Abfrage definiert?
Ich konnte im Manual nichts passendes finden...

[fehlerhaftes] MySQL [...]

Das war mit schon bekannt. Das wird ja auch oft genug im Forum propagiert. =)

Ich konnte nur mit HAVING noch nicht sehr viel anfangen. Für mich war HAVING quasi die WHERE-Klausel für alle Datensätze, die den gleichen Wert aufwiesen.

MfG H☼psel

[Vinzenz Mai]

Hallo,

Das war eigentlich nicht meine Frage. Ich habe in meiner Spaltenliste ja gar keine Aggregatsfunktion und brauche auch keine.

inzwischen :-) Du benötigst inzwischen auch (im äußeren SELECT) kein GROUP BY.

Meine Frage war jetzt, ob es aber möglich ist, GROUP BY _ohne_ Aggregatsfunktion in der Projektion zu nutzen.

ja. Gruppiere nach allen Spalten, die in der Spaltenliste vorkommen :-)

Um bei dem bisherigen Beispiel zu bleiben:

SELECT id,wert

FROM test
GROUP BY wert

  

> Wie ist das Verhalten dieser Abfrage definiert?  
  
Standard: Fehler  
MySQL: gib in jeder Gruppe mit gleichen wert-Werten eine beliebige id aus der Gruppe aus - wie ich bereits schrieb.  
  
ohne Aggregatsfunktion in korrektem SQL:  
  
~~~sql
SELECT  
    id,  
    wert  
FROM  
    test  
GROUP BY  
    wert,  
    id  

Ich konnte nur mit HAVING noch nicht sehr viel anfangen. Für mich war HAVING quasi die WHERE-Klausel für alle Datensätze, die den gleichen Wert aufwiesen.

HAVING ist nicht die "WHERE-Klausel" innerhalb einer Gruppe, sondern die Einschränkung für die Datensätze *nach* dem Gruppieren, d.h. also welche Gruppen in die Ergebnismenge sollen.

Freundliche Grüße

Vinzenz

[Hopsel]

Hi Vinzenz!

Nochmal ein Dankeschön für deine Erläuterungen.

Jetzt ist mir auch die Funktionsweise von HAVING (wieder) klar.

SELECT id,wert

FROM test
GROUP BY wert

>   
> > Wie ist das Verhalten dieser Abfrage definiert?  
>   
> Standard: Fehler  
> MySQL: gib in jeder Gruppe mit gleichen wert-Werten eine beliebige id aus der Gruppe aus - wie ich bereits schrieb.  

Das war mir halt so nicht bewusst und mir war auch nicht klar, dass dieses Fehlverhalten innerhalb MySQL eng mit der fehlerhaften Implementierung von GROUP BY in Kombination mit Aggregatsfunktionen zusammenhängt.  
  
MfG H☼psel

-- 
"It's amazing I won. I was running against peace, prosperity, and incumbency."  
George W. Bush speaking to Swedish Prime Minister unaware a live television camera was still rolling, June 14, 2001  
  
[Selfcode](http://community.de.selfhtml.org/fanprojekte/selfcode.htm): ie:% fl:( br:> va:) ls:& fo:) rl:? n4:& ss:| de:] js:| ch:? sh:( mo:) zu:)

[Vinzenz Mai]

Hallo,

Nochmal ein Dankeschön für deine Erläuterungen.

bitte, gerne. Ich bin noch nicht fertig.

Jetzt ist mir auch die Funktionsweise von HAVING (wieder) klar.

Ja, ich war verwundert. Ich dachte, Du beherrschtest das :-)

zurück zu Deinem Ausgangsproblem. Du hattest - was gelegentlich vorkommt - Dein Problem zu stark reduziert. Ich füge eine dritte Spalte hinzu, an deren Inhalten Du interessiert bist, so dass Du zwingend die korrelierte Unterabfrage benötigst:

id | wert | name
---+---------+-----
 1 |       9 | Hopsel
 2 |      83 | Vinzenz
 3 |    NULL | dedlfix
 4 |      92 | Sven
 5 |    NULL | Tom
 6 |    NULL | Edgar
 7 |      92 | Chris
 8 |      92 | Steel

Gewünschtes Ergebnis:

id | wert | name
---+---------+-----
 1 |       9 | Hopsel
 2 |      83 | Vinzenz
 3 |    NULL | dedlfix
 4 |      92 | Sven
 5 |    NULL | Tom
 6 |    NULL | Edgar

weil Du innerhalb einer Gruppe mit gleichem und vorhandenem Wert in der Spalte wert nur den Datensatz haben möchtest, der innerhalb der Gruppe die kleinste id aufweist, sowie alle Datensätze, die in der Spalte "wert" den NULL-Wert aufweisen, d.h. keine Daten vorhanden. Außerdem soll aufsteigend nach der Spalte id sortiert werden.

Du wirst sehen, dass Du noch nicht einmal eine UNION benötigst :-)

1. Schritt (korrelierte Unterabfrage)

SELECT                         -- Gib mir  
    t1.id,                     -- id  
    t1.wert,                   -- wert  
    t1.name                    -- und name  
FROM                           -- aus  
    tabelle t1                 -- meiner Tabelle, die hier außen mit t1  
                               -- angesprochen wird,  
WHERE                          -- wobei mich nur die Werte interessieren  
    t1.id = (                  -- bei denen die id gleich  
    SELECT                     -- dem  
        MIN(t2.id)             -- kleinsten id-Wert aus  
    FROM                       -- meiner Tabelle ist, die hier innen mit t2  
        tabelle t2             -- angesprochen wird,  
    WHERE                      -- wobei  
        t1.wert = t2.wert      -- die Werte in der Spalte wert außen wie innen  
)                              -- gleich sein müssen

Aufgrund des speziellen Verhaltens von NULL-Werten: Der Vergleich mit dem Gleichheitsoperator zwischen NULL und NULL liefert NULL.

Tipp dazu: prüfe im Client Deiner Wahl folgendes:

SELECT NULL = NULL test

NULL ist nicht wahr, somit sind Deine Zeilen mit NULL-Werten eh' schon draußen, das Ergebnis dieser Abfrage ist daher (in beliebiger Reihenfolge):

id | wert | name
---+---------+-----
 1 |       9 | Hopsel
 2 |      83 | Vinzenz
 4 |      92 | Sven

2. Schritt:
Es fehlen also noch die Werte mit NULL als wert und die Sortierung.
Wir müssen daher nur noch in der WHERE-Klausel ein

OR t1.wert IS NULL

ergänzen und am Schluß die ORDER-BY-Klausel. Somit erhältst Du:

SELECT  
    t1.id,  
    t1.wert,  
    t1.name  
FROM  
    tabelle t1  
WHERE  
    t1.id = (  
    SELECT  
        MIN(t2.id)  
    FROM  
        tabelle t2  
    WHERE  
        t1.wert = t2.wert  
)  
OR  
    t1.wert IS NULL        -- zu dem Ergebnis aus 1) noch alle Zeilen mit  
                           -- NULL-Werten  
ORDER BY                   -- sortiert  
    t1.id                  -- nach der Spalte id (aufsteigend = Standard)  

als SQL-Statement, das Dir das gewünschte Resultat:

id | wert | name
---+---------+-----
 1 |       9 | Hopsel
 2 |      83 | Vinzenz
 3 |    NULL | dedlfix
 4 |      92 | Sven
 5 |    NULL | Tom
 6 |    NULL | Edgar

liefert.

Freundliche Grüße

Vinzenz

[Hopsel]

Hi Vinzenz!

Nochmal ein Dankeschön für deine Erläuterungen.

bitte, gerne. Ich bin noch nicht fertig.

Ich schaff´ dich schon noch! ;-)

Jetzt ist mir auch die Funktionsweise von HAVING (wieder) klar.

Ja, ich war verwundert. Ich dachte, Du beherrschtest das :-)

Im Auswendiglernen war ich nie besonders gut.
Und so oft musste ich das noch nicht einsetzen. Dafür bin ich jetzt um ein Erfahrung reicher, and I am not having any problems with HAVING anymore.™ =)

zurück zu Deinem Ausgangsproblem. Du hattest - was gelegentlich vorkommt - Dein Problem zu stark reduziert.

Ich hatte es soweit reduziert, weil ich ja schon eine Lösung hatte und irgendwie nur darauf fixiert war, die Aufgabe mit GROUP BY zu lösen.
Nach einem anderen Lösungsweg _wollte_ ich ja gar nicht suchen.
Aber irgendwie habe ich auf einen dieser genialen Schubser gehofft, die man wirklich nur in diesem Forum bekommt.

Aufgrund des speziellen Verhaltens von NULL-Werten: Der Vergleich mit dem Gleichheitsoperator zwischen NULL und NULL liefert NULL.
Tipp dazu: prüfe im Client Deiner Wahl folgendes:
    SELECT NULL = NULL test

Nää! Die Wertetabelle (true, false, null) x (true, false, null) hab´ ich sogar noch im Kopf. :-P

NULL ist nicht wahr, somit sind Deine Zeilen mit NULL-Werten eh' schon draußen [...]

Und selbst wenn dem nicht so wäre, könnte ich sie in der Unterabfrage immer noch ausschließen.

Danke für deine Hilfe.

Ich kann ja noch schildern, wofür das ganze Gedöns überhaupt gut ist und du kannst dann beurteilen, ob ich gleich vollständig mit der Sprache hätte rausrücken sollen. ;-)

Die SQL-Abfrage benötige ich für ein Volltext-Such-Addon für das CMS Redaxo.

In Redaxo gibt es Artikel, die durch das Addon indexiert werden. Außerdem bietet das Addon die Möglichkeit, beliebige Datenbankspalten ebenfalls zu indexieren. Dabei kann es vorkommen, dass in einem Suchergebnis auch Datensätze aus Datenbankspalten enthalten sind, die zu einem Artikel gehören, der auch gefunden wurde. Diese DB-Spalten und die indexierten Artikel, können über die Artikel-ID eindeutig einem Artikel zugeordnet werden.

Meine Anforderung an die SQL-Abfrage war jetzt, dass dann der Datensatz mit der höchsten Relevanz das Rennen macht.

Die NULL-Werte folgen aus der Flexibilität des Addons. Da beliebige Datenbankspalten indexiert werden können, sind manche dieser Spalten eben nicht mit einem Artikel über dessen ID verknüpft, sondern haben an dieser Stelle einfach einen (in diesem Sinne definierten) NULL-Wert.

Da das Addon in diesem Fall nicht entscheiden kann, ob diese Datensätze in irgendeiner Weise zusammengehören, müssen alle zurückgegeben werden.

Eine Variante der Abfrage könnte dann z. B. so aussehen:

SELECT ((( MATCH (`plaintext`) AGAINST ('suche')) * 1) + (( MATCH (`plaintext`) AGAINST ('diesen')) * 1) + (( MATCH (`plaintext`) AGAINST ('begriff')) * 1) + 1) AS RELEVANCE587,  
  id,  
  fid,         -- fid und  
  catid,  
  ftable,      -- ftable  
  fcolumn,     -- bestimmen die eindeutige Zugehörigkeit zu einem  
  texttype,    -- Artikel (oder ähnlichen Objekten, die es in Redaxo gibt)  
  clang,  
  unchangedtext,  
  plaintext,  
  teaser  
FROM `rex_587_searchindex` r1  
WHERE (  
    (  
      (  
        (  
          `plaintext` LIKE '%suche%'  
        )  
      )  
    AND (  
        (  
          `plaintext` LIKE '%diesen%'  
        )  
      )  
    AND (  
        (  
          `plaintext` LIKE '%begriff%'  
        )  
      )  
    )  
  )  
AND (  
    ((( MATCH (`plaintext`) AGAINST ('suche')) * 1) + (( MATCH (`plaintext`) AGAINST ('diesen')) * 1) + (( MATCH (`plaintext`) AGAINST ('begriff')) * 1) + 1)  
    =  
    (SELECT MAX((( MATCH (`plaintext`) AGAINST ('suche')) * 1) + (( MATCH (`plaintext`) AGAINST ('diesen')) * 1) + (( MATCH (`plaintext`) AGAINST ('begriff')) * 1) + 1)  
    FROM `rex_587_searchindex` r2  
    WHERE r1.ftable = r2.ftable  
    AND r1.fid      = r2.fid  
    )  
  )  
OR fid IS NULL  
ORDER BY RELEVANCE587 DESC LIMIT 0,20

Das meiste dieser Abfrage wird dynamisch zusammengesetzt. Der Grundbaustein sieht im Grunde folgendermaßen aus (Formatstring für sprintf):

SELECT %s  
FROM `%s` r1  
WHERE (  
    %s  
  )  
AND (  
    %s =  
    (SELECT MAX(%s)  
    FROM `%s` r2  
    WHERE r1.ftable = r2.ftable  
    AND r1.fid      = r2.fid  
    )  
  )  
OR fid IS NULL  
ORDER BY %s LIMIT %d,%d

Wenn du jetzt weitere Verbesserungsvorschläge hast, würde ich mich natürlich weiterhin freuen, diese einzubeziehen.

Wenn du allerdings die gesamte Abfrage über den Haufen schmeißen würdest, dann hörst du die nächsten drei Monate nichts mehr von mir. Ich werde mich dann irgendwo auf einen hohen Berg setzen und meine verbliebenen Haare zählen.

In diesem Sinne

gute Nacht,
H☼psel

[dedlfix]

Hi!

MySQL: gib in jeder Gruppe mit gleichen wert-Werten eine beliebige id aus der Gruppe aus - wie ich bereits schrieb.
Das war mir halt so nicht bewusst und mir war auch nicht klar, dass dieses Fehlverhalten innerhalb MySQL eng mit der fehlerhaften Implementierung von GROUP BY in Kombination mit Aggregatsfunktionen zusammenhängt.

Ob es ein Fehler ist oder nicht, sei mal dahingestellt. Es geht einfach nicht anders. Wenn du mit der Gruppierung eine Zeile pro Gruppe bekommst, dann muss sich das DBMS für einen der Werte entscheiden. Oder du nimmst GROUP_CONCAT().

Lo!