Sergei Petrunia added a comment - 2024-12-10 12:44 The commit is in bb-11.8- MDEV-6017 -variant3 branch: commit 75773e1d28877d7f3c4599b554921c472adcc6bb (HEAD -> bb-11.8-MDEV-6017-variant3, origin/bb-11.8-MDEV-6017-variant3) Author: Sergei Petrunia <sergey@mariadb.com> Date: Tue Nov 26 14:50:41 2024 +0200   MDEV-35616: Add basic optimizer support for virtual column (Review input addressed) After this patch, the optimizer can handle virtual column expressions in WHERE/ON clauses. If the table has an indexed virtual column: ALTER TABLE t1 ADD COLUMN vcol INT AS (col1+1), ADD INDEX idx1(vcol); and the query uses the exact virtual column expression: SELECT * FROM t1 WHERE col1+1 <= 100 then the optimizer will be able use index idx1 for it. This is achieved by walking the WHERE/ON clauses and replacing instances of virtual column expression (like "col1+1" above) with virtual column's Item_field (like "vcol"). The latter can be processed by the optimizer. Replacement is considered (and done) only in items that are potentially usable to the range optimizer.

Sergei Petrunia added a comment - 2025-01-08 08:33 The testcases in the patch use JSON_EXTRACT() and JSON_UNQUOTE when necessary. TODO: also add test coverage with JSON_VALUE().

Sergei Petrunia added a comment - 2025-01-10 11:18 - edited Hit this with testing: for indexes on virtual columns, index-only scans are supported: https://jira.mariadb.org/browse/MDEV-8326?focusedCommentId=297240&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-297240 but Index Condition Pushdown is intentionally disabled in InnoDB (although it can work, and does work with MyISAM). It's the same in MySQL 8.4.3. ha_innodb.cc has this comment (which makes little sense to me): /* MDEV-31154: For pushed down index condition we don't support virtual column and idx_cond_push() does check for it. For row ID filtering we don't need such restrictions but we get into trouble trying to use the ICP path.   1. It should be fine to follow no_icp path if primary key is generated. However, with user specified primary key(PK), the row is identified by the PK and those columns need to be converted to mysql format in row_search_idx_cond_check before doing the comparison. Since secondary indexes always have PK appended in innodb, it works with current ICP handling code when fetch_primary_key_cols is set to TRUE.   2. Although ICP comparison and Row ID comparison works on different columns the current ICP code can be shared by both.   3. In most cases, it works today by jumping to goto no_icp when we encounter a virtual column. This is hackish and already have some issues as it cannot handle PK and all states are not reset properly, for example, idx_cond_n_cols is not reset.   4. We already encountered MDEV-28747 m_prebuilt->idx_cond was being set.   Neither ICP nor row ID comparison needs virtual columns and the code is simplified to handle both. It should handle the issues. */ Fundamentally, there should be no problem to enable ICP for indexes with vcols. Index-only scans work, so one can unpack the VCOL_FIELD from index entry into its field.

Alice Sherepa added a comment - 2025-01-24 14:48 bb-11.8- MDEV-6017 -variant3 4119c0185cb0559b9fde397f4892c2ab01fbf8e2 is ok to push into 11.8

Sergei Petrunia added a comment - 2025-01-27 11:35 Documentation, work in progress: https://mariadb.com/kb/en/virtual-column-support-in-the-optimizer/