[fdo-internals] MySQL Defect with Count(1)

[Dan Stoica]

Hi Bruno,

I understand that but I just wondered how you came across this bug. I did some quick research on Count() and found a couple of not very obvious things:

1) Count(column) is excluding the rows with null values for 'column'
2) Count(1) is the fastest because it doesn't evaluate the values (behind the scenes)

Note: as a consequence, using the geometry column as the argument is not a very good idea for performance.

Thanks,
Dan.

-----Original Message-----
From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Bruno Scott
Sent: Monday, May 05, 2008 2:56 AM
To: fdo-internals at lists.osgeo.org
Subject: RE: [fdo-internals] MySQL Defect with Count(1)


Hi Dan, we have a huge application that does a lot of database stuff.
We use Count(1) on any database just to know how many record we are going to
process.
Count(1) works on any databse(access,sqlserver,oracle,postgres,mysql...)
Using Count(1) is easier than passing a column name.



Dan Stoica wrote:
>
> Hi Bruno,
>
> Quick question: why do you need Count(1)? For performance perhaps?
>
> Thanks,
> Dan.
>
> -----Original Message-----
> From: fdo-internals-bounces at lists.osgeo.org
> [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Bruno Scott
> Sent: Tuesday, April 29, 2008 2:37 AM
> To: fdo-internals at lists.osgeo.org
> Subject: Re: [fdo-internals] MySQL Defect with Count(1)
>
>
> By the way i have found the same piece of code in the sqlserver provider
>
>
> Bruno Scott wrote:
>>
>> I just found and enter a ticket about this one.
>> I've been a little further.
>>
>> Select Count(1) failes with "Unsupported FDO type in expression"
>>
>> It was working ok in release 3.2
>> I think i have found were the problem is.
>> ProcessAggregateFunction has been overridden in release 3.3 ( to handle
>> ALL and distinct i guess)
>>
>> extract of ProcessAggregateFunction  function
>>             if (dataValue->GetDataType() == FdoDataType_String)
>>             {
>>                 // Omit ALL if specified as this keyword is not supported
>> in
>>                 // MySQL.
>>                 ...
>>             }
>>             else
>>                 throw FdoFilterException::Create(NlsMsgGet(FDORDBMS_29,
>> "Unsupported FDO type in expression"));
>>
>> As 1 is not a string -> boom
>> I have tried with
>> Select Count('1') and it works fine
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/MySQL-Defect-with-Count%281%29-tp16939555p16953704.html
> Sent from the FDO Internals mailing list archive at Nabble.com.
>
> _______________________________________________
> fdo-internals mailing list
> fdo-internals at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-internals
> _______________________________________________
> fdo-internals mailing list
> fdo-internals at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-internals
>
>

--
View this message in context: http://www.nabble.com/MySQL-Defect-with-Count%281%29-tp16939555p17053950.html
Sent from the FDO Internals mailing list archive at Nabble.com.

_______________________________________________
fdo-internals mailing list
fdo-internals at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-internals