[GitHub] [incubator-doris] EmmyMiao87 edited a comment on issue #8191: [Bug] Select sql cause an NullPointerException on Fe

[GitBox]

EmmyMiao87 edited a comment on issue #8191:
URL: 
https://github.com/apache/incubator-doris/issues/8191#issuecomment-1050662479


   At present, it is found that if the cte of the last layer of query is 
directly taken out, the problem of this null pointer can be solved.
   For example,
   ```
   with cte as (xxx)
   select * from cte;
   ```
   The declaration of this CTE is completely unnecessary and can be changed 
directly to
   ```
   select * from xxx;
   ```
   You can avoid this problem.
   
   The following query can resolve this issue ~
   ```
   WITH basic_data as
   (
   SELECT *
   FROM dwd_srv_solutionprocess_dd limit 100
   ),
   unresolved_12 as(
   SELECT * ,'非一次性解决' as '解决情况'
   FROM basic_data
   where (new_uncompletereason !='' OR new_complete_reason_id !='' OR 
new_process_of like '%未解决%'
   OR (new_formtype = 1 AND new_timeoutreason1 !=''))
   OR (new_suggestmemo = '否' OR new_suggestmemo = 2)
   ),
   id_34 as
   (
   SELECT new_srv_workorder_id as unresolved_id
   FROM ods_new_srv_outsidelineBase_dd ol
   WHERE ol.statecode =0 and ol.new_iscompanion = 0 AND ol.new_statecode NOT IN 
( 7, 8, 9 )
   group by new_srv_workorder_id
   having count(1) >=2
   union
   SELECT distinct UPPER(new_relationid) as unresolved_id
   FROM ods_new_srv_surveyresultBase_dd s
   WHERE statecode = 0 and new_subject_name = '故障是不是一次性解决？'
   AND new_answer != '' AND new_answer is not null AND new_answer != '一次性解决'
   ),
   unresolved as
   (
   select * from unresolved_12
   union
   SELECT bd.* ,'非一次性解决' as '解决情况'
   FROM basic_data bd
   join id_34 on bd.workorderId = id_34.unresolved_id
   )
   SELECT *, '一次性解决' as '解决情况'
   FROM basic_data b
   WHERE workorderId not in (select workorderId from unresolved);
   ```
   The main change point is to remove the cte of the ```solved``` layer
   origin stmt:
   ```
   xxxxx
   xxxxx
   solved as
   (
   SELECT *, '一次性解决' as '解决情况'
   FROM basic_data b
   WHERE workorderId not in (select workorderId from unresolved)
   )
   select * from solved;
   ```
   rewritten stmt:
   ```
   xxxxx
   SELECT *, '一次性解决' as '解决情况'
   FROM basic_data b
   WHERE workorderId not in (select workorderId from unresolved);
   ```
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org