#2767 (ok 3.0.1) SQL error after sorting a subset

Hello

starting with this simple table ...

CREATE TABLE \`users\` \(
\`name\` varchar\(16\) collate latin1\_german2\_ci NOT NULL,
\`id\_for\` int\(11\) NOT NULL,
\`id\` int\(10\) unsigned NOT NULL auto\_increment,
PRIMARY KEY  \(\`id\`\)
\) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1\_german2\_ci;

INSERT INTO \`users\` VALUES\('alpha', 17, 1\);
INSERT INTO \`users\` VALUES\('beta', 17, 2\);
INSERT INTO \`users\` VALUES\('gamma', 18, 3\);
INSERT INTO \`users\` VALUES\('delta', 19, 4\);

I dipslayed a subset by searching (via "Search" button) for rows with `id_for` = 17:

SELECT \*
FROM \`users\`
WHERE \`id\_for\` =17
LIMIT 0 , 50

Now every attempt to sort resultant subset by clicking on column names throws a SQL-error:

Error

SQL query: Documentation

SELECT \*
FROM \`users\`
WHERE \`id\_for\` = 17ORDER
BY \`users\`.\`name\` ASC
LIMIT 0 , 50

MySQL said: Documentation
\#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY \`users\`.\`name\` ASC
LIMIT 0, 50' at line 1

Cheers,
Peter

[Anonymous]

Oops sorry:

* phpMyAdmin Version information: 3.0.0
* MySQL Server 5.0.51
* MySQL charset: UTF-8 Unicode (utf8)
* Apache 2.2.9
* PHP Version 5.2.6-3

Peter

[plus-media]

This also happens to me on 3.0.0 and I tested it on http://pma.cihar.com/TESTING/ SVN version 11626 and it still happens.

Cheers,

Chris

[plus-media]

This also happens to me on 3.0.0 and I tested it on http://pma.cihar.com/TESTING/ SVN version 11626 and it still happens.

Cheers,

Chris

[Marc Delisle]

Modified: branches/QA_3_0/phpMyAdmin/libraries/display_tbl.lib.php

--- branches/QA_3_0/phpMyAdmin/libraries/display_tbl.lib.php 2008-10-01 20:48:40 UTC (rev 11626)
+++ branches/QA_3_0/phpMyAdmin/libraries/display_tbl.lib.php 2008-10-02 16:55:44 UTC (rev 11627)
@@ -466,6 +466,10 @@
if (isset($analyzed_sql[0]['unsorted_query'])) {
$unsorted_sql_query = $analyzed_sql[0]['unsorted_query'];
}
+ // Handles the case of multiple clicks on a column's header
+ // which would add many spaces before "ORDER BY" in the
+ // generated query.
+ $unsorted_sql_query = trim($unsorted_sql_query);

// sorting by indexes, only if it makes sense (only one table ref)
if (isset($analyzed_sql) && isset($analyzed_sql[0]) &&
@@ -765,9 +769,9 @@
// If it contains one, it's probably a function column
// like 'COUNT(`field`)'
if (strpos($name_to_use_in_sort, '(') !== false) {
- $sort_order = 'ORDER BY ' . $name_to_use_in_sort . ' ';
+ $sort_order = ' ORDER BY ' . $name_to_use_in_sort . ' ';
} else {
- $sort_order = 'ORDER BY ' . $sort_tbl . PMA_backquote($name_to_use_in_sort) . ' ';
+ $sort_order = ' ORDER BY ' . $sort_tbl . PMA_backquote($name_to_use_in_sort) . ' ';
}
unset($name_to_use_in_sort);