Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.17
-
Windows Server 2008R2/MS SQL Server 2012
Description
CONNECT returns error "Unsupported command" when encountering "if exists(select * from table) then" statement in stored procedure.
Welcome to the MariaDB monitor. Commands end with ; or \g. |
Your MariaDB connection id is 10 |
Server version: 10.0.17-MariaDB mariadb.org binary distribution |
|
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. |
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
|
MariaDB [(none)]> use test |
Database changed |
MariaDB [test]> show create table test\G |
*************************** 1. row ***************************
|
Table: test |
Create Table: CREATE TABLE `test` ( |
`col1` int(10) DEFAULT NULL, |
`col2` varchar(255) DEFAULT NULL, |
`col3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAM |
P
|
) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=CCRDB01-WH_FDB_1;uid=username;pwd=password' `TABLE_TYPE`='ODBC' `BLOCK_SIZE`=10 |
1 row in set (0.00 sec) |
|
MariaDB [test]> delimiter ;;
|
MariaDB [test]> CREATE PROCEDURE `connect_if_exists_test`() |
-> LANGUAGE SQL
|
-> NOT DETERMINISTIC |
-> CONTAINS SQL |
-> SQL SECURITY DEFINER
|
-> COMMENT '' |
-> BEGIN |
->
|
-> if (exists(select * from test)) then |
-> select 1; |
-> else |
-> select 0; |
-> end if; |
-> END;; |
Query OK, 0 rows affected (0.01 sec) |
|
MariaDB [test]>
|
MariaDB [test]> delimiter ;
|
MariaDB [test]> call connect_if_exists_test()\G
|
ERROR 1148 (42000): CONNECT Unsupported command |
MariaDB [test]>
|
The problem is most likely caused by the IF statement and not the "EXISTS" statement as the IF function can be used as a workaround. Like "if(exists(select * from test), true, false)". If this workaround is used, one should take note of issue MDEV-7616.