Parser drops table alias in CSV export

[GuillaumeRossolini]

Steps to reproduce

This was a high-level query, no exact reproduce case at the moment.
Query goes like this:

SELECT 'LBL1' AS idx, 'LBL2' AS code_journal, 'LBL3' AS libelle_journal
    , DATE_FORMAT(prs1.payment_at, '%d/%m/%Y') AS d
    , 'LBL4' AS no_compte, 'LBL5' AS libelle_compte
    , o.increment_id AS reference
    , CONVERT(CONCAT('C', LPAD(o.acheteur_id, 7, '0')) USING utf8) AS libelle
    , CAST(REPLACE(ROUND(IFNULL(
        prq1.amount + IFNULL(prq2.amount, 0)
        , 0), 2), '.', ',') AS CHAR(25))
    AS debit
    , 0 AS credit
FROM table1 AS o
INNER JOIN table2 AS prq1 ON prq1.order_id = o.order_id
INNER JOIN table3 AS prs1 ON prs1.request_id = prq1.id AND prs1.authorization_nb IS NOT NULL
LEFT JOIN table2 AS prq2 ON prq2.order_id = o.nego_prev_order_id
LEFT JOIN table3 AS prs2 ON prs2.request_id = prq2.id AND prs2.authorization_nb IS NOT NULL
WHERE o.qty_invoiced > 0
    AND o.state IN (...)
    AND o.status NOT IN (...)
    AND DATE(prs1.payment_at) BETWEEN '2016-01-01' AND '2016-04-30'
GROUP BY DATE_FORMAT(prs1.payment_at, '%d/%m/%Y'), o.order_id

Schema goes like this:
table1 (increment_id, qty_invoiced, state, status, order_id, acheteur_id, nego_prev_order_id)
table2 (id, order_id, amount)
table3 (id, payment_at, request_id, authorization_nb)

Expected behaviour

Data is exported to CSV file

Actual behaviour

The query is executed in the Web UI and the data is displayed and browseable, but the query fails when exporting to CSV.
Apparently the parser rewrites the query with inconsistent table aliases (or removes some aliases altogether but inconsistently), wich produces "1054 - Unknown column 'X.Y' in 'on clause'" an error.

Server configuration

Operating system: Debian Wheezy

Web server: httpd-2.4.20

Database: 5.5.49-0+deb7u1-log - (Debian)

PHP version: 5.6.21 + libmysql - mysqlnd 5.0.11-dev - 20120503

phpMyAdmin version: 4.6.1
(issue not present in v4.4.15.4)

Client configuration

Browser: Firefox Developer Edition 48.0a2

Operating system: Windows 10

[GuillaumeRossolini]

By the way, the "1054 - Unknown column 'X.Y' in 'on clause'" error was referring to the full table name as X and the column name as Y. In other words, it should have displayed X as the table alias but displayed the table name, which might be the problem the parser is having.

[devenbansod]

Hi @GuillaumeRossolini I am not able to reproduce this on a similar structure and query. Could you please try to reproduce this issue on latest version 4.6.4 ?
You can give it a try on demo server : https://demo.phpmyadmin.net/STABLE/?pma_username=root

[SeptamusNonovant]

Hello,

I am also having this issue, and I have been able to reproduce this on the demo server:

SELECT a.`id` AS 'CUST_ID', a.`name` AS 'CUST_NAME', b.`type` AS 'CUST_TYPE'
FROM `customer_list` AS a
INNER JOIN `customer_type` AS b
ON a.`customer_type` = b.`id`
WHERE a.`disabled` = 1
UNION
SELECT a.`id` AS 'CUST_ID', a.`name` AS 'CUST_NAME', b.`type` AS 'CUST_TYPE'
FROM `customer_list` AS a
INNER JOIN `customer_type` AS b
ON a.`customer_type` = b.`id`
WHERE a.`disabled` = 0

When I export to CSV, in the error output, this is the query that gets executed (and fails):

SELECT customer_list.`id` AS 'CUST_ID', customer_list.`name` AS 'CUST_NAME', b.`type` AS 'CUST_TYPE'
FROM `customer_list` AS a		
INNER JOIN `customer_type` AS b		
ON customer_list.`customer_type` = b.`id`		
WHERE customer_list.`disabled` = 1		
UNION		
SELECT customer_list.`id` AS 'CUST_ID', customer_list.`name` AS 'CUST_NAME', b.`type` AS 'CUST_TYPE'
FROM `customer_list` AS a		
INNER JOIN `customer_type` AS b		
ON customer_list.`customer_type` = b.`id`		
WHERE customer_list.`disabled` = 0		

If for some reason this DB gets wiped (it happened a few times when I tried to test this), here is the full code to reproduce the tables:

-- phpMyAdmin SQL Dump
-- version 4.6.6
-- https://www.phpmyadmin.net/
--
-- Host: 192.168.30.23
-- Generation Time: Jan 25, 2017 at 12:10 AM
-- Server version: 5.5.52-0+deb8u1
-- PHP Version: 7.0.15-1+0~20170120094752.20+jessie~1.gbpe03972

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `export_test`
--

-- --------------------------------------------------------

--
-- Table structure for table `customer_list`
--

CREATE TABLE `customer_list` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `disabled` tinyint(1) NOT NULL DEFAULT '0',
  `customer_type` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `customer_list`
--

INSERT INTO `customer_list` (`id`, `name`, `disabled`, `customer_type`) VALUES
(1, 'Allco', 0, 1),
(2, 'Ambivalent Enterprises', 1, 1),
(3, 'Betaco', 0, 2),
(4, 'Brilliant Decision', 0, 3),
(5, 'Bravo Company', 0, 2),
(6, 'Company Corporation', 1, 2),
(7, 'Creature Feature', 0, 2),
(8, 'Decision Point Inccorporated', 1, 3);

-- --------------------------------------------------------

--
-- Table structure for table `customer_type`
--

CREATE TABLE `customer_type` (
  `id` int(11) NOT NULL,
  `type` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `customer_type`
--

INSERT INTO `customer_type` (`id`, `type`) VALUES
(1, 'Type A'),
(2, 'Type B'),
(3, 'Type C');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `customer_list`
--
ALTER TABLE `customer_list`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `customer_type`
--
ALTER TABLE `customer_type`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `customer_list`
--
ALTER TABLE `customer_list`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
--
-- AUTO_INCREMENT for table `customer_type`
--
ALTER TABLE `customer_type`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;



/* Run this query and export the result to get the error */
SELECT a.`id` AS 'CUST_ID', a.`name` AS 'CUST_NAME', b.`type` AS 'CUST_TYPE'
FROM `customer_list` AS a
INNER JOIN `customer_type` AS b
ON a.`customer_type` = b.`id`
WHERE a.`disabled` = 1
UNION
SELECT a.`id` AS 'CUST_ID', a.`name` AS 'CUST_NAME', b.`type` AS 'CUST_TYPE'
FROM `customer_list` AS a
INNER JOIN `customer_type` AS b
ON a.`customer_type` = b.`id`
WHERE a.`disabled` = 0

[dziudek]

Hi,

I have the same problem - query is working properly in the data view, but after selecting export, I am getting an error about non-existing table (which exists). And in the query I saw replaced table aliases with full names of the table. As I saw it replaces only the alias of table which is used in the FROM clause.

I have checked it with PMA 4.6.5.2 and after update to 4.6.6 the problem still exists.

I my local environement on v.4.4.10 the query works without any problems and exports to CSV.

In fact this error appears during export to any format so it is not related only to CSV export.

[chenghung]

got similar error on phpmyadmin 4.8.3

[williamdes]

@Tithugues
(please let me know if you want me to find you issues to fix if you want ?)

[Tithugues]

In tbl_export.php, there is this piece of code:

        // Removing the aliases by finding the alias followed by a dot.
        $tokens = PhpMyAdmin\SqlParser\Lexer::getTokens($sql_query);
        foreach ($aliases as $alias => $table) {
            $tokens = PhpMyAdmin\SqlParser\Utils\Tokens::replaceTokens(
                $tokens,
                array(
                    array(
                        'value_str' => $alias,
                    ),
                    array(
                        'type' => PhpMyAdmin\SqlParser\Token::TYPE_OPERATOR,
                        'value_str' => '.',
                    )
                ),
                array(
                    new PhpMyAdmin\SqlParser\Token($table),
                    new PhpMyAdmin\SqlParser\Token('.',PhpMyAdmin\SqlParser\Token::TYPE_OPERATOR)
                )
            );
        }

@williamdes @MauricioFauth @ibennetch , do you see any reason to remove the aliases please?

[Tithugues]

For information, removing this "tokens removals" do the job.

Looking at ce6c465#diff-6a4db2d5db5d33a4f7d97eaeb58377c6, I would say this is something kept from an old version when export was available on one table only.
But now, I think that aliases are mandatory.

[williamdes]

@williamdes @MauricioFauth @ibennetch , do you see any reason to remove the aliases please?

For me, no reason.
We should keep the user's query untouched.