SQL error 1166 when using concat and substring/substring_index together in mysql

user_13404487 0
Viewed 68

I met a strange error when using concat and substring_index together in mysql, my code is here

CREATE TEMPORARY TABLE candidate (ori int, dest int, myPath varchar(500), depth int ) as (
    select transforms.ori, baseTransforms.dest, concat(substring_index(baseTransforms.road,',',-1),',',substring_index(transforms.road,',',1)), 2
    from transforms, baseTransforms
    where transforms.dest=baseTransforms.ori and transforms.ori <> baseTransforms.dest);

where definitions are here

create table baseTransforms(
    ori int,
    dest int,
    road varchar(500)
);
create table transforms(
    ori int,
    dest int,
    road varchar(500)
);

and baseTransform's data is filled like this

insert into baseTransforms select * from transforms;

Then I get

Error Code: 1166. Incorrect column name 'concat(substring_index(baseTransforms.road,',',-1),',',substring_index(transforms.road,',',1))'

but when I modify the code by deleting one of my substring_index phase, it doesn't raise up an error, i.e.

CREATE TEMPORARY TABLE candidate (ori int, dest int, myPath varchar(500), depth int ) as (
        select transforms.ori, baseTransforms.dest, concat(',',substring_index(transforms.road,',',1)), 2
        from transforms, baseTransforms
        where transforms.dest=baseTransforms.ori and transforms.ori <> baseTransforms.dest);

and 

CREATE TEMPORARY TABLE candidate (ori int, dest int, myPath varchar(500), depth int ) as (
        select transforms.ori, baseTransforms.dest, concat(substring_index(baseTransforms.road,',',-1),','), 2
        from transforms, baseTransforms
        where transforms.dest=baseTransforms.ori and transforms.ori <> baseTransforms.dest);

both works well, so in fact both column names are right ,it's so confusing.

p.s. when I try to use substring function instead of substring_index , it got the same problem.

Can anyone help me solve this problem?

mysql
1 Answers
user_43862780
Related