Unexpected ambiguous error

[coocood]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

create table DocumentUnion (
    name varchar(255) not null,
    parent_name varchar(255),
    xsize integer,
    primary key (name)
) engine=InnoDB;

create table FolderUnion (
    name varchar(255) not null,
    parent_name varchar(255),
    primary key (name)
) engine=InnoDB;

create table SymbolicLinkUnion (
   name varchar(255) not null,
    parent_name varchar(255),
    target_name varchar(255) not null,
    primary key (name)
) engine=InnoDB;

SELECT this_.name           AS name1_1_3_, 
       this_.parent_name    AS parent_n2_1_3_, 
       this_.xsize          AS xsize1_0_3_, 
       this_.target_name    AS target_n1_3_3_, 
       this_.clazz_         AS clazz_3_, 
       folder2_.name        AS name1_1_0_, 
       folder2_.parent_name AS parent_n2_1_0_, 
       folder3_.name        AS name1_1_1_, 
       folder3_.parent_name AS parent_n2_1_1_, 
       file4_.name          AS name1_1_2_, 
       file4_.parent_name   AS parent_n2_1_2_, 
       file4_.xsize         AS xsize1_0_2_, 
       file4_.target_name   AS target_n1_3_2_, 
       file4_.clazz_        AS clazz_2_ 
FROM   (SELECT name, 
               parent_name, 
               NULL AS xsize, 
               NULL AS target_name, 
               1    AS clazz_ 
        FROM   folderunion 
        UNION ALL 
        SELECT name, 
               parent_name, 
               xsize, 
               NULL AS target_name, 
               2    AS clazz_ 
        FROM   documentunion 
        UNION ALL 
        SELECT name, 
               parent_name, 
               NULL AS xsize, 
               target_name, 
               3    AS clazz_ 
        FROM   symboliclinkunion) this_ 
       LEFT OUTER JOIN folderunion folder2_ 
                    ON this_.parent_name = folder2_.name 
       LEFT OUTER JOIN folderunion folder3_ 
                    ON folder2_.parent_name = folder3_.name 
       LEFT OUTER JOIN (SELECT name, 
                               parent_name, 
                               NULL AS xsize, 
                               NULL AS target_name, 
                               1    AS clazz_ 
                        FROM   folderunion 
                        UNION ALL 
                        SELECT name, 
                               parent_name, 
                               xsize, 
                               NULL AS target_name, 
                               2    AS clazz_ 
                        FROM   documentunion 
                        UNION ALL 
                        SELECT name, 
                               parent_name, 
                               NULL AS xsize, 
                               target_name, 
                               3    AS clazz_ 
                        FROM   symboliclinkunion) file4_ 
                    ON this_.target_name = file4_.name; 

2. What did you expect to see?

Empty set (0.01 sec)

3. What did you see instead?

ERROR 1105 (HY000): Column folder2_.name is ambiguous

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

Release Version: v3.0.0-beta.1-167-g5f0346216
Git Commit Hash: 5f03462166c976ca2a229f00b8ae5e2c6c102fbd
Git Branch: master
UTC Build Time: 2019-05-05 04:43:36
GoVersion: go version go1.12 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

[coocood]

Possible the same bug caused failure

create table Child (
   name varchar(255) not null,
    familyName_name varchar(255),
    favouriteToy varchar(255),
    father_name varchar(255),
    mother_name varchar(255),
    primary key (name)
) engine=InnoDB;

create table Family (
   name varchar(255) not null,
    primary key (name)
) engine=InnoDB;

create table Man (
   name varchar(255) not null,
    familyName_name varchar(255),
    hobby varchar(255),
    wife_name varchar(255),
    primary key (name)
) engine=InnoDB;

create table Person (
   name varchar(255) not null,
    familyName_name varchar(255),
    primary key (name)
) engine=InnoDB;

create table Woman (
   name varchar(255) not null,
    familyName_name varchar(255),
    job varchar(255),
    husband_name varchar(255),
    primary key (name)
) engine=InnoDB;

SELECT members0_.familyname_name    AS familyNa2_3_0_, 
       members0_.name               AS name1_3_0_, 
       members0_.name               AS name1_3_1_, 
       members0_.familyname_name    AS familyNa2_3_1_, 
       members0_.father_name        AS father_n2_0_1_, 
       members0_.favouritetoy       AS favourit1_0_1_, 
       members0_.mother_name        AS mother_n3_0_1_, 
       members0_.hobby              AS hobby1_2_1_, 
       members0_.wife_name          AS wife_nam2_2_1_, 
       members0_.husband_name       AS husband_2_4_1_, 
       members0_.job                AS job1_4_1_, 
       members0_.clazz_             AS clazz_1_, 
       tablepercl1_.name            AS name1_3_2_, 
       tablepercl1_.familyname_name AS familyNa2_3_2_, 
       tablepercl1_.husband_name    AS husband_2_4_2_, 
       tablepercl1_.job             AS job1_4_2_, 
       tablepercl2_.name            AS name1_1_3_, 
       tablepercl3_.name            AS name1_3_4_, 
       tablepercl3_.familyname_name AS familyNa2_3_4_, 
       tablepercl3_.hobby           AS hobby1_2_4_, 
       tablepercl3_.wife_name       AS wife_nam2_2_4_, 
       tablepercl4_.name            AS name1_1_5_, 
       tablepercl5_.name            AS name1_3_6_, 
       tablepercl5_.familyname_name AS familyNa2_3_6_, 
       tablepercl5_.hobby           AS hobby1_2_6_, 
       tablepercl5_.wife_name       AS wife_nam2_2_6_, 
       tablepercl6_.name            AS name1_3_7_, 
       tablepercl6_.familyname_name AS familyNa2_3_7_, 
       tablepercl6_.husband_name    AS husband_2_4_7_, 
       tablepercl6_.job             AS job1_4_7_ 
FROM   (SELECT name, 
               familyname_name, 
               NULL AS favouriteToy, 
               NULL AS father_name, 
               NULL AS mother_name, 
               NULL AS hobby, 
               NULL AS wife_name, 
               NULL AS job, 
               NULL AS husband_name, 
               0    AS clazz_ 
        FROM   person 
        UNION ALL 
        SELECT name, 
               familyname_name, 
               favouritetoy, 
               father_name, 
               mother_name, 
               NULL AS hobby, 
               NULL AS wife_name, 
               NULL AS job, 
               NULL AS husband_name, 
               1    AS clazz_ 
        FROM   child 
        UNION ALL 
        SELECT name, 
               familyname_name, 
               NULL AS favouriteToy, 
               NULL AS father_name, 
               NULL AS mother_name, 
               hobby, 
               wife_name, 
               NULL AS job, 
               NULL AS husband_name, 
               2    AS clazz_ 
        FROM   man 
        UNION ALL 
        SELECT name, 
               familyname_name, 
               NULL AS favouriteToy, 
               NULL AS father_name, 
               NULL AS mother_name, 
               NULL AS hobby, 
               NULL AS wife_name, 
               job, 
               husband_name, 
               3    AS clazz_ 
        FROM   woman) members0_ 
       LEFT OUTER JOIN woman tablepercl1_ 
                    ON members0_.wife_name = tablepercl1_.name 
       LEFT OUTER JOIN family tablepercl2_ 
                    ON tablepercl1_.familyname_name = tablepercl2_.name 
       LEFT OUTER JOIN man tablepercl3_ 
                    ON tablepercl1_.husband_name = tablepercl3_.name 
       LEFT OUTER JOIN family tablepercl4_ 
                    ON tablepercl3_.familyname_name = tablepercl4_.name 
       LEFT OUTER JOIN man tablepercl5_ 
                    ON members0_.father_name = tablepercl5_.name 
       LEFT OUTER JOIN woman tablepercl6_ 
                    ON members0_.mother_name = tablepercl6_.name 
WHERE  members0_.familyname_name = 'McCloud'; 

ERROR 1105 (HY000): Column tablepercl1_.name is ambiguous

[coocood]

another failed case

create table Child (
   id bigint not null,
    parent_id bigint,
    primary key (id)
) engine=InnoDB;

create table Father (
   id bigint not null,
    fathersDay varchar(255),
    primary key (id)
) engine=InnoDB;

create table Mother (
   id bigint not null,
    mothersDay varchar(255),
    primary key (id)
) engine=InnoDB;

SELECT unionsubcl0_.id        AS id1_0_, 
       unionsubcl0_.parent_id AS parent_i2_0_ 
FROM   child unionsubcl0_ 
       LEFT OUTER JOIN (SELECT id, 
                               fathersday, 
                               NULL AS mothersDay, 
                               1    AS clazz_ 
                        FROM   father 
                        UNION ALL 
                        SELECT id, 
                               NULL AS fathersDay, 
                               mothersday, 
                               2    AS clazz_ 
                        FROM   mother) unionsubcl1_ 
                    ON unionsubcl0_.parent_id = unionsubcl1_.id 
       LEFT OUTER JOIN (SELECT id, 
                               fathersday, 
                               NULL AS mothersDay, 
                               1    AS clazz_ 
                        FROM   father 
                        UNION ALL 
                        SELECT id, 
                               NULL AS fathersDay, 
                               mothersday, 
                               2    AS clazz_ 
                        FROM   mother) unionsubcl2_ 
                    ON unionsubcl0_.parent_id = unionsubcl2_.id 
WHERE  unionsubcl1_.clazz_ = 1 
       AND unionsubcl1_.fathersday = 'FD1' 
        OR unionsubcl2_.clazz_ = 2 
           AND unionsubcl2_.mothersday = 'MD1';

ERROR 1105 (HY000): Column id is ambiguous

[erjiaqing]

A simpler case:

SELECT 
       folderunion.parent_name
FROM   (SELECT parent_name
        FROM   folderunion 
        UNION ALL 
        SELECT parent_name
        FROM   documentunion ) this_ 
       JOIN folderunion 
                    ON 1
       LEFT OUTER JOIN folderunion folder3_ 
                    ON 1
       JOIN folderunion file4_ 
                    ON 1; 

[erjiaqing]

For now, maybe you can use some alias in your SQL as a workaround.

For example, this SQL works.

SELECT this_.nm AS name1_1_3_, 
       this_.p_n AS parent_n2_1_3_, 
       this_.xsize          AS xsize1_0_3_, 
       this_.target_name    AS target_n1_3_3_, 
       this_.clazz_         AS clazz_3_, 
       folder2_.name        AS name1_1_0_, 
       folder2_.parent_name AS parent_n2_1_0_, 
       folder3_.name        AS name1_1_1_, 
       folder3_.parent_name AS parent_n2_1_1_, 
       file4_.nm AS name1_1_2_, 
       file4_.parent_name   AS parent_n2_1_2_, 
       file4_.xsize         AS xsize1_0_2_, 
       file4_.target_name   AS target_n1_3_2_, 
       file4_.clazz_        AS clazz_2_ 
FROM   (SELECT name as nm, 
               parent_name as p_n, 
               NULL AS xsize, 
               NULL AS target_name, 
               1    AS clazz_ 
        FROM   folderunion 
        UNION ALL 
        SELECT name as nm, 
               parent_name as p_n, 
               xsize, 
               NULL AS target_name, 
               2    AS clazz_ 
        FROM   documentunion 
        UNION ALL 
        SELECT name as nm, 
               parent_name as p_n, 
               NULL AS xsize, 
               target_name, 
               3    AS clazz_ 
        FROM   symboliclinkunion) this_ 
       LEFT OUTER JOIN folderunion folder2_ 
                    ON this_.p_n= folder2_.name 
       LEFT OUTER JOIN folderunion folder3_ 
                    ON folder2_.parent_name = folder3_.name 
       LEFT OUTER JOIN (SELECT name as nm, 
                               parent_name, 
                               NULL AS xsize, 
                               NULL AS target_name, 
                               1    AS clazz_ 
                        FROM   folderunion 
                        UNION ALL 
                        SELECT name as nm, 
                               parent_name, 
                               xsize, 
                               NULL AS target_name, 
                               2    AS clazz_ 
                        FROM   documentunion 
                        UNION ALL 
                        SELECT name as nm, 
                               parent_name, 
                               NULL AS xsize, 
                               target_name, 
                               3    AS clazz_ 
                        FROM   symboliclinkunion) file4_ 
                    ON this_.target_name = file4_.nm;