[DRILL-5427] SQL Execution Syntax incorrect for Sybase RDBMS

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.10.0
• Fix Version/s: Future
• Component/s: Client - JDBC, SQL Parser, Storage - JDBC

Environment

Windows Linux

• Assignee: Unassigned
• Reporter: David Lee
• Created: 10/Apr/17 21:44
• Updated: 19/Jan/18 13:16

Description

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

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

Issue Links

• is a clone of: DRILL-4908 Unable to setup Sybase JDBC Plugin with access to multiple databases
• relates to: DRILL-4458 JDBC plugin case sensitive table names