Force mysql to return no result on bit_or

Question

I have a mysql query that uses bit_or on a result set. When there is no result set, the bit_or function returns 0 (this is correct as per the mysql documentation).

For example:

select n from t1 where a=1;

Returns no rows.

select bit_or(n) from t1 where a=1;

Returns 0

I need to force the last query to instead return no result from the bit_or query, e.g. (in pseudo-code, because I do not know the correct invocation):

select bit_or(n) unless 0 from t1 where a=1;

Does anybody know how I can do this? (Clarification, I need the "no result on 0" return value for an external program, which unfortunately cannot be changed).

Answer 1

You can use a case statement to null out the row, like so:

select
    case 
        when bit_or(n) = 0 then null
        else bit_or(n)
    end
from
    t1
where
    a=1

Or, if you want to completely ignore the row, you can do it in the having clause:

select
    bit_or(n)
from
    t1
where
    a=1
having
    bit_or(n) <> 0

The having clause is used to essentially do a where on aggregate columns (such as a sum or a max). In this case, your aggregate column is a bit_or. If you have additional columns in your result set, you will need to include them in the group by statement after the where clause, but before the having clause.

The first example will return null for that row, while the second example won't return the row if bit_or(n) is 0.

Answer 2

SELECT *
FROM (
    SELECT BIT_OR(`n`) AS `bit`
    FROM `t1`
    WHERE `a` = 1
) AS `bit`
WHERE `bit`