Wir haben hier eine Tabelle mit einer text Spalte in der manchmal XML drin steht und manchmal nicht. Vereinfacht ausgedrückt, sowas:
create table data
(
id int, content text
);
insert into data (id, content)
values
(1, '<info><name>peter</name><location>somewhere</location></info>'),
(2, '$bla=blub');
(nicht unser Design, gekaufte Anwendung)
Ich möchte die Daten aus dem XML auslesen, und dachte das Folgende müsste doch eigentlich funktionieren:
select d.id,
x.*
from data d
left join xmltable ('/info'
passing cast (d.content as xml)
columns name text path 'name',
location text path 'location'
) as x on d.content like '<info%'
Leider sieht es so aus, als ob die JOIN Bedingung die nicht-XML Datensätze nicht vor dem Aufruf von XMLTABLE rausfiltert, sondern danach - und das hat mich etwas verwirrt bzw. überrascht. Ich hätte erwartet, dass die JOIN Bedingung vor dem Aufruf der XMLTABLE Funktion ausgeführt wird. Auch ein ON d.content like '<%' funktioniert nicht.
Mir ist klar, wie ich das umgehen kann (mit einem CASE), aber ist meine Annahme, dass eine join Bedingung Datensätze vor der Verarbeitung ausfiltert, falsch?
Für mich sieht das eher nach einem Bug aus wenn ich ehrlich bin.
Kommt drauf an, wie das ausgeführt wird (im Feld, nicht im Beispiel)
Explain sagt:
QUERY PLAN
Nested Loop Left Join (cost=0.00..2883.38 rows=1270 width=68)
Join Filter: (d.content ~~ '<info%'::text)
-> Seq Scan on data d (cost=0.00..22.70 rows=1270 width=36)
-> Table Function Scan on "xmltable" x (cost=0.00..1.00 rows=100 width=64)
Der Typ Text erlaubt ja keine Garantien, das es valides XML ist.
Warum nicht sowas:
select * from data where content::xml is document
Und mit der Menge dann weiterarbeiten?
Ich könnte mir vorstellen, dass man vielleicht mit der gezeigten Konstellation große Datenmengen überspringen will, aber vielleicht verstehe ich den Sinn davon auch nicht. XMLTable ist vermutlich auf validen Content angewiesen. like <info% garantiert das aber als Filter auch nicht.
Das ist kein Bug; PostgreSQL rechnet die rechte Tabelle aus, bevor es die Join-Bedingung anwendet. Eliminiere die bösen Zeilen einfach früher:
SELECT d.id,
x.*
FROM (SELECT id,
CASE WHEN content LIKE '<info%'
THEN content
END AS content
FROM data) AS d
CROSS JOIN LATERAL xmltable(
'/info'
PASSING cast (d.content AS xml)
COLUMNS name text path 'name',
location text path 'location'
);
Effizienter wäre es aber, die XMLTABLE Funktion gar nicht erst aufzurufen, wenn die JOIN Bedingung ein false liefert. Ich habe das CASE direkt in den CAST Operator aufgenommen.