#3903 (ok 4.1.1) Query fails when using aliases after ordering results

[Sakari Laine]

3.5.8.1deb1

Apache/2.2.22 (Ubuntu)
Database client version: libmysql - 5.5.31
PHP extension: mysqli

Table (can be reproduced on any table)

CREATE TABLE LabelValues (
labelvalue_id int(10) NOT NULL AUTO_INCREMENT,
label_id int(10) unsigned NOT NULL,
object_id int(10) unsigned NOT NULL,
object_type enum('RC','PT','ST','EX','CC','FI','SH') NOT NULL,
PRIMARY KEY (labelvalue_id),
UNIQUE KEY NewIndex (object_id,object_type,label_id),
KEY LabelValues_FKIndex1 (label_id),
KEY NewIndex3 (object_type,object_id)
) ENGINE=InnoDB AUTO_INCREMENT=4938 DEFAULT CHARSET=utf8;

INSERT INTO LabelValues (labelvalue_id, label_id, object_id, object_type) VALUES(7, 5, 1, 'CC');
INSERT INTO LabelValues (labelvalue_id, label_id, object_id, object_type) VALUES(143, 2, 2, 'CC');
INSERT INTO LabelValues (labelvalue_id, label_id, object_id, object_type) VALUES(144, 2, 3, 'CC');
INSERT INTO LabelValues (labelvalue_id, label_id, object_id, object_type) VALUES(3, 4, 4, 'CC');

Reproduction:

First go to "Browse" and order results by object_id.

then write your own query with alias

SELECT * FROM LabelValues AS alias;

causes error: #1054 - Unknown column 'LabelValues.object_id' in 'order clause'

phpmyadmin really runs query like this:

SELECT *
FROM LabelValues AS alias
ORDER BY LabelValues.object_id ASC
LIMIT 0 , 30

[J.M. Rütter]

• Description has changed:

Diff:

--- old
+++ new
@@ -42,18 +42,3 @@
 FROM `LabelValues` AS `alias`
 ORDER BY `LabelValues`.`object_id` ASC
 LIMIT 0 , 30
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-

• status: open --> pending-invalid
• Priority: 5 --> 4

[J.M. Rütter]

Can't reproduce.
Are you able to reproduce this on demo.phpmyadmin.net for QA_3_5 and/or demo.phpmyadmin.net for QA_4_0?

[Sakari Laine]

I can reproduce it on both.

Steps to do it.

1. Click Browse on LabelValues
2. Click object_id to order browse results by object_id
3. Click SQL to run your own sql query
4. put query "SELECT * FROM LabelValues AS alias WHERE 1" to textbox

5. Click Go

6. Error is reproduced.

Tried with Firefox and Cromium, but I dont believe that it is browser related bug.

[Sakari Laine]

attached screenshot for error

[Sakari Laine]

screencast for reproducing bug

[Marc Delisle]

I can reproduce the problem in 4.0, 4.1.1-dev and current master (b8f54080ab91d15954b448a7a3beb2c1bfc01022).

[Smita Kumari]

Hello sir, I have tried to fix this bug. here is the pull request i have submitted, https://github.com/phpmyadmin/phpmyadmin/pull/784 . Please review and let me know if i'm doing it wrong or if I need to make any changes in the same.
Thanks

[Marc Delisle]

Fixed with https://github.com/phpmyadmin/phpmyadmin/commit/72f422b157e70d3495b010450b739d0e5234bba6