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

4.1.0
fixed
None
1
2014-08-18
2013-05-02
No

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

Discussion

  • Ann + J.M.

    Ann + J.M. - 2013-05-03
    • 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
     
  • Sakari Laine

    Sakari Laine - 2013-05-03

    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

    Sakari Laine - 2013-05-03

    screencast for reproducing bug

     
  • Marc Delisle

    Marc Delisle - 2013-05-22
    • status: pending-invalid --> open
     
  • Marc Delisle

    Marc Delisle - 2013-12-10

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

     
  • Marc Delisle

    Marc Delisle - 2013-12-10
    • Group: 3.5.8 --> 4.1.0
     
  • Smita Kumari

    Smita Kumari - 2013-12-12

    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

    Marc Delisle - 2013-12-13
    • assigned_to: Marc Delisle
     
  • Marc Delisle

    Marc Delisle - 2013-12-13
    • summary: Query fails when using aliases after ordering results. --> (ok 4.1.1) Query fails when using aliases after ordering results
    • status: open --> resolved
    • Priority: 4 --> 1
     
  • Marc Delisle

    Marc Delisle - 2013-12-17
    • Status: resolved --> fixed