Why is SQLite conditional between generated variables not comparing correctly?

Question

I'm a little confused why I'm getting results in the following example. Let's pretend that we want to rank how much someone likes animals. They like any colored tiger best, red animals second, and doesn't like any others.

Consider the following table:

----------------------
| Color  | Animal    |
----------------------
| Yellow | Butterfly |
| Red    | Lion      |
| Red    | Tiger     |
| Green  | Lion      | 
| Green  | Donkey    |
| Yellow | Tiger     |
----------------------

Now when I run this code:

CREATE VIEW animal_colors as select "farm"."color" as "color",
CASE WHEN "farm"."color" = 'Red' THEN 1 ELSE 0 END AS "color_red",
CASE WHEN "farm"."animal" = 'Tiger' THEN 1 ELSE 0 END AS "animal_tiger",
CASE
    WHEN "animal_tiger" = 1 THEN 1
    WHEN "color_red" = 1 THEN 0
    ELSE -1
END 
AS "rank"

FROM "farm"

I get the following output:

----------------------------------------------------------
| Color  | Animal    | color_red | animal_tiger | rank  |
---------------------------------------------------------
| Yellow | Butterfly | 0         | 0            | -1    |
| Red    | Lion      | 1         | 0            | -1    |
| Red    | Tiger     | 1         | 1            | -1    |
| Green  | Lion      | 0         | 0            | -1    |
| Green  | Donkey    | 0         | 0            | -1    |
| Yellow | Tiger     | 0         | 1            | -1    |
---------------------------------------------------------

I thought I was comparing the variables color_red and animal_tiger to see if they are 1, but it doesn't look like that is happening. Could anyone shed some light on why the expression is always evaluating as -1?

MikeT · Accepted Answer

You are comparing "animal_tiger" (a string) to 'Tiger' which will not be true.

To access the derived columns animal_tiger and color_red you need to make the select a subquery, or use the actual columns.

So you could use either (not using derived columns) :-

SELECT farm.color AS color,
    CASE
        WHEN farm.color = 'Red' THEN 1 ELSE 0 END AS color_red,
    CASE 
        WHEN farm.animal = 'Tiger' THEN 1 ELSE 0 END AS animal_tiger,
    CASE 
        WHEN farm.animal = 'Tiger' THEN 1
        WHEN farm.color = 'Red' THEN 0
        ELSE -1
        END AS rank
FROM farm;

resulting in

or (using derived columns via a subquery) :-

SELECT *, 
    CASE 
        WHEN animal_tiger = 1 THEN 1
        WHEN color_red = 1 THEN 0
        ELSE -1
        END AS rank
 FROM (
    SELECT "farm"."color" as "color",
    CASE WHEN "farm"."color" = 'Red' THEN 1 ELSE 0 END AS "color_red",
    CASE WHEN "farm"."animal" = 'Tiger' THEN 1 ELSE 0 END AS "animal_tiger"
    FROM "farm"
);

Resulting in :-

Note done as SELECTS rather than creating VIEWS for my convenience

Additional

Perhaps you could simplify using the following as the basis :-

SELECT farm.color AS color, 
    -1 + (farm.color = 'Red') + (farm.animal = 'Tiger') AS rank 
FROM farm

This yields :-

Or perhaps even

SELECT farm.color AS color, 
    0 + (farm.color = 'Red') + ((farm.animal = 'Tiger') * 2) AS rank 
FROM farm

(giving a ranking base of zero and giving tiger a higher rank then red thus you could easily discriminate say between a red and yellow tiger, the former having a higher rank)

This would yield :-

Why is SQLite conditional between generated variables not comparing correctly?

Answers (2)

Additional

Related Questions