How can I determine which condition on WHERE clause fails?

Question

I have a table like this:

// cookies
+----+---------+-------------------+------------+
| id | user_id |       token       |   expire   |
+----+---------+-------------------+------------+
| 1  | 32423   | dki3j4rf9u3e40... | 1467586386 |
| 2  | 65734   | erhj5473fv34gv... | 1467586521 |
| 3  | 21432   | 8u34ijf34t43gf... | 1467586640 |
+----+---------+-------------------+------------+

And I have this query:

SELECT 1
FROM cookies
WHERE user_id = :id AND
      token   = :token

Note: Always the result is either one row or zero row. (token column is unique)

When there is a matched row, ok, all fine, but when there isn't any matched row, I want to understand why?

• user_id exists but token doesn't
• token exists but user_id doesn't
• non of those columns doesn't exist

How can I determine the reason of "no row selected (matched)" ?

---

EDIT: Here is all possible outputs:

1. row exists
2. row doesn't exist because user_id = :id is false
3. row doesn't exist because token = :token is false
4. row doesn't exist because both user_id = :id and token = :token are false
5. row doesn't exist because user_id = :id and token = :token are true but not in the same row.

Answer 1

PREFACE: This problem, notwithstanding my dislike of MySQL, was a rather tough puzzle to solve. No use of an FULL OUTER JOIN made it even harder. Even the "solution" I initially gave was insufficient for the task.

OUTER JOINS behave in a specific pattern, and it was important to think about each inner query as independent from the outer opposite side as the explicit direction.

SELECT A.Col_A, B.Col_B
TableA A
LEFT OUTER JOIN TableB B ON B.ID = A.ID

This query only produces results if TableA has something to match TableB with. Because every nested query is logically the same as the above, even adding a dummy table only left me with just the result from the dummy table and me feeling like a...dummy.

While some smart users have tried UNION/UNION ALL to solve this, that answer is unreliable and actually failed when I attempted to use the same table twice.

• The Solution: FOR REAL!!

The trick is to guarantee the results will always return.

SELECT C.user_id
     , B.token
FROM (SELECT NULL AS C) A
LEFT OUTER JOIN (SELECT token
                 FROM  Example
                 WHERE token = @token) AS B ON 1 = 1 
LEFT OUTER JOIN (SELECT user_id
                 FROM Example
                 WHERE user_id = @user_id) AS C ON 1 = 1

• We use a DUMMY table to ensure we always get a row...even if it is empty.
• Notice On 1 = 1. That guarantees that the results from both sides will return and since we made sure those tables only retrieved the exact information we wanted, bingo! we get out beautiful solution that will work regardless of whether one or both sides are NULL.

A RECHECK ON THE ORIGINAL PROCEDURE

TABLE DECLARATIONS:

CREATE TABLE sys.EXAMPLE (ID INT auto_increment PRIMARY KEY NOT NULL
                    , user_id INT NULL
                    , token NVARCHAR(100) NULL
                    , `expire` INT NULL ); 
INSERT INTO sys.EXAMPLE (user_Id, token, `expire`)
VALUES  (32423, N'dki3j4rf9u3e40...', 1467586386)
      , (65734, N'erhj5473fv34gv...', 1467586521)
      , (21432, N'8u34ijf34t43gf...', 1467586640);

-- My MySQL Workbench bugs out at the beginning, for some reason the variables are persisting beyond the transaction.

PROCEDURE SOLUTION

-- DROP PROCEDURE sys.MyExample
DELIMITER $$
CREATE PROCEDURE sys.MyExample(IN user_ID INT, IN token NVARCHAR(255) )   
BEGIN

SELECT B.token, C.user_id INTO @token_chk, @user_chk
FROM (SELECT NULL AS C) A
LEFT OUTER JOIN (SELECT token
                FROM  sys.Example
                WHERE token = @token
                 LIMIT 1) AS B ON 1 = 1 
LEFT OUTER JOIN (SELECT user_id
                 FROM sys.Example
                 WHERE user_id = @user_id
                 LIMIT 1) AS C ON 1 = 1;

IF @user_chk IS NOT NULL AND @token_chk IS NOT NULL
-- RESULT_1: FOUND BOTH COLUMNS
THEN SELECT 'FOUND IT';
    ELSE IF @user_chk IS NOT NULL AND @token_chk IS NULL
-- RESULT_2: TOKEN IS MISSING
         THEN SELECT 'TOKEN IS MISSING';
         ELSE IF @user_chk IS NULL AND @token_chk IS NOT NULL
-- RESULT_3: USER_ID IS MISSING
                    THEN SELECT 'user_ID IS MISSING';
                    ELSE IF @user_chk IS NULL AND @token_chk IS NULL
-- RESULT_4: BOTH USER_ID AND TOKEN ARE MISSING
                          THEN SELECT 'BOTH user_id AND token are missing';
                          ELSE -- return message saying that an unknown error has occurred
                          SELECT 'AN UNKNOWN ERROR HAS OCCURRED';
                          END IF;
                    END IF;
            END IF;
    end if;
END
$$
DELIMITER ; 

Note that this works on T-SQL and works in MySQL before I run the PROCEDURE. For some reason, I am having a bug where the second variable in my proc loses its value during the query...even when I explicitly call the same value before the query inside the Procedure.

-- Should Return complete set
    CALL sys.MyExample(21432, 'erhj5473fv34gv...');
-- Should Return 'token is missing'
    CALL sys.MyExample(21432, 'erhj5473fv34gv..X'); 
-- Should Return 'user_id is missing'
    CALL sys.MyExample(2143, 'erhj5473fv34gv...'); 
-- Should Return 'Both user_id AND token are missing'
    CALL sys.MyExample(2143, 'erhj5473fv34gv..X');  

Answer 2

This always returns one row. 1 for matched if there is a match otherwise 0. It also shows if the userID or the token exists in the cookies table. Using this method you can determine why the where clause failed.

SELECT (  select count(*) > 0 matched from cookies where user_id = :id  and token   = :token ) matched
, ( select count(*) > 0 userIdExists from cookies where user_id = :id ) userIdExists
, ( select count(*) > 0 tokenExists from cookies where token = :token ) tokenExists