[#MDEV-5341] ConnectSE: discovery for ODBC tables does not work if tables with the same names present in multiple schemas

Create tables with the same names in two different schemas in Oracle XE 11:

[bar@home ~]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 26 12:37:51 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: system

Enter password:

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> create table hr.t1 (a char(10));

Table created.

SQL> insert into hr.t1 values ('test1');

1 row created.

SQL> create table system.t1 (a char(10));

Table created.

SQL> insert into system.t1 values ('test2');

1 row created.

SQL> commit;

Commit complete.

Now try to create a ConnectSE ODBC table in MariaDB.
It fails:

mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;

Query OK, 0 rows affected (0.01 sec)

ERROR 1939 (HY000): Engine CONNECT failed to discover table `test`.`t1` with 'CREATE TABLE whatever (`A` CHAR(10),`A` CHAR(10)) TABLE_TYPE='odbc' TABNAME='T1' CONNECTION='DSN=oraodbc;UID=system;PWD=manager''

ERROR 1146 (42S02): Table 'test.t1' doesn't exist

Notice, the column "A" presents two times.
The first "A" comes from the table "hr.t1".
The second "A" comes from the table "system.t1".

Now drop the table hr.t1 in Oracle:

SQL> drop table hr.t1;

Table dropped.

and try to create the MariaDB table again:

mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (2.61 sec)

+-------+

| A     |

+-------+

| test2 |

+-------+

1 row in set (0.06 sec)

It works as expected and returns data from the Oracle's table system.t1
(Note, "system" is the default schema for the user "system" in Oracle).

The discovery code should be fixed to collect column data only from a
single remote table "t1".

Also, discovery does not work for a table in a non-default schema.
Create a table in a single non-default Oracle schema:

[bar@home ~]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 26 13:11:27 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: system

Enter password:

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> drop table hr.t1;

Table dropped.

SQL> create table hr.t1 (a char(10));

Table created.

SQL> insert into hr.t1 values ('test3');

1 row created.

SQL> commit;

Commit complete.

Now try to create a ConnectSE table using discovery. It fails:

mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='HR.T1'; select * from t1;

Query OK, 0 rows affected (0.00 sec)

ERROR 1105 (HY000): No data found for table HR.T1

ERROR 1146 (42S02): Table 'test.t1' doesn't exist

However, It works fine when the table structure is specified explicitly:

mysql> drop table if exists t1; create table t1 (a char(10)) engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='HR.T1'; select * from t1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

+-------+

| a     |

+-------+

| test3 |

+-------+

1 row in set (0.07 sec)

The same problems are reproducible when connecting to Interystems Cache.

The problem with multiple schemas is not repeatable with PostgreSQL.
The problem with a non-default schema IS repeatable with PostgreSQL.

The problem with multiple schemas is not repeatable with MS SQL Server.
The problem with a non-default schema IS repeatable with MS SQL Server.

[MDEV-5341] ConnectSE: discovery for ODBC tables does not work if tables with the same names present in multiple schemas Created: 2013-11-26 Updated: 2014-04-09 Resolved: 2014-04-09
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	10.0.10
Fix Version/s:	10.0.11

[MDEV-5341] ConnectSE: discovery for ODBC tables does not work if tables with the same names present in multiple schemas Created: 2013-11-26 Updated: 2014-04-09 Resolved: 2014-04-09