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

server crash on select with where clause that calls a function doing a subquery

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.4.13
    • Fix Version/s: N/A
    • Labels:
      None
    • Environment:
      docker container using official docker image mariadb/server:10.4
      docker runs on linux Ubuntu 16.04.6 LTS

      Description

      Shell commands to prepare environment:

      $ docker pull mariadb/server:10.4
      $ docker run --name bugreport -e MYSQL_ROOT_PASSWORD=mypass -d mariadb/server:10.4
      $ docker exec bugreport hostname -I
      --> 172.17.0.2
      $ sudo mysql -h 172.17.0.2 -u root -p

      Now at mariadb/mysql> prompt:

      (you can source the attached bug-reproduce.sql file which contains all followig commands)

      mysql> select version();
      +----------------------------------------+
      | version()                              |
      +----------------------------------------+
      | 10.4.13-MariaDB-1:10.4.13+maria~bionic |
      +----------------------------------------+
      1 row in set (0,00 sec)
       
      mysql> create database if not exists test;
      Query OK, 1 row affected (0,00 sec)
       
      mysql> use test;
      Database changed
       
      mysql> create table if not exists workstation (
        id            INT NOT NULL AUTO_INCREMENT,
        prodlineid    INT NOT NULL DEFAULT 1,
        primary key (id)
      ) engine=innodb;
      Query OK, 0 rows affected (0,26 sec)
       
      mysql> create table if not exists process_workstation (
        processid                             INT NOT NULL,
        workstationid                         INT NOT NULL,
        unique index (processid, workstationid),
        index (workstationid),
        constraint foreign key (workstationid) references workstation(id) on delete cascade on update cascade
      ) ENGINE = InnoDB;
      Query OK, 0 rows affected (0,23 sec)
       
      mysql> create function proc_on_ws(process_id int, ws_id int, prodline_id int) returns int
        return
          exists(
              select * from process_workstation pw
              where pw.workstationid = ws_id and pw.processid = process_id
          )
        or (
              not isnull(prodline_id) and
              not isnull(process_id) and
              not exists(
                      select * from workstation ws, process_workstation pw
                      where ws.prodlineid = prodline_id and
                            pw.workstationid = ws.id and pw.processid = process_id
              )
      );
      Query OK, 0 rows affected (0,03 sec)
       
      mysql> insert ignore into  workstation (id) values
              (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
              (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
              (30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
              (40),(41),(42),(43),(44),(45),(46),(47),(48),(49),
              (50),(51),(52),(53),(54),(55),(56),(57),(58),(59),
              (60),(61),(62),(63),(64),(65),(66),(67),(68),(69),
              (70),(71),(72),(73),(74),(75),(76),(77),(78),(79),
              (80),(81),(82),(83),(84),(85),(86),(87),(88),(89)
              ;
      Query OK, 80 rows affected (0,08 sec)
      Records: 80  Duplicates: 0  Warnings: 0
       
      -- This query crash the server:
       
      mysql> select nextws.id from workstation nextws
      where nextws.prodlineid = 1 AND proc_on_ws( 7 , nextws.id, nextws.prodlineid);
       
      ERROR 2013 (HY000): Lost connection to MySQL server during query
      

      Logs:

      Back to shell, the output of
      $ docker logs bugreport 2>&1
      is attached as bug-mariadb.logs

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              yablacky L. Schwarz
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

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