Bug #18212 Circular query causes "ambiguous column name" error.

• Submitted: 14 Mar 2006 8:25
• Modified: 15 Mar 2006 19:05
• Reporter: Chris Robinson
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.18
• OS: Windows (Windows 2000 Server)
• CPU Architecture: Any

[14 Mar 2006 8:25] Chris Robinson

Description:
I was testing out the following query I'd written:

SELECT entry_maindata_tbl.index_serial, ft_maindata_tbl.unit_serial, ut_maindata_tbl.filename AS ut_filename, ft_maindata_tbl.filename AS ft_filename, ut_maindata_tbl.cell, ut_maindata_tbl.ut_compensated_tr_reg, ut_maindata_tbl.ut_compensated_5r_reg, ut_maindata_tbl.ut_tr_peak_pressure, ut_maindata_tbl.ut_5r_peak_pressure, ft_maindata_tbl.test_line & ft_maindata_tbl.final_test_head AS ft_head_id, ft_maindata_tbl.max_pressure_loss, ft_maindata_tbl.tr_reg_err, ft_maindata_tbl.5r_reg_error, ft_maindata_tbl.avg_tr_pressure_loss, ft_maindata_tbl.5r_max_ploss, "output_passfirst" AS source_query, entry_maindata_tbl.filename AS entry_filename, ut_maindata_tbl.datetime AS UTDateTimeTested, ft_maindata_tbl.ft_start_datetime AS FTDateTimeTested
FROM (entry_maindata_tbl INNER JOIN ut_maindata_tbl ON entry_maindata_tbl.unit_serial = ut_maindata_tbl.unit_serial) INNER JOIN ft_maindata_tbl ON ut_maindata_tbl.unit_serial = ft_maindata_tbl.unit_serial
WHERE (((ft_maindata_tbl.unit_serial)=entry_maindata_tbl.unit_serial)
AND ((ut_maindata_tbl.datetime)=ft_maindata_tbl.datetime)
AND ((ft_maindata_tbl.record_type)<>5 And (ft_maindata_tbl.record_type)<>7)
AND ((entry_maindata_tbl.datetime) Between ft_maindata_tbl.tr_datetime-1 And ft_maindata_tbl.tr_datetime+1)
AND ((ft_maindata_tbl.pretest_pareto_flag)="0000000000000000" Or (ft_maindata_tbl.pretest_pareto_flag)="0000000000010000")
AND ((ft_maindata_tbl.ft_pareto_flag)="00000000000000000000000000000000"))

I expected to have a set of results returned, but instead I got the error "ambiguous colum name 'RecordID' in field list.  As you can see, 'RecordID' is not in the field list above.  It is a column name in each of the tables entry_maindata_tbl, ut_maindata_tbl and ft_maindata_tbl though (it is the primary key in each of these tables).

Here is the schema for the tables involved in the above query.

entry_maindata_tbl:
FileUID
RecordID (Primary Key)
datetime
index_serial
unit_serial
filename
in_output

ft_maindata_tbl:
FileUID
RecordID (Primary Key)
xx
record_type
unit_serial
datetime
activity
tr_datetime
test_line
min_pressure_loss
max_pressure_loss
avg_pressure_loss
pretest_pareto_flag
ft_start_datetime
final_test_head
min_tr_pressure_loss
max_tr_pressure_loss
avg_tr_pressure_loss
tr_reg_err
ft_pareto_flag
5r_max_ploss
5r_reg_error
rt_pt_max_ploss
rt_pt_pareto
rt_final_test_head
rt_avg_tr_pressure_loss
rt_tr_reg_err
rt_pareto_flag
rt_5r_max_ploss
rt_5r_reg_error
filename
in_output

ut_maindata_tbl:
FileUID
RecordID (Primary Key)
xx
unit_serial
datetime
cell
r_or_l
ut_compensated_tr_reg
ut_compensated_5r_reg
ut_tr_peak_pressure
ut_5r_peak_pressure
filename
in_output

How to repeat:
It repeats every time I try to run that query.  Just creating tables as above and then running the query should do it.

Suggested fix:
I posted to the MySQL win32 list about this, and found out that my query is somewhat illogical and this could be confusing the parser.  So, a re-write of the query into a more logical one may fix it.  That said, the query (I think) is syntactically correct, so it should run.  Changing the 'RecordID' field in each table to a unique name (i.e. EntryRecordID, UTRecordID and FTRecordID) fixes the problem, and the query runs.

The only other thing I've since found out is that I should be using ADDDATE and SUBDATE instead of "Between ft_maindata_tbl.tr_datetime-1 And ft_maindata_tbl.tr_datetime+1" (i.e. instead of -1 and +1) in the query.

[14 Mar 2006 9:14] Jorge del Conde

Can you please provide us with the CREATE TABLE statements so that we can attempt to execute your query ?

Thanks

[15 Mar 2006 19:05] MySQL Verification Team

Thank you for the bug report. Could you please test with the 5.0.19
released version. Thanks in advance.

mysql> SELECT entry_maindata_tbl.index_serial, ft_maindata_tbl.unit_serial,
    -> ut_maindata_tbl.filename AS ut_filename, ft_maindata_tbl.filename AS
    -> ft_filename, ut_maindata_tbl.cell, ut_maindata_tbl.ut_compensated_tr_reg,
    -> ut_maindata_tbl.ut_compensated_5r_reg, ut_maindata_tbl.ut_tr_peak_pressure,
    -> ut_maindata_tbl.ut_5r_peak_pressure, ft_maindata_tbl.test_line &
    -> ft_maindata_tbl.final_test_head AS ft_head_id,
    -> ft_maindata_tbl.max_pressure_loss, ft_maindata_tbl.tr_reg_err,
    -> ft_maindata_tbl.5r_reg_error, ft_maindata_tbl.avg_tr_pressure_loss,
    -> ft_maindata_tbl.5r_max_ploss, "output_passfirst" AS source_query,
    -> entry_maindata_tbl.filename AS entry_filename, ut_maindata_tbl.datetime AS
    -> UTDateTimeTested, ft_maindata_tbl.ft_start_datetime AS FTDateTimeTested
    -> FROM (entry_maindata_tbl INNER JOIN ut_maindata_tbl ON
    -> entry_maindata_tbl.unit_serial = ut_maindata_tbl.unit_serial) INNER JOIN
    -> ft_maindata_tbl ON ut_maindata_tbl.unit_serial = ft_maindata_tbl.unit_serial
    -> WHERE (((ft_maindata_tbl.unit_serial)=entry_maindata_tbl.unit_serial)
    -> AND ((ut_maindata_tbl.datetime)=ft_maindata_tbl.datetime)
    -> AND ((ft_maindata_tbl.record_type)<>5 And (ft_maindata_tbl.record_type)<>7)
    -> AND ((entry_maindata_tbl.datetime) Between ft_maindata_tbl.tr_datetime-1 And
    -> ft_maindata_tbl.tr_datetime+1)
    -> AND ((ft_maindata_tbl.pretest_pareto_flag)="0000000000000000" Or
    -> (ft_maindata_tbl.pretest_pareto_flag)="0000000000010000")
    -> AND ((ft_maindata_tbl.ft_pareto_flag)="00000000000000000000000000000000"));
Empty set (0.24 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19-nt |
+-----------+
1 row in set (0.25 sec)

mysql>