[jira] [Created] (DRILL-5427) SQL Execution Syntax incorrect for Sybase RDBMS

["David Lee (JIRA)" <ji...@apache.org>]

David Lee created DRILL-5427:
--------------------------------

             Summary: SQL Execution Syntax incorrect for Sybase RDBMS
                 Key: DRILL-5427
                 URL: https://issues.apache.org/jira/browse/DRILL-5427
             Project: Apache Drill
          Issue Type: Bug
          Components: Client - JDBC, SQL Parser, Storage - JDBC
    Affects Versions: 1.10.0
         Environment: Windows Linux
            Reporter: David Lee
             Fix For: Future, 1.10.0


The Sybase table syntax should be "database"."owner"."tablename", but "owner" is not being added which produces incorrect SQL when executed.

The default owner is "DBO" for most tables. If DBO is omitted then the syntax should be just "database".."tablename" 

When SYBASE sees  "database".."tablename" it:

A. Checks if there is a tablename owned by the logged in user.
B. If there is no tablename owned by the logged in user it uses DBO by default

This is what I'm seeing using a JDBC plug-in connection to Sybase ASE:

The following statements work fine:

A. show schemas

SCHEMA_NAME
cp.default
dfs.default
dfs.root
dfs.tmp
INFORMATION_SCHEMA
SYB1U
SYB1U.tempdb

B. use SYB1U.tempdb

ok	summary
true	Default schema changed to [SYB1U.tempdb]

C. show tables

TABLE_SCHEMA	TABLE_NAME
SYB1U.tempdb	sysalternates
SYB1U.tempdb	sysattributes
SYB1U.tempdb	syscolumns
SYB1U.tempdb	syscomments
SYB1U.tempdb	sysconstraints
etc.. etc.. etc..

D. SELECT * FROM INFORMATION_SCHEMA.`COLUMNS`
where TABLE_SCHEMA = 'SYB1U.tempdb'
and TABLE_NAME = 'syscolumns'

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE
DRILL	SYB1U.tempdb	syscolumns	id	1	(null)	NO	INTEGER
DRILL	SYB1U.tempdb	syscolumns	number	2	(null)	NO	SMALLINT
DRILL	SYB1U.tempdb	syscolumns	colid	3	(null)	NO	SMALLINT
DRILL	SYB1U.tempdb	syscolumns	status	4	(null)	NO	TINYINT
DRILL	SYB1U.tempdb	syscolumns	type	5	(null)	NO	TINYINT
DRILL	SYB1U.tempdb	syscolumns	length	6	(null)	NO	INTEGER
DRILL	SYB1U.tempdb	syscolumns	offset	7	(null)	NO	SMALLINT
DRILL	SYB1U.tempdb	syscolumns	usertype	8	(null)	NO	SMALLINT
DRILL	SYB1U.tempdb	syscolumns	cdefault	9	(null)	NO	INTEGER
DRILL	SYB1U.tempdb	syscolumns	domain	10	(null)	NO	INTEGER
etc.. etc.. etc..

However, the following statements fail:

A. select * from SYB1U.tempdb.syscolumns
DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query. 
sql SELECT *
FROM "tempdb"."syscolumns"
plugin SYB1U
Fragment 0:0

B. select * from SYB1U.tempdb.dbo.syscolumns
VALIDATION ERROR: From line 1, column 15 to line 1, column 19: Table 'SYB1U.tempdb.dbo.syscolumns' not found

C. select * from SYB1U.tempdb..syscolumns
PARSE ERROR: Encountered ".." at line 1, column 27.

In A, the execution engine doesn't include the "owner" portion.
In B, adding dbo fails validation
In C, the default behavior in Sybase for ".." isn't recognized




use SYB1U.tempdb

show tables

SELECT *
FROM INFORMATION_SCHEMA.`COLUMNS`
where TABLE_SCHEMA = 'SYB1U.tempdb'
and TABLE_NAME = 'syscolumns'

I'm not sure if this is a Drill issue or a Calcite issue.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)