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

Fails to reopen temp table within standard CTE

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2, 10.3, 10.2.11
    • Fix Version/s: 10.2.13
    • Component/s: Optimizer - CTE
    • Labels:
      None
    • Environment:
      Ubuntu 16.04.3 LTS running MariaDB 10.2.11.

      Description

      Whilst working on data analysis using temporary tables on "10.2.11-MariaDB-10.2.11+maria~xenial-log" I noticed an issue with CTE's. Mariadb fails to reopen temporary tables if they are used more than once with the CTE query. (Note that if temporary tables are converted to permanent tables the below test case work fine).

      Running the below test case in database dbtestcase returns : "ERROR 1146 (42S02) at line 10 in file: 'testcase.sql': Table 'dbtestcase.tbcte2' doesn't exist"

      Testcase:

      drop table if exists tbcte1;
      drop table if exists tbcte2;

      create table tbcte1 (term char(10));
      create temporary table tbcte2 (term char(10));

      insert into tbcte1 values ('TERM01'),('TERM02'),('TERM03');
      insert into tbcte2 values ('TERM02'),('TERM03'),('TERM04');

      with c1 as (select * from tbcte1),
      c2 as (select * from tbcte2)
      select *
      from c1
      left outer join c2 on c1.term = c2.term
      union all
      select *
      from c1
      right outer join c2 on c1.term = c2.term
      where c1.term is null;

      Server config:

      --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --basedir=/usr --datadir=/var/lib/mysql --tmpdir=/tmp --lc_messages_dir=/usr/share/mysql --lc_messages=en_US --skip-external-locking --bind-address=0.0.0.0 --max_connections=100 --connect_timeout=5 --wait_timeout=600 --max_allowed_packet=16M --thread_cache_size=128 --sort_buffer_size=4M --bulk_insert_buffer_size=16M --tmp_table_size=32M --max_heap_table_size=32M --myisam_recover_options=BACKUP --key_buffer_size=128M --table_open_cache=400 --myisam_sort_buffer_size=512M --concurrent_insert=2 --read_buffer_size=2M --read_rnd_buffer_size=1M --query_cache_limit=128K --query_cache_size=64M --log_warnings=2 --slow_query_log_file=/var/log/mysql/mariadb-slow.log --long_query_time=10 --log_slow_verbosity=query_plan --log_bin=/var/log/mysql/mariadb-bin --log_bin_index=/var/log/mysql/mariadb-bin.index --expire_logs_days=10 --max_binlog_size=100M --sql_mode=ONLY_FULL_GROUP_BY --default_storage_engine=InnoDB --innodb_buffer_pool_size=256M --innodb_log_buffer_size=8M --innodb_file_per_table=1 --innodb_open_files=400 --innodb_io_capacity=400 --innodb_flush_method=O_DIRECT

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              kpenza Kenneth Penza
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: