Query, giving back string, even if SELECT statement is empty, is faulty

Question

simple question, but no idea, how to fix it. Following Query should give back "No values", if SELECT Part will return nothing. I will get error message:

[21000][1241] (conn=80188403) Operand should contain 1 column(s)

What's wrong with this query?

SET @produktNummer = 01000051311;
SET @languageID = '2fbb5fe2e29a4d70aa5854ce7ce3e20b';
SELECT IFNULL(
      (SELECT product.id, product_number, property_group_translation.name AS AttributBez
       FROM product
       LEFT JOIN product_property 
         ON product.id = product_property.product_id
       LEFT JOIN property_group_option 
         ON product_property.property_group_option_id = property_group_option.id
       LEFT JOIN property_group_translation 
         ON property_group_option.property_group_id = property_group_translation.property_group_id
       WHERE product_number = @produktNummer
          AND property_group_translation.language_id = UNHEX(@languageID)
          AND (
              property_group_translation.name = '' OR
              property_group_translation.name IS NULL
              )
       )
  ,'No values');

If I try without ISNULL(), I won't get any results. If I change Query like this, I will get multiple records back

SET @produktNummer = 01000051311;
SET @languageID = '2fbb5fe2e29a4d70aa5854ce7ce3e20b';
SELECT product.id, product_number, property_group_translation.name AS AttributBez
FROM product
LEFT JOIN product_property
ON product.id = product_property.product_id
LEFT JOIN property_group_option
ON product_property.property_group_option_id = property_group_option.id
LEFT JOIN property_group_translation
ON property_group_option.property_group_id = property_group_translation.property_group_id
WHERE product_number = @produktNummer
AND property_group_translation.language_id = UNHEX(@languageID)

Answer 1

You can get at least 1 row with data or 'NoValue'.
Include other side parameters as subquery and join other data to them.

(
  select @produktNummer as produktNummer, UNHEX(@languageID) as languageID
  ) request

See example

SET @produktNummer = 01000051311;
SET @languageID = '2fbb5fe2e29a4d70aa5854ce7ce3e20b';

SELECT produktNummer,languageID
  ,coalesce(product.id,'NoValue') id
  ,coalesce(product_number,'NoValue')product_number
  ,coalesce(property_group_translation.name,'NoValue') AS AttributBez
FROM (
  select @produktNummer as produktNummer, UNHEX(@languageID) as languageID
  ) request
left join product p on p.product_number = request.produktNummer 
LEFT JOIN product_property
   ON product.id = product_property.product_id
LEFT JOIN property_group_option
   ON product_property.property_group_option_id = property_group_option.id
LEFT JOIN property_group_translation
   ON property_group_option.property_group_id = property_group_translation.property_group_id
WHERE property_group_translation.language_id = request.languageID

If no data found, output is

produktNummer	languageID	id	product_number	AttributBez
01000051311	2fbb5fe2e29a4d70aa5854ce7ce3e20b	NoValue	NoValue	NoValue

Or

id	product_number	AttributBez
NoValue	NoValue	NoValue

If you want only check, whether is data present

SET @produktNummer = 01000051311;
SET @languageID = '2fbb5fe2e29a4d70aa5854ce7ce3e20b';
SELECT 
   case when 
      (SELECT count(*)
       FROM product
       LEFT JOIN product_property 
         ON product.id = product_property.product_id
       LEFT JOIN property_group_option 
         ON product_property.property_group_option_id = property_group_option.id
       LEFT JOIN property_group_translation 
         ON property_group_option.property_group_id = property_group_translation.property_group_id
       WHERE product_number = @produktNummer
          AND property_group_translation.language_id = UNHEX(@languageID)
          AND (
              property_group_translation.name = '' OR
              property_group_translation.name IS NULL
              )
       )>0 then  'Values available'
    else 'No values'
    end result ;

Upd1.
If query result is dumped to csv-format file, all columns will be converted to strings.

SELECT ...
INTO OUTFILE '/out-files/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';