Home » Structures » Structures_DataGrid_DataSource_MDB2 » Bug #9913

[2007-01-22 13:34 UTC] HarrySchool at netscape dot net

Description:
------------
Counting the number of records for a query is done by replacing the columns by COUNT(*).
This is done incorrectly when using subqueries.

In function count() line 285
The following codelines are concerned:
$query = preg_replace('#SELECT\s.*\sFROM#is',
'SELECT COUNT(*) FROM',
$this->_query);

Replacing this with
$query = preg_replace('#SELECT\s.*?\sFROM#is',
'SELECT COUNT(*) FROM',
$this->_query, 1);

should cure the problem.

Test script:
---------------
$qry = 'select aa from bb where cc in select dd from ee;';
$pattern = '#SELECT\s.*\sFROM#is';
$query = preg_replace($pattern,
'SELECT COUNT(*) FROM',
$qry);
echo 'result: '.$query;

Expected result:
----------------
The actual result is:
$query = 'SELECT COUNT(*) FROM ee;'

The correct result should be:
$query =
'SELECT COUNT(*) FROM bb where cc in select dd from ee;'

Actual result:
--------------
The actual (wrong) result is:
$query = 'SELECT COUNT(*) FROM ee;'

In function count() line 285
The following codelines are concerned:
$query = preg_replace('#SELECT\s.*\sFROM#is',
'SELECT COUNT(*) FROM',
$this->_query);

Replacing this with
$query = preg_replace('#SELECT\s.*?\sFROM#is',
'SELECT COUNT(*) FROM',
$this->_query, 1);

should cure the problem.

[2007-01-25 16:46 UTC] HarrySchool at netscape dot net

Dear Mark,

the fix in version 0.1.6 is partially correct. You forgot the question mark in the pattern: '#SELECT\s.*?\sFROM#is'

Harry

[2007-01-30 20:37 UTC] HarrySchool at netscape dot net

The question mark makes the * less greedy.
Without the question mark everthing till the last FROM is replaced, whereas with questionmark only till the first FROM is replaced. The one (1) couses only one replacement. In the following example there could be two replacements because the select has two SELECt ... FROM combinations.

Example:
'SELECT COUNT(*) FROM bb where cc in SELECT dd FROM ee;' =
preg_replace('#SELECT\s.*?\sFROM#is',
'SELECT COUNT(*) FROM',
'SELECT aa FROM bb where cc in select dd from ee;',
1);

'SELECT COUNT(*) FROM ee;' =
preg_replace('#SELECT\s.*\sFROM#is',
'SELECT COUNT(*) FROM',
'SELECT aa FROM bb where cc in select dd from ee;',
1);

[2007-02-01 12:22 UTC] HarrySchool at netscape dot net

Mark,

I had to fill in a summary, otherwise I could nog post a new comment. I had no idea that this would change the original summary.

Thanks for fixing.

Harry

[2007-02-12 12:24 UTC] mail at coppermine-gallery dot net

This fix breaks previously working code.

Example query:

SELECT ticket_id, phone, ticket_time, ticket_status, amount, total_odds, ROUND(amount * total_odds, 2) AS poss_win,
(SELECT COUNT(*) FROM bets WHERE ticketid = t.ticket_id) AS numbets
FROM tickets AS t
INNER JOIN subscribers AS s ON s.subscriber_id = t.owner
WHERE approved = 'APPROVED'

Expected result:

SELECT COUNT(*) FROM tickets AS t
INNER JOIN subscribers AS s ON s.subscriber_id = t.owner
WHERE approved = 'APPROVED'

Actual result:

SELECT COUNT(*) FROM bets WHERE ticketid = t.ticket_id) AS numbets
FROM tickets AS t
INNER JOIN subscribers AS s ON s.subscriber_id = t.owner
WHERE approved = 'APPROVED'

Which is not valid SQL. 0.1.5 and 0.1.6 both give the expected result.

[2007-02-12 19:18 UTC] HarrySchool at netscape dot net

Tom Atkinson is right. Both his case and mine are valid SQL. They can't be solved both the way it was and the way i proposed.

Here is a new proposal to solve both: Every query which contains two times SELECT will be excecuted as is.

current line 260:

elseif (preg_match('#GROUP\s*BY#is', $this->_query) === 1 ||
preg_match('#\sUNION\s#is', $this->_query) === 1 ||
preg_match('#SELECT.*DISTINCT.*FROM#is', $this->_query) === 1

replace them with the following lines:

elseif (preg_match('#GROUP\s*BY#is', $this->_query) === 1 ||
preg_match('#SELECT.*SELECT#is', $this->_query) === 1 ||
preg_match('#\sUNION\s#is', $this->_query) === 1 ||
preg_match('#SELECT.*DISTINCT.*FROM#is', $this->_query) === 1