In-Portal Issue Tracker

Welcome to the In-Portal Open Source CMS Issue Tracker! This is a central management / tracking tool for all types of tasks / issues / bugs for the In-Portal Project. Before reporting any issues, please make sure to read the Guide into Issue Tracker and How to Properly Test and Report Bugs!

Viewing Issue Simple Details [ Jump to Notes ] [ Wiki ]

[ View Advanced ] [ Issue History ] [ Print ]

ID

Category

Type

Reproducibility

Date Submitted

Last Update

0000938

[In-Portal CMS] Database

bug report

always

2010-11-30 12:10

2011-03-30 09:53

Reporter

BaSSiST

View Status

public

Project Name

In-Portal CMS

Assigned To

alex

Developer

Priority

normal

Resolution

fixed

Fixed in Version

5.1.2-B1

Status

closed

Product Version

5.1.1

Target Version

5.1.2

Time Estimate

No estimate

Summary

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

Description

There is a bug in 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.

Additional Information

Patch also, checks for ANY number of whitespaces between SELECT and DISTINCT, instead of one space.

Tags

No tags attached.

Reference

http://groups.google.com/group/in-portal-bugs/browse_thread/thread/4635504373ca3d86

Change Log Message

Fixes Count SQLs with DISTINCT in sub-query

Estimate Points

0

count+sql+with+distinct.patch [^] (622 bytes) 2010-11-30 12:10 [Show Content]

Index: dblist.php
===================================================================
--- dblist.php	(revision 14094)
+++ dblist.php	(working copy)
@@ -352,9 +352,9 @@
 
 	function getCountSQL($sql)
 	{
-		if ( preg_match("/DISTINCT(.*?)FROM(?!_)/is",$sql,$regs ) )
+		if ( preg_match("/^\s*SELECT\s+DISTINCT(.*?)FROM(?!_)/is",$sql,$regs ) )
 		{
-			return preg_replace("/^\s*SELECT DISTINCT(.*?)FROM(?!_)/is", "SELECT COUNT(DISTINCT ".$regs[1].") AS count FROM", $sql);
+			return preg_replace("/^\s*SELECT\s+DISTINCT(.*?)FROM(?!_)/is", "SELECT COUNT(DISTINCT ".$regs[1].") AS count FROM", $sql);
 		}
 		else
 		{

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0003160) alex (manager) 2011-01-05 06:05	ok

(0003161) alex (manager) 2011-01-05 06:05	Fix committed to 5.1.x branch. Commit Message: Fixes 0000938: Generation of COUNT SQL Fails with DISTINCT in sub-query Commit on behalf of Sergey

(0003358) Dmitry (manager) 2011-03-30 09:53	Closing, since 5.1.2 release has been released.

Related Changesets
In-Portal CMS: 5.1.x r14119 Timestamp: 2011-01-05 06:05:38 Author: alex [ Details ] [ Diff ]	Fixes 0000938: Generation of COUNT SQL Fails with DISTINCT in sub-query Commit on behalf of Sergey
	mod - /in-portal/branches/5.1.x/core/kernel/db/dblist.php		[ Diff ] [ File ]

Issue History
Date Modified	Username	Field	Change
2011-03-30 09:53	Dmitry	Note Added: 0003358
2011-03-30 09:53	Dmitry	Status	resolved => closed
2011-01-05 06:05	alex	Note Added: 0003161
2011-01-05 06:05	alex	Status	reviewed and tested => resolved
2011-01-05 06:05	alex	Fixed in Version	=> 5.1.2-B1
2011-01-05 06:05	alex	Resolution	open => fixed
2011-01-05 06:05	alex	Assigned To	!COMMUNITY => alex
2011-01-05 06:05	alex	Changeset attached	5.1.x r14119
2011-01-05 06:05	alex	Note Added: 0003160
2011-01-05 06:05	alex	Status	needs testing => reviewed and tested
2010-11-30 14:01	alex	Developer	Dmitry => BaSSiST
2010-11-30 12:11	Dmitry	Assigned To	=> !COMMUNITY
2010-11-30 12:11	Dmitry	Developer	BaSSiST => Dmitry
2010-11-30 12:11	Dmitry	Status	active => needs testing
2010-11-30 12:11	Dmitry	Reporter	Dmitry => BaSSiST
2010-11-30 12:11	Dmitry	Developer	=> BaSSiST
2010-11-30 12:10	Dmitry	New Issue
2010-11-30 12:10	Dmitry	File Added: count+sql+with+distinct.patch
2010-11-30 12:10	Dmitry	Reference	=> http://groups.google.com/group/in-portal-bugs/browse_thread/thread/4635504373ca3d86
2010-11-30 12:10	Dmitry	Change Log Message	=> Fixes Count SQLs with DISTINCT in sub-query

Web Development by Intechnic

In-Portal Open Source CMS

In-Portal Open Source CMS

Copyright © 2000 - 2009 MantisBT Group