Parameters van MySQL procedures; let op!

Inleiding

Tijdens de bouw van mijn tot nu toe laatste webapplicatie liep ik tegen een eigenaardig probleem aan. De sp_vw_-stored procedures van mijn MySQL-database leken de gevraagde records niet meer te filteren als ik daar wel om vroeg. Het was alsof de volledige WHERE-clausule genegeerd werd. Na een aantal dagen zoeken, broeden en nog steeds geen oplossing te hebben gevonden, besloot ik een vraag te gaan stellen op StackOverflow.

Beslagen ten ijs

Daartoe wilde ik beslagen ten ijs komen, om de lezer een minimalistisch maar functionerend voorbeeld te geven van het wangedrag van mijn database. Ik kopieerde de SQL-code van mijn project en stripte bijna alles, tot het nu volgende script overbleef:

DROP TABLE IF EXISTS `Book`;
CREATE TABLE `Book` (
  `book_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `book_name` varchar(50) NOT NULL,
  PRIMARY KEY (`book_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT `Book` (`book_name`) VALUES
    ('Book One'),
    ('Book Two'),
    ('Book Three');

DROP PROCEDURE IF EXISTS `sp_vw_book`;
CREATE PROCEDURE `sp_vw_book`(
    IN `BOOK_ID` BIGINT(20)
)
SELECT * FROM `Book` WHERE
(BOOK_ID IS NULL OR `book_id` = BOOK_ID);

Als ik deze procedure aanriep, maakte het geen verschil of ik nou op book_id wilde filteren of niet:

mysql> CALL sp_vw_book(null);
+---------+------------+
| book_id | book_name  |
+---------+------------+
|       1 | Book One   |
|       2 | Book Two   |
|       3 | Book Three |
+---------+------------+
3 rows in set (0.00 sec)

mysql> CALL sp_vw_book(2);
+---------+------------+
| book_id | book_name  |
+---------+------------+
|       1 | Book One   |
|       2 | Book Two   |
|       3 | Book Three |
+---------+------------+
3 rows in set (0.00 sec)

De oorzaak

Wat blijkt? Als een parameter van een stored procedure dezelfde naam heeft als een kolom van de tabel waarop gefilterd wordt (ongeacht hoofd- of kleine letters), dan vindt er geen enkele filtering meer plaats. De filterclausule wordt dan gelijk aan WHERE (... OR BOOK_ID = BOOK_ID) oftewel WHERE 1 = 1.

De oplossing

De uiteindelijke oplossing is zowel eenvoudig als elegant: hernoem de parameter zodat deze verschilt van de te filteren kolomnaam. Je moet er maar op komen!

DROP PROCEDURE IF EXISTS `sp_vw_book`;
CREATE PROCEDURE `sp_vw_book`(
    IN `BOOKID` BIGINT(20)
)
SELECT * FROM `Book` WHERE
(BOOKID IS NULL OR `book_id` = BOOKID);

mysql> CALL sp_vw_book(null);
+---------+------------+
| book_id | book_name  |
+---------+------------+
|       1 | Book One   |
|       2 | Book Two   |
|       3 | Book Three |
+---------+------------+
3 rows in set (0.00 sec)

mysql> CALL sp_vw_book(2);
+---------+-----------+
| book_id | book_name |
+---------+-----------+
|       2 | Book Two  |
+---------+-----------+
1 row in set (0.01 sec)