Skip to content

Commit 752533d

Browse files
alvherrealvherre
committed
Test EXPLAIN (FORMAT JSON) ... XMLTABLE
Also, add an alias to the XMLTABLE expression in an existing test. This covers some code in explain.c that wasn't previously covered. I patched xml_2.out blindly :-( Discussion: https://postgr.es/m/202401181146.fuoeskfzriq7@alvherre.pgsql
1 parent b0f0a94 commit 752533d

File tree

4 files changed

+142
-16
lines changed

4 files changed

+142
-16
lines changed

‎src/test/regress/expected/xml.out

Lines changed: 46 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1549,19 +1549,60 @@ SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan"
15491549
(1 row)
15501550

15511551
EXPLAIN (VERBOSE, COSTS OFF)
1552-
SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
1552+
SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) AS f WHERE "COUNTRY_NAME" = 'Japan';
15531553
QUERY PLAN
15541554
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
15551555
Nested Loop
1556-
Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
1556+
Output: f."COUNTRY_NAME", f."REGION_ID"
15571557
-> Seq Scan on public.xmldata
15581558
Output: xmldata.data
1559-
-> Table Function Scan on "xmltable"
1560-
Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
1559+
-> Table Function Scan on "xmltable" f
1560+
Output: f."COUNTRY_NAME", f."REGION_ID"
15611561
Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer)
1562-
Filter: ("xmltable"."COUNTRY_NAME" = 'Japan'::text)
1562+
Filter: (f."COUNTRY_NAME" = 'Japan'::text)
15631563
(8 rows)
15641564

1565+
EXPLAIN (VERBOSE, FORMAT JSON, COSTS OFF)
1566+
SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) AS f WHERE "COUNTRY_NAME" = 'Japan';
1567+
QUERY PLAN
1568+
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1569+
[ +
1570+
{ +
1571+
"Plan": { +
1572+
"Node Type": "Nested Loop", +
1573+
"Parallel Aware": false, +
1574+
"Async Capable": false, +
1575+
"Join Type": "Inner", +
1576+
"Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], +
1577+
"Inner Unique": false, +
1578+
"Plans": [ +
1579+
{ +
1580+
"Node Type": "Seq Scan", +
1581+
"Parent Relationship": "Outer", +
1582+
"Parallel Aware": false, +
1583+
"Async Capable": false, +
1584+
"Relation Name": "xmldata", +
1585+
"Schema": "public", +
1586+
"Alias": "xmldata", +
1587+
"Output": ["xmldata.data"] +
1588+
}, +
1589+
{ +
1590+
"Node Type": "Table Function Scan", +
1591+
"Parent Relationship": "Inner", +
1592+
"Parallel Aware": false, +
1593+
"Async Capable": false, +
1594+
"Table Function Name": "xmltable", +
1595+
"Alias": "f", +
1596+
"Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], +
1597+
"Table Function Call": "XMLTABLE(('/ROWS/ROW[COUNTRY_NAME=\"Japan\" or COUNTRY_NAME=\"India\"]'::text) PASSING (xmldata.data) COLUMNS \"COUNTRY_NAME\" text, \"REGION_ID\" integer)",+
1598+
"Filter": "(f.\"COUNTRY_NAME\" = 'Japan'::text)" +
1599+
} +
1600+
] +
1601+
} +
1602+
} +
1603+
]
1604+
(1 row)
1605+
15651606
-- should to work with more data
15661607
INSERT INTO xmldata VALUES('<ROWS>
15671608
<ROW id="10">

‎src/test/regress/expected/xml_1.out

Lines changed: 46 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1174,19 +1174,60 @@ SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan"
11741174
(0 rows)
11751175

11761176
EXPLAIN (VERBOSE, COSTS OFF)
1177-
SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
1177+
SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) AS f WHERE "COUNTRY_NAME" = 'Japan';
11781178
QUERY PLAN
11791179
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
11801180
Nested Loop
1181-
Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
1181+
Output: f."COUNTRY_NAME", f."REGION_ID"
11821182
-> Seq Scan on public.xmldata
11831183
Output: xmldata.data
1184-
-> Table Function Scan on "xmltable"
1185-
Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
1184+
-> Table Function Scan on "xmltable" f
1185+
Output: f."COUNTRY_NAME", f."REGION_ID"
11861186
Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer)
1187-
Filter: ("xmltable"."COUNTRY_NAME" = 'Japan'::text)
1187+
Filter: (f."COUNTRY_NAME" = 'Japan'::text)
11881188
(8 rows)
11891189

1190+
EXPLAIN (VERBOSE, FORMAT JSON, COSTS OFF)
1191+
SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) AS f WHERE "COUNTRY_NAME" = 'Japan';
1192+
QUERY PLAN
1193+
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1194+
[ +
1195+
{ +
1196+
"Plan": { +
1197+
"Node Type": "Nested Loop", +
1198+
"Parallel Aware": false, +
1199+
"Async Capable": false, +
1200+
"Join Type": "Inner", +
1201+
"Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], +
1202+
"Inner Unique": false, +
1203+
"Plans": [ +
1204+
{ +
1205+
"Node Type": "Seq Scan", +
1206+
"Parent Relationship": "Outer", +
1207+
"Parallel Aware": false, +
1208+
"Async Capable": false, +
1209+
"Relation Name": "xmldata", +
1210+
"Schema": "public", +
1211+
"Alias": "xmldata", +
1212+
"Output": ["xmldata.data"] +
1213+
}, +
1214+
{ +
1215+
"Node Type": "Table Function Scan", +
1216+
"Parent Relationship": "Inner", +
1217+
"Parallel Aware": false, +
1218+
"Async Capable": false, +
1219+
"Table Function Name": "xmltable", +
1220+
"Alias": "f", +
1221+
"Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], +
1222+
"Table Function Call": "XMLTABLE(('/ROWS/ROW[COUNTRY_NAME=\"Japan\" or COUNTRY_NAME=\"India\"]'::text) PASSING (xmldata.data) COLUMNS \"COUNTRY_NAME\" text, \"REGION_ID\" integer)",+
1223+
"Filter": "(f.\"COUNTRY_NAME\" = 'Japan'::text)" +
1224+
} +
1225+
] +
1226+
} +
1227+
} +
1228+
]
1229+
(1 row)
1230+
11901231
-- should to work with more data
11911232
INSERT INTO xmldata VALUES('<ROWS>
11921233
<ROW id="10">

0 commit comments

Comments
 (0)