Details
Major 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) |