[Dbix-class] using distinct and literal SQL in select list does not work

[BUCHMULLER Norbert]

Hi,

maybe it's a known issue, nevertheless, it's annoying.. The code below
throws an exception:

$schema->resultset('Artist')->search(
    {
    },
    {
      select => [ \'1 AS one' ],
      distinct => 1,
    }
)->first;

The exception:

DBIx::Class::ResultSet::first(): DBI Exception: DBD::Pg::st execute
failed: ERROR:  syntax error at or near "AS" at character 43 [for
Statement "SELECT 1 AS one FROM artist me GROUP BY 1 AS one"] at test.pl
line 31

Apparently what 'distinct' does is to add the whole 'select' list (as it
is) to the 'group_by' list (at least in this case:-). The problem is that
the select list contains an 'AS' modifier, and that is forbidden in GROUP
BY.

It would be useful to give some means to allow adding the SQL 'AS'
modifier to columns without writing literal SQL. (Eg. adding an 'sql_as'
attribute that takes an array ref, or changing the 'as' attribute to
automatically define SQL 'AS' aliases, or changing the 'select' attribute
to take hash refs, ... each has its own downsides.)

Perl: v5.8.8 built for i486-linux-gnu-thread-multi
DBIx::Class: 0.08010
SQL::Abstract: 1.21

norbi
-------------- next part --------------
A non-text attachment was scrubbed...
Name: distinct_and_literal_sql_in_select.tbz
Type: application/x-bzip-compressed-tar
Size: 977 bytes
Desc: not available
Url : http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20081014/ec6cc04a/distinct_and_literal_sql_in_select.bin