Presto自定义函数@SqlNullable血泪史

看到标题咱们会想到是因为@SqlNullable注解引发的问题，咱们先看一段代码，正是这段有意思的代码，让我纠结了2个多小时，引发了Presto的问题。

@Description("user_id")
@ScalarFunction("user_id")
@SqlType(StandardTypes.VARCHAR)
public static Slice userId(@SqlType(StandardTypes.VARCHAR) Slice value) {
    String _value = value.toStringUtf8();
    if (StringUtils.containsWhitespace(_value)) {
        _value = StringUtils.replace(_value, " ", "+");
    } 
    return Slices.utf8Slice(makeErrorMsgBase64(_value));
}

这段代码很简单，就是咱们将传递进来的base64的字符串解码成实践的字符串，单单从代码上看是不会有什么问题的。当咱们实践运转这个函数的时分问题就呈现了，以下是一个运用示例：

select user_id(str) from temp.users limit 100;

看履行的SQL好像也没有什么异常，但就是这么简简单单的一个函数引发了Presto的问题，那就是java.lang.NullPointerException: undefined过错，这个过错的具体内容如下

java.lang.NullPointerException: undefined
	at io.prestosql.type.VarcharOperators.equal(VarcharOperators.java:53)
	at io.prestosql.$gen.CursorProcessor_20200927_063218_2398.filter(Unknown Source)
	at io.prestosql.$gen.CursorProcessor_20200927_063218_2398.process(Unknown Source)
	at io.prestosql.operator.ScanFilterAndProjectOperator$RecordCursorToPages.process(ScanFilterAndProjectOperator.java:323)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorUtils.getNextState(WorkProcessorUtils.java:221)
	at io.prestosql.operator.WorkProcessorUtils$YieldingProcess.process(WorkProcessorUtils.java:181)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorUtils.getNextState(WorkProcessorUtils.java:221)
	at io.prestosql.operator.WorkProcessorUtils.lambda$processStateMonitor$2(WorkProcessorUtils.java:200)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorUtils.lambda$flatten$6(WorkProcessorUtils.java:277)
	at io.prestosql.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:319)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:306)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorUtils.getNextState(WorkProcessorUtils.java:221)
	at io.prestosql.operator.WorkProcessorUtils.lambda$processStateMonitor$2(WorkProcessorUtils.java:200)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorUtils.getNextState(WorkProcessorUtils.java:221)
	at io.prestosql.operator.WorkProcessorUtils.lambda$finishWhen$3(WorkProcessorUtils.java:215)
	at io.prestosql.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:372)
	at io.prestosql.operator.WorkProcessorSourceOperatorAdapter.getOutput(WorkProcessorSourceOperatorAdapter.java:149)
	at io.prestosql.operator.Driver.processInternal(Driver.java:379)
	at io.prestosql.operator.Driver.lambda$processFor$8(Driver.java:283)
	at io.prestosql.operator.Driver.tryWithLock(Driver.java:675)
	at io.prestosql.operator.Driver.processFor(Driver.java:276)
	at io.prestosql.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:1076)
	at io.prestosql.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:shichengoooo@163.com)
	at io.prestosql.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:484)
	at io.prestosql.$gen.Presto_341____20200925_110330_2.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)

通过上面过错咱们推算出应该是因为数据导致空指针异常，那么问题出在哪里呢？问题就出在查询str这个字段中，这个字段咱们经过实践的查询发现是有”的数据，在做转化的时分呈现了空指针问题，所以咱们修改UDF的源码为

@Description("user_id")
@ScalarFunction("user_id")
@SqlType(StandardTypes.VARCHAR)
@SqlNullable
public static Slice userId(@SqlNullable @SqlType(StandardTypes.VARCHAR) Slice value) {
    String _value = value.toStringUtf8();
    if (StringUtils.containsWhitespace(_value)) {
        _value = StringUtils.replace(_value, " ", "+");
    } 
    return Slices.utf8Slice(makeErrorMsgBase64(_value));
}

咱们在办法和参数上添加了@SqlNullable注解用于符号此函数可以接纳空的数据，这好像看着也没有问题，咱们将该函数从头发布，再次履行SQL发现还存在相同的问题，所以又将代码修改为以下内容

@Description("user_id")
@ScalarFunction("user_id")
@SqlType(StandardTypes.VARCHAR)
public static Slice userId(@SqlNullable @SqlType(StandardTypes.VARCHAR) Slice value) {
    String _value = value.toStringUtf8();
    if (StringUtils.containsWhitespace(_value)) {
        _value = StringUtils.replace(_value, " ", "+");
    } 
    return Slices.utf8Slice(makeErrorMsgBase64(_value));
}

删除了办法上的@SqlNullable注解，再次运转发现不会再呈现这个过错，但是Presto服务中不断的报出空指针过错，仅仅不在反馈给查询客户端，本来以为此问题已经解决，然而更有意思的工作发生了，咱们运用了343版别测试成功后，线上版别是341，升级线上后发现此问题再次复现，如果再次在办法上加上@SqlNullable注解在341版别上又会修正这个问题，现在已将这个问题反馈给官方，推荐大家运用343版别！