MySQL Split String Function SPLIT_STR does not work with LEFT JOIN. Any issue?

Question

Create MySQL Split String Function SPLIT_STR fedecarg.com/.../mysql-split-string-function/

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

Run SQL:

SELECT t.en AS `type`, SPLIT_STR(l.en, ',', 1) AS city,
SPLIT_STR(l.en, ',', 2) AS country
FROM table1
JOIN table2
USING ( id )
LEFT JOIN table3 AS t ON table1.type = t.id
/* the next line has failure with SPLIT_STR */
LEFT JOIN table3 AS l ON table1.location = l.id
WHERE language.lang =  'en'
AND language.url =  'europe-countries'
LIMIT 1;

table1

id               | type            | location
-----------------+-----------------+-----------------
6BC45C02         | place           | london,england

table2

id               | url
-----------------+-----------------
6BC45C02         | europe-countries

table3

id               | en
-----------------+-----------------
london           | London
england          | England

Failed result:

type             | city            | country
-----------------+-----------------+----------------
place            | NULL            | NULL

Expected result would be to return city and country:

type             | city            | country
-----------------+-----------------+-----------------
place            | London          | England

On checking if SPLIT_STR is working with simple SQL:

SELECT SPLIT_STR(location, ',', 1) AS city, SPLIT_STR(location, ',', 2) AS contry
FROM table1
WHERE id =  '6BC45C02'
LIMIT 1;

it returns fine result:

city             | contry
-----------------+-----------------
london           | england

Are you sure that you are getting the results?, what happens if you change your current `SELECT` to this `SELECT t.en AS [type], l.en`? — Lamak, Jan 02 '12 at 21:04
Also (but not the real problem), you should change `SPLIT_STR(l.en, ',', 1) AS country` to `SPLIT_STR(l.en, ',', 2) AS country` — Lamak, Jan 02 '12 at 21:05
The left join in some cases will result in null values that your function is not handling. But worse unless your example is messed up, Table1.type value 'Place' <> table3.id value 'london' so you're getting nulls. the problem continues with the table3 as l join as value 'london,england' <> 'london' or 'england' you should ahve a 2 as well on the select for country. — xQbert, Jan 02 '12 at 21:08
Also, the join that you are doing on table3 doesn't make any sense, `id` and `en` are the same. And you are applying your split on the `en` field, but that field has the values splitted already — Lamak, Jan 02 '12 at 21:13
@Lamak - `SELECT t.en AS [type], l.en` would not work. And thanks for mentioning `SPLIT_STR(l.en, ',', 2) AS country` in the question - I updated it. — Binyamin, Jan 02 '12 at 21:15
@Lamak - `table3` column `id` and `en` queries are different and might be different. I have posted just an simplified example. — Binyamin, Jan 02 '12 at 21:21

xQbert · Accepted Answer · 2012-01-02T21:44:04.793

Maybe this... but performance will be terrible.

SELECT T1.type,
  SPLIT_STR(t.en, ',', 1) AS city,
  SPLIT_STR(l.en, ',', 2) AS country
FROM table1 t1
INNER JOIN table2 t2 
  ON T1.ID = T2.ID
LEFT JOIN table3 t 
  ON t.id = SPLIT_STR(t1.location, ',', 1)
LEFT JOIN table3 l 
  ON l.id = SPLIT_STR(t1.location, ',', 2)
WHERE t2.url = 'europe-countries'
LIMIT 1;

This would be better as it doesn't require calling the function 4 times: (used coalesce to determine if function isn't working as expected will return proper case, then lower case, then function broke if it's not working as expected)

SELECT InTable.Type, 
   coalesce(t.en, inTable.City, 'FunctionBroke') as city, 
   coalesce(l.en, intable.country, 'FunctionBroke2') as country
FROM 
    (SELECT t1.type,
      SPLIT_STR(T1.Location, ',', 1) AS City,
      SPLIT_STR(T1.Location, ',', 2) AS Country
    FROM table1 T1
    INNER JOIN table2 T2 
      ON T1.ID = T2.ID 
        AND t2.url='europe-countries'
    ) InTable
LEFT JOIN table3 t 
  ON InTable.City = t.id
LEFT join table3 l 
  ON InTable.Country = l.id
LIMIT 1;

Still better exists: is table 3's only purpose to Proper case the city/country name; and is the only purpose of the UDF (user defined function) to split the values?

EDIT 1 Fixed first should have been t.en instead of l.en on first left join.
EDIT 2 Fixed 2nd should have been t1 (that's copy and paste for you)
EDIT 3 Relooked at everything in both responses copied several of the errors in the OP

For your second SQL I get error `#1054 - Unknown column 'l.en' in 'field list'`. — Binyamin, Jan 02 '12 at 21:28
Your first updated SQL returns query just for `type`=>place, for `city` and `country` returns NULL. — Binyamin, Jan 02 '12 at 21:32
Thanks, xQbert! Your first SQL (Query took 0.0011 sec) returns `type`:place, `city`:London and **empty query for `country`**. The second SQL (Query took 0.0009 sec) works fine and returns expected result `type`:place, `city`:London and `country`:England. — Binyamin, Jan 02 '12 at 22:27

a1ex07 · Answer 2 · 2012-01-02T22:25:34.863

1

From what I see, SPLIT_STR(l.en, ',', 1) will always be null (there is nothing to split in table3.en) Also, joining condition for table table1.location = l.id always false for your data (neither london nor england equals to london,england).

Based on desired output you posted, I think you are looking for something like (I'm not sure what is language.url = 'europe-countries' , I don't see table or alias named "language" in the question, so I just ignored it)

SELECT t1.`type` AS `type`, 
MAX(CASE WHEN t3.id = SPLIT_STR(t1.location, ',', 1) THEN t3.en END) as `city`,
MAX(CASE WHEN t3.id = SPLIT_STR(t1.location, ',', 2) THEN t3.en END) as `country`
FROM table1 t1
INNER JOIN table2 t2 ON (t2.id = t1.id)
LEFT JOIN table3 t3 ON 
 (t3.id = SPLIT_STR(t1.location, ',', 1) OR t3.id = SPLIT_STR(t1.location, ',', 2))
GROUP BY t1.`type`

Updated (l.en replaced by t1.location)

edited Jan 02 '12 at 22:25

answered Jan 02 '12 at 21:29

a1ex07

36,826
12
90
103

It returns an error `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM table1 t1 INNER JOIN table2 t2 ON (t2.id = t1.id) LEFT JOIN table3 t' at line 4`. – Binyamin Jan 02 '12 at 21:36
going to have similar problems I am l.en is not an aliased table name. – xQbert Jan 02 '12 at 21:37
sorry for misprint, extra comma after `country` ... Fixing – a1ex07 Jan 02 '12 at 21:42
I also forgot closing round bracket in `LEFT JOIN` condition... Fixed. – a1ex07 Jan 02 '12 at 21:44
Your latest updated SQL returns error `#1054 - Unknown column 'l.en' in 'field list'`. – Binyamin Jan 02 '12 at 22:16
I replace `GROUP BY t1.[type]` with `WHERE t2.url = 'europe-countries'` and it now returns the expected result. Your SQL query takes 0.0010 sec. It is slower than @xQbert second SQL. – Binyamin Jan 02 '12 at 22:43
I think `group by` should be there. Regardless of `WHERE` – a1ex07 Jan 02 '12 at 22:45

MySQL Split String Function SPLIT_STR does not work with LEFT JOIN. Any issue?

2 Answers2