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

mariadb gives error for unknown column in subquery but mysql runs fine

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.0.2, 10.3.39, 10.4.29, 10.5.20, 10.6.12, 10.6.13
    • 10.4, 11.0
    • Parser
    • Ubuntu based linux

    Description

      Like the title suggests I have a query that runs fine in mysql but gives error with mariadb:

      [42S22][1054] (conn=38) Unknown column 'ME.membershipId' in 'where clause'

      The script that can be used to replicate the error is added as an attachement.

      create table Membership
      (
          id   int primary key,
          name varchar(50)
      );
       
      create table MembershipType
      (
          id   int primary key,
          name varchar(50) unique not null
      );
       
      create table MembershipEvent
      (
          id               int primary key,
          membershipId     int      not null,
          membershipTypeId int      not null,
          eventTime        datetime not null,
          foreign key (membershipTypeId) references MembershipType (id),
          foreign key (membershipId) references Membership (id)
      );
       
      select M.id as MembershipId, M.name as memberName, MT.name as currentMembershipTypeName, ME.eventTime as currentMembershipStartTime
      from Membership M
               left join MembershipEvent ME on M.id = ME.membershipId
               left join MembershipType MT on ME.membershipTypeId = MT.id
       
      where exists(select *
                   from (select InnerME.id, InnerME.membershipTypeId
                         from MembershipEvent InnerME
                         where InnerME.membershipId = ME.membershipId
                           and InnerME.eventTime <= NOW()
                         order by InnerME.eventTime desc
                         limit 1) TEMP
                   where TEMP.membershipTypeId not in (4)
                     and TEMP.id = ME.id);
      

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              HanY2 Hannes Härm
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.