Description:
Run with different query plan will get inconsisent results
How to repeat:
mysql> create database test10;
Query OK, 1 row affected (0.06 sec)
mysql> use test10;
Database changed
mysql> create table t1(a int, b bit(5));
Query OK, 0 rows affected (0.34 sec)
mysql> insert into t1 values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.12 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> explain format=tree select ifnull(b,b) from t1 group by b;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary> (cost=2.09..3.78 rows=3)
-> Temporary table with deduplication (cost=1.24..1.24 rows=3)
-> Table scan on t1 (cost=0.55 rows=3)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
--Got result with temporary agg as below
mysql> select ifnull(b,b) from t1 group by b;
+--------------------------+
| ifnull(b,b) |
+--------------------------+
| 0x01 |
| 0x02 |
| 0x03 |
+--------------------------+
3 rows in set (0.01 sec)
--Got result with group agg as below.
--Use `set optimizer_switch='hypergraph_optimizer=on'` to generate this plan
mysql> explain format=tree select ifnull(b,b) from t1 group by b;
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Group (no aggregates) (cost=2.46..2.74 rows=1.73)
-> Sort: t1.b (cost=2.08..2.08 rows=3)
-> Table scan on t1 (cost=0.295..0.885 rows=3)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select ifnull(b,b) from t1 group by b;
+--------------------------+
| ifnull(b,b) |
+--------------------------+
| 0x31 |
| 0x32 |
| 0x33 |
+--------------------------+
3 rows in set (0.01 sec)
Description: Run with different query plan will get inconsisent results How to repeat: mysql> create database test10; Query OK, 1 row affected (0.06 sec) mysql> use test10; Database changed mysql> create table t1(a int, b bit(5)); Query OK, 0 rows affected (0.34 sec) mysql> insert into t1 values(1,1),(2,2),(3,3); Query OK, 3 rows affected (0.12 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> explain format=tree select ifnull(b,b) from t1 group by b; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on <temporary> (cost=2.09..3.78 rows=3) -> Temporary table with deduplication (cost=1.24..1.24 rows=3) -> Table scan on t1 (cost=0.55 rows=3) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) --Got result with temporary agg as below mysql> select ifnull(b,b) from t1 group by b; +--------------------------+ | ifnull(b,b) | +--------------------------+ | 0x01 | | 0x02 | | 0x03 | +--------------------------+ 3 rows in set (0.01 sec) --Got result with group agg as below. --Use `set optimizer_switch='hypergraph_optimizer=on'` to generate this plan mysql> explain format=tree select ifnull(b,b) from t1 group by b; +------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Group (no aggregates) (cost=2.46..2.74 rows=1.73) -> Sort: t1.b (cost=2.08..2.08 rows=3) -> Table scan on t1 (cost=0.295..0.885 rows=3) | +------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select ifnull(b,b) from t1 group by b; +--------------------------+ | ifnull(b,b) | +--------------------------+ | 0x31 | | 0x32 | | 0x33 | +--------------------------+ 3 rows in set (0.01 sec)