Thread: unintuitive subquery record wrapping
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
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
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
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)
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
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;
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