#3117 (ok 4.3) QBE generates wrong query

4.2.7
resolved
1
2014-08-18
2010-09-21
Michal Čihař
No

When there are three tables in QBE, two of them have relations and one does not, QBE generates following query:

SELECT `extra` . * , `master` . * , `slave` . *
FROM `slave` , `extra`
LEFT JOIN `qbetest`.`master` ON `slave`.`master_id` = `master`.`id`;

While it should generate:

SELECT `extra`.*, `master`.*, `slave`.*
FROM `extra`, `slave`
LEFT JOIN `qbetest`.`master` ON `slave`.`master_id` = `master`.`id` ;

Tables to reproduce:

CREATE TABLE IF NOT EXISTS `extra` (
`id` int(11) NOT NULL,
`data` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `master` (
`id` int(11) NOT NULL,
`data` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `slave` (
`id` int(11) NOT NULL,
`master_id` int(11) NOT NULL,
`data` text NOT NULL,
PRIMARY KEY (`id`),
KEY `master_id` (`master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `slave`
ADD CONSTRAINT `slave_ibfk_1` FOREIGN KEY (`master_id`) REFERENCES `master` (`id`);

To reproduce just do a QBE and select extra.*, master.*, slave.* and check display for all of these.

Discussion

1 2 > >> (Page 1 of 2)
  • Marc Delisle
    Marc Delisle
    2010-09-24

    I don't believe that using InnoDB keys is supported at all in phpMyAdmin, as the logic calls getRelatives() which only looks in pma_relation.

     
  • Michal Čihař
    Michal Čihař
    2010-10-01

    Okay I had internal relations configured as well (same as for innodb).

     
  • Marc Delisle
    Marc Delisle
    2010-10-05

    Same problem on 3.3.7.

     
  • Marc Delisle
    Marc Delisle
    2010-10-05

    • milestone: 284140 --> 3.3.7
    • assigned_to: nobody --> lem9
     
  • Marc Delisle
    Marc Delisle
    2010-10-07

    • assigned_to: lem9 --> nobody
     
  • Marc Delisle
    Marc Delisle
    2010-10-07

    The logic would have to be changed. Right now it starts by checking which table has the least number of rows and this table name is put just after the FROM clause; then other tables are verified for relations and LEFT JOIN clauses are generated.

     
  • I tried to reproduced it but the result is not the same:
    SELECT extra.*, master.*, slave.*
    FROM master
    LEFT JOIN qbetest.slave ON master.id = slave.master_id

    It seems that this issue doesn't exist any more, at least as described. As you can see, one table (extra) is missing in the query.
    Does anyone can reproduce the initial issue please?
    If not, should we close this one and create a new one, or update this ticket?

    Thanks.

     
  • Marc Delisle
    Marc Delisle
    2014-08-09

    Hugues,
    which branch did you test?

     
  • Marc,
    My test was on master.

     
  • Marc Delisle
    Marc Delisle
    2014-08-09

    Hugues,
    please retest on QA_4_2 because there was a recent change in master about this. If you can reproduce in QA_4_2, you can mark this as fixed in master for 4.3.

     
1 2 > >> (Page 1 of 2)