Query Result Export not using table aliases

[imacarthur]

Describe the bug

Original query:

select m.company, o.id, cd1.scheduled_date as pu_date, cd2.scheduled_date as del_date, o.total, p.name, a1.line1, a1.city, a1.postalcode, a2.line1, a2.city, a2.postalcode

from orders o, custom_delivery cd1, custom_delivery cd2, addresses a1, addresses a2, product p, order_lines ol, members m

where o.member_id=556 and cd1.order_id = o.id and cd1.service_type = 'P' and cd2.order_id = o.id and cd2.service_type='D' and a1.ownertype='order' and a1.ownerid=o.id and a2.ownertype='order' and a2.ownerid='order' and a1.addresstype=12 and a2.addresstype=13 and ol.order_id = o.id and ol.custom_package='S' and p.id = ol.product_id and m.id = o.member_id

query executes no errors. I want to export the results to a spreadsheet.

The results export, but the exported file is actually an html fragment with a server error:
#1066 - Not unique table/alias: 'custom_delivery'

In the error report is the actual query executed:

select members.company, orders.id, custom_delivery.scheduled_date as pu_date, custom_delivery.scheduled_date as del_date, orders.total, product.name, addresses.line1, addresses.city, addresses.postalcode, addresses.line1, addresses.city, addresses.postalcode 

FROM `orders`, `custom_delivery`, `custom_delivery`, `addresses`, `addresses`, `product`, `order_lines`, `members`

where orders.member_id=556 and custom_delivery.order_id = orders.id and custom_delivery.service_type = 'P' and custom_delivery.order_id = orders.id and custom_delivery.service_type='D' and addresses.ownertype='order' and addresses.ownerid=orders.id and addresses.ownertype='order' and addresses.ownerid='order' and addresses.addresstype=12 and addresses.addresstype=13 and order_lines.order_id = orders.id and order_lines.custom_package='S' and product.id = order_lines.product_id and members.id = orders.member_id

The table aliases are gone (custom_delivery cd1 was changed to custom_delivery)

Complete exported fragment attached.
members (1).zip

Server configuration

• Operating system: Windows 10
• Web server: apache 2.4
• Database version: MariaDB 15.1
• PHP version: 7.1
• phpMyAdmin version: 4.7.7 & 4.8.2

[imacarthur]

Simpler query that also fails:

select m1.id, m2.id from members m1, members m2 where m1.id = m2.id

Same issue, the table aliases get dropped.

select members.id, members.id FROM `members`, `members` where members.id = members.id

[williamdes]

@imacarthur Can you please explain how to reproduce this issue ?
Can try on demo-servers ?
5.0.0-dev
4.8.x

[imacarthur]

I tried the kovenko database. Query I used is: SELECT e1.empno, e2.empno FROM `Emp` e1, Emp e2 WHERE e1.deptno = e2.deptno The query executes, and I go to export the results. In the downloaded csv file I have an error with the offending statement: SELECT Emp.empno, Emp.empno FROM `Emp`, `Emp` WHERE Emp.deptno = Emp.deptno Note that e1.empno is now Emp.empno and e2.empno is Emp.empno etc. See attached for the csv I downloaded.

[williamdes]

@imacarthur Thank you !

Steps

1. Insert following data:

CREATE TABLE `Dept` (
  `deptno` decimal(3,0) NOT NULL,
  `dname` varchar(14) NOT NULL,
  `loc` varchar(13) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `Dept` (`deptno`, `dname`, `loc`) VALUES
('10', 'ACCOUNTING', 'NEW_YORK'),
('20', 'RESEARCH', 'DALLAS'),
('30', 'SALES', 'CHICAGO'),
('40', 'OPERATIONS', 'BOSTON'),
('50', 'RESEARCH2', 'HONKONG'),
('60', 'SALES2', 'HONKONG'),
('100', 'SALES3', 'NEW_YORK');

CREATE TABLE `Emp` (
  `empno` decimal(4,0) NOT NULL,
  `ename` varchar(20) DEFAULT NULL,
  `job` varchar(9) DEFAULT NULL,
  `mgr` decimal(4,0) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` decimal(3,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `Emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES
('7369', 'SMITH', 'CLERK', '7902', '2010-12-17', '800.00', NULL, '20'),
('7499', 'ALLEN', 'SALESMAN', '7698', '2011-02-20', '1600.00', '300.00', '30'),
('7521', 'WARD', 'SALESMAN', '7698', '2011-02-22', '1250.00', '500.00', '30'),
('7566', 'JONES', 'MANAGER', '7839', '2011-04-02', '2975.00', NULL, '20'),
('7654', 'MARTIN', 'SALESMAN', '7698', '2011-09-28', '1250.00', '1400.00', '30'),
('7698', 'BLAKE', 'MANAGER', '7839', '2011-05-01', '2850.00', NULL, '30'),
('7782', 'CLARK', 'MANAGER', '7839', '2011-06-09', '1500.00', NULL, '10'),
('7788', 'SCOTT', 'ANALYST', '7566', '2012-12-09', '3000.00', NULL, '20'),
('7839', 'KING', 'PRESIDENT', NULL, '2011-11-17', '5000.00', NULL, '10'),
('7844', 'TURNER', 'SALESMAN', '7698', '2011-09-08', '1500.00', '0.00', '30'),
('7876', 'ADAMS', 'CLERK', '7788', '2013-01-12', '1100.00', NULL, '20'),
('7900', 'JAMES', 'CLERK', '7698', '2011-12-03', '950.00', NULL, '30'),
('7902', 'FORD', 'ANALYST', '7566', '2011-12-03', '3000.00', NULL, '20'),
('7934', 'MILLER', 'CLERK', '7782', '2012-01-23', '1300.00', NULL, '10'),
('8000', 'JACKIE CHAN', 'SALESMAN', '7839', '2011-09-28', '2250.00', '1700.00', '60'),
('8001', 'JET LI', 'SALESMAN', '8000', '2011-02-20', '2600.00', '600.00', '60'),
('8002', 'BRUCE LEE', 'SALESMAN', '8000', '2011-09-08', '2500.00', NULL, '60'),
('8003', 'DR NO', 'ANALYST', '7839', '2011-09-11', '2500.00', NULL, NULL);

CREATE TABLE `Salgrade` (
  `grade` decimal(1,0) NOT NULL,
  `minsal` decimal(7,2) DEFAULT NULL,
  `hisal` decimal(7,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `Salgrade` (`grade`, `minsal`, `hisal`) VALUES
('1', '0.00', '1000.00'),
('2', '1000.01', '2000.00'),
('3', '2000.01', '3000.00'),
('4', '3000.01', '4000.00'),
('5', '4000.01', '10000.00');
ALTER TABLE `Dept`
  ADD PRIMARY KEY (`deptno`),
  ADD UNIQUE KEY `dept_naem_uk` (`dname`);
ALTER TABLE `Emp`
  ADD PRIMARY KEY (`empno`),
  ADD KEY `emp_deptno_fk` (`deptno`),
  ADD KEY `fk_Self` (`mgr`);
ALTER TABLE `Salgrade`
  ADD PRIMARY KEY (`grade`);
ALTER TABLE `Emp`
  ADD CONSTRAINT `emp_deptno_fk` FOREIGN KEY (`deptno`) REFERENCES `Dept` (`deptno`),
  ADD CONSTRAINT `fk_Self` FOREIGN KEY (`mgr`) REFERENCES `Emp` (`empno`) ON DELETE CASCADE;
COMMIT;

2. Run query SELECT e1.empno, e2.empno FROM Emp e1, Emp e2 WHERE e1.deptno = e2.deptno in SQL
3. Export results using export button at the bottom of the page.
4. Change SQL to OpenDocument Spreadsheet (.ods)
5. View the downloaded file

Expected

Data in ODS

Actual behavior (from ODS sheet)

Erreur
Requête SQL : Éditer
SELECT Emp.empno, Emp.empno FROM Emp, Emp WHERE Emp.deptno = Emp.deptno
MySQL a répondu :
#1066 - Table/alias: 'Emp' non unique

PS (note for contributors)

Check that encoding is correct in the downloaded ODS (maybe a bug to report, maybe not)

[williamdes]

Duplicate of #12241