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

sys.table_exists doesn't recognize system views, sequences, versioned tables

    XMLWordPrintable

    Details

      Description

      The procedure sys.table_exists defines the out parameter as ENUM('', 'BASE TABLE', 'VIEW', 'TEMPORARY'). So, it cannot recognize SEQUENCE, SYSTEM VERSIONED and SYSTEM VIEW.

      The first two limitations affect all versions starting from 10.6 where sys schema was introduced.

      The last one, SYSTEM VIEW (information_schema table-views) wasn't a practical issue before, because it didn't work anyway – the procedure attempts to create a temporary table in the given schema, so it would fail for information_schema and performance_schema not being able to do it. But MDEV-12459 planned for 10.9 modifies the procedure to avoid this exploratory creation, so the procedure works for these databases too.

      create table t (a int) with system versioning;
      set @a= 'N/A'; call sys.table_exists('test','t',@a); show warnings; select @a;
      create sequence s;
      set @a= 'N/A'; call sys.table_exists('test','s',@a); show warnings; select @a;
      

      10.6 4e1ca3883

      MariaDB [test]> create table t (a int) with system versioning;
      Query OK, 0 rows affected (0.026 sec)
       
      MariaDB [test]> set @a= 'N/A'; call sys.table_exists('test','t',@a); show warnings; select @a;
      Query OK, 0 rows affected (0.000 sec)
       
      Query OK, 0 rows affected, 1 warning (0.003 sec)
       
      +---------+------+-------------------------------------------------+
      | Level   | Code | Message                                         |
      +---------+------+-------------------------------------------------+
      | Warning | 1265 | Data truncated for column 'out_exists' at row 2 |
      +---------+------+-------------------------------------------------+
      1 row in set (0.000 sec)
       
      +------+
      | @a   |
      +------+
      |      |
      +------+
      1 row in set (0.000 sec)
      

      MariaDB [test]> create sequence s;
      Query OK, 0 rows affected (0.026 sec)
       
      MariaDB [test]> set @a= 'N/A'; call sys.table_exists('test','s',@a); show warnings; select @a;
      Query OK, 0 rows affected (0.000 sec)
       
      Query OK, 0 rows affected, 1 warning (0.002 sec)
       
      +---------+------+-------------------------------------------------+
      | Level   | Code | Message                                         |
      +---------+------+-------------------------------------------------+
      | Warning | 1265 | Data truncated for column 'out_exists' at row 2 |
      +---------+------+-------------------------------------------------+
      1 row in set (0.000 sec)
       
      +------+
      | @a   |
      +------+
      |      |
      +------+
      1 row in set (0.000 sec)
      

      preview-10.9-MDEV-20119-misc c906db303

      MariaDB [test]> set @a= 'N/A'; call sys.table_exists('information_schema','tables',@a); show warnings; select @a;
      Query OK, 0 rows affected (0.000 sec)
       
      Query OK, 0 rows affected, 1 warning (0.002 sec)
       
      +---------+------+-------------------------------------------------+
      | Level   | Code | Message                                         |
      +---------+------+-------------------------------------------------+
      | Warning | 1265 | Data truncated for column 'out_exists' at row 2 |
      +---------+------+-------------------------------------------------+
      1 row in set (0.000 sec)
      

        Attachments

          Activity

            People

            Assignee:
            wlad Vladislav Vaintroub
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.