Invalid format: "null"

[wyu...@gmail.com]

Hi

I use presto 0.112 + jdk1.8.0_45 + HDP 2.1 + hive connector + CentOS 6.5.

I see the strange result.

For example, aaa table contains col = 'null' data and I execute the following query with presto cli.

SELECT parse_datetime(col, 'YYYY-MM-dd') FROM aaa

The result is the following. I think that it is correct. But sometimes result returns without error, so I doubt that this is a bug.

com.facebook.presto.spi.PrestoException: Invalid format: "null"
at com.facebook.presto.operator.scalar.DateTimeFunctions.parseDateTimeHelper(DateTimeFunctions.java:475)
at com.facebook.presto.operator.scalar.DateTimeFunctions.parseDatetime(DateTimeFunctions.java:466)
at com_facebook_presto_$gen_PageProcessor_2607.project_0(Unknown Source)
at com_facebook_presto_$gen_PageProcessor_2607.process(Unknown Source)
at com.facebook.presto.operator.ScanFilterAndProjectOperator.getOutput(ScanFilterAndProjectOperator.java:232)
at com.facebook.presto.operator.Driver.processInternal(Driver.java:380)
at com.facebook.presto.operator.Driver.processFor(Driver.java:303)
at com.facebook.presto.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:587)
at com.facebook.presto.execution.TaskExecutor$PrioritizedSplitRunner.process(TaskExecutor.java:505)
at com.facebook.presto.execution.TaskExecutor$Runner.run(TaskExecutor.java:639)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.IllegalArgumentException: Invalid format: "null"
at org.joda.time.format.DateTimeFormatter.parseDateTime(DateTimeFormatter.java:899)
at com.facebook.presto.operator.scalar.DateTimeFunctions.parseDateTimeHelper(DateTimeFunctions.java:472)
... 12 more

Thanks

Regards
Wataru Yukawa

[Dain Sundstrom]

My guess is your input table contains varchar containing the literal value “null”. The following reproduces the stack for me:

presto:local_gold> select parse_datetime('null', 'yyyy-mm-dd’);

Query 20150731_064652_07711_y9qv6 failed: Invalid format: "null"

com.facebook.presto.spi.PrestoException: Invalid format: "null"
at com.facebook.presto.operator.scalar.DateTimeFunctions.parseDateTimeHelper(DateTimeFunctions.java:475)
at com.facebook.presto.operator.scalar.DateTimeFunctions.parseDatetime(DateTimeFunctions.java:466)

Caused by: java.lang.IllegalArgumentException: Invalid format: "null"
at org.joda.time.format.DateTimeFormatter.parseDateTime(DateTimeFormatter.java:899)
at com.facebook.presto.operator.scalar.DateTimeFunctions.parseDateTimeHelper(DateTimeFunctions.java:472)

... 58 more

-dain

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

[Wataru Yukawa]

Thank you for reply.

If I execute the following query, Invalid format error "always" occur. I understand that it is correct.

select parse_datetime('null', 'yyyy-mm-dd');

But, if I execute query to the table that contains 'null' data, "sometimes" error doesn't occur.

This is a strange result.

for example, the following two step is the same, but result is not same.

presto:default> select parse_datetime(col, 'YYYY-MM-dd') FROM aaa;

Query 20150731_080312_06001_u769t, FAILED, 8 nodes

Splits: 8 total, 6 done (75.00%)

0:00 [538 rows, 0B] [30.5K rows/s, 0B/s]

Query 20150731_080312_06001_u769t failed: Invalid format: "null"

presto:default> select parse_datetime(col, 'YYYY-MM-dd') FROM aaa;

               _col0                

------------------------------------

 1990-01-01 00:00:00.000 Asia/Tokyo 

 1990-01-02 00:00:00.000 Asia/Tokyo

 1990-01-03 00:00:00.000 Asia/Tokyo 

...

Thanks

Regards
Wataru Yukawa

You received this message because you are subscribed to a topic in the Google Groups "Presto" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/presto-users/lt3OWW53I0k/unsubscribe.
To unsubscribe from this group and all its topics, send an email to presto-users...@googlegroups.com.

[袁安峰]

What is your datasource? Is it always changing?

在 2015年7月31日星期五 UTC+8下午4:12:30，Wataru Yukawa写道：

[Wataru Yukawa]

I use hive connector