[
https://issues.apache.org/jira/browse/DRILL-5427?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
David Lee updated DRILL-5427:
-----------------------------
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.
was:
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.
> 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
> 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)
