Problem mit JOIN und GROUP BY

Hallo zusammen,

ich habe folgendes Problem:

Folgende Abfrage funktioniert problemlos in MySQL 5.0 und in PostgresQL 8.3: (es soll die Anzahl der Ergeinisse in einem Ort angezeigt werden)

SELECT count(ereignisse.*) AS anzahl_ereignisse,
orte.id
FROM orte LEFT JOIN ereignisse
ON (ereignisse.orte_id = orte.id)
GROUP BY orte.id

Da ich aber mehr als nur den Primärschlüssel des Ortes abfragen möchte und mich bei den Ereignissen nur die Anzahl interessiert, modifiziere ich die Abfrage in der zweiten Zeile wie folgt:


SELECT count(ereignisse.*) AS anzahl_ereignisse,
orte.*
FROM orte LEFT JOIN ereignisse
ON (ereignisse.orte_id = orte.id)
GROUP BY orte.id

In MySQL kein Problem, in Postgres führt das jedoch zu einem Fehler:

FEHLER:  Spalte »orte.plz« muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden

********** Error **********

FEHLER: Spalte »orte.plz« muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden
SQL state: 42803

So vorzugehen, wie der Fehlerhinweis vorschlägt, ist für mich keine Lösung, denn dann müßte ich zukünftig alle Felder der Tabelle in die GROUP-Anweisung schreiben.

Wer kann helfen? :confused:
Vielen Dank

Das ist allerdings gewollt, da sonst das Ergebnis nicht eindeutig ist. Du könntest bei zwei Abfragen auf die gleichen Daten ggf. unterschiedliche Ergebnisse bekommen.

Wenn du in MySQL den ganzen Ansi-Kram anschaltest, funktioniert diese Abfrage imho auch nicht mehr.

Hallo Andreas,

Deine Antwort hat mir die Augen geöffnet. Natürlich wäre das Ergebnis reiner Zufall, wenn ich nach einer beliebigen Spalte gruppieren würde. Das ist in meinem Fall jedoch ausgeschlossen, weil ich den Join auf den Primärschlüssel (id) der Tabelle “orte” ausführe und auch nur nach diesem Primärschlüsselfeld gruppiere. Somit ist das Ergebnis eindeutig. Aber ich kann nachvollziehen, daß Postgres diese Prüfung nicht macht, sondern nichteindeutige bzw. nichtreproduzierbare Ergebnisse verhindern will

Wie komme ich nun in Postgres zu meinem gewünschten Ergebnis? Sollte ich eher in Richtung doppelter Join oder Subselect experimentieren?

Vielen Dank
ratio

Du solltest es einfach machen wie es korrekt wäre, also eindeutig.

D.h. auch in mySQL wie gefordert gruppieren … so wie es SQL-standard verlangt und daher Postgres bemängelt

Hallo Symbiont,

ich bin für jeden Hinweis dankbar, mit dem ich überhaupt zu dem von mir gewünschten Ergebnis komme, denn bisher habe ich das leider noch nicht geschafft. Leider konnte ich auch nicht herausfinden, was der SQL-konforme Weg dafür ist. In der GROUP-BY Klausel alle Spalten aufzuführen, kann jedoch nicht der richtige Weg sein, weil die Gruppierung dann nicht greifen würde. Stell Dir vor, es gäbe in der Tabelle “orte” ein Feld “bundesland_id” und ich würde nun gerne die aggregierte Ansicht der “ereignisse” pro Bundsland anzeigen wollen. Das würde nicht gelingen, wenn mich Postgres dazu zwingt, “GROUP BY orte.id, orte.name, orte.plz, orte.bundesland_id, …” zu schreiben.

Ich komme nochmal zurück auf mein zweites Posting. Läßt sich denn nicht das Ergebnis der funktionierenden Abfrage, das mir “anzahl_ereignisse” und “orte.id” zurücklifert, für einen weiteren JOIN benutzen?

… ich würde so gerne von MySQL wegkommen … :rolleyes:

also … group by

GROUP BY wird immer dann benoetigt, wenn du auf irgendeine spalte (oder ausdruck) eine aggregat-funktion anwendest - also sowas wie count, avg max min usw. In so einem fall MUSST du lt. standard -> alle anderen (nicht aggregierten felder) die du ausgeben willst, gruppieren - ebenso die sonstwo verwendeten felder in deiner query - z.B. jene in bedingungen usw.

verwendest du keine aggregat-funktionen, so kannst du natuerlich auch GROUP BY verwenden … musst aber nicht …

mysql erlaubt dir halt das group by wegzulassen - was allerdings (theoretisch) zu einem unbestimmten ergebnis fuehrt. praktisch ist es nur bestimmt, wenn die auswahl der daten nach irgendwelchen technischen regeln folgt - die sind aber mysql spezifisch … vielleicht ists aber wirklich “zufall” nach dem motto - nimm halt nen feld von dem ersten datensatz den du dazu findest …

nutz die zeit - und machs einfach richtig -> group by auch in MySQL :slight_smile:

folge einfach den fehlermeldungen von Postgres

Ich glaube, wir reden aneinander vorbei. Ich habe überhaupt keine Einwände dagegen, GROUP BY anzuwenden. Ich möchte aber nur nach genau einem Kriterium gruppieren, und vielleicht nach anderen Kriterien sortieren. Und ich möchte alle Felder der Tabelle “orte” zusammen mit der Anzahl der “ereignisse” haben, ohne weitere Queries an die DB senden zu müssen. Ein sehr vergleichbares Beispiel findet sich auf dieser Seite:

Using GROUP BY with inner join : INNER JOIN « Table Joins « PostgreSQL

Dies Seite beschreibt genau die Variante 1 aus meinem ersten Posting. Nun hätte ich aber gerne (übertragen auf das neue Beispiel) in der Ergebnistabelle alle Felder der Tabelle “publisher” neben der Summe der “editions”. Das sollte doch auch in Postgres gehen…

Ich habe auch schon überlegt, ob JOIN hier der falsche Ansatz ist. Aber es gibt nun mal “orte” ohne “ereignisse” bzw. “publisher” ohne “editions”, und da kmmt man nur mit einem OUTER JOIN weiter.

nein - reden wir nicht …

das wichtigste gruppen kriterium kommt immer vorn ran

nehmen wir mal an da gibts die gruppe a … potentiell gibts dann zu kriterium a mehr als einen datensatz … was soll mit denen denn dann passieren?

wenn du nur einen satz fuer a willst, musst du die anderen kriterien aggregieren … wenn du die nicht aggregieren willst, musst du auch die anderen kriterien nach und nach gruppieren.

mysql liefert dir halt fuer das eine a -> IRGENDEINEN zufaelligen datensatz fuer jedes andere kriterium … das willst du doch auch nicht …

also sei gruendlich … d.h. arbeit :slight_smile:

Doch. Genau das will ich. Denn indem ich GROUP BY <Primärschlüssel> sage, ist doch klar, daß wirklich alle Datensätze dieser Tabelle in die Anzeige wandern sollen. Nach GROUP BY noch alle weiteren Feldnamen zu schreiben (was bei einem SELECT table.* erforderlich wäre), würde niemals ein anderes Ergebnis liefern. Denn der Primärschlüssel ist UNIQUE und NOT NULL. Sobald also in einer Gruppierungs-Anweisung zumindest auch der Primärschlüssel auftaucht, heißt das doch, daß die Ergebnismenge nicht eingeschränkt wird.

Das grundsätzliche Problem, weshalb der Error-Code 82803 angezeigt wird, habe ich - wie gesagt - schon verstanden. Ich benötige nun eine praxistaugliche Lösung, denn die Entscheidung zum Wechsel von MySQL nach PostgreSQL ist schon gefallen und ich habe viele Statements mit dieser Problematik.

Das Problem taucht übrigens nicht nur mit LEFT JOINS, sondern auch mit “normalen” INNER JOINS auf.

SELECT a.*, b.*
FROM a, b
WHERE a.id=b.id
GROUP BY a.id

Ob nun Unsinn, SQL-konform oder nicht. Überall dort, wo sie zum Einsatz kommt, hat sie ihre Berechtigung. Das Motiv ist meist die Ersparnis von zusätzlichen Queries und die Ersparnis von viel mehr Code unter Inkaufnahme von zusätzlichen Overhead. Wenn Postgres nichts ähnliches bietet, dann wäre das aus meiner Sicht (des mündigen DB-Admins :slight_smile: ) ein klarer Nachteil.

Also, irgendwelche Ideen, wie ich die MySQL-nicht-SQL-konforme Funktionalität nachbilden kann?

*habe soeben gelernt, daß Änderungen können nur innerhalb von 60 Minuten vorgenommen werden können…

Doch. Genau das will ich. Denn indem ich GROUP BY <Primärschlüssel> sage, ist doch klar, daß wirklich alle Datensätze dieser Tabelle in die Anzeige wandern sollen. Und wenn ich alle diese Datensätze in einem JOIN mit der Aggregationsfunktion COUNT() verbinde, sollte auch das eindeutig sein. Nach GROUP BY noch alle weiteren Feldnamen aus der ersten Tabelle zu schreiben (was bei einem SELECT table1. erforderlich wäre), würde niemals ein anderes Ergebnis liefern. Denn der Primärschlüssel ist UNIQUE und NOT NULL. Sobald also in einer Gruppierungs-Anweisung zumindest auch der Primärschlüssel auftaucht, heißt das doch, daß die Ergebnismenge für diese Tabelle nicht eingeschränkt wird.

Das grundsätzliche Problem, weshalb der Error-Code 82803 angezeigt wird, habe ich - wie gesagt - schon verstanden. Ich benötige nun eine praxistaugliche Lösung, denn die Entscheidung zum Wechsel von MySQL nach PostgreSQL ist schon gefallen und ich habe viele Statements mit dieser Problematik.

Das Problem taucht übrigens nicht nur mit LEFT JOINS, sondern auch mit “normalen” INNER JOINS auf.

SELECT a.*, b.*
FROM a, b
WHERE a.id=b.id
GROUP BY a.id

Ob nun Unsinn, SQL-konform oder nicht. Überall dort, wo diese Form der Abfrage zum Einsatz kommt, hat sie ihre Berechtigung. Das Motiv ist meist die Ersparnis von zusätzlichen Queries oder die Ersparnis von viel mehr Code unter Inkaufnahme von zusätzlichen Overhead. Wenn ich in meinem Ausgangsbeispiel in der ersten Tabelle 20 Spalten habe, dann muß ich 20 Feldnamen in die GROUP BY Anweisung schreiben. Wenn Postgres nichts ähnliches bietet, dann wäre das aus meiner Sicht (des mündigen DB-Admins :slight_smile: ) ein klarer Nachteil und ich würde die Frage “feature or bug” mit letzterem beantworten.

Also, irgendwelche Ideen, wie ich die MySQL-nicht-SQL-konforme Funktionalität nachbilden kann? Was meinen die Fortgeschrittenen hier im Forum? Sollte man soetwas auf die Wunschliste der Postgres-Entwickler setzen? Wie löst Ihr das Problem? Durch zusätzliche Queries oder durch massenhafte Einträge nach GROUP BY?

Ich meine, daß:

  1. nachträgliches Ändern von Beiträgen, außer für Rechtschreibkorrekturen, sehr schlecht ist

  2. Du mal nun aufhören könntest, wie ein kleines Kind mit dem Fuß zu stampfen, nur weil es im Supermarkt mal nicht die Bonbons bekommen hat

  3. Zeige eine Beispieltabelle, idealerweise gleich mit create table und insert-befehlen, und Dein Wunschresultat.


    Andreas

Du könntest sowas probieren:

SELECT orte.*, t.anzahl_ereignisse 
FROM orte 
  JOIN (
    SELECT oi.id AS id, count(ereignisse.*) AS anzahl_ereignisse
    FROM orte oi 
    LEFT JOIN ereignisse ON ereignisse.orte_id = oi.id
    GROUP BY oi.id) t
  ON t.id = orte.id

Da Du kein CREATE TABLE und keine INSERTs als Beispiel angegeben hast, konnte ich das natürlich nicht testen.

das ist DIR klar - aber nicht dem DB-Server … auch MySQL weiss nix davon … da kommt dann nur zufällig ein sinnvolles Ergebnis bei raus …

[edit]
und ob sql-konform oder nicht - postgres machts nunmal so - andere dbms guenstiger weise auch

ausserdem hat dies alles nix mit der wahl des joins zu tun … du wirst darueber auch bei nur einer tabelle stolpern …

du hast gesagt - du willst von MySQL weg kommen - dann gewöhn dich schonmal daran, dass andere DBMS penibler reagieren und dich fuer dinge abstrafen :slight_smile: die MySQL einfach so geschluckt hat - du darfst dich im gegenzug dann natuerlich auch daran gewoehnen, dass das was das DBMS als Ergebnis liefert sehr wahrscheinlich das ist, was du wolltest - weil kein raum für spekulationen bleibt

und ob du meinst, dass “es fuer dich keine loesung sei alle verwendeten columns in GROUP BY zu notieren” spielt auch keine rolle - wenn du postgres nutzen willst, dann MUSST du mit Postgres so sprechen wie es verlangt

@castorp
Herzlichen Dank, das war die Lösung, die ich suchte.

@symbiont
Vielen Dank für die anregende Diskussion, die wirklich erhellend war. Gleichwohl behalte ich die Überzeugung, daß Postgres dahingehend verbessert werden sollte, daß auf die Angabe weiterer Feldnamen aus einer spezifischen Tabelle in der GROUP BY Klausel verzichtet werden kann, sobald mindestens einer der folgenden Spaltentypen aus dieser spezifischen Tabelle in GROUP BY auftaucht:

  • Primärschlüssel-Feld
  • Felddefinition mit UNIQUE und NOT NULL

@akretschmer
Keine Sorge, es ist nicht das Stampfen des kleinen Kindes, sondern das beharrliche Erforschen der Unterschiede zwischen MySQL und PgSQL und die Suche nach praxistauglichen Lösungen. Ich bereite seit über einem Jahr den Umstieg von MySQL zu PgSQL mit vielen und wirklich großen Datenbanken vor und stecke nun mitten im Umzug. Ich dokumentiere diese Arbeit sehr ausführlich und werde sie in Kürze als deutschsprachigen Migrationsleitfaden auf einer eigenen Webseite veröffentlichen. Das wird dann hoffentlich mehr Admins den Umstieg erleichtern und das gefällt Dir bestimmt auch. Also mach Dich gefaßt auf viele weitere Fragen von mir hier im Forum. :astonished:

@ads
Wann erscheint endlich Dein Buch. Habe es schon vor drei Monaten bei einem großen online-Versandhandelshaus vorbestellt und erwarte es sehnsüchtig.

Nein, dass würde dem SQL Standard widersprechen an den sich (zumindest in diesem Bereich) alle anderen DBMS halten. Zumindest die die mir bekannt sind: Oracle, SQL Server, DB2, Firebird, HSQL, H2, Ingres.

Nur MySQL ist (mal wieder) die unrühmliche Ausnahme

Aber MySQL ist ja bekannt dafür, dass es seine Prioritäten auf Geschwindigkeit legt - und nicht auf Korrektheit :rolleyes:

Das gibt es schon :wink:
Ich habe eines gerade in meinem Rucksack.

hi,

auf pgsql-hackers läuft gerade eine Diskussion zu diesem Thema, wobei dort der Ausgangspunkt Drupal ist.
Dabei scheint es für den Fall des OP (group by über PK) schon eine Ausnahme in der Spec zu geben, aber es hat noch niemand die Zeit (und das Geld) gehabt, in diese Richtung zu entwickeln.
Hier der Link zur Diskussion:Re: Feedback about Drupal SQL debugging

Ack.
mysql schmeisst dir das genauso um die Ohren, wenn es im Ansi-Modus betrieben wird.

Freundliche Grüße
hkocam

nunja … auch ein group ueber den PK einer tabelle löst doch nicht das problem, dass es zu jedem key spaetestens bei joins mehr als einen satz geben kann … und wenn, in welcher reihenfolge sollen die “anderen” felder denn gruppiert werden …

Die referenzierte Liste von “Problemen” mit Postgres (auf der Drupal Seite) ist aber ziemlich naiv.

In dem Mail-Thread hat das jemand gut zusammengefasst:
“If you don’t write standards-compliant SQL, you shouldn’t be surprised when you find out that it’s not portable”.

Letztendlich laufen alle Probleme in der Liste darauf hinaus, dass sich MySQL nicht an Standards hält.

Das Problem des OP lässt sich entweder mit dem Join über eine derived table lösen (Standard SQL, wie in meinem Posting geziegt) oder mit Windowing Functions die auch im ANSI Standard definiert sind.
Allerdings nur von Postgres 8.4 und Oracle in einem vernünftigen Umfang implementiert sind.

Edit:

habe gerade noch die Bemerkung “mysql schmeisst dir das genauso um die Ohren, wenn es im Ansi-Modus betrieben wird.” gesehen

Wobei ich das Gefühl habe, dass niemand MySQL im ANSI modus betreibt :wink:

Doch, ich. Und dann fallen eine ganze Menge Applikationen auf die censored. Schön ist das nicht.