MySQL 5.0 reporting "concat does not exist"

Question

I have a query with a complex concatenation in it. It's something along the lines of concatenating the results of several sub-selects if the results are not null. It works fine (though the complexity makes me a little sick) on our development server which runs MySQL 5.1 but our production server, which runs 5.0 reports

FUNCTION database.concat does not exist

Any ideas why this might be? Upgrading is unfortunately not an option at this point so unless someone has a bright idea I'm pretty much stuck rewriting this report.

Query is as follows (Yes I realize I'm horribly abusing mysql. Let's set that aside for now. If anyone knows how of a way to refer to a subselect by alias that would be handy, but I've never been able to do it, hence the repeated subselect. Also concat returns NULL if any of the arguments are NULL, hence the if statements.)

select (concat(if((select 
                       concat(if(b.foo is not null, b.foo, ""), 
                       " ", 
                       if(f.bar is not null, f.bar, ""))
                     from  `foo_table`  as f
                      left join `bar_table` as b
                        on b.SOME_ID = f.SOME_ID
                     where f.STUDENT_ID = t.STUDENT_ID
                       and bar.NewID = t.OldID order by bar.id limit 1) is not null,
                   (select 
                      concat(if(b.foo is not null, b.foo, ""), 
                       " ", 
                       if(f.bar is not null, f.bar, ""))
                     from  `foo_table`  as f
                      left join `bar_table` as b
                        on b.SOME_ID = f.SOME_ID
                     where f.STUDENT_ID = t.STUDENT_ID
                       and bar.NewID = t.OldID order by bar.id limit 1),
                   ""),
       " ",
       t.reason) as Reason
   from table as t

Solution 1

And make sure, that there is no whitespace between concat and (. Something like that:

SELECT CONCAT ('a', 'b');

Solution 2

CONCAT is avaiable in 5.0.

Make sure you didn't make a typo:

SELECT  CONTACT(id, name)
FROM    items;

Error Code: 1305
FUNCTION test.CONTACT does not exist

Solution 3

I rewrote the query using concat_ws which simply ignores NULL values instead of returning NULL like concat does. Here is the result that is greatly simplified and actually works.

select concat_ws(" ", 
                 (select 
                    concat_ws(" ", b.bar, f.foo)
                   from  `foo_table`  as f
                   left join `bar_table` as  b
                     on b.SOME_ID = f.SOME_ID
                  where f.STUDENT_ID = t.STUDENT_ID
                    and f.NewID = t.OldID order by f.id limit 1 ),
               t.reason) as Reason,
   from table as t

Comments

[baudtack]

Thanks for the suggestion. Not the problem though.

[baudtack]

Yeah, sorry, that was just in my question. The same query runs on our development server under 5.1 but doesn't under 5.0 so I know that it's not a typo issue.

[rMX]

As i told you, there is space between concat and (. MySQL 5.0 had such problem. In 5.1 it has been solved.

[baudtack]

Typo, sorry. Will post a simplified query in a moment.

[baudtack]

Pretty sure this is what caused the original issue now. But I missed it before on one of them in all the complexity. I rewrote the whole thing and didn't make that mistake again.

[olix20]

there are times when MySql makes me say WHAT THE FOO!