Count SQL generation fails on DISTINCT in subquery

[Sergejs Gribs]

There's bug with kDBList::getCountSQL method, which generates SQL for counting list totals. It checks if there's DISTINCT in SQL and, if it's there, tries to replace it for COUNT(DISTINCT ...) construction instead of simple COUNT(*). Problem is that it checks for distinct in ANY place of query, but replaces it in beginning, so, if there's DISTINCT in subquery, it won't replace anything. So, count SQL will be equal to list SQL, and you will get first record's first field's value (according to kDBConnection::GetOne logic) as record count.

I attached patch that fixes it. Also, it now checks for ANY whitespaces between SELECT and DISTINCT instead of one space.

[Alexander Obuhovich]

Thanks for noticing that. Now we will know, that if pagination is incorrect and there is a DISTINCT somewhere, then this patch should fix that.

On Tue, Nov 30, 2010 at 10:07 AM, Sergejs Gribs <serg...@gmail.com> wrote:

There's bug with kDBList::getCountSQL method, which generates SQL for counting list totals. It checks if there's DISTINCT in SQL and, if it's there, tries to replace it for COUNT(DISTINCT ...) construction instead of simple COUNT(*). Problem is that it checks for distinct in ANY place of query, but replaces it in beginning, so, if there's DISTINCT in subquery, it won't replace anything. So, count SQL will be equal to list SQL, and you will get first record's first field's value (according to kDBConnection::GetOne logic) as record count.

I attached patch that fixes it. Also, it now checks for ANY whitespaces between SELECT and DISTINCT instead of one space.

--
You received this message because you are subscribed to the Google Groups "In-Portal Bugs Team" group.
To post to this group, send email to in-port...@googlegroups.com.
To unsubscribe from this group, send email to in-portal-bug...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/in-portal-bugs?hl=en.

--
Best Regards,

http://www.in-portal.com
http://www.alex-time.com

[Dmitry Andrejev]

Thanks Sergey!

We have filed a new task for this bug:

938: Generation of COUNT SQL Fails with DISTINCT in sub-query

http://tracker.in-portal.org/view.php?id=938

DA

Best regards,

Dmitry A.