|
This script returns correct results:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
INSERT INTO t1 VALUES (1,'b1'),(2,'b2');
|
SELECT *,IF(@i:=a,ExtractValue('<a><b>b1</b><b>b2</b></a>','//b[$@i]'),0) AS xpath FROM t1;
|
+------+------+-------+
|
| a | b | xpath |
|
+------+------+-------+
|
| 1 | b1 | b1 |
|
| 2 | b2 | b2 |
|
+------+------+-------+
|
2 rows in set (0.00 sec)
|
If I move ExtractValue() from the SELECT list to the WHERE condition,
the results are not as expected:
mysql> SELECT * FROM t1 WHERE b=IF(@i:=a,ExtractValue('<a><b>b1</b><b>b2</b></a>','//b[$@i]'),0);
|
+------+------+
|
| a | b |
|
+------+------+
|
| 1 | b1 |
|
+------+------+
|
1 row in set (0.00 sec)
|
The expected result would be to return both rows.
|