Oracle and COUNT

[Kevin McGrath]

The SQL generated for 'count' when more than one column is involved does not work for Oracle.

In Rose::DB::Object::Manager::get_objects

$select = $use_distinct ? 

          'COUNT(DISTINCT ' . join(', ', map { "t1.$_" } @$pk_columns) . ')' :

          'COUNT(*)';

Will kick out a ORA-00909: invalid number of arguments for the COUNT function

Valid example SQL statements I've used are:

SELECT COUNT (*) FROM (

    SELECT COUNT(*)

    FROM

      map_table t1

      JOIN map_from t2 ON (t1.map_from_id = t2.id)

    WHERE

      t1.map_to_id = 1231 AND

      t2.some_condition > 0

    GROUP BY t1.map_to_id, t1.map_from_id

)

SELECT COUNT(*) FROM (

    SELECT DISTINCT t1.cs_id, t1.dev_id

    FROM

      map_table t1

      JOIN map_from t2 ON (t1.map_from_id = t2.id)

    WHERE

      t1.map_to_id = 1231 AND

      t2.some_condition > 0

)

I do not know which is more expensive GROUP BY or DISTINCT?

Would this format break other databases? Is the best option to put a DB Driver check here?

-Kevin

[John Siracusa]

On Mon, Nov 21, 2011 at 11:33 AM, Kevin McGrath <kmcgra...@gmail.com> wrote:
> The SQL generated for 'count' when more than one column is involved does not
> work for Oracle.

What version of Oracle are you using? It works for me:

---

% sqlplus xxx/xxx@xxx

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 21 11:37:39 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

xxx@xxx> select count(distinct name) from mytable;
COUNT(DISTINCTNAME)
-------------------
3245

-John

[Kevin McGrath]

A single column with DISTINCT works fine, but if any other columns are involved it breaks

WORKS:

select count(distinct name) from mytable; 

FAILS:

select count(distinct t1.map_from_id, t1.map_to_id) from mytable;

We are on 10g for the DB I'm using, but we are using the 9.2.0 client because we still have Oracle 8 DBs we need to connect to (I know... )

-Kevin

[John Siracusa]

On Mon, Nov 21, 2011 at 11:53 AM, Kevin McGrath <kmcgra...@gmail.com> wrote:
> A single column with DISTINCT works fine, but if any other columns are
> involved it breaks
> WORKS:
> select count(distinct name) from mytable;
>
> FAILS:
> select count(distinct t1.map_from_id, t1.map_to_id) from mytable;

Hm, then the outer count(*) query wrapping the distinct query seems
like the best bet, but hacking it in after a driver check is going to
be a pain. You want to take a shot at it?

-John

[Kevin McGrath]

sure. I'll see what I can come up with.

On Nov 21, 12:04 pm, John Siracusa <sirac...@gmail.com> wrote:

[Kevin McGrath]

back from T-day vacation and with some fresh eyes I noticed it may be
a very simple fix.

Set the Oracle DB Driver:
sub supports_multi_column_count_distinct { 0 };

Looks like you already have logic for wrapping in Manager.pm:

Line 1711:
if($wrap)
{
$sql = "SELECT COUNT(*) FROM ($sql) sq";
}

Need to do some more testing, but my initial problem looks to be
fixed.

I'm going to take a stab at creating a 'Oracle' section in t/db-
object-
relationship.t