I have the following problem in a stored procedure:
{"code":"ER_WRONG_FIELD_WITH_GROUP","errno":1055,"sqlState":"42000","index":0}
Initially I suspected that it was the passing of parameters that were wrong but when debugging my application I noticed that the parameters are correct, with this I do not know what may be happening, below is my Procedure code. My Stored Procedure:
DROP PROCEDURE IF EXISTS clusbe.proc_buscarMidias;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_buscarMidias`(IN `id_usuario` INT, IN `latitude` DOUBLE, IN `longitude` DOUBLE, IN `interesses` VARCHAR(255), IN `val_offset` INT)
BEGIN
SET @id = id_usuario;
SET @offset = val_offset;
SET @lat = latitude;
SET @long = longitude;
SET @int = interesses;
SET @sql = ("SELECT * , SUM(peso) AS peso_total FROM (
(SELECT p.id,
p.midia,
CASE
WHEN func_distancia(?,?, e.latitude, e.longitude) < 100 THEN 15
WHEN func_distancia(?,?, e.latitude, e.longitude) < 200 THEN 10
WHEN func_distancia(?,?, e.latitude, e.longitude) < 500 THEN 5
ELSE 0
END AS peso,
(SELECT group_concat(' ', i.interesse) FROM interesse AS i
LEFT JOIN post_interesse AS pi ON i.id = pi.interesse
WHERE pi.post = p.id) AS interesses
FROM post AS p
LEFT JOIN post_interesse AS pi ON pi.post = p.id
LEFT JOIN interesse AS i ON pi.interesse = i.id
INNER JOIN endereco AS e ON p.localizacao = e.id
INNER JOIN usuario AS u ON p.criador = u.id
WHERE (FIND_IN_SET (i.interesse, ?) OR ? = '')
AND (p.tipo = '0' OR p.tipo= '1')
AND p.criador <> ?
AND p.criador NOT IN (SELECT usuario FROM usuario_bloqueado WHERE usuario_bloqueado = ?)
AND (p.id NOT IN (SELECT post_ocultado FROM post_ocultado WHERE usuario = ? ) AND p.criador NOT IN (SELECT usuario_ocultado FROM post_ocultado WHERE usuario = ? ))
AND p.area_post = 'feed')
UNION
(SELECT p.id,
p.midia,
COUNT(p.id) AS peso,
(SELECT group_concat(i.interesse) FROM interesse AS i
LEFT JOIN post_interesse AS pi ON i.id = pi.interesse
WHERE pi.post = p.id) AS interesses
FROM post AS p
LEFT JOIN post_interesse AS pi ON pi.post = p.id
LEFT JOIN interesse AS i ON pi.interesse = i.id
INNER JOIN endereco AS e ON p.localizacao = e.id
INNER JOIN usuario AS u ON p.criador = u.id
WHERE (FIND_IN_SET (i.interesse, ?) OR ? = '')
AND (p.tipo = '0' OR p.tipo = '1')
AND p.criador <> ?
AND p.criador NOT IN (SELECT usuario FROM usuario_bloqueado WHERE usuario_bloqueado = ?)
AND (p.id NOT IN (SELECT post_ocultado FROM post_ocultado WHERE usuario = ? ) AND p.criador NOT IN (SELECT usuario_ocultado FROM post_ocultado WHERE usuario = ? ))
AND p.area_post = 'feed'
GROUP BY p.id)
) AS tbl
where tbl.peso > 0
GROUP BY tbl.id
ORDER BY peso_total DESC
LIMIT 10
OFFSET ?");
PREPARE stmt_midias FROM @sql;
EXECUTE stmt_midias USING @lat, @long, @lat, @long, @lat, @long, @int, @int, @id, @id, @id, @id,
@int, @int, @id, @id, @id, @id, @offset;
DEALLOCATE PREPARE stmt_midias;
END;
I wonder if anyone knows how I can solve this or what may be happening?
