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

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

    [ https://issues.apache.org/jira/browse/DRILL-5427?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16225849#comment-16225849 ] 

David Lee commented on DRILL-5427:
----------------------------------

Here's a real example include the Drill query plan.

Table Setup:

use tempdb
go

create table my_table
(column_a int, column_b varchar(20))
go

insert my_table values (1, 'abc')
insert my_table values (2, 'xyz')
go

All the following statements work in Native Transact-SQL:

select * from my_table
go

select * from tempdb..my_table
go

select * from tempdb.guest.my_table
go

 column_a    column_b
 ----------- --------------------
           1 abc
           2 xyz

Here's what happens if you run this in Drill: The JDBC SQL executed does not match any of the Transact-SQL statements above.

select * from Sybase.tempdb.my_table

00-00    Screen : rowType = RecordType(INTEGER column_a, VARCHAR(20) column_b): rowcount = 100.0, cumulative cost = {110.0 rows, 110.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 2041
00-01      Project(column_a=[$0], column_b=[$1]) : rowType = RecordType(INTEGER column_a, VARCHAR(20) column_b): rowcount = 100.0, cumulative cost = {100.0 rows, 100.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 2040
00-02        Jdbc(sql=[SELECT *
FROM "tempdb"."my_table"]) : rowType = RecordType(INTEGER column_a, VARCHAR(20) column_b): rowcount = 100.0, cumulative cost = {100.0 rows, 100.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 2007

This is not a valid SQL statement in Sybase:

SELECT * FROM "tempdb"."my_table"

You need to either a) omit the schema, b) add an extra "." for the schema owner or c) add the schema owner which is "guest" for tempdb.

> 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)