MySQL GROUP_CONCAT with inner CONCAT cutting out values? inconsequent results

Question

I've been trying to set up a rather large query which has a select which should return a group_concat(inated) column which should contain exactly 4 concat(inated) results which are a result of a larger query.

I'll show the select to clear up any confusion (hopefully):

# Group the local category_feature_product.id, local feature value -
# and external feature value for the product together.
GROUP_CONCAT(
    CONCAT(cfpt.category_feature_product_id, '||', pcfpt.category_feature_product_id, '||', cfpt.value, '||', pcfpt.value)
    SEPARATOR ';'
) AS mysql_category_feature_products

I expect the query above to always return 4 results, may there be nulls in it or not, but it sometimes just only returns 3 or 2 results per group_concat. (i.e.: 273458||2658774||5 gets returned).

How can I assure that there is at least ALWAYS a null returned? I could show the large query if needed.

-- UPDATE --

Hereby the full query, a valid data example, actual result and the expected result as requested.

Full query:

I'm going to filter the results down to one product, since there are tens of thousands results. I will also only use the mysql_category_feature_products column.

Actual result (Notice the last row):

Expected result (notice last row) & valid data example:

-- UPDATED (AGAIN) --

The actual expected reuslt for the last row is not 250258||2583482||null||6 BUT 250258||2583482||47||67 not null, my bad.

Rerunning the query with the new ifnull statement returns a different oucome for the last row: 250258||2583482||4 instead of 250258||2583482||6

seems strange .. please .. updated you question add a valid data sample, your actual result and the expected result .. (and also the complete query code) — ScaisEdge, May 18 '18 at 07:19

score 1 · Answer 1 · answered May 18 '18 at 07:34

1

you could check for null (in this case i add a - char but you can use a '')

GROUP_CONCAT(
    CONCAT(ifnull(cfpt.category_feature_product_id, '-')
    , '||'
    , ifnull(pcfpt.category_feature_product_id, '-')
    , '||'
    , ifnull(cfpt.value,'-')
    , '||'
    , ifnull(pcfpt.value,'-')
    SEPARATOR ';'
) AS mysql_category_feature_products

answered May 18 '18 at 07:34

ScaisEdge

131,976
10
91
107

So I was a bit too quick in the actual data example as null shouldn't be possible anymore. (See the new update for the actual expected result). – Handige Harrie May 18 '18 at 07:44
Is there a length limit to the GROUP_CONCAT method? It seems like the result gets cut off.. – Handige Harrie May 18 '18 at 07:45
Yes .. there is a GROUP_CONCAT limit .. and you can change .. anyway .. the problem related to missing columns in solved or not? – ScaisEdge May 18 '18 at 09:58

score 0 · Answer 2 · answered May 18 '18 at 07:49

0

So the results got broken because of the default 1024 character limit set by MySQL on default, this can be circumvented by changing the length for your current session. see MySQL and GROUP_CONCAT() maximum length

answered May 18 '18 at 07:49

Handige Harrie

147
3
13

MySQL GROUP_CONCAT with inner CONCAT cutting out values? inconsequent results

2 Answers2