Ok… I believe the reason you are getting the errors is that Drill is quite sensitive to data types. The TO_CHAR function is used to convert numeric data into characters (VARCHAR). However, you are calling the TO_CHAR on data which is already a VARCHAR. Since there isn’t a function called TO_CHAR that accepts VARCHAR as input, you get the unhelpful error messages you received.
I think the function you probably want to use in the JOIN statements is TO_NUMBER rather than TO_CHAR. (https://drill.apache.org/docs/data-type-conversion/#to_number <https://drill.apache.org/docs/data-type-conversion/#to_number>) I suspect the join will work if you substitute the TO_CHAR with TO_NUMBER. — C > On May 28, 2018, at 12:00, Peter Edike <peter.ed...@interswitchgroup.com> > wrote: > > I GET VARCHAR > > Best regards, > Peter Edike > > Senior Software Engineer > Interswitch > > Tel. | Mobile. | IP Phone. > Fax. | mailto:peter.ed...@interswitchgroup.com | http:// > > http://www.interswitchgroup.com > > InterswitchThis e-mail and all attachments transmitted with it remain the > property of InterSwitch Limited , the information contained herein are > private confidential and intended solely for the use of the addressee. If > you have received this e-mail in error, kindly notify the sender. If you are > not the addressee, you should not disseminate, distribute or copy this > e-mail. Kindly notify InterSwitch immediately by email if you have received > this email in error and delete this email and any attachment from your system > Emails cannot be guaranteed to be secure or error free as the message and > any attachments could be intercepted, corrupted, lost, delayed, incomplete or > amended. the contents of this email or its attachments have been scanned for > all viruses and all reasonable measures have been taken to ensure that no > viruses are present. InterSwitch Limited and its subsidiaries do not accept > liability for damage caused by this email or any attachments. > > > > This message has been marked as CONFIDENTIAL on Monday, May 28, 2018 @ > 5:00:55 PM > > -----Original Message----- > From: Charles Givre <cgi...@gmail.com> > Sent: Monday, May 28, 2018 4:59 PM > To: user@drill.apache.org > Cc: Adedamola Kolade <adedamola.kol...@interswitchgroup.com> > Subject: Re: Error Joining Two Tables In Apache Drill > > What do you get when you run: > > SELECT typeof(table1.product_code) from dfs.vtucare.vw_tbl_transactions as > table1 LIMIT 1] > > > >> On May 28, 2018, at 11:54, Peter Edike <peter.ed...@interswitchgroup.com> >> wrote: >> >> Same Exception occurs when I run the queries >> >> SELECT TO_CHAR(table1.product_code, '######') from >> dfs.vtucare.vw_tbl_transactions as table1 LIMIT 10 >> >> SELECT TO_CHAR(table2.product_code, '#####’) FROM >> dfs.vtucare.vw_tbl_products AS table2 LIMIT 10 >> >> >> And yes, I changed the default storage plug in for the local >> filesystem >> >> >> >> Best regards, >> Peter Edike >> >> Senior Software Engineer >> Interswitch >> >> Tel. | Mobile. | IP Phone. >> Fax. | mailto:peter.ed...@interswitchgroup.com | http:// >> >> http://www.interswitchgroup.com >> >> InterswitchThis e-mail and all attachments transmitted with it remain the >> property of InterSwitch Limited , the information contained herein are >> private confidential and intended solely for the use of the addressee. If >> you have received this e-mail in error, kindly notify the sender. If you are >> not the addressee, you should not disseminate, distribute or copy this >> e-mail. Kindly notify InterSwitch immediately by email if you have received >> this email in error and delete this email and any attachment from your >> system Emails cannot be guaranteed to be secure or error free as the >> message and any attachments could be intercepted, corrupted, lost, delayed, >> incomplete or amended. the contents of this email or its attachments have >> been scanned for all viruses and all reasonable measures have been taken to >> ensure that no viruses are present. InterSwitch Limited and its >> subsidiaries do not accept liability for damage caused by this email or any >> attachments. >> >> >> >> This message has been marked as CONFIDENTIAL on Monday, May 28, 2018 @ >> 4:54:04 PM >> >> -----Original Message----- >> From: Charles Givre <cgi...@gmail.com> >> Sent: Monday, May 28, 2018 4:50 PM >> To: user@drill.apache.org >> Subject: Re: Error Joining Two Tables In Apache Drill >> >> Hi Peter, >> In that case, I’m a little confused. In your query, you have dfs as the >> storage plugin which is the default storage plugin for the local file >> system. Did you change that? >> >> What happens if you execute the following queries: >> SELECT TO_CHAR(table1.product_code, '######') from >> dfs.vtucare.vw_tbl_transactions as table1 LIMIT 10 >> >> SELECT TO_CHAR(table2.product_code, '#####’) FROM >> dfs.vtucare.vw_tbl_products AS table2 LIMIT 10 >> >> Do those queries produce results? >> —C >> >> >>> On May 28, 2018, at 11:43, Peter Edike <peter.ed...@interswitchgroup.com> >>> wrote: >>> >>> The query queries a no-sql database....so I don’t think there is type >>> information. However, Querying without the cast returns an empty >>> result set, but I can see matching rows when I query the two views >>> independently >>> >>> Best regards, >>> Peter Edike >>> >>> Senior Software Engineer >>> Interswitch >>> >>> Tel. | Mobile. | IP Phone. >>> Fax. | mailto:peter.ed...@interswitchgroup.com >>> <mailto:peter.ed...@interswitchgroup.com> | http:// >>> >>> http://www.interswitchgroup.com <http://www.interswitchgroup.com/> >>> >>> InterswitchThis e-mail and all attachments transmitted with it remain the >>> property of InterSwitch Limited , the information contained herein are >>> private confidential and intended solely for the use of the addressee. If >>> you have received this e-mail in error, kindly notify the sender. If you >>> are not the addressee, you should not disseminate, distribute or copy this >>> e-mail. Kindly notify InterSwitch immediately by email if you have received >>> this email in error and delete this email and any attachment from your >>> system Emails cannot be guaranteed to be secure or error free as the >>> message and any attachments could be intercepted, corrupted, lost, delayed, >>> incomplete or amended. the contents of this email or its attachments have >>> been scanned for all viruses and all reasonable measures have been taken to >>> ensure that no viruses are present. InterSwitch Limited and its >>> subsidiaries do not accept liability for damage caused by this email or any >>> attachments. >>> >>> >>> >>> This message has been marked as CONFIDENTIAL on Monday, May 28, 2018 >>> @ >>> 4:43:53 PM >>> >>> -----Original Message----- >>> From: Charles Givre <cgi...@gmail.com <mailto:cgi...@gmail.com>> >>> Sent: Monday, May 28, 2018 4:40 PM >>> To: user@drill.apache.org <mailto:user@drill.apache.org> >>> Subject: Re: Error Joining Two Tables In Apache Drill >>> >>> Hi Peter, >>> Out of curiosity, what is the native data type of the product_code field? >>> Is it really necessary to cast it to a character in the join statement? >>> You might want to try the join w/o the data type conversion. >>> >>> >>>> On May 28, 2018, at 11:33, Peter Edike <peter.ed...@interswitchgroup.com> >>>> wrote: >>>> >>>> Hallos everyone >>>> >>>> I have the following query that attempts to join the result set of >>>> two views on a common column >>>> >>>> select * >>>> from dfs.vtucare.vw_tbl_transactions as table1 join >>>> dfs.vtucare.vw_tbl_products as table2 ON >>>> TO_CHAR(table1.product_code, >>>> '######') = TO_CHAR(table2.product_code, '#####') limit 1 >>>> >>>> >>>> On Running the Query, I get the following Error >>>> >>>> Error in expression at index -1. Error: Missing function implementation: >>>> [castTINYINT(VARCHAR-OPTIONAL)]. Full expression: --UNKNOWN EXPRESSION--.. >>>> Fragment 2:0 >>>> [Error Id: 6c84fc61-c9d7-4d55-9ebd-bbbf7be84865 on >>>> BGDTEST2.INTERSWITCH.COM:31010 >>>> <http://bgdtest2.interswitch.com:31010/><http://bgdtest2.interswitch.com:31010/ >>>> <http://bgdtest2.interswitch.com:31010/>>]. >>>> at >>>> com.mapr.drill.drill.dataengine.DRQryResultListener.checkAndThrowExc >>>> e >>>> ption(Unknown Source) at >>>> com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unk >>>> n >>>> own Source) at >>>> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchDat >>>> a >>>> (Unknown Source) at >>>> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unkn >>>> o >>>> wn Source) at >>>> com.mapr.drill.drill.dataengine.DRJDBCQueryExecutor.execute(Unknown >>>> Source) at >>>> com.mapr.drill.jdbc.common.SStatement.executeNoParams(Unknown >>>> Source) at com.mapr.drill.jdbc.common.SStatement.execute(Unknown >>>> Source) at >>>> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingState >>>> m >>>> ent.java:291) at >>>> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingState >>>> m >>>> ent.java:291) at >>>> org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter. >>>> j >>>> ava:581) at >>>> org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.j >>>> a >>>> va:692) at >>>> org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOp >>>> e >>>> nInterpreter.java:97) at >>>> org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$Inter >>>> p >>>> retJob.jobRun(RemoteInterpreterServer.java:498) >>>> at org.apache.zeppelin.scheduler.Job.run(Job.java:175) >>>> at >>>> org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(Parall >>>> e >>>> lScheduler.java:162) at >>>> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:5 >>>> 1 >>>> 1) at java.util.concurrent.FutureTask.run(FutureTask.java:266) >>>> at >>>> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask. >>>> access$201(ScheduledThreadPoolExecutor.java:180) >>>> at >>>> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask. >>>> run(ScheduledThreadPoolExecutor.java:293) >>>> at >>>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor. >>>> java:1149) at >>>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecuto >>>> r >>>> .java:624) Caused by: >>>> com.mapr.drill.support.exceptions.GeneralException: >>>> [MapR][DrillJDBCDriver](500165) Query execution error. Details: SYSTEM >>>> ERROR: SchemaChangeException: Failure while trying to materialize incoming >>>> schema. Errors: >>>> Error in expression at index -1. Error: Missing function implementation: >>>> [castTINYINT(VARCHAR-OPTIONAL)]. Full expression: --UNKNOWN EXPRESSION--.. >>>> Fragment 2:0 >>>> [Error Id: 6c84fc61-c9d7-4d55-9ebd-bbbf7be84865 on >>>> BGDTEST2.INTERSWITCH.COM:31010 >>>> <http://bgdtest2.interswitch.com:31010/><http://bgdtest2.interswitch.com:31010/ >>>> <http://bgdtest2.interswitch.com:31010/>>]. >>>> ... 21 more >>>> >>>> >>>> >>>> Please what am I doing wrong >>>> >>>> Kind Regards >>>> >>>> Peter Edike >>>> Senior Software Engineer >>>> Research and Development >>>> Group Shared Technology >>>> >>>> Office NO: >>>> Mobile NO: >>>> Email: peter.ed...@interswitchgroup.com >>>> <mailto:peter.ed...@interswitchgroup.com> >>>> <mailto:peter.ed...@interswitchgroup.com >>>> <mailto:peter.ed...@interswitchgroup.com>> >>>> Interswitch Limited >>>> 1648C Oko-Awo Street, Victoria Island Lagos Customer Contact Centre >>>> 0700-9065000 ü http://www.interswitchgroup.com >>>> <http://www.interswitchgroup.com/> <http://www.interswitchgroup.com/ >>>> <http://www.interswitchgroup.com/>> >>>> <https://www.quickteller.com/delight/ >>>> <https://www.quickteller.com/delight/>> >>>> This e-mail and all attachments transmitted with it remain the >>>> property of Interswitch Limited , the information contained herein >>>> are private confidential and intended solely for the use of the >>>> addressee. If you have received this e-mail in error, kindly notify >>>> the sender. If you are not the addressee, you should not >>>> disseminate, distribute or copy this e-mail. Kindly notify >>>> Interswitch immediately by email if you have received this email in >>>> error and delete this email and any attachment from your system >>>> Emails cannot be guaranteed to be secure or error free as the >>>> message and any attachments could be intercepted, corrupted, lost, >>>> delayed, incomplete or amended. the contents of this email or its >>>> attachments have been scanned for all viruses and all reasonable >>>> measures have been taken to ensure that no viruses are present. >>>> Interswitch Limited and its subsidiaries do not accept liability for >>>> damage caused by this email or any attachments.This message has been >>>> marked as CONFIDENTIAL on Monday, May 28, 2018 @ 4:33:42 PM >> >
