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

Fails to reopen temp table within standard CTE

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.11, 10.2(EOL), 10.3(EOL)
    • 10.2.13
    • Optimizer - CTE
    • None
    • 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

            igor Igor Babaev
            kpenza Kenneth Penza
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.