You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by SanjiV SwaraJ <sa...@gmail.com> on 2016/05/31 18:27:55 UTC
Oracle Query Problem
Hello I have Oralce Query for Selecting all the columns:-
SELECT tc.column_name, tc.owner, tc.table_name, tc.column_id, tc.nullable,
tc.data_type, c.constraint_type, c.r_owner AS reference_owner,
rcc.table_name AS reference_table, rcc.column_name AS reference_column_name
FROM SYS.ALL_TAB_COLUMNS tc LEFT OUTER JOIN SYS.ALL_CONS_COLUMNS cc ON (
tc.owner = cc.owner AND tc.table_name = cc.table_name AND tc.column_name =
cc.COLUMN_NAME ) LEFT OUTER JOIN SYS.ALL_CONSTRAINTS c ON ( tc.owner =
c.owner AND tc.table_name = c.table_name AND c.constraint_name =
cc.constraint_name ) LEFT OUTER JOIN ALL_CONS_COLUMNS rcc ON ( c.r_owner =
rcc.owner AND c.r_constraint_name = rcc.constraint_name ) WHERE
tc.table_name = 'REPORTSETTING' AND tc.OWNER = 'NVN' ORDER BY tc.column_id;
*This query is working fine in Oracle DB, but while using same query in
Drill, it giving error. Query for Drill is:-*
SELECT tc.column_name, tc.owner, tc.table_name,
tc.column_id,tc.nullable,tc.data_type,c.constraint_type,c.r_owner AS
reference_owner, rcc.table_name AS reference_table, rcc.column_name AS
reference_column_name FROM OracleDB.SYS.ALL_TAB_COLUMNS tc LEFT OUTER JOIN
OracleDB.SYS.ALL_CONS_COLUMNS cc ON ( tc.owner = cc.owner AND tc.table_name
= cc.table_name AND tc.column_name = cc.COLUMN_NAME ) LEFT OUTER JOIN
OracleDB.SYS.ALL_CONSTRAINTS c ON ( tc.owner = c.owner AND tc.table_name =
c.table_name AND c.constraint_name = cc.constraint_name ) LEFT OUTER JOIN
OracleDB.SYS.ALL_CONS_COLUMNS rcc ON ( c.r_owner = rcc.owner AND
c.r_constraint_name = rcc.constraint_name ) WHERE tc.table_name =
'REPORTSETTING' AND tc.OWNER = 'NVN' ORDER BY tc.column_id ASC;
Following Error Showing:-
org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR:
The JDBC storage plugin failed while trying setup the SQL query. sql SELECT
* FROM (SELECT "t1"."OWNER", "t1"."TABLE_NAME", "t1"."COLUMN_NAME",
"t1"."DATA_TYPE", "t1"."NULLABLE", "t1"."COLUMN_ID",
"ALL_CONSTRAINTS"."CONSTRAINT_TYPE", "ALL_CONSTRAINTS"."R_OWNER",
"ALL_CONSTRAINTS"."R_CONSTRAINT_NAME" FROM (SELECT "t0"."OWNER",
"t0"."TABLE_NAME", "t0"."COLUMN_NAME", "t0"."DATA_TYPE",
"t0"."DATA_TYPE_MOD", "t0"."DATA_TYPE_OWNER", "t0"."DATA_LENGTH",
"t0"."DATA_PRECISION", "t0"."DATA_SCALE", "t0"."NULLABLE",
"t0"."COLUMN_ID", "t0"."DEFAULT_LENGTH", "t0"."DATA_DEFAULT",
"t0"."NUM_DISTINCT", "t0"."LOW_VALUE", "t0"."HIGH_VALUE", "t0"."DENSITY",
"t0"."NUM_NULLS", "t0"."NUM_BUCKETS", "t0"."LAST_ANALYZED",
"t0"."SAMPLE_SIZE", "t0"."CHARACTER_SET_NAME", "t0"."CHAR_COL_DECL_LENGTH",
"t0"."GLOBAL_STATS", "t0"."USER_STATS", "t0"."AVG_COL_LEN",
"t0"."CHAR_LENGTH", "t0"."CHAR_USED", "t0"."V80_FMT_IMAGE",
"t0"."DATA_UPGRADED", "t0"."HISTOGRAM", "ALL_CONS_COLUMNS"."OWNER"
"OWNER0", "ALL_CONS_COLUMNS"."CONSTRAINT_NAME",
"ALL_CONS_COLUMNS"."TABLE_NAME" "TABLE_NAME0",
"ALL_CONS_COLUMNS"."COLUMN_NAME" "COLUMN_NAME0",
"ALL_CONS_COLUMNS"."POSITION", CAST("t0"."OWNER" AS VARCHAR(120) CHARACTER
SET "ISO-8859-1") "$f36" FROM (SELECT "OWNER", "TABLE_NAME", "COLUMN_NAME",
"DATA_TYPE", "DATA_TYPE_MOD", "DATA_TYPE_OWNER", "DATA_LENGTH",
"DATA_PRECISION", "DATA_SCALE", "NULLABLE", "COLUMN_ID", "DEFAULT_LENGTH",
"DATA_DEFAULT", "NUM_DISTINCT", "LOW_VALUE", "HIGH_VALUE", "DENSITY",
"NUM_NULLS", "NUM_BUCKETS", "LAST_ANALYZED", "SAMPLE_SIZE",
"CHARACTER_SET_NAME", "CHAR_COL_DECL_LENGTH", "GLOBAL_STATS", "USER_STATS",
"AVG_COL_LEN", "CHAR_LENGTH", "CHAR_USED", "V80_FMT_IMAGE",
"DATA_UPGRADED", "HISTOGRAM", CAST("COLUMN_NAME" AS VARCHAR(4000) CHARACTER
SET "ISO-8859-1") "$f31" FROM "SYS"."ALL_TAB_COLUMNS" WHERE "TABLE_NAME" =
'REPORTSETTING' AND "OWNER" = 'NVN') "t0" LEFT JOIN
"SYS"."ALL_CONS_COLUMNS" ON "t0"."OWNER" = "ALL_CONS_COLUMNS"."OWNER" AND
"t0"."TABLE_NAME" = "ALL_CONS_COLUMNS"."TABLE_NAME" AND "t0"."$f31" =
"ALL_CONS_COLUMNS"."COLUMN_NAME") "t1" LEFT JOIN "SYS"."ALL_CONSTRAINTS" ON
"t1"."$f36" = "ALL_CONSTRAINTS"."OWNER" AND "t1"."TABLE_NAME" =
"ALL_CONSTRAINTS"."TABLE_NAME" AND "t1"."CONSTRAINT_NAME" =
"ALL_CONSTRAINTS"."CONSTRAINT_NAME") "t2" LEFT JOIN (SELECT
"CONSTRAINT_NAME", "TABLE_NAME", "COLUMN_NAME", CAST("OWNER" AS
VARCHAR(120) CHARACTER SET "ISO-8859-1") "$f5" FROM
"SYS"."ALL_CONS_COLUMNS") "t3" ON "t2"."R_OWNER" = "t3"."$f5" AND
"t2"."R_CONSTRAINT_NAME" = "t3"."CONSTRAINT_NAME" plugin OracleDB Fragment
0:0 [Error Id: 2a11fed2-ec79-4ef1-9d29-781af21274f6
*Please Tell me what i am doing wrong in this query?*
--
Thanks & Regards.
Sanjiv
Swaraj
Re: Oracle Query Problem
Posted by Sudheesh Katkam <sk...@maprtech.com>.
Can you enable verbose logging and post the resulting error message? You can do this by executing the following statement, and then the failing query.
SET `exec.errors.verbose` = true;
Thank you,
Sudheesh
> On May 31, 2016, at 11:27 AM, SanjiV SwaraJ <sa...@gmail.com> wrote:
>
> Hello I have Oralce Query for Selecting all the columns:-
>
> SELECT tc.column_name, tc.owner, tc.table_name, tc.column_id, tc.nullable,
> tc.data_type, c.constraint_type, c.r_owner AS reference_owner,
> rcc.table_name AS reference_table, rcc.column_name AS reference_column_name
> FROM SYS.ALL_TAB_COLUMNS tc LEFT OUTER JOIN SYS.ALL_CONS_COLUMNS cc ON (
> tc.owner = cc.owner AND tc.table_name = cc.table_name AND tc.column_name =
> cc.COLUMN_NAME ) LEFT OUTER JOIN SYS.ALL_CONSTRAINTS c ON ( tc.owner =
> c.owner AND tc.table_name = c.table_name AND c.constraint_name =
> cc.constraint_name ) LEFT OUTER JOIN ALL_CONS_COLUMNS rcc ON ( c.r_owner =
> rcc.owner AND c.r_constraint_name = rcc.constraint_name ) WHERE
> tc.table_name = 'REPORTSETTING' AND tc.OWNER = 'NVN' ORDER BY tc.column_id;
>
> *This query is working fine in Oracle DB, but while using same query in
> Drill, it giving error. Query for Drill is:-*
>
> SELECT tc.column_name, tc.owner, tc.table_name,
> tc.column_id,tc.nullable,tc.data_type,c.constraint_type,c.r_owner AS
> reference_owner, rcc.table_name AS reference_table, rcc.column_name AS
> reference_column_name FROM OracleDB.SYS.ALL_TAB_COLUMNS tc LEFT OUTER JOIN
> OracleDB.SYS.ALL_CONS_COLUMNS cc ON ( tc.owner = cc.owner AND tc.table_name
> = cc.table_name AND tc.column_name = cc.COLUMN_NAME ) LEFT OUTER JOIN
> OracleDB.SYS.ALL_CONSTRAINTS c ON ( tc.owner = c.owner AND tc.table_name =
> c.table_name AND c.constraint_name = cc.constraint_name ) LEFT OUTER JOIN
> OracleDB.SYS.ALL_CONS_COLUMNS rcc ON ( c.r_owner = rcc.owner AND
> c.r_constraint_name = rcc.constraint_name ) WHERE tc.table_name =
> 'REPORTSETTING' AND tc.OWNER = 'NVN' ORDER BY tc.column_id ASC;
>
> Following Error Showing:-
>
> org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR:
> The JDBC storage plugin failed while trying setup the SQL query. sql SELECT
> * FROM (SELECT "t1"."OWNER", "t1"."TABLE_NAME", "t1"."COLUMN_NAME",
> "t1"."DATA_TYPE", "t1"."NULLABLE", "t1"."COLUMN_ID",
> "ALL_CONSTRAINTS"."CONSTRAINT_TYPE", "ALL_CONSTRAINTS"."R_OWNER",
> "ALL_CONSTRAINTS"."R_CONSTRAINT_NAME" FROM (SELECT "t0"."OWNER",
> "t0"."TABLE_NAME", "t0"."COLUMN_NAME", "t0"."DATA_TYPE",
> "t0"."DATA_TYPE_MOD", "t0"."DATA_TYPE_OWNER", "t0"."DATA_LENGTH",
> "t0"."DATA_PRECISION", "t0"."DATA_SCALE", "t0"."NULLABLE",
> "t0"."COLUMN_ID", "t0"."DEFAULT_LENGTH", "t0"."DATA_DEFAULT",
> "t0"."NUM_DISTINCT", "t0"."LOW_VALUE", "t0"."HIGH_VALUE", "t0"."DENSITY",
> "t0"."NUM_NULLS", "t0"."NUM_BUCKETS", "t0"."LAST_ANALYZED",
> "t0"."SAMPLE_SIZE", "t0"."CHARACTER_SET_NAME", "t0"."CHAR_COL_DECL_LENGTH",
> "t0"."GLOBAL_STATS", "t0"."USER_STATS", "t0"."AVG_COL_LEN",
> "t0"."CHAR_LENGTH", "t0"."CHAR_USED", "t0"."V80_FMT_IMAGE",
> "t0"."DATA_UPGRADED", "t0"."HISTOGRAM", "ALL_CONS_COLUMNS"."OWNER"
> "OWNER0", "ALL_CONS_COLUMNS"."CONSTRAINT_NAME",
> "ALL_CONS_COLUMNS"."TABLE_NAME" "TABLE_NAME0",
> "ALL_CONS_COLUMNS"."COLUMN_NAME" "COLUMN_NAME0",
> "ALL_CONS_COLUMNS"."POSITION", CAST("t0"."OWNER" AS VARCHAR(120) CHARACTER
> SET "ISO-8859-1") "$f36" FROM (SELECT "OWNER", "TABLE_NAME", "COLUMN_NAME",
> "DATA_TYPE", "DATA_TYPE_MOD", "DATA_TYPE_OWNER", "DATA_LENGTH",
> "DATA_PRECISION", "DATA_SCALE", "NULLABLE", "COLUMN_ID", "DEFAULT_LENGTH",
> "DATA_DEFAULT", "NUM_DISTINCT", "LOW_VALUE", "HIGH_VALUE", "DENSITY",
> "NUM_NULLS", "NUM_BUCKETS", "LAST_ANALYZED", "SAMPLE_SIZE",
> "CHARACTER_SET_NAME", "CHAR_COL_DECL_LENGTH", "GLOBAL_STATS", "USER_STATS",
> "AVG_COL_LEN", "CHAR_LENGTH", "CHAR_USED", "V80_FMT_IMAGE",
> "DATA_UPGRADED", "HISTOGRAM", CAST("COLUMN_NAME" AS VARCHAR(4000) CHARACTER
> SET "ISO-8859-1") "$f31" FROM "SYS"."ALL_TAB_COLUMNS" WHERE "TABLE_NAME" =
> 'REPORTSETTING' AND "OWNER" = 'NVN') "t0" LEFT JOIN
> "SYS"."ALL_CONS_COLUMNS" ON "t0"."OWNER" = "ALL_CONS_COLUMNS"."OWNER" AND
> "t0"."TABLE_NAME" = "ALL_CONS_COLUMNS"."TABLE_NAME" AND "t0"."$f31" =
> "ALL_CONS_COLUMNS"."COLUMN_NAME") "t1" LEFT JOIN "SYS"."ALL_CONSTRAINTS" ON
> "t1"."$f36" = "ALL_CONSTRAINTS"."OWNER" AND "t1"."TABLE_NAME" =
> "ALL_CONSTRAINTS"."TABLE_NAME" AND "t1"."CONSTRAINT_NAME" =
> "ALL_CONSTRAINTS"."CONSTRAINT_NAME") "t2" LEFT JOIN (SELECT
> "CONSTRAINT_NAME", "TABLE_NAME", "COLUMN_NAME", CAST("OWNER" AS
> VARCHAR(120) CHARACTER SET "ISO-8859-1") "$f5" FROM
> "SYS"."ALL_CONS_COLUMNS") "t3" ON "t2"."R_OWNER" = "t3"."$f5" AND
> "t2"."R_CONSTRAINT_NAME" = "t3"."CONSTRAINT_NAME" plugin OracleDB Fragment
> 0:0 [Error Id: 2a11fed2-ec79-4ef1-9d29-781af21274f6
>
> *Please Tell me what i am doing wrong in this query?*
>
> --
> Thanks & Regards.
> Sanjiv
> Swaraj