On Aug 23, 2007, at 10:47 , Ben Tilly wrote:

On 8/22/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:

*This* seems like a bug:
test=# select record_id
        , count(observation_id) as bar
        from observation
        group by record_id
            , case when true
                   then 'foo'
record_id | bar
          1 |   4
          2 |   4
          3 |   2
(3 rows)

Why does it seem like a bug to you?

Turn it around, and tell me in what way is its behaviour surprising to
someone who knows SQL.  You asked to group on something that is the
same for all rows.  That group by condition did nothing.  (Except
rendered the syntax valid when it might not have been.)  As I would

Considering that I expect the GROUP BY clause to include only column references (or other groupings of column references), not expressions. Whether or not the value is the same, it surprises me that something other than a column reference is accepted at all. I hadn't realized this behavior was accepted in PostgreSQL, but I learn something new every day.

My non-rigorous way of thinking about GROUP BY is that it groups this listed columns when the values of the listed columns are the same. An expression that evaluates to anything other than a column name doesn't provide any information about which column to consider grouped, and expressions don't evaluate to column names, or identifiers in general. If I understand you correctly, a GROUP BY item that isn't a column name would be a value that's applied to all columns, and the actual value is irrelevant—different values don't change the result.

So the only purpose it would serve would be to prevent a trailing comma from raising a syntax error: you'd still need to explicitly list the other columns (unless the implementation behavior is changed to extend the spec there as well). What this does is allow you to use something like this (psuedocode):

group_columns = [ 'foo', 'bar', 'baz' ]

group_column_list = ''
for col in group_columns { group_column_list += col + ',' } # group_column_list = "foo,bar,baz,"

group_by_clause = "GROUP BY $group_column_list CASE WHEN TRUE THEN 'quux' END"

rather than

group_column_list = join group_columns, ',' # group_column_list = "foo,bar,baz"
group_by_clause = "GROUP BY $group_column_list"

I still feel I'm missing something. If that's it, it seems like something easy enough to handle in middleware. Sorry if it appears I'm being dense. I've definitely learned things in this thread.

Furthermore ask yourself whether anyone who wrote that would likely
have written it by accident.

I don't see what that has to do with anything. There are plenty of things I can write on purpose that would be nonsense. You might even consider my posts as prime examples :)

Michael Glaesemann
grzm seespotcode net

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not

Reply via email to