Thread: unintuitive subquery record wrapping

unintuitive subquery record wrapping

From
Rikard Pavelic
Date:
I'm puzzled if this is by design or just overlooked...

create table t(a int, b varchar);

insert into t values(1,'x');

For basic query:
select t from t
result is of type t.

If I query:
select sq from (select t from t) sq;
result is of type record.

I need to query like this:
select (sq).t from (select t from t) sq;
or like this:
select t from (select t from t) t;
to get result of type t.

Wrapping t to record if another alias is used seems a bit unintuitive to me?
Is this by design and if yes, why?

Regards,
Rikard

Re: unintuitive subquery record wrapping

From
Tom Lane
Date:
Rikard Pavelic <rikard.pavelic@zg.htnet.hr> writes:
> For basic query:
> select t from t
> result is of type t.

yeah ...

> If I query:
> select sq from (select t from t) sq;
> result is of type record.

yeah ... it's a record containing a single field of type t.

            regards, tom lane

Re: unintuitive subquery record wrapping

From
Rikard Pavelic
Date:
On 18.9.2010 4:31, Tom Lane wrote:
> Rikard Pavelic <rikard.pavelic@zg.htnet.hr> writes:
>
>> For basic query:
>> select t from t
>> result is of type t.
>>
> yeah ...
>
>
>> If I query:
>> select sq from (select t from t) sq;
>> result is of type record.
>>
> yeah ... it's a record containing a single field of type t.
>
>             regards, tom lane
>
>
That was unhelpful ;(

You removed the important part of question ;)
I'll try again...

I'm arguing that is seems wrong that I need to match alias name like this
select t from (select t from t) t
or like this
select sq from (select sq from t sq) sq
to get unwrapped type.

Maybe I'm alone arguing this because nobody writes queries like that :)

Regards,
Rikard


Re: unintuitive subquery record wrapping

From
Arjen Nienhuis
Date:
On Sat, Sep 18, 2010 at 9:31 AM, Rikard Pavelic
<rikard.pavelic@zg.htnet.hr> wrote:
> On 18.9.2010 4:31, Tom Lane wrote:
>> Rikard Pavelic <rikard.pavelic@zg.htnet.hr> writes:
>>
>>> For basic query:
>>> select t from t
>>> result is of type t.
>>>
>> yeah ...
>>
>>
>>> If I query:
>>> select sq from (select t from t) sq;
>>> result is of type record.
>>>
>> yeah ... it's a record containing a single field of type t.
>>
>>                       regards, tom lane
>>
>>
> That was unhelpful ;(
>
> You removed the important part of question ;)
> I'll try again...
>
> I'm arguing that is seems wrong that I need to match alias name like this
> select t from (select t from t) t
> or like this
> select sq from (select sq from t sq) sq
> to get unwrapped type.
>
> Maybe I'm alone arguing this because nobody writes queries like that :)
>
> Regards,
> Rikard

I'm not sure what you want but maybe it's this:


=> select * from (select t from t) sq;
   t
-------
 (1,x)
(1 row)

=> select (sq.t).* from (select t from t) sq;
 a | b
---+---
 1 | x
(1 row)

Re: unintuitive subquery record wrapping

From
Rikard Pavelic
Date:
On 18.9.2010 11:36, Arjen Nienhuis wrote:
> I'm not sure what you want but maybe it's this:
>
>
> => select * from (select t from t) sq;
>    t
> -------
>  (1,x)
> (1 row)
>
> => select (sq.t).* from (select t from t) sq;
>  a | b
> ---+---
>  1 | x
> (1 row)
>
>

I know how to expand record to type or set by hand. That's not the issue.
I'm just trying to understand if changing type of record when alias is
different is intentional decision or unintentional.
Because, if it's unintentional, I would like it to get fixed
(I guess I'll have to take a look at the code if nobody answers me).

I don't see any benefit in loosing type info because variable alias
don't match.

Regards,
Rikard

Re: unintuitive subquery record wrapping

From
Arjen Nienhuis
Date:
On Sat, Sep 18, 2010 at 12:40 PM, Rikard Pavelic
<rikard.pavelic@zg.htnet.hr> wrote:
> On 18.9.2010 11:36, Arjen Nienhuis wrote:
>> I'm not sure what you want but maybe it's this:
>>
>>
>> => select * from (select t from t) sq;
>>    t
>> -------
>>  (1,x)
>> (1 row)
>>
>> => select (sq.t).* from (select t from t) sq;
>>  a | b
>> ---+---
>>  1 | x
>> (1 row)
>>
>>
>
> I know how to expand record to type or set by hand. That's not the issue.
> I'm just trying to understand if changing type of record when alias is
> different is intentional decision or unintentional.
> Because, if it's unintentional, I would like it to get fixed
> (I guess I'll have to take a look at the code if nobody answers me).
>
> I don't see any benefit in loosing type info because variable alias
> don't match.

It's not the alias. Subqueries are typed as "record". I think thats
because there is little difference between these:

(SELECT * FROM t)
(SELECT a, b FROM t)
(SELECT a + 1, b FROM t)
(SELECT 1, b FROM t)

Which of these should have type "t"?

You need to do this:

SELECT x FROM t x;

Re: unintuitive subquery record wrapping

From
Tom Lane
Date:
Rikard Pavelic <rikard.pavelic@zg.htnet.hr> writes:
> I'm arguing that is seems wrong that I need to match alias name like this
> select t from (select t from t) t
> or like this
> select sq from (select sq from t sq) sq
> to get unwrapped type.

This reminds me of the old joke ...
    Patient: Doctor, it hurts when I do this.
    Doctor: So don't do that!

When you write a query like that, there are two ways to resolve the
reference: it could be naming the (single) output column of the
subquery, or it could be naming the entire record result of the
subquery.  PG tries those alternatives in that order.  If you don't
like that, maybe you shouldn't use conflicting alias names.

            regards, tom lane