Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.6.19, 10.11.11
-
None
-
Unexpected results
Description
Hello Team,
Good day All,
Problem:
1) When I write a query in a function with same alias name but with different cases on same table, it returns schema.alias doesn't exist.
2) I observed, it works when I ran as a separate query outside the function or in a SP.
3) It is not happening in 10.6.16, but observed this behaviour in 10.6.21, 10.6.22, 10.11.11 .
*Steps to reproduce: *
create table t1 ( id int primary key auto_increment, name varchar(10)); |
insert into t1 (name) values ('wrbyviwb'); |
insert into t1 (name) values ('wrbyrwb1'); |
insert into t1 (name) values ('wrbrwb3'); |
Failures:
case 1)
DELIMITER //
|
DROP FUNCTION IF EXISTS t1test// |
CREATE FUNCTION t1test(val INT) RETURNS varchar(400) CHARSET utf8 |
BEGIN
|
DECLARE output VARCHAR(400) DEFAULT ''; |
SET output = (select cnt.name from t1 cnt join ( select CNT.id from t1 CNT where CNT.id=val) t2 on t2.id=cnt.id); |
RETURN output; |
END// |
DELIMITER ;
|
select t1test(1); --> fails
case 2)
DELIMITER //
|
DROP FUNCTION IF EXISTS t1test// |
CREATE FUNCTION t1test(val INT) RETURNS varchar(400) CHARSET utf8 |
BEGIN
|
DECLARE output VARCHAR(400) DEFAULT ''; |
DECLARE output1 VARCHAR(400) DEFAULT ''; |
SET output1 = (select Cnt.name from t1 Cnt where Cnt.id=val); |
SET output = (select cnt.name from t1 cnt where cnt.id=val); |
RETURN output; |
END// |
DELIMITER ;
|
select t1test(1); --> fails
case 3)
DELIMITER //
|
DROP FUNCTION IF EXISTS t1test// |
CREATE FUNCTION t1test(val INT) RETURNS varchar(400) CHARSET utf8 |
BEGIN
|
DECLARE output VARCHAR(400) DEFAULT ''; |
DECLARE output1 VARCHAR(400) DEFAULT ''; |
DECLARE output2 VARCHAR(400) DEFAULT ''; |
|
|
SET output1 = (select Cnt.name from t1 Cnt where Cnt.id=val); |
SET output = (select cnt.name from t1 cnt where cnt.id=val); |
SET output2 = (select cnt.name from t1 cnt join ( select cnt.id from t1 cnt where cnt.id=val) sub1 on sub1.id=cnt.id); |
RETURN output; |
END// |
DELIMITER ;
|
select t1test(1); – fails
success:
case 1)
DELIMITER //
|
DROP FUNCTION IF EXISTS t1test// |
CREATE FUNCTION t1test(val INT) RETURNS varchar(400) CHARSET utf8 |
BEGIN
|
DECLARE output VARCHAR(400) DEFAULT ''; |
DECLARE output1 VARCHAR(400) DEFAULT ''; |
SET output1 = (select cnt.name from t1 cnt where cnt.id=val); |
SET output = (select cnt.name from t1 cnt where cnt.id=val); |
RETURN output; |
END// |
DELIMITER ;
|
select t1test(1); --> pass
case 2)
select cnt.name from t1 cnt join ( select CNT.id from t2 CNT where CNT.id=1) t2 on t2.id=cnt.id; – > pass
case 3)
DELIMITER //
|
DROP PROCEDURE IF EXISTS t1test1// |
CREATE PROCEDURE t1test1(IN val INT, OUT output VARCHAR(400)) |
BEGIN
|
SET output = ( |
SELECT cnt.name |
FROM t1 cnt |
JOIN ( |
SELECT CNT.id |
FROM t2 CNT |
WHERE CNT.id = val |
) t2
|
ON t2.id = cnt.id |
);
|
END// |
DELIMITER ;
|
call t1test1(1,@result);
select @result; – pass
I have also attached the logs for reference.
Can you please check this ?
Kindly let me know in case of any further information needed.
Thanks,
Akilan RM.