Fehler: column ... does not exist

Hi,

ich bin ein neuer PostgreSQL-Anwender und teste damit gerade meine Web-Anwendung. Mit MySQL 5.0 und SQLite funktioniert alles prima, SQL Server 2005 kann leider nicht genug SQL, und jetzt ist PostgreSQL 8.2 dran. Was man so liest, soll dieses System ja so ziemlich alles können. Naja gut. Mit meiner einer Seite langen SQL-Abfrage hat es dann aber doch Probleme. Ich werd euch einfach mal die Abfrage zeigen, in der Hoffnung, dass das nicht zu sehr abschreckt…

SELECT
    (EXISTS
        -- Message has no read access keylist
        (SELECT 1
        FROM "#message" m
        WHERE m."MessageId" = ? AND
            m."ReadAccessKeylist" IS NULL)
    OR EXISTS
        -- Message\'s read access keylist contains personal or additional user key
        (SELECT 1
        FROM "#message" m, "#keylist" mk
        WHERE mk."KeylistId" = m."ReadAccessKeylist" AND
            m."MessageId" = ? AND
            (mk."UserId" = ? OR
                mk."UserId" IN
                    (SELECT uk."UserId"
                    FROM "#user" u, "#keylist" uk
                    WHERE uk."KeylistId" = u."AdditionalKeylist" AND
                        u."UserId" = ?))))
    AND NOT EXISTS
        -- There must not be any tag that denies access
        (SELECT
            EXISTS
                -- Tag\'s read access keylist contains personal or additional user key
                (SELECT 1
                FROM "#keylist" tk
                WHERE tk."KeylistId" = t."ReadAccessKeylist" AND
                    (tk."UserId" = ? OR
                        tk."UserId" IN
                            (SELECT uk."UserId"
                            FROM "#user" u, "#keylist" uk
                            WHERE uk."KeylistId" = u."AdditionalKeylist" AND
                                u."UserId" = ?)))
            AS "Allowed"
        FROM "#message" m
            JOIN "#message_tag" mt USING ("MessageId")
            JOIN "#tag" t USING ("TagId")
        WHERE m."MessageId" = ? AND
            t."ReadAccessKeylist" IS NOT NULL   -- Only regard tags with read access keylist (only they can fail the access test)
        GROUP BY 1   -- SQLite requires a GROUP BY clause before HAVING
        HAVING NOT "Allowed")

Dazu noch die benötigten Tabellen in Kurzform:

message (MessageId, Author, ReadAccessKeylist, …)
keylist (KeylistId, UserId)
user (UserId, AdditionalKeylist, …)
tag (TagId, ReadAccessKeylist, …)
message_tag (MessageId, TagId, …)

Und wenn ich das alles in den Server werfe, kommt folgendes zurück:

ERROR: column “Allowed” does not exist LINE 37: HAVING NOT “Allowed”) ^
(Fehlernummer: 7, PDO-Fehlercode: 42703)

“Allowed” ist keine Spalte einer echten Tabelle, sondern eine Spalte im Ergebnis der Abfrage. Warum sollte das nicht funktionieren?

Vielleicht deswegen. Gibt der Spalte in dieser Tabelle einen explizieten Namen und prüfe darauf.

test=*# select * from foo;
 id
----
  1
  2
(2 rows)

test=*# select * from (select *, 1::bool as "Allowed" from foo) bar group by id, "Allowed" having  "Allowed";
 id | Allowed
----+---------
  1 | t
  2 | t
(2 rows)

“Allowed” ist nun eine Spalte von bar.

Andreas

Ähm, geht das wirklich nicht anders? Das macht die Sache in meinem Fall nämlich noch ein Stück komplizierter, als sie sowieso schon ist… Ich berechne “Allowed” ja bereits aus 3 anderen Tabellen und selektiere die Datensätze für diese Berechnung. Außerdem hätte ich dann noch eine Subquery mehr.

Ich hab grad nochmal im SQL1992-Text gelesen, und ich denke, dass jede Spaltenangabe in HAVING eine “grouping column” oder “outer reference” sein soll. Aber auch, wenn ich anstelle GROUP BY 1 ein GROUP BY “Allowed” hinschreibe, bekomme ich den gleichen Fehler. (Sonderlich einleuchtend ist die Formulierung dort aber nicht.)

Auch wenn ich das NOT “Allowed” von HAVING in den WHERE-Teil schiebe, bekomme ich einen solchen Fehler. Man wird ja wohl in SQL noch nach berechneten Spalten filtern können?!

Wie auch immer, ich hab diese Bedingung jetzt mit einer weiteren Subquery formuliert und nun funktioniert es, wie es soll. (Auch mit MySQL und SQLite. :slight_smile: ) Danke für den Tipp!

Ich hab mit HAVING kaum Erfahrung, aber mein Verständniss davon ist, daß das faktisch wie WHERE funktioniert, nur auf gruppierte Spalten.

Andreas

Ich bin mir nicht mehr sicher, bei welchem der beiden anderen Datenbanksysteme ich das gelesen hab, aber in WHERE darf man dort keine im SELECT-Teil berechneten Spalten verwenden, da deren Wert während der Selektion der Zeilen u.U. noch gar nicht berechnet wurde. Deshalb wird das dort von vornherein ausgeschlossen. Vielleicht wären auch beide (MySQL und SQLite) davon betroffen und ich hab’s gleich beim ersten Test so geändert. SQLite jedenfalls mag kein HAVING ohne ein GROUP BY, sei es auch noch so nutzlos. PostgreSQL versteht diese berechneten Spalten scheinbar ja leider weder in WHERE noch in HAVING, was andere Lösungen (wie die hier genannte) erfordert.

Ich mache mir jetzt nicht erst die Mühe zu versuchen Deine Abfrage zu verstehen.
Aber

ich kann nirgendwo ein AS Allowed entdecken und DBMS offensichtlich uach nicht.

Ja, aber Du kannst ein
WHERE spalte1+spalte2 < spalte 4
angeben.

Ein HAVING ohne GROUP ist sinnfrei. Having filtert auf gruppierte Ergebnisse.

PostgreSQL versteht diese berechneten Spalten scheinbar ja leider weder in WHERE noch in HAVING, was andere Lösungen (wie die hier genannte) erfordert.

SQL lernen, z.B. :slight_smile:

Bei manchen Systemen kann HAVING offensichtlich mehr als WHERE, und so muss ich Bedingungen in HAVING verschieben, auch ohne dass ich irgendwas gruppieren will. Hat ja wunderbar funktioniert, bis PGSQL kam… :wink:

Wenn andere Systeme sich nicht an den Sprachstandard halten, so ist das deren Ding. Man sollte dann aber nicht erwarten, das dies dann von allen anderen dumb nachgemacht wird.

Andreas

Oh, welches System sich hier besser an den “Sprachstandard” hält, möchte ich momentan lieber nicht entscheiden. (Aber ich hätt mir ja denken können, dass es auf dieses Thema rausläuft, wenn ich in diesem Forum andere Datenbanksysteme und deren Unterschiede erwähne…)

HAVING ist etwas grundsätzlich anderes als WHERE!
Der Vergleich “kann mehr” ist einfach sinnfrei.

Um das mal klarzustellen: das soll nicht auf einen Schwanzvergleich hinauslaufen, wer sich mehr an was auch immer hält. Es geht auch nicht darum, andere Systeme NICHT zu nennen. Ganz im Gegenteil, wenn andere Systeme etwas besser machen oder anders und man da was lernen kann, nur zu. Aber offensichtliche Fehler in anderen Systemen als Vorteil auszugeben und zu mosern, daß das hier nicht geht ist sinnfrei.

HAVING arbeitet über bereits aggregierten Daten, die vorher vom WHERE ausgesucht werden. Dein im ersten Beitrag gezeigtes SQL war ganz offensichtlich in dem Sinne kapott, daß Dein HAVING auf einem Alias für eine ganze Tabelle angesetzt war, was flasch ist. Du hättest da auch kein weiteres subselect machen brauchen, sondern nur Deiner Ergebnisspalte einen Namen (Alias) geben und den im HAVING verwenden. Vermute ich mal.

Das, was in anderen Systemen an kapottem SQL funktioniert, wird möglicherweise bereits in der nächsten Version dort auch nicht mehr gehen. Speziell MySQL hat da in den letzten Jahren viel kapotten Scheiß repariert (übrigens auch PostgreSQL).
Sehe es also eher als Feature denn als Nachteil an, wenn etwas nicht so geht wie woanders. Zumindest dann, wenn man dadurch Zeitbomben findet.


Andreas

WHERE sucht Datensätze aus, die zum Joinen und zur Gruppierung verwendet werden. HAVING sucht Datensätze aus, die danach noch übrig bleiben. In beiden Fällen wird die Ergebnismenge reduziert, nur in unterschiedlichen Phasen der Anfrageverarbeitung. Wenn ich in einem Fall einen dieser Filter in HAVING verwenden kann, in WHERE aber nicht, dann kann für mich HAVING mehr als WHERE. Mag nicht ganz wissenschaftlich klingen, ist aber mein Eindruck.

Hab ich doch? Und es ging nicht, weil genau dieser Alias nicht bekannt war, was mich eben verwundert hat, weil ich bisher der Ansicht war, dass man spätestens in HAVING alle Spalten der Ergebnismenge auch mit ihrem Alias* für Bedingungsausdrücke verwenden können sollte, und das in zwei anderen Systemen ja auch ging.

*) Anstatt dem Alias “Allowed” den Spaltennamen oder Berechnungsausdruck anzugeben halte ich aufgrund dessen Länge nicht für sinnvoll. Dann mach ich aus dem Alias lieber durch eine Subquery einen “richtigen Spaltennamen”.

Ist doch sooo einfach, wenn man sich die Syntax-Regeln hält!

SELECT select_list FROM … [WHERE …] GROUP BY … > HAVING > boolean_expression

Der Alias “Allowed” ist eine Tabellenreferenz.


Statt

...
HAVING NOT "Allowed")

muss man nur

...
HAVING NOT EXISTS ("Allowed"))

schreiben.



mfg

fristo

fristo, nachdem mir unerklärlich war, warum dein Vorschlag funktionieren sollte, hab ich ihn ausprobiert. Funktioniert aber leider weder in PostgreSQL noch in MySQL.

Hallo LonelyPixel,

ich hatte heute Nacht nicht die Möglichkeit auf die schnelle einen Test durchzuführen. Und noch etwas zu schreiben, war mir zu aufwendig. Die Befehlszeile war daher ungetestet.

Eindeutig ist aber, dass laut SYNTAX nach HAVING ein boolean_expression gefordert ist. Du hattest mit dem Alias “Allowed” ist eine Tabellenreferenz angegeben.

So, nun habe ich etwas Code geschrieben und getestet. Was funktioniert ist dieses Form:

select title, year from files AS aaa
group by title, year
having EXISTS (SELECT year FROM files);

und wenn man der PostgreSQL Doku glauben schenkt

A temporary name can be given to tables and complex table references > to be used > for references to the derived table > in the rest of the query> . This is called a > table alias> .

dann sollte auch diese Form funktionieren:

select title, year from files AS aaa
group by title, year
having EXISTS (SELECT year FROM aaa);

Hier hat man Pech, die Datenbank meldet einen Fehler, weil sie nicht die Tabellenreferenz “aaa” kennt. Was ist nun Standard, was in der Doku steht oder was die Datenbank akzeptiert?


Nix für ungut, es sollte ein Tip auf die Schnelle sein. Nur sollte man ihn doch vorher testen.



mfg

fristo

Warum sollte PG auf die Idee kommen, daß das in der Klammer sich auf den Alias vorher beziehen sollte?

Im übrigen, Dein Beispiel, leicht abgewandelt, funktioniert bei mir:

test=# select * from foo;
 i | j
---+---
 1 | 1
 2 | 2
 3 | 3
(3 rows)

test=*# select i,j from foo as bar group by i,j having exists (select i from bar);
 i | j
---+---
 1 | 1
 3 | 3
 2 | 2
(3 rows)

Allerdings nicht lange…

test=*# drop table bar;
DROP TABLE
test=*# select i,j from foo as bar group by i,j having exists (select i from bar);
ERROR:  relation "bar" does not exist

Andreas

Mal abgesehen davon, dass “Allowed” vom Typ boolean ist und somit theoretisch als boolean_expression ausgewertet werden können sollte, hat PGSQL ja nicht bemängelt, dass irgendein Typ nicht passt, sondern dass “Allowed” völlig unbekannt wäre. Dass man zum Ausfiltern von Datensätzen ein Prädikat (Ergebnistyp: boolean) braucht, ist ja klar.

Vielleicht sollte man mal versuchen, einen Testfall aus meiner ursprünglichen SQL-Abfrage abzuleiten und das den Entwicklern als Fehlerbericht zusenden. Dann bekommt man schon raus, ob das so beabsichtigt war oder nicht. Bis dahin funktioniert der “Workaround” mit dem Einkapseln in ein Subselect aber ganz gut.

Okay, PostgreSQL-Support werd ich in meiner Anwendung wohl auch streichen müssen. Die zuvor besprochene Abfrage funktioniert zwar mit allen drei Systemen, aber jetzt kommt eine Erweiterung dazu: Diese Abfrage muss auch als Subquery in einer anderen Select-Abfrage laufen. Dazu wird sie als Correlated Subquery verwendet, was weder in MySQL (wie dokumentiert), noch in SQLite funktioniert, wenn man sie im FROM-Teil verwendet. Da funktionieren Correlated Subqueries anscheinend nur, wenn man sie im SELECT-Teil verwendet.

Wie’s scheint, ist es in komplexeren Projekten völlig unmöglich, portablen SQL-Code zu verfassen. Auf irgendein System und dessen Fähigkeiten und Fehler muss man sich festlegen. Tut mir leid, aber MySQL und SQLite sind mir dabei wichtiger als PostgreSQL. Und solange PostgreSQL die Abfrage in ihrer ursprünglichen Form (siehe erster Beitrag) nicht verarbeiten kann, hilft mir das einfach nicht weiter.

Danke trotzdem für eure Unterstützung.