Problems in a query performed within a Stored Funcion in Mysql

Question

I have a problem in a consultation on Mysql held within a Funcion.

The question is the following, among the various activities that the ai function performs, is to test whether certain data has already been inserted in a table.

The table is simple, it has the ID (primary key) id_fb (foreign key) and data insertion date (datetime).

When I perform the query in phpMyAdmin, it returns the correct values, but when I run the same query inside the function, it makes the value of the first row of the table.

The function is as follows (only with the relevant part)

DELIMITER $$

 CREATE FUNCTION teste1 (id_fb INT)
   RETURNS VARCHAR(512)

  BEGIN

    DECLARE ID_final varchar(20);

    SELECT `id` INTO ID_final FROM `brindes_teste` WHERE DATE(`data_hora`) = CURDATE() AND `id_fb` = id_fb LIMIT 1;

    RETURN CONCAT('JÁ PARTICIPOU HOJE = ', ID_final, ' ID_FB=' ,id_fb );        

 END $$

The table is as follows:

Table structure brindes_teste

CREATE TABLE `brindes_teste` (
  `id` int(11) NOT NULL,
  `id_fb` bigint(17) DEFAULT NULL,
  `data_hora` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Tabela brindes - DEV';

Doing data dump to table brindes_teste

INSERT INTO `brindes_teste` (`id`, `id_fb`, `data_hora`) VALUES
(2, 1235900, '2016-09-18 00:00:00'),
(3, 1441809, '2016-09-18 00:00:00'),
(4, 1453793, '2016-09-18 00:00:00'),
(7, 1249127, '2016-09-18 00:00:00'),
(9, 1737770, '2016-09-18 00:00:00');

When I perform the following consultation:

SELECT `id` FROM `brindes_teste` WHERE DATE(`data_hora`) = CURDATE() AND `id_fb` = 1249127 LIMIT 1;

The result is 7, what is expected

Now, when the same query is performed within the funcion:

SELECT teste1(1249127) 

The result is 2 (id of the first table record)

Someone can identify the source of the problem?

Answer 1

Change the name of your input variable. Always good to put a prefix in the name of the variables so as not to confuse with the column name. ex: p_id_fb. p = parameter. Ex:

DELIMITER $$

 CREATE FUNCTION teste1 (p_id_fb INT) -- Nome da váriavel diferente do nome da coluna da tabela
   RETURNS VARCHAR(512)

  BEGIN

DECLARE ID_final varchar(20);

SELECT `id` INTO ID_final FROM `brindes_teste` WHERE DATE(`data_hora`) = CURDATE() AND `id_fb` = p_id_fb LIMIT 1;

RETURN CONCAT('JÁ PARTICIPOU HOJE = ', ID_final, ' ID_FB=' ,p_id_fb );        

 END $$