Unable to filter DATE type data through lingual shell

[santlal gupta]

Hi,

I am not able to filter DATE data type in where clause. I had tried below query, but getting exceptions.

To register schema, stereotype and table, i had use below commands:

lingual catalog --schema logP --add

lingual catalog --schema logP --stereotype logP -add --columns f1,f2,f3 --types string,double,date

lingual catalog  --schema logP --table logP --stereotype logP -add /user/hduser/lingualdata.csv 

Query : 

1. select * from "logP"."logP" where "f3" > '2001-03-05';

    2016-05-31 15:41:39,963 ERROR [main] jdbc.LingualStatement (LingualStatement.java:handleThrowable(217)) - failed with: From line 1, column 35 to line 1, column 51 (     Cannot apply '>' to arguments of type '<DATE> > <CHAR(10)>'. Supported form(s): '<COMPARABLE_TYPE> > <COMPARABLE_TYPE>') 

    Error: From line 1, column 35 to line 1, column 51: "Cannot apply '>' to arguments of type '<DATE> > <CHAR(10)>'. Supported form(s): '<COMPARABLE_TYPE> >             <COMPARABLE_TYPE>'" (state=,code=0

2. select * from "logP"."logP" where "f3" = CURRENT_DATE;

   2016-05-31 15:43:23,436 INFO [IPC Server handler 4 on 51812] org.apache.hadoop.mapred.TaskAttemptListenerImpl: Diagnostics report from         

   attempt_1463647781439_0354_m_000000_0: Error: cascading.operation.OperationException: could not compile script: {

   return !cascading.lingual.optiq.Functions.falseIfNull(f3 != null && f3.intValue() == (Integer) net.hydromatic.optiq.runtime.SqlFunctions.currentDate(root));}

   Caused by: cascading.thirdparty.org.codehaus.commons.compiler.CompileException: Line 2, Column 156: Unknown variable or type "root"

3. select * from "logP"."logP" where "f3"= cast('2000-02-02' as Date);

   Caused by: java.lang.NumberFormatException: For input string: "2000-02-02"

        at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)

        at java.lang.Integer.parseInt(Integer.java:492)

        at java.lang.Integer.parseInt(Integer.java:527)

        at net.hydromatic.optiq.runtime.SqlFunctions.toInt(SqlFunctions.java:872)

  2016-05-31 15:57:17,578 ERROR [main] cascading.flow.stream.SourceStage: caught throwable

  cascading.operation.OperationException: could not evaluate expression: {

  return !cascading.lingual.optiq.Functions.falseIfNull(f3 != null && f3.intValue() == (Integer) net.hydromatic.optiq.runtime.SqlFunctions.toInt("2000-02-02")); }

 I have attached input file for your reference.

I am below version:

Lingual      : 1.2.1

Cascading  : 3.0.1

Hadoop       : 2.6.0

Thanks

Santlal

[santlal gupta]

Hi,

while running query (select * from "logP"."logP" where "f3"= cast('2000-02-02' as Date) ), i found as follows:

     1.  '2000-02-02' was treated as CHAR.

So i am not able to link as when i am casting '2000-02-02' as Date then it should be treated as DATE, why it was treated as CHAR.

so any one can give an example how to convert  '2000-02-02' into date that i can use to compare two date?

I also found that when two columns are date then i can compare it

example : (f2,f3 are date)

select * from "avroSchema"."debugTable" where "f2" > "f3"

Thanks

Santlal

     

[Andre Kelpe]

The syntax to use is

select foo from bar where field > date '2016-06-09';

That should work.

- André

> --
> You received this message because you are subscribed to the Google Groups
> "Lingual User" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to lingual-user...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



--
André Kelpe
an...@concurrentinc.com
http://concurrentinc.com

[santlal gupta]

Thanks Andre. It works.