[MDEV-29179] Condition pushdown from HAVING into WHERE is not shown in optimizer trace Created: 2022-07-27  Updated: 2023-03-15  Resolved: 2023-03-15

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 11.1.0, 10.11.3, 11.0.2, 10.4.29, 10.5.20, 10.6.13, 10.8.8, 10.9.6, 10.10.4

Type: Bug Priority: Major
Reporter: Oleg Smirnov Assignee: Rex Johnston
Resolution: Fixed Votes: 0
Labels: optimizer_trace

Issue Links:
Relates
relates to MDEV-24819 Wrong result or ASAN use-after-poison... In Review

 Description    

CREATE TABLE t1 (a INT, b VARCHAR(1), KEY (a), KEY(b,a)) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES (4,'n'),(1,'h'),(NULL,'w');
 
 
SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7;

Condition pushdown from HAVING into WHERE puts "t1.a <=7" into the WHERE:

  $6 = 0x555557aff480 <dbug_item_print_buf> "(t1.b <> 'p' or multiple equal(4, t1.a)) and t1.a <= 7"

But this is not visible in the optimizer trace.

Observed on 10.5 but probably affects other versions as well.

 Comments   
Comment by Rex Johnston [ 2023-02-03 ]

Hi Sergei, I needed to make some alterations to Json_writer. Please review. Thanks, Rex

Comment by Sergei Petrunia [ 2023-02-14 ]

Review input provided in the pull request. Need to discuss.

Comment by Sergei Petrunia [ 2023-02-22 ]

Ok to push the last patch

Generated at Thu Feb 08 10:06:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.