Bug #30847 Critical error with a stored function on WHERE
Description:
First check my stored function:
FUNCTION `sigmuestra`(muestra VARCHAR(15)) RETURNS varchar(15) CHARSET latin1
BEGIN
DECLARE I INT;
DECLARE car INT;
DECLARE resp VARCHAR(15);
DECLARE bandera BOOL;
SET I = LENGTH(muestra);
SET resp = muestra;
Buscar: LOOP
SET car = ASCII(MID(resp,I,1));
IF car >47 and car < 57 THEN
set car=car+1;
SET bandera = true;
elseif car = 57 THEN
set car=48;
SET bandera = false;
END IF;
SET resp = CONCAT(LEFT(resp,I-1), CHAR(car), right(resp,LENGTH(resp)-I));
SET I = I - 1;
IF I = 0 OR bandera THEN LEAVE Buscar; END IF;
ITERATE Buscar;
END LOOP Buscar;
RETURN resp;
END
Now check this other SELECT statements and returns... they are logically impossible. But it work properly in other computers:
table muestra:
Nombre
A01b
A02b
A04b
A06b
A07b
A09b
I will use "count(*)" in SELECT statement... but the problem is the same with "*"
1- select count(*) from muestra as m, muestra as n
return: 36
2-select count(*) from muestra as m, muestra as n WHERE sigmuestra(m.nombre) =n.nombre;
return: 0
3-select count(*) from muestra as m, muestra as n WHERE sigmuestra(m.nombre) <> n.nombre;
return: 34
My function is working well... look:
SELECT sigmuestra(m.nombre), n.nombre FROM muestra as m, muestra as n;
Return
+----------------------+--------+
| sigmuestra(m.nombre) | nombre |
+----------------------+--------+
| A02b | A01b |
| A03b | A01b |
| A05b | A01b |
| A07b | A01b |
| A08b | A01b |
| A10b | A01b |
| A02b | A02b |
| A03b | A02b |
| A05b | A02b |
| A07b | A02b |
| A08b | A02b |
| A10b | A02b |
| A02b | A04b |
| A03b | A04b |
| A05b | A04b |
| A07b | A04b |
| A08b | A04b |
| A10b | A04b |
| A02b | A06b |
| A03b | A06b |
| A05b | A06b |
| A07b | A06b |
| A08b | A06b |
| A10b | A06b |
| A02b | A07b |
| A03b | A07b |
| A05b | A07b |
| A07b | A07b |
| A08b | A07b |
| A10b | A07b |
| A02b | A09b |
| A03b | A09b |
| A05b | A09b |
| A07b | A09b |
| A08b | A09b |
| A10b | A09b |
+----------------------+--------+
How to repeat:
It doesn't occurs in other computer :P
Suggested fix:
Kill me please!!!
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.044 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: First check my stored function: FUNCTION `sigmuestra`(muestra VARCHAR(15)) RETURNS varchar(15) CHARSET latin1 BEGIN DECLARE I INT; DECLARE car INT; DECLARE resp VARCHAR(15); DECLARE bandera BOOL; SET I = LENGTH(muestra); SET resp = muestra; Buscar: LOOP SET car = ASCII(MID(resp,I,1)); IF car >47 and car < 57 THEN set car=car+1; SET bandera = true; elseif car = 57 THEN set car=48; SET bandera = false; END IF; SET resp = CONCAT(LEFT(resp,I-1), CHAR(car), right(resp,LENGTH(resp)-I)); SET I = I - 1; IF I = 0 OR bandera THEN LEAVE Buscar; END IF; ITERATE Buscar; END LOOP Buscar; RETURN resp; END Now check this other SELECT statements and returns... they are logically impossible. But it work properly in other computers: table muestra: Nombre A01b A02b A04b A06b A07b A09b I will use "count(*)" in SELECT statement... but the problem is the same with "*" 1- select count(*) from muestra as m, muestra as n return: 36 2-select count(*) from muestra as m, muestra as n WHERE sigmuestra(m.nombre) =n.nombre; return: 0 3-select count(*) from muestra as m, muestra as n WHERE sigmuestra(m.nombre) <> n.nombre; return: 34 My function is working well... look: SELECT sigmuestra(m.nombre), n.nombre FROM muestra as m, muestra as n; Return +----------------------+--------+ | sigmuestra(m.nombre) | nombre | +----------------------+--------+ | A02b | A01b | | A03b | A01b | | A05b | A01b | | A07b | A01b | | A08b | A01b | | A10b | A01b | | A02b | A02b | | A03b | A02b | | A05b | A02b | | A07b | A02b | | A08b | A02b | | A10b | A02b | | A02b | A04b | | A03b | A04b | | A05b | A04b | | A07b | A04b | | A08b | A04b | | A10b | A04b | | A02b | A06b | | A03b | A06b | | A05b | A06b | | A07b | A06b | | A08b | A06b | | A10b | A06b | | A02b | A07b | | A03b | A07b | | A05b | A07b | | A07b | A07b | | A08b | A07b | | A10b | A07b | | A02b | A09b | | A03b | A09b | | A05b | A09b | | A07b | A09b | | A08b | A09b | | A10b | A09b | +----------------------+--------+ How to repeat: It doesn't occurs in other computer :P Suggested fix: Kill me please!!!