#4249 (ok 4.1.8) Incorrect number of result rows for SQL with subqueries

[Lukáš Chmela]

If an SQL query involving subqueries is executed, the number of returned rows (shown in a rectangle above the query) is incorrectly counted from the currently selected table/database, not from the executed query. As an example:

SELECT COUNT(*) FROM (SELECT 1) AS TB
or
SELECT AVG(1) FROM (SELECT 1) AS TB

says "Showing rows 0 - -1 (0 total, Query took 0.0003 sec)" on a database and "Showing rows 0 - 24 (209 total, 0 in query, Query took 0.0003 sec)" on a table with 209 entries, though only one row with the expected value 1 is always returned.

I have not tested older PMA versions.

[Sachith Seneviratne]

Managed to replicate this in master and MAINT_4_1_6. Bug is not present in MAINT_4_1_5.
Will look into it further.

[Sachith Seneviratne]

This is due to some recently committed code in libraries/sql.lib.php at line 842, in response to another bug (4239). In particular, changing
" && ($select_from || $analyzed_sql_results['is_subquery'])" to
" && $select_from" fixes this bug but introduces the earlier one.

[Marc Delisle]

Sachith,
with your proposed fix (in the QA_4_1 branch), I don't see the bug 4239 coming back. Tested with SELECT sum(Size) as bytes, count(CRC) as num FROM backups GROUP BY CRC ORDER BY num DESC

[Sachith Seneviratne]

I didn't test it in QA_4_1, I will test it out some more.

[Sachith Seneviratne]

with the fix above,
on QA_4_1
SELECT sum(Size) as bytes, count(CRC) as num FROM backups GROUP BY CRC ORDER BY num DESC
and
SELECT COUNT(*) FROM (SELECT 1) AS TB
work fine.
but
SELECT * FROM (SELECT filename, crc, sum(Size) as bytes, count(CRC) as num FROM backups GROUP BY CRC ORDER BY num DESC) as dupes where num > 1
displays the first 25 records correctly but when I try to navigate to the next set of results, the records don't get updated. The display indicates that it has moved to page 2 however, the records are the same as those on page 1. I also get this message (Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.)

[Sampath Liyanage]

Hi,
I did some debugging and found 2 reasons for this bug,

1. the function PMA_SQP_analyze($arr) doesn't mark queries like "SELECT COUNT(*) FROM (SELECT 1) AS TB" as select queries.
2. the function PMA_isJustBrowsing($analyzed_sql_results, $find_real_end) returns true for these queries (should return false because executing a sub query is not "just browsing").
   (both functions are in the sql.lib.php file)

made some changes and sent a pull request..
https://github.com/phpmyadmin/phpmyadmin/pull/913

[Marc Delisle]

Fixed with https://github.com/phpmyadmin/phpmyadmin/commit/b6cd8cdb33741c9cf8f7c36b01c57fc9ed9f0c44