pt-upgrade v. 2.0.3 fails to execute SELECT query when it contains duplicate labels

[Jaime Sicam]

Below, is the a test case of running: SELECT first_name as 'Name', last_name as 'Name' FROM employees.employees LIMIT 1

./pt-upgrade -h localhost D=employees --query="SELECT first_name as 'Name', last_name as 'Name' FROM employees.employees LIMIT 1"

# Query 1: ID 0x02A224E9A90784F3 at byte 0 _______________________________
# Found 0 differences in 1 samples:
# checksums 0
# column counts 0
# column types 0
# query times 0
# row counts 0
# warning counts 0
# warning levels 0
# warnings 0
# localhost:3306
# Errors 1
# Warnings 1
# Query_time
# sum 0
# min 0
# max 0
# avg 0
# pct_95 0
# stddev 0
# median 0
# row_count
# sum 0
# min 0
# max 0
# avg 0
# pct_95 0
# stddev 0
# median 0
use `employees`;
SELECT first_name as 'Name', last_name as 'Name' FROM employees.employees LIMIT 1
/* 02A224E9A90784F3-1 */ SELECT first_name as 'Name', last_name as 'Name' FROM employees.employees LIMIT 1

[Jaime Sicam (jssicam)]

My apologies. The description is not complete. This is the output of pt-upgrade:

use `employees`;
SELECT first_name as 'Name', last_name as 'Name' FROM employees.employees LIMIT 1
/* 02A224E9A90784F3-1 */ SELECT first_name as 'Name', last_name as 'Name' FROM employees.employees LIMIT 1

# Errors
# Query ID Host Error
# ================== ============== ==========================================
# 02A224E9A90784F3-1 localhost:3306 Failed to execute query: DBD::mysql::db do failed: Duplicate column name 'Name' at ./pt-upgrade line 8945.

# Statistics
# CompareResults_after_execute_skipped 1
# CompareResults_execute_error 1
# events 1

[Baron Schwartz (baron-xaprb)]

What's happening here is that pt-upgrade is trying to create a temporary table with the SELECT:

CREATE TEMPORARY TABLE ... AS SELECT ...

MySQL will allow you to write a SELECT statement with duplicate columns, but not create a table with duplicates. The best way to handle this is to fix the original statement, because it is likely to cause all kinds of problems, now or in the future. For example, imagine if MySQL gets more strict in the future? Or, what if there is some code that fetches each row and puts it into a hash table or dictionary, and then accesses the 'Name' column?

[Baron Schwartz (baron-xaprb)]

This is one of those thigns that we should trap and print out a nice error message with a knowledgebase link. I'll add a KB tag.

[Daniel Nichter (daniel-nichter)]

pt-upgrade was rewritten for 2.2 and I've tested it and this bug no longer affects 2.2. We're not going to fix 2.1.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-943