MySQL Bugs: #98937: Functional indexes prevent JOINs to use them

Bug #98937	Functional indexes prevent JOINs to use them
Submitted:	12 Mar 2020 16:06	Modified:	13 Mar 2020 13:37
Reporter:	Saverio Miroddi	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In MySQL 5.7, joins involving indexed generated columns don't use the index if the function is part of the join condition.

The same is true of functional key parts (MySQL 8.0).

The difference though, is that while with indexed generated columns, using the generated column makes the join use the index, with functional key parts, the underlying generated column is not visible.

Therefore, functional key parts entirely preclude the index to be used in JOINs.

How to repeat:
------------------------------------------------------------------------------------
-- Behavior with functional key part (index not used)
------------------------------------------------------------------------------------

CREATE TEMPORARY TABLE date_functional_index
(
  id         INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  created_at DATETIME NOT NULL,
  INDEX ( (DATE(created_at)) )
);

INSERT INTO date_functional_index (created_at)
WITH RECURSIVE sequence (n) AS
(
  SELECT 0
  UNION ALL
  SELECT n + 1 FROM sequence WHERE n + 1 < 100000
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 100K) */
  NOW() - INTERVAL (90 * RAND()) DAY `created_at`
FROM sequence;

ANALYZE TABLE date_functional_index;

EXPLAIN FORMAT=TREE
WITH RECURSIVE dates_range (d) AS
(
  SELECT CURDATE() - INTERVAL 90 DAY
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM dates_range WHERE d + INTERVAL 1 day <= CURDATE()
)
SELECT d, COUNT(id)
FROM
  dates_range
  LEFT JOIN date_functional_index ON d = DATE(created_at)
GROUP BY d;

-- -> Table scan on <temporary>
--     -> Aggregate using temporary table
--         -> Nested loop left join
--             -> Table scan on dates_range
--                 -> [...]
--             -> Filter: (dates_range.d = cast(date_functional_index.created_at as date))  (cost=3429.97 rows=100649)
--                 -> Table scan on date_functional_index  (cost=3429.97 rows=100649)

------------------------------------------------------------------------------------
-- Behavior with indexed generated column (index not used)
------------------------------------------------------------------------------------

CREATE TEMPORARY TABLE date_generated_column_functional_index
(
  id              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  created_at      DATETIME NOT NULL,
  created_at_date DATE AS (DATE(created_at)),
  INDEX (created_at_date)
)
WITH RECURSIVE sequence (n) AS
(
  SELECT 0
  UNION ALL
  SELECT n + 1 FROM sequence WHERE n + 1 < 100000
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 100K) */
  NOW() - INTERVAL (90 * RAND()) DAY `created_at`
FROM sequence;

ANALYZE TABLE date_generated_column_functional_index;

EXPLAIN FORMAT=TREE
WITH RECURSIVE dates_range (d) AS
(
  SELECT CURDATE() - INTERVAL 90 DAY
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM dates_range WHERE d + INTERVAL 1 day <= CURDATE()
)
SELECT d, COUNT(id)
FROM
  dates_range
  LEFT JOIN date_generated_column_functional_index ON d = created_at_date
GROUP BY d;

-- -> Table scan on <temporary>
--     -> Aggregate using temporary table
--         -> Nested loop left join
--             -> Table scan on dates_range
--                 -> [...]
--             -> Index lookup on date_generated_column_functional_index using created_at_date (created_at_date=dates_range.d)  (cost=36.18 rows=1026)

Suggested fix:
Add support for functional indexes to JOINs.

Hi Mr. Miroddi,

Thank you for your feature request.

I find that a feature that you are asking for sounds very interesting. Please, do note that 5.7 is unlikely to get any major new features.

Verified as reported.

I'd say "JOINs don't use functional key parts" is a bug in MySQL 8.0, not a feature request. 

Alternatively (but of course less preferred), the documentation should include it as a limitation of functional indexes.

ps. The blog post of the author of this bug is quite insightful: https://saveriomiroddi.github.io/An-introduction-to-functional-indexes-in-mysql-8.0-and-th...

Hi,

If one type of index is designed for only one specific purpose, then adding any additional functionality is definitely a feature request.

I think I then missed in the documentation that functional indexes are only for that "one specific purpose", should I have derived that from https://dev.mysql.com/doc/refman/8.0/en/create-index.html ?

Are multi-value indices based on json also functional indexes?  I'm seeing the same behavior with joining on a multi-value index. Even with index hints, I can only ever get a full table scan.  The same multi-value index works fine when not joining.  I'm trying to figure out if this bug report is the root cause of what I am seeing, or if I should try to submit another report.

Hi,

This is not a forum for free support or for asking questions .......

I was simply trying to avoid a duplicate bug report.  I will assume that I’m seeing a separate issue and submit a new report with test cases.

I've filed a documentation bug to clarify the current limitations (bug#110059).

Thank you, Knut.

I just ran into this feature request (link: https://stackoverflow.com/q/77661711/5006866). Would it be possible to open https://bugs.mysql.com/bug.php?id=110059 for public, and have this behavior documented?

Hi Mr. Kustermann,

We have verified a separate bug for the documentation to be amended. 

It is not done yet, so we shall press on that issue.