Description:
I am working on a storage engine for MySQL 8. ECP works properly in my engine in 8.0.19, the ha_warp::cond_push function is called when appropriate.
When I compile against 8.0.20, engine condition pushdown no longer works. A breakpoint placed at ha_warp::cond_push confirms the function is not called anymore in 8.0.20.
What changed in 8.0.20 that prevents ECP code that was working in 8.0.19 from working now?
To give context, the WARP engine uses ECP to automatically create bitmap indexes for filter expressions (WHERE clauses) and pushes evaluations on those columns down to those indexes. MySQL doesn't have optimizer support for bitmap indexes, thus ECP is necessary to use them effectively.
This is my test table:
create database test;
create table t1(c1 int, c2 int, c3 varchar(32)) engine=warp;
insert into t1 values (1,1,'abc'), (2,2,'def');
I populated the table repeatedly with INSERT .. SELECT
(multiple iterations of insert select until 524288 rows are inserted)
mysql> insert into t1 select * from t1;
Query OK, 524288 rows affected (15.33 sec)
Records: 524288 Duplicates: 0 Warnings: 0
Then I inserted another record:
insert into t1 values (3,3,'efg');
This is MySQL 8.0.20:
mysql> select * from t1 where c3 = 'efg';
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 3 | 3 | efg |
+------+------+------+
1 row in set (.12 sec)
Note the query is the same speed on the second execution. This is because
the condition wasn't pushed down, so it didn't produce an index, and it didn't apply the condition to the index on the second execution:
mysql> select * from t1 where c3 = 'efg';
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 3 | 3 | efg |
+------+------+------+
1 row in set (.12 sec)
This is 8.0.19 (where hander::cond_push creates an index on the first query)
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 3 | 3 | efg |
+------+------+------+
1 row in set (0.13 sec)
As you can see, the second query is a lot faster because the automatically created index is used:
mysql> select * from t1 where c3 = 'efg';
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 3 | 3 | efg |
+------+------+------+
1 row in set (0.01 sec)
summary:
ha_warp::cond_push is not called in 8.0.20 but is called in 8.0.19
How to repeat:
A github fork of MySQL with the WARP engine is available here:
https://github.com/greenlion/warp
IMPORTANT: There are currently no changes to the MySQL source code, the only code different from upstream is the addition of the WARP storage engine.
If you compile it, and run gdb against mysqld, and place a breakpoint at ha_warp::cond_push then run the following:
create database test;
create table t1(c1 int, c2 int, c3 varchar(32)) engine=warp;
insert into t1 values (1,1,'abc'), (2,2,'def');
select * from t1 where c1 = 1;
You will find the debugger never breaks at the ha_cond function.
However, the ha_warp::ha_cond function IS CALLED in 8.0.19. You can find the pre-merge 8.0.19 code at:
git checkout 20482022c8082f0d9c6af6b477806070dc367596
NOTE: you might have to fix sql/rpl_applier_reader.cc, line 69 to get MySQL to compile. This is a bug in 8.0.19. Change = to ==
If you run gdb mysqld, and set a breakpoint at ha_warp::cond_push you will find the debugger does indeed break in the function:
Thread 54 "mysqld" hit Breakpoint 1, ha_warp::cond_push
(this=0x7ffe9c114888, cond=0x7ffe9c0f1200, other_tbls_ok=true)
at /home/greenlion/warp/storage/warp/ha_warp.cc:1805
1805 if(lock.type != TL_READ) return cond;
Suggested fix:
My storage engine can not work without ECP. Please explain if I need to change something in my SE to get ECP to work, or if this is a bug in MySQL introduced in 8.0.20 which prevents ECP from working, and if so, please fix this regression.
