Case trick; "Empty set" handling

    A small hack that allows you to process the "Empty set" in older versions of MySQL (4+), in which there are no functions.


    When writing a small request, I stalled on the following problem: there was no requested information in the database. It looked something like this:

    set @var = (
        select
            case
                when
                    t1.field is not null
                then
                    t1.field
                else
                    1
            end as field_name
        from
            table1 t1
            join table2 t2 on t1.tid = t2.id
        where
             t1.field2 = '_UNEXISTING_IN_DB_VALUE_'
        order by t1.tid desc
        limit 1
    );


    In this case block, as I just did not try to get one: functions, mathematical, logical operations, but it helped nothing: in response, when testing, there was only “Empty set (0.06 sec)”.

    I already decided to leave the crutch written almost immediately in the form:

    set @ var2 = (select if (@var is null, 1, @ var));


    My old friend helped me find a solution and advised the following approach: use column numbers instead of their names, as well as use the usual count. As a result, the correct query looks like this:

    set @var = (
        select
            case
                when
                    count (1)> 0
                then
                    t1.field
                else
                    1
            end as field_name
        from
            table1 t1
            join table2 t2 on t1.tid = t2.id
        where
            t1.field2 = '_UNEXISTING_IN_DB_VALUE_'
        order by t1.tid desc
        limit 1
    );


    see also:
    dev.mysql.com/doc/refman/4.1/en/all-subqueries.html

    Also popular now: