Description:
Some complex queries containing views and UDF calls return invalid resultsets.
Previous versions (5.0.32, 5.0.33, 5.0.34 and 5.0.36) returned correct resultsets.
For example,
mysql> delimiter GO
mysql> SELECT
-> `v_hosts`.`nId`,
-> `v_hosts`.`strDisplayName`,
-> `v_host_status`.`status_id`,
-> `v_host_status`.`status_mask`,
-> `v_hosts`.`nGroup`
-> FROM
-> v_hosts INNER JOIN v_host_status ON v_hosts.nId = v_host_status.host_id
-> GO
+-----+----------------+-----------+-------------+--------+
| nId | strDisplayName | status_id | status_mask | nGroup |
+-----+----------------+-----------+-------------+--------+
| 1 | ANDKAZ-VM2-XP | 1 | 32 | 0 |<-- That's correct, there are two rows with `v_hosts`.`nGroup` = 0
| 2 | ANDKAZ | 1 | 32 | 0 |<--
+-----+----------------+-----------+-------------+--------+
2 rows in set (0.08 sec)
mysql>
mysql> SELECT
-> `v_hosts`.`nId`,
-> `v_hosts`.`strDisplayName`,
-> `v_host_status`.`status_id`,
-> `v_host_status`.`status_mask`,
-> `v_hosts`.`nGroup`
-> FROM
-> v_hosts INNER JOIN v_host_status ON v_hosts.nId = v_host_status.host_id
-> WHERE
-> v_hosts.nGroup = 0 -- this WHERE clause really must do nothing as both of rows have `v_hosts`.`nGroup` = 0 (see previous query)
-> GO
+-----+----------------+-----------+-------------+--------+
| nId | strDisplayName | status_id | status_mask | nGroup |
+-----+----------------+-----------+-------------+--------+
| 2 | ANDKAZ | 1 | 32 | 0 | <- incorrect only one row returned
+-----+----------------+-----------+-------------+--------+
1 row in set (0.03 sec)
mysql>
How to repeat:
Do following steps to reproduce
Step 1. Create database with name KAVNEW
CREATE DATABASE IF NOT EXISTS `KAVNEW` DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`;
Step 2. Fill created database with provided data
mysql --default-character-set=utf8 --password="" --port=3306 --user="root" --host="127.0.0.1" "KAVNEW" < kavsqldb.sql
Step 3. Execute queries described below.
mysql --default-character-set=utf8 --password="" --port=3306 --user="root" --host="127.0.0.1" "KAVNEW"
mysql> delimiter GO
mysql> SELECT
-> `v_hosts`.`nId`,
-> `v_hosts`.`strDisplayName`,
-> `v_host_status`.`status_id`,
-> `v_host_status`.`status_mask`,
-> `v_hosts`.`nGroup`
-> FROM
-> v_hosts INNER JOIN v_host_status ON v_hosts.nId = v_host_status.host_id
-> GO
+-----+----------------+-----------+-------------+--------+
| nId | strDisplayName | status_id | status_mask | nGroup |
+-----+----------------+-----------+-------------+--------+
| 1 | ANDKAZ-VM2-XP | 1 | 32 | 0 |
| 2 | ANDKAZ | 1 | 32 | 0 |
+-----+----------------+-----------+-------------+--------+
2 rows in set (0.08 sec)
mysql>
mysql> SELECT
-> `v_hosts`.`nId`,
-> `v_hosts`.`strDisplayName`,
-> `v_host_status`.`status_id`,
-> `v_host_status`.`status_mask`,
-> `v_hosts`.`nGroup`
-> FROM
-> v_hosts INNER JOIN v_host_status ON v_hosts.nId = v_host_status.host_id
-> WHERE
-> v_hosts.nGroup = 0
-> GO
+-----+----------------+-----------+-------------+--------+
| nId | strDisplayName | status_id | status_mask | nGroup |
+-----+----------------+-----------+-------------+--------+
| 2 | ANDKAZ | 1 | 32 | 0 |
+-----+----------------+-----------+-------------+--------+
1 row in set (0.03 sec)
mysql>
Description: Some complex queries containing views and UDF calls return invalid resultsets. Previous versions (5.0.32, 5.0.33, 5.0.34 and 5.0.36) returned correct resultsets. For example, mysql> delimiter GO mysql> SELECT -> `v_hosts`.`nId`, -> `v_hosts`.`strDisplayName`, -> `v_host_status`.`status_id`, -> `v_host_status`.`status_mask`, -> `v_hosts`.`nGroup` -> FROM -> v_hosts INNER JOIN v_host_status ON v_hosts.nId = v_host_status.host_id -> GO +-----+----------------+-----------+-------------+--------+ | nId | strDisplayName | status_id | status_mask | nGroup | +-----+----------------+-----------+-------------+--------+ | 1 | ANDKAZ-VM2-XP | 1 | 32 | 0 |<-- That's correct, there are two rows with `v_hosts`.`nGroup` = 0 | 2 | ANDKAZ | 1 | 32 | 0 |<-- +-----+----------------+-----------+-------------+--------+ 2 rows in set (0.08 sec) mysql> mysql> SELECT -> `v_hosts`.`nId`, -> `v_hosts`.`strDisplayName`, -> `v_host_status`.`status_id`, -> `v_host_status`.`status_mask`, -> `v_hosts`.`nGroup` -> FROM -> v_hosts INNER JOIN v_host_status ON v_hosts.nId = v_host_status.host_id -> WHERE -> v_hosts.nGroup = 0 -- this WHERE clause really must do nothing as both of rows have `v_hosts`.`nGroup` = 0 (see previous query) -> GO +-----+----------------+-----------+-------------+--------+ | nId | strDisplayName | status_id | status_mask | nGroup | +-----+----------------+-----------+-------------+--------+ | 2 | ANDKAZ | 1 | 32 | 0 | <- incorrect only one row returned +-----+----------------+-----------+-------------+--------+ 1 row in set (0.03 sec) mysql> How to repeat: Do following steps to reproduce Step 1. Create database with name KAVNEW CREATE DATABASE IF NOT EXISTS `KAVNEW` DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`; Step 2. Fill created database with provided data mysql --default-character-set=utf8 --password="" --port=3306 --user="root" --host="127.0.0.1" "KAVNEW" < kavsqldb.sql Step 3. Execute queries described below. mysql --default-character-set=utf8 --password="" --port=3306 --user="root" --host="127.0.0.1" "KAVNEW" mysql> delimiter GO mysql> SELECT -> `v_hosts`.`nId`, -> `v_hosts`.`strDisplayName`, -> `v_host_status`.`status_id`, -> `v_host_status`.`status_mask`, -> `v_hosts`.`nGroup` -> FROM -> v_hosts INNER JOIN v_host_status ON v_hosts.nId = v_host_status.host_id -> GO +-----+----------------+-----------+-------------+--------+ | nId | strDisplayName | status_id | status_mask | nGroup | +-----+----------------+-----------+-------------+--------+ | 1 | ANDKAZ-VM2-XP | 1 | 32 | 0 | | 2 | ANDKAZ | 1 | 32 | 0 | +-----+----------------+-----------+-------------+--------+ 2 rows in set (0.08 sec) mysql> mysql> SELECT -> `v_hosts`.`nId`, -> `v_hosts`.`strDisplayName`, -> `v_host_status`.`status_id`, -> `v_host_status`.`status_mask`, -> `v_hosts`.`nGroup` -> FROM -> v_hosts INNER JOIN v_host_status ON v_hosts.nId = v_host_status.host_id -> WHERE -> v_hosts.nGroup = 0 -> GO +-----+----------------+-----------+-------------+--------+ | nId | strDisplayName | status_id | status_mask | nGroup | +-----+----------------+-----------+-------------+--------+ | 2 | ANDKAZ | 1 | 32 | 0 | +-----+----------------+-----------+-------------+--------+ 1 row in set (0.03 sec) mysql>