[Maria-developers] Review #3 for: MDEV-26278 Elimination: consider GROUP BY as unique key

Hi Oleg, Please fix compile errors on this platform: https://buildbot.mariadb.org/#/builders/146/builds/13126/steps/5/logs/stdio also please apply the attached diff that fixes the coding style and wording of comments (input and/or adjustments are welcome). After this, the patch is ok to push. The next steps are to create a branch with name like preview-10.10-mdev26278-table-elimination and to officially submit it for preview tree build and testing.

commit 7275bf4939248959b5017314d6b0393296831c0c (HEAD, origin/bb-10.9-MDEV-26278-look) Author: Oleg Smirnov Date: Thu Feb 17 22:53:37 2022 +0700

MDEV-26278 Add functionality to eliminate derived tables from the query

Elimination of unnecessary tables from SQL queries is already present in MariaDB. But it only works for regular tables and not for derived ones.

Imagine we have a view: CREATE VIEW v1 AS SELECT a, b, max(c) AS maxc FROM t1 GROUP BY a, b

Due to "GROUP BY a, b" the values of combinations {a, b} are unique, and this fact can be treated as like derived table "v1" has a unique key on fields {a, b}.

Suppose we have a SQL query: SELECT t2.* FROM t2 LEFT JOIN v1 ON t2.a=v1.a and t2.b=v1.b

1. Since {v1.a, v1.b} is unique and both these fields are bound to t2, "v1" is functionally dependent on t2. This means every record of "t2" will be either joined with a single record of "v1" or NULL-complemented. 2. No fields of "v1" are present on the SELECT list

These two facts allow the server to completely exclude (eliminate) the derived table "v1" from the query.

BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net