mysql - Query with IFNULL and CONCAT_WS returns empty string instead predefined IFNULL parameter in-CodePudding

I am working on the complex MySQL query with subquery and JOINs and this is my query:

SELECT
    id,
    ancient_source_name,
    ancient_source_name_alt,
    ancient_source_type,
    ancient_source_type_id,
    IFNULL(IF(ancient_source_material = 'Unknown', NULL, ancient_source_material), '-') AS ancient_source_material,
    ancient_source_year,
    dynasty,
    ancient_period,
    author,
    IFNULL(CONCAT_WS(', ',
        IF(provenance_country = 'Unknown' OR provenance_country = 'Not Applicable', NULL, provenance_country),
        IF(provenance_locality = 'Unknown' OR provenance_locality = 'Not Applicable', NULL, provenance_locality),
        IF(provenance_place = 'Unknown' OR provenance_place = 'Not Applicable', NULL, provenance_place)
    ), '-') AS provenance_loc,
    IFNULL(CONCAT_WS(', ',
        IF(current_country = 'Unknown' OR current_country = 'Not Applicable', NULL, current_country),
        IF(current_locality = 'Unknown' OR current_locality = 'Not Applicable', NULL, current_locality),
        IF(current_place = 'Unknown' OR current_place = 'Not Applicable', NULL, current_place)
    ), '-') AS current_loc
FROM (
    SELECT
        hgn_ancient_sources.id AS id,
        ancient_source_name,
        IFNULL(ancient_source_name_alt, '-') AS ancient_source_name_alt,
        IFNULL(ancient_source_year, '-') AS ancient_source_year,
        ancient_source_type,
        ancient_source_type_id,
        ancient_source_material,
        dynasty,
        ancient_period,
        provenance_countries.country AS provenance_country,
        provenance_localities.locality AS provenance_locality,
        provenance_places.place AS provenance_place,
        current_countries.country AS current_country,
        current_localities.locality AS current_locality,
        current_places.place AS current_place,
        IFNULL(author_name, '-') AS author
    FROM hgn_ancient_sources
    JOIN hgn_ancient_source_types ON hgn_ancient_sources.ancient_source_type_id = hgn_ancient_source_types.id
    JOIN hgn_ancient_source_materials ON hgn_ancient_sources.ancient_source_material_id = hgn_ancient_source_materials.id
    JOIN hgn_dynasties ON hgn_ancient_sources.ancient_source_dynasty_id = hgn_dynasties.id
    JOIN hgn_ancient_periods ON hgn_ancient_sources.ancient_source_period_id = hgn_ancient_periods.id
    LEFT JOIN junc_ancient_source_has_author ON hgn_ancient_sources.id = junc_ancient_source_has_author.ancient_source_id
    LEFT JOIN hgn_authors ON junc_ancient_source_has_author.author_id = hgn_authors.id
    JOIN junc_place_has_location AS provenance_place_has_location ON hgn_ancient_sources.ancient_source_provenance_place_id = provenance_place_has_location.id
    JOIN junc_place_has_location AS current_place_has_location ON hgn_ancient_sources.ancient_source_current_place_id = current_place_has_location.id
    JOIN junc_locality_has_country AS provenance_locality_has_country ON provenance_place_has_location.location_id = provenance_locality_has_country.id
    JOIN junc_locality_has_country AS current_locality_has_country ON current_place_has_location.location_id = current_locality_has_country.id
    JOIN hgn_places AS provenance_places ON provenance_place_has_location.place_id = provenance_places.id
    JOIN hgn_places AS current_places ON current_place_has_location.place_id = current_places.id
    JOIN hgn_localities AS provenance_localities ON provenance_locality_has_country.locality_id = provenance_localities.id
    JOIN hgn_localities AS current_localities ON current_locality_has_country.locality_id = current_localities.id
    JOIN hgn_countries AS provenance_countries ON provenance_locality_has_country.country_id = provenance_countries.id
    JOIN hgn_countries AS current_countries ON current_locality_has_country.country_id = current_countries.id
) AS subquery

Everything works fine except this part of code:

IFNULL(CONCAT_WS(', ',
    IF(provenance_country = 'Unknown' OR provenance_country = 'Not Applicable', NULL, provenance_country),
    IF(provenance_locality = 'Unknown' OR provenance_locality = 'Not Applicable', NULL, provenance_locality),
    IF(provenance_place = 'Unknown' OR provenance_place = 'Not Applicable', NULL, provenance_place)
), '-') AS provenance_loc,
IFNULL(CONCAT_WS(', ',
    IF(current_country = 'Unknown' OR current_country = 'Not Applicable', NULL, current_country),
    IF(current_locality = 'Unknown' OR current_locality = 'Not Applicable', NULL, current_locality),
    IF(current_place = 'Unknown' OR current_place = 'Not Applicable', NULL, current_place)
), '-') AS current_loc

I am getting good results when there are 1/3 or 2/3 columns with Unknown or Not Applicable value, but when 3/3 columns (e.g. current_country, current_locality, current_place) has one of these two values, I get empty cell as a result, and not -, as should be expected.

I tried different changes in the code, and nothing works. I should mention that, for example, this part of the query:

IFNULL(IF(ancient_source_material = 'Unknown', NULL, ancient_source_material), '-') AS ancient_source_material

work perfectly, so my assumption is that there is some problem with CONCAT_WS() function, but I cannot find what.

CodePudding user response：

CONCAT_WS() returns an empty string if all arguments after a non-null separator are NULL.
See the demo.

I did not find this stated explicitly in MySql's documentation (like it is made clear in SQL Server's documentation).

What you can do is use also NULLIF() to check if the result is an empty string and change it to NULL before you use IFNULL():

  IFNULL(NULLIF(CONCAT_WS(', ',
      IF(provenance_country = 'Unknown' OR provenance_country = 'Not Applicable', NULL, provenance_country),
      IF(provenance_locality = 'Unknown' OR provenance_locality = 'Not Applicable', NULL, provenance_locality),
      IF(provenance_place = 'Unknown' OR provenance_place = 'Not Applicable', NULL, provenance_place)
  ), ''), '-') AS provenance_loc

See the demo.