ARP - Sybase not recognizing "dbo"

1

I’m not sure if this is a problem with the Community Plug-In or with the ARP.

If I click Preview, the SQL below runs FINE.
When I click Run, the SQL ERROS out.

Dremio is trying to select “table_a”.“key_column” when it should be “table_a”.dbo.“key_column”.

Adding any type of criteria or join also errors in both Preview and Run. I suspect it is adding criteria like where “table_a”.“key_column” = ‘ABC’ when it should be “table_a”.dbo.“key_column” = ‘ABC’

From error details:

"sql":"SELECT a.* FROM \"Sybase\".mydb.dbo.\"table_a\" a join \"Sybase\".mydb.dbo.\"table_b\" b on b.key_column = a.key_column limit 100","requestType":7,"user":"test","startTime":1569272961125,"finishTime":1569272961175,"datasetPath":["tmp","UNTITLED"],"datasetVersion":"0003694130033052","parents":[{"datasetPath":["Sybase","mydb","dbo","table_a"],"type":2},{"datasetPath":["Sybase","mydb","dbo","table_b"],"type":2}],"queryType":1,"failureInfo":"DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query. \n\nsql SELECT \"table_a\".\"key_column\", \"table_a\".\"key_type\", \"table_a\".\"key_group\", \"table_a\".\"key_desc1\", \"table_a\".\"key_desc2\", \"table_a\".\"maturity\", \"table_a\".\"ticker\", \"table_a\".\"source\", \"table_a\".\"change_date\", \"table_a\".\"del_flag\", \"table_a\".\"issue_date\", \"table_a\".\"wi_flag\", \"table_a\".\"pricing_key_column\", \"table_a\".\"liquidity\", \"table_a\".\"put_call\", \"table_a\".\"coup_freq\", \"table_a\".\"prin_freq\", \"table_a\".\"date_conv\", \"table_a\".\"currency\", \"table_a\".\"pmt_delay\", \"table_a\".\"pricing\", \"table_a\".\"modified_by\", \"table_a\".\"modification_time\", \"table_a\".\"reviewed_by\", \"table_a\".\"first_pmt_date\", \"table_a\".\"first_cpn_date\", \"table_a\".\"accrual_date\", \"table_a\".\"assoc_tbls\", \"table_a\".\"coupon_type\", \"table_a\".\"long_short\", \"table_a\".\"flag_144a\", \"table_a\".\"notional_flag\", \"table_a\".\"MTN\", \"table_a\".\"country\", \"table_a\".\"market\", \"table_a\".\"calc_type\", \"table_a\".\"owner\", \"table_a\".\"key_long_name\"\nFROM \"mydb\".\"dbo\".\"table_a\"\nINNER JOIN \"mydb\".\"dbo\".\"table_b\" ON \"table_b\".\"key_column\" = \"table_a\".\"key_column\"\nplugin Sybase\nSqlOperatorImpl JDBC_SUB_SCAN\nLocation

Is there a ARP file for Microsoft SQL Server? MS-SQL has the concept of DBO as well.

2

Hi @david.lee, Dremio is shipped with a MS SQL Server connector, and it uses ARP

3

Is it possible to get a copy of the MS-SQL ARP file so I can compare it to the community SYBASE ARP file?? Both products have the same historical lineage and support schema / syntax including DBO (Database Owner).

A normal SQL statement for Sybase would look something like:

select a.key, a.column_a, b.column_b
from mydb.dbo.my_table1 a
join mydb.dbo.my_table2 b on b.key = a.key

Dremio should be converting this internally to:

select mydb.dbo.my_table1.key, mydb.dbo.my_table1.column_a, mydb.dbo.my_table2.column_b
from mydb.dbo.my_table1
join mydb.dbo.my_table2 on mydb.dbo.my_table2.key = mydb.dbo.my_table1.key

However in the logs I’m seeing:

select mydb.my_table1.key, mydb.my_table1.column_a, mydb.my_table2.column_b
from mydb.dbo.my_table1
join mydb.dbo.my_table2 on mydb.my_table2.key = mydb.my_table1.key

“dbo” is missing from the columns and where conditions…

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/ownership-and-user-schema-separation-in-sql-server

Built-In Schemas

SQL Server ships with ten pre-defined schemas that have the same names as the built-in database users and roles. These exist mainly for backward compatibility. You can drop the schemas that have the same names as the fixed database roles if you do not need them. You cannot drop the following schemas:

  • dbo
  • guest
  • sys
  • INFORMATION_SCHEMA
4

@david.lee, can you attach the profile for the successful Preview and the unsuccessful Run? This would give some insight into what, exactly, is getting pushed down into the relational source.

5

Profile_Downloads.zip (14.6 KB)

Preview works. Run doesn’t work with “The JDBC storage plugin failed while trying setup the SQL query.” error

use tempdb
go

create table test_table_a (a int)
go
create table test_table_b (b int)
go

insert test_table_a values(1)
insert test_table_a values(1)
insert test_table_a values(2)
go

insert test_table_b values(1)
insert test_table_b values(1)
insert test_table_b values(2)
go

Dremio SQL

SELECT * FROM "Sybase Test".tempdb.guest."test_table_a" a
join "Sybase Test".tempdb.guest."test_table_b" b on a.a = b.b
go

The profiles clearly show different SQL with the pushdown being execute for the RUN option.

Jdbc(sql=[SELECT *\nFROM \"tempdb\".\"guest\".\"test_table_b\"])
Jdbc(sql=[SELECT *\nFROM \"tempdb\".\"guest\".\"test_table_a\"])

vs

Jdbc(sql=[SELECT *\nFROM \"tempdb\".\"guest\".\"test_table_a\"\nINNER JOIN \"tempdb\".\"guest\".\"test_table_b\" ON \"test_table_a\".\"a\" = \"test_table_b\".\"b\"])

The pushdown version is missing “tempdb.guest” in the On join condition.

1> SELECT * FROM tempdb.guest.test_table_a
INNER JOIN tempdb.guest.test_table_b
ON test_table_a.a = test_table_b.b
2> go
Msg 107, Level 15, State 1:
Server 'my_server', Line 1:
The column prefix 'test_table_a' does not match with a table name or alias name
used in the query. Either the table is not specified in the FROM clause or it
has a correlation name which must be used instead.
Msg 107, Level 15, State 1:
Server 'myserver', Line 2:
The column prefix 'test_table_b' does not match with a table name or alias name
used in the query. Either the table is not specified in the FROM clause or it
has a correlation name which must be used instead.