Consider these TWO test cases
CREATE TABLE price (id INT PRIMARY KEY, active BOOLEAN NOT NULL, price NUMERIC);
insert into price values (1,false,42), (10,false,100), (11,true,17.99);
select row(price.*) from price;
Expected output
row
--------------
(1,f,42)
(10,f,100)
(11,t,17.99)
(3 rows)
Obtained output
ERROR: input of anonymous composite types is not implemented
If we use explicit type cast it works fine i.e.
select row(price.*)::price from price;
produces correct results.
Now consider this test case
create table "my table"(a int, b int);
insert into "my table" values(1,11),(2,22),(3,33);
select "my table".*::"my table" from "my table";
Expected Output
my table
----------
(1,11)
(2,22)
(3,33)
(3 rows)
Obtained Output
ERROR: type ""my table"" does not exist
Add a third test case in this group too
CREATE SCHEMA my_sch;
CREATE TABLE my_sch.my_price (id INT PRIMARY KEY, active BOOLEAN NOT NULL, my_price NUMERIC);
insert into my_sch.my_price values (2,true,54);
select my_sch.my_price.*::my_sch.my_price from my_sch.my_price;
ERROR: type "my_sch.my_price" does not exist
This is a query deparsing problem.
postgres=# explain verbose select "my table".*::"my table" from "my table";
QUERY PLAN
----------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: "my table".*
Node/s: dn1, dn2
Remote query: SELECT "my table" AS "my table" FROM "my table"
(4 rows)
Should have been fixed. Need test to close.
Q4
With master at f1be4f5, this fails:
postgres=# explain verbose select "my table".*::"my table" from "my table";
QUERY PLAN
-------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: "my table".*
Node/s: dn1, dn2
Remote query: SELECT "my table".*::"my table" AS "my table" FROM "my table"
(4 rows)
postgres=# SELECT "my table".*::"my table" AS "my table" FROM "my table";
ERROR: syntax error at or near "table"
CONTEXT: invalid type name "my table"
With 1.0 stable at 65e9076, this fails with the same error.
This should have been fixed by commit d6f7d4f in vanilla postgres, but it looks it did not.
Consider this simplified test case taken from with.sql
CREATE TABLE parent ( id int, val text );
INSERT INTO parent VALUES ( 1, 'p1' );
WITH rcte AS ( SELECT max(id) AS maxid FROM parent ) DELETE FROM parent USING rcte WHERE id = maxid;
It results in error
ERROR: input of anonymous composite types is not implemented
Due to this error the following test cases are affected
rules.sql
with.sql
First test case is fixed in the current master.
Second case remains similar, but in the current master, this is regarded as a syntax error.
The test below shows that only datanode does not analyze double-quoted identifiers correctly.
-------------------------
explain verbose select "my table".*::"my table" from "my table";
QUERY PLAN
-------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: "my table".*
Node/s: datanode1, datanode2, datanode3, datanode4
Remote query: SELECT "my table".*::"my table" AS "my table" FROM "my table"
(4 rows)
koichi=# execute direct on (datanode1) 'select "my table".*::"my table" from "my table"';
ERROR: syntax error at or near "table"
CONTEXT: invalid type name "my table"
koichi=#
------------------
Now the former works, but the latter still remains problem.
The quoted cast may not work.