[MDEV-24776] Left join condition not fully considered, causing warnings during function evaluation on unrelated rows Created: 2021-02-03  Updated: 2021-03-28

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3.25
Fix Version/s: 10.3

Type: Bug Priority: Minor
Reporter: Dennis Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: optimizer
Environment:

Ubuntu


 Description   

Apologies in advance if this is a duplicate in one of the 20 pages of join bugs.

I was trying to compile a summary listing by combining several pivots into one big result set. Running the pseudo query below however caused from_unixtime() to generate tons of warnings regarding bad input. I'm guessing that only the primary key was used for the join, then from_unixtime() was called on all values before finally applying filters?

select * from (
 
  select
 
    P.primary key,
 
    P.date,
 
    T1.value,
 
    from_unixtime(T2.value) as alias, <-- lots of warnings here for unrelated values
 
    T3.value
 
  from parent P
 
  left join properties T1 on (primary key and property = 'first')
 
  left join properties T2 on (primary key and property = 'second')
 
  left join properties T3 on (primary key and property = 'third')
 
) where alias is not null <-- without this extra filter, no warnings
 
order by date, primary key;

I was able to get rid of the warnings by adding wrappers around each from_unixtime() call as shown below. But I'm guessing that behind the scenes, tons of extra data was still being gathered and discarded which isn't ideal.

select * from (
 
  select
 
    P.primary key,
 
    P.date,
 
    T1.value,
 
    case when T2.property = 'second' then from_unixtime(T2.value) end as alias, <-- no more warnings
 
    T3.value
 
  from parent P
 
  left join properties T1 on (primary key and property = 'first')
 
  left join properties T2 on (primary key and property = 'second')
 
  left join properties T3 on (primary key and property = 'third')
 
) where alias is not null
 
order by date, primary key;

Maybe I'm just doing it wrong?

Description updated, the "where" filter is critical for generating these warnings.
Generated at Thu Feb 08 09:32:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.