Description:
Hello,
I use a JOIN-Query with a Function to check a String. If I put a String from table B in my function and bind it with OR, the function call is ignored.
mysql> SELECT * FROM A;
+----+-------+
| ID | Value |
+----+-------+
| 1 | AAA |
| 2 | BBB |
+----+-------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM B;
+----+-------+
| ID | Value |
+----+-------+
| 1 | AAA |
| 2 | BBB |
+----+-------+
2 rows in set (0.00 sec)
Function a_test returns simply the parameter back.
mysql> SELECT a_test('XYZ');
+---------------+
| a_test('XYZ') |
+---------------+
| XYZ |
+---------------+
1 row in set (0.00 sec)
Now my Query. I think the Row ( 2, BBB) is lost or ignored.
mysql> SELECT A.ID, A.Value
-> FROM A
-> INNER JOIN B
-> ON A.ID = B.ID
-> WHERE A.ID = 1
-> OR a_test(B.Value) = 'BBB';
+----+-------+
| ID | Value |
+----+-------+
| 1 | AAA |
+----+-------+
1 row in set (0.00 sec)
The same Query without the function a_test.
mysql> SELECT A.ID, A.Value
-> FROM A
-> INNER JOIN B
-> ON A.ID = B.ID
-> WHERE A.ID = 1
-> OR B.Value = 'BBB';
+----+-------+
| ID | Value |
+----+-------+
| 1 | AAA |
| 2 | BBB |
+----+-------+
2 rows in set (0.00 sec)
How to repeat:
CREATE TABLE `testdb`.`A` (
`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Value` VARCHAR( 20 ) NOT NULL
) ENGINE = MYISAM
CREATE TABLE `testdb`.`B` (
`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Value` VARCHAR( 20 ) NOT NULL
) ENGINE = MYISAM
INSERT INTO `testdb`.`A` (`ID` ,`Value` )
VALUES ('1', 'AAA'), ('2', 'BBB');
INSERT INTO `testdb`.`B` (`ID` ,`Value` )
VALUES ('1', 'AAA'), ('2', 'BBB');
DELIMITER //
CREATE FUNCTION a_test(param1 vARCHAR(50))
RETURNS varchar(50) CHARSET latin1
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
begin
RETURN param1;
end;
//
DELIMITER ;
Description: Hello, I use a JOIN-Query with a Function to check a String. If I put a String from table B in my function and bind it with OR, the function call is ignored. mysql> SELECT * FROM A; +----+-------+ | ID | Value | +----+-------+ | 1 | AAA | | 2 | BBB | +----+-------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM B; +----+-------+ | ID | Value | +----+-------+ | 1 | AAA | | 2 | BBB | +----+-------+ 2 rows in set (0.00 sec) Function a_test returns simply the parameter back. mysql> SELECT a_test('XYZ'); +---------------+ | a_test('XYZ') | +---------------+ | XYZ | +---------------+ 1 row in set (0.00 sec) Now my Query. I think the Row ( 2, BBB) is lost or ignored. mysql> SELECT A.ID, A.Value -> FROM A -> INNER JOIN B -> ON A.ID = B.ID -> WHERE A.ID = 1 -> OR a_test(B.Value) = 'BBB'; +----+-------+ | ID | Value | +----+-------+ | 1 | AAA | +----+-------+ 1 row in set (0.00 sec) The same Query without the function a_test. mysql> SELECT A.ID, A.Value -> FROM A -> INNER JOIN B -> ON A.ID = B.ID -> WHERE A.ID = 1 -> OR B.Value = 'BBB'; +----+-------+ | ID | Value | +----+-------+ | 1 | AAA | | 2 | BBB | +----+-------+ 2 rows in set (0.00 sec) How to repeat: CREATE TABLE `testdb`.`A` ( `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `Value` VARCHAR( 20 ) NOT NULL ) ENGINE = MYISAM CREATE TABLE `testdb`.`B` ( `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `Value` VARCHAR( 20 ) NOT NULL ) ENGINE = MYISAM INSERT INTO `testdb`.`A` (`ID` ,`Value` ) VALUES ('1', 'AAA'), ('2', 'BBB'); INSERT INTO `testdb`.`B` (`ID` ,`Value` ) VALUES ('1', 'AAA'), ('2', 'BBB'); DELIMITER // CREATE FUNCTION a_test(param1 vARCHAR(50)) RETURNS varchar(50) CHARSET latin1 DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' begin RETURN param1; end; // DELIMITER ;