aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorAlvaro Herrera <alvherre@alvh.no-ip.org>2024-01-22 17:14:19 +0100
committerAlvaro Herrera <alvherre@alvh.no-ip.org>2024-01-22 17:14:19 +0100
commit752533d40fd50de0b09d4b956cc32c38f5df2f05 (patch)
tree78acbf73fed38d4a48c6deaff4a2788b5ce096a9
parentb0f0a9432d0b6f53634a96715f2666f6d4ea25a1 (diff)
downloadpostgresql-752533d40fd50de0b09d4b956cc32c38f5df2f05.tar.gz
postgresql-752533d40fd50de0b09d4b956cc32c38f5df2f05.zip
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
Diffstat
-rw-r--r--src/test/regress/expected/xml.out51
-rw-r--r--src/test/regress/expected/xml_1.out51
-rw-r--r--src/test/regress/expected/xml_2.out51
-rw-r--r--src/test/regress/sql/xml.sql5
4 files changed, 142 insertions, 16 deletions
diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out
index 13e4296bf8f..6500cff885d 100644
--- a/src/test/regress/expected/xml.out
+++ b/src/test/regress/expected/xml.out
@@ -1549,19 +1549,60 @@ SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan"
(1 row)
EXPLAIN (VERBOSE, COSTS OFF)
-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';
+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';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop
- Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
+ Output: f."COUNTRY_NAME", f."REGION_ID"
-> Seq Scan on public.xmldata
Output: xmldata.data
- -> Table Function Scan on "xmltable"
- Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
+ -> Table Function Scan on "xmltable" f
+ Output: f."COUNTRY_NAME", f."REGION_ID"
Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer)
- Filter: ("xmltable"."COUNTRY_NAME" = 'Japan'::text)
+ Filter: (f."COUNTRY_NAME" = 'Japan'::text)
(8 rows)
+EXPLAIN (VERBOSE, FORMAT JSON, COSTS OFF)
+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';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Nested Loop", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Inner", +
+ "Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], +
+ "Inner Unique": false, +
+ "Plans": [ +
+ { +
+ "Node Type": "Seq Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "xmldata", +
+ "Schema": "public", +
+ "Alias": "xmldata", +
+ "Output": ["xmldata.data"] +
+ }, +
+ { +
+ "Node Type": "Table Function Scan", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Table Function Name": "xmltable", +
+ "Alias": "f", +
+ "Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], +
+ "Table Function Call": "XMLTABLE(('/ROWS/ROW[COUNTRY_NAME=\"Japan\" or COUNTRY_NAME=\"India\"]'::text) PASSING (xmldata.data) COLUMNS \"COUNTRY_NAME\" text, \"REGION_ID\" integer)",+
+ "Filter": "(f.\"COUNTRY_NAME\" = 'Japan'::text)" +
+ } +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
-- should to work with more data
INSERT INTO xmldata VALUES('<ROWS>
<ROW id="10">
diff --git a/src/test/regress/expected/xml_1.out b/src/test/regress/expected/xml_1.out
index eb9c6f2ed41..9323b84ae21 100644
--- a/src/test/regress/expected/xml_1.out
+++ b/src/test/regress/expected/xml_1.out
@@ -1174,19 +1174,60 @@ SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan"
(0 rows)
EXPLAIN (VERBOSE, COSTS OFF)
-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';
+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';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop
- Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
+ Output: f."COUNTRY_NAME", f."REGION_ID"
-> Seq Scan on public.xmldata
Output: xmldata.data
- -> Table Function Scan on "xmltable"
- Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
+ -> Table Function Scan on "xmltable" f
+ Output: f."COUNTRY_NAME", f."REGION_ID"
Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer)
- Filter: ("xmltable"."COUNTRY_NAME" = 'Japan'::text)
+ Filter: (f."COUNTRY_NAME" = 'Japan'::text)
(8 rows)
+EXPLAIN (VERBOSE, FORMAT JSON, COSTS OFF)
+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';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Nested Loop", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Inner", +
+ "Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], +
+ "Inner Unique": false, +
+ "Plans": [ +
+ { +
+ "Node Type": "Seq Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "xmldata", +
+ "Schema": "public", +
+ "Alias": "xmldata", +
+ "Output": ["xmldata.data"] +
+ }, +
+ { +
+ "Node Type": "Table Function Scan", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Table Function Name": "xmltable", +
+ "Alias": "f", +
+ "Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], +
+ "Table Function Call": "XMLTABLE(('/ROWS/ROW[COUNTRY_NAME=\"Japan\" or COUNTRY_NAME=\"India\"]'::text) PASSING (xmldata.data) COLUMNS \"COUNTRY_NAME\" text, \"REGION_ID\" integer)",+
+ "Filter": "(f.\"COUNTRY_NAME\" = 'Japan'::text)" +
+ } +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
-- should to work with more data
INSERT INTO xmldata VALUES('<ROWS>
<ROW id="10">
diff --git a/src/test/regress/expected/xml_2.out b/src/test/regress/expected/xml_2.out
index c8ed8e0cfa6..e1d165c6c93 100644
--- a/src/test/regress/expected/xml_2.out
+++ b/src/test/regress/expected/xml_2.out
@@ -1529,19 +1529,60 @@ SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan"
(1 row)
EXPLAIN (VERBOSE, COSTS OFF)
-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';
+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';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop
- Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
+ Output: f."COUNTRY_NAME", f."REGION_ID"
-> Seq Scan on public.xmldata
Output: xmldata.data
- -> Table Function Scan on "xmltable"
- Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
+ -> Table Function Scan on "xmltable" f
+ Output: f."COUNTRY_NAME", f."REGION_ID"
Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer)
- Filter: ("xmltable"."COUNTRY_NAME" = 'Japan'::text)
+ Filter: (f."COUNTRY_NAME" = 'Japan'::text)
(8 rows)
+EXPLAIN (VERBOSE, FORMAT JSON, COSTS OFF)
+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';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Nested Loop", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Inner", +
+ "Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], +
+ "Inner Unique": false, +
+ "Plans": [ +
+ { +
+ "Node Type": "Seq Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "xmldata", +
+ "Schema": "public", +
+ "Alias": "xmldata", +
+ "Output": ["xmldata.data"] +
+ }, +
+ { +
+ "Node Type": "Table Function Scan", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Table Function Name": "xmltable", +
+ "Alias": "f", +
+ "Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], +
+ "Table Function Call": "XMLTABLE(('/ROWS/ROW[COUNTRY_NAME=\"Japan\" or COUNTRY_NAME=\"India\"]'::text) PASSING (xmldata.data) COLUMNS \"COUNTRY_NAME\" text, \"REGION_ID\" integer)",+
+ "Filter": "(f.\"COUNTRY_NAME\" = 'Japan'::text)" +
+ } +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
-- should to work with more data
INSERT INTO xmldata VALUES('<ROWS>
<ROW id="10">
diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql
index 34dc4f1e39c..953bac09e45 100644
--- a/src/test/regress/sql/xml.sql
+++ b/src/test/regress/sql/xml.sql
@@ -505,7 +505,10 @@ SELECT xmltable.*
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';
EXPLAIN (VERBOSE, COSTS OFF)
-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';
+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';
+
+EXPLAIN (VERBOSE, FORMAT JSON, COSTS OFF)
+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';
-- should to work with more data
INSERT INTO xmldata VALUES('<ROWS>
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-19 08:31:28 +0000