#4510 (ok 4.5) QBE generates wrong query

[Hugues Peccatte]

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

SELECT master.*, slave.*
FROM master
LEFT JOIN qbetest.slave ON master.id = slave.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.

[Ashutosh Dhundhara]

Hi Hugues,
as per the discussion [0] on mailing list, it was found that user generally needs to display all the rows of the master table even for those there are no matching rows in the slave table. Also it was seen that UI can't help much to deduce the intentions of the user in case of 3 tables.
But still, the thread is open for discussion.

[0] https://sourceforge.net/p/phpmyadmin/mailman/message/32679744/

[Madhura Jayaratne]

Related https://sourceforge.net/p/phpmyadmin/bugs/4795/

[Madhura Jayaratne]

PR https://github.com/phpmyadmin/phpmyadmin/pull/1723
Commit https://github.com/madhuracj/phpmyadmin/commit/115607abb222c5da8a33c80266582ebba9e14b56

[Madhura Jayaratne]

Fixed with https://github.com/phpmyadmin/phpmyadmin/commit/115607abb222c5da8a33c80266582ebba9e14b56