#3117 (ok 4.3) QBE generates wrong query

4.2.7
fixed
1
2014-12-05
2010-09-21
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

  • 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.

     
  • Hugues Peccatte

    Hugues Peccatte - 2014-08-09

    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?

     
  • Hugues Peccatte

    Hugues Peccatte - 2014-08-09

    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.

     
  • Hugues Peccatte

    Hugues Peccatte - 2014-08-10

    Here is the result on 4.2:
    SELECT extra., master., slave.*
    FROM slave, extra, master

    The left join is missing…

     
  • Marc Delisle

    Marc Delisle - 2014-08-10
    • summary: QBE generates wrong query --> (ok 4.3) QBE generates wrong query
    • status: open --> resolved
    • assigned_to: Hugues Peccatte
    • Group: 3.3.7 --> 4.2.7
    • Priority: 5 --> 1
     
  • Hugues Peccatte

    Hugues Peccatte - 2014-08-10

    Marc,
    Ok to close this ticket because the initial issue doesn't exist any more.
    But should we create a new one for the missing LEFT JOIN or maybe the master modifications are not over?

     
  • Marc Delisle

    Marc Delisle - 2014-08-10

    Hugues,
    please close the ticket and open another one. I believe that the master modifications are over.

     
  • Marc Delisle

    Marc Delisle - 2014-12-05
    • Status: resolved --> fixed
     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks