Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24776

Left join condition not fully considered, causing warnings during function evaluation on unrelated rows

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.3.25
    • Fix Version/s: 10.3
    • Component/s: Optimizer
    • Labels:
    • 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.

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            dbuteyn Dennis
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration