Sunday, April 3, 2011

"select abc from (select 1) as abc" produces "(1)" instead of "1"

In Postgre, why does

select abc from (select 1) as abc

produces:

(1)

and

select * from (select 1) as abc

produces:

1

That's really strange to me. Is that the case with MySQL, Oracle, etc? I spent hours figuring out why my conditions were failing...

From stackoverflow
  • What does

    select foo from ( select 1 foo ) as abc
    

    produce?

    presario : did you mean? select foo from ( select 1 as foo ) as abc i guess so, anyway i got it, thanks
  • The rows returned by your queries have different type: the first one is ROW(INT), while the second one is INT.

    MySQL and others lack this feature.

    In your first query, you are selecting a whole ROW as a single column. This query

    SELECT abc FROM (SELECT 1, 2) abc
    

    will produce (1, 2), which is a single column too and has type ROW.

    To select the INT value, use:

    SELECT  abc.col
    FROM    (
            SELECT  1 AS col
            ) abc
    
    presario : aha! so that's a feature, not a bug! thanks! got it now

0 comments:

Post a Comment