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

["Pritesh Maker (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DRILL-5427?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Pritesh Maker updated DRILL-5427:
---------------------------------
    Fix Version/s:     (was: 1.10.0)

> 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
>
>
> 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.4.14#64029)