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)
