1) When the table/file doesn't exist it should fail. 2) If the file is empty Drill is trying to discover a schema, but unable to do so since there are no records. Perhaps try to create a View on these "temp" tables so you can declare dat types used of joins. This way at least you give Drill a data type for the JOIN even if the file has no data.
--Andries > On Dec 17, 2015, at 9:39 PM, <masahide.mi...@brother.co.jp> > <masahide.mi...@brother.co.jp> wrote: > > Hi, > > Is any good way to avoid error on join query with empty table? > I've come across 2 types of error as bellow. > > Sample SQL: > select A.*, B.* > from cp.`tpch/nation.parquet` as A > join dfs.tmp.`dummy` as B > on A.n_nationkey = B.n_nationkey > > 1) When the table(file) doesn't exist. > > org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR > Table 'TABLENAME' not found > > 2) When the table(file) exists, but it has no records. > ex) No recode file is created as csv with header and filled properly > header text. > such as: "n_nationkey","iso_code" > > org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: > DrillRuntimeException: > Join only supports implicit casts between 1. Numeric data 2. > Varchar, Varbinary data 3. Date, Timestamp data Left type: VARCHAR, Right > type: INT > > Surely, I know these errors should occur in those case. > Though, we expect null values like RDB-SQL join with empty tables. > > I hope your great idea. > > p.s. : for your information. > Actually, we are going to query to drill in batch process. > And, we predict some tables can be empty and it's ok. > For example, temporary table is so. > We wouldn't like to suspend batch in such situation. > > -- > Miura, Masahide >