Count SQL generation fails on DISTINCT in subquery

2 views
Skip to first unread message

Sergejs Gribs

unread,
Nov 30, 2010, 3:07:52 AM11/30/10
to in-port...@googlegroups.com
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.
count sql with distinct.patch

Alexander Obuhovich

unread,
Nov 30, 2010, 3:19:09 AM11/30/10
to in-port...@googlegroups.com
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

unread,
Nov 30, 2010, 1:12:49 PM11/30/10
to in-port...@googlegroups.com
Thanks Sergey!

We have filed a new task for this bug:

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



DA
Best regards,

Dmitry A.

Reply all
Reply to author
Forward
0 new messages