Re: Ambiguous column name error.

Chris Robinson <[email protected]> wrote on 03/13/2006 
09:50:36 AM:

> Hi all,
> 
> Running the query below, I get the error "column 'RecordID' in field 
> list is ambiguous".  However (as you can see), RecordID isn't even in 
> the field list.  The field does exist in the tables ut_maindata_tbl, 
> ft_maindata_tbl and entry_maindata_tbl (it's an auto-incrementing ID 
> field which is also the primary key).  I'm using MySQL 5.0.18 on a 
> Windows 2000 server box.
> 
> 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"))
> 
> Any ideas?  I'm guessing it's something to do with me linking the 
> tables, but all the tables having a primary key called "RecordID".
> 
> Thanks in advance,
> Rgds,
> Chris.
> 

Same query, unwrapped:

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"))


This logic seems a bit convoluted to me:

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 think that this is a logical equivalent (based on transitive equalities: 
if A=B and B=C then A=C)
FROM ft_maindata_tbl 
INNER JOIN entry_maindata_tbl 
        ON entry_maindata_tbl.unit_serial = ft_maindata_tbl.unit_serial
        AND entry_maindata_tbl.datetime Between 
                ft_maindata_tbl.tr_datetime-1 
                And ft_maindata_tbl.tr_datetime+1
INNER JOIN ut_maindata_tbl 
        ON entry_maindata_tbl.unit_serial = ut_maindata_tbl.unit_serial 
        AND ut_maindata_tbl.datetime=ft_maindata_tbl.datetime
WHERE (
                ft_maindata_tbl.record_type <5
                OR ft_maindata_tbl.record_type >7
                OR ft_maindata_tbl.recordtype=6
        )
        AND (
                ft_maindata_tbl.pretest_pareto_flag="0000000000000000" 
                Or ft_maindata_tbl.pretest_pareto_flag="0000000000010000"
        )
        AND 
ft_maindata_tbl.ft_pareto_flag="00000000000000000000000000000000"


If you change your <table reference> clause to match my changes, does that 
avoid the confusion in the parser?  I agree that your original SQL appears 
syntactically correct. However, your clause may have been convoluted 
enough that the parser became confused. Please support the developemnt by 
submitting your query (along with the table definitions) as a bug report. 
It's circular and parenthetical queries like yours that help us to expose 
the logical gaps in the parsing routines.  Good Job!!

Yours,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine