using a user variable in where clause without subquery

Question

I have the following scenario:

+-----------+
| my_column |
+-----------+
| A         |
| B         |
| C         |
| D         |
| E         |
+-----------+

I have simplified my_function bellow for this example;

DROP FUNCTION IF EXISTS my_function;
CREATE FUNCTION my_function(
    phrase VARCHAR(255), 
    column_value VARCHAR(255)
)
RETURNS FLOAT(20,10)
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
    IF(column_value = 'A') THEN RETURN 1.0000000000;
    ELSEIF(column_value = 'B') THEN RETURN 0.7500000000;
    ELSEIF(column_value = 'C') THEN RETURN 0.7500000000;
    ELSEIF(column_value = 'D') THEN RETURN 0.5000000000;
    ELSEIF(column_value = 'E') THEN RETURN 0.0000000000;
    END IF;
END;

Here is my main stored procedure:

DROP PROCEDURE IF EXISTS my_procedure;
CREATE PROCEDURE my_procedure(  
    IN phrase VARCHAR(255)
)
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
    SET @phrase = phrase;
    SET @query = "
        SELECT  
            my_column, 
            @score_var := my_function(?,my_column) as score, 
            @score_var
        FROM my_table
        ORDER BY score DESC;
    ";
    PREPARE stmt FROM @query;
    EXECUTE stmt USING @phrase; 
    DEALLOCATE PREPARE stmt;
END;

Now if I call my_procedure

call my_procedure('anything');

The result is:

+-----------+--------------+------------+
| my_column | score        | @score_var |
+-----------+--------------+------------+
| A         | 1.0000000000 |          1 |
| B         | 0.7500000000 |       0.75 |
| C         | 0.7500000000 |       0.75 |
| D         | 0.5000000000 |        0.5 |
| E         | 0.0000000000 |          0 |
+-----------+--------------+------------+

But if I add WHERE @score_var > 0.5 inside of the query in my_procedure, the result is:

+-----------+--------------+------------+
| my_column | score        | @score_var |
+-----------+--------------+------------+
| A         | 1.0000000000 |          1 |
| C         | 0.7500000000 |       0.75 |
| E         | 0.0000000000 |          0 |
+-----------+--------------+------------+

Expected result ´> 0.5´:

+-----------+--------------+------------+
| my_column | score        | @score_var |
+-----------+--------------+------------+
| A         | 1.0000000000 |          1 |
| B         | 0.7500000000 |       0.75 |
| C         | 0.7500000000 |       0.75 |
+-----------+--------------+------------+

I have seen some answers that use a subquery, but my question is: can (in this case) I not use a subquery?

Alternative approaches are also welcome.

Paul Spiegel · Accepted Answer · 2019-02-16 20:23:17Z

When you read and write a user variable in the same statement, the behavior is documented as "undocumented". In other words the result is unpredictable unlesss you read and understand the source code of your MySQL version.

However - I think you are complicating things here unnecessarily. I don't see a reason to use a prepared statement, neither to use u user variable. Your procedure body could be just:

SELECT  
    my_column, 
    my_function(phrase, my_column) as score, 
FROM my_table
HAVING score > 0.5
ORDER BY score DESC

Also your function could be written with less code duplicatin:

RETURN
    CASE column_value
        WHEN 'A' THEN 1.0000000000
        WHEN 'B' THEN 0.7500000000
        WHEN 'C' THEN 0.7500000000
        WHEN 'D' THEN 0.5000000000
        WHEN 'E' THEN 0.0000000000
    END

YES SR! ... HAVING respond as my espected. Thank you (also for the less code example)

Collectives™ on Stack Overflow

using a user variable in where clause without subquery

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related