Menu

#302 A few problems with whole row references

Development Queue
open
5
2013-06-12
2012-04-27
Abbas Butt
No

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

Discussion

  • Abbas Butt

    Abbas Butt - 2012-04-27

    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

     
  • Michael Paquier

    Michael Paquier - 2012-05-31

    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)

     
  • Koichi Suzuki

    Koichi Suzuki - 2012-06-03
    • milestone: 2663467 --> 2663488
     
  • Koichi Suzuki

    Koichi Suzuki - 2012-06-03

    Should have been fixed. Need test to close.

     
  • Koichi Suzuki

    Koichi Suzuki - 2012-06-03
    • assigned_to: nobody --> gabbasb
     
  • Koichi Suzuki

    Koichi Suzuki - 2012-06-03
    • milestone: 2663488 --> 2663467
     
  • Koichi Suzuki

    Koichi Suzuki - 2012-09-12

    Q4

     
  • Michael Paquier

    Michael Paquier - 2012-09-12

    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.

     
  • Koichi Suzuki

    Koichi Suzuki - 2012-12-12

    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

     
  • Koichi Suzuki

    Koichi Suzuki - 2013-03-12

    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=#
    ------------------

     
  • Koichi Suzuki

    Koichi Suzuki - 2013-03-12
    • milestone: 2663467 --> 5139567
     
  • Koichi Suzuki

    Koichi Suzuki - 2013-06-12

    Now the former works, but the latter still remains problem.

    The quoted cast may not work.

     
  • Koichi Suzuki

    Koichi Suzuki - 2013-06-12
    • milestone: 5139567 --> Development Queue
     

Log in to post a comment.

Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.