Hibernate concat_ws and null fielnds

Question

Im using CONCAT_WS within hibernate query, to create a giant string and search by all fields using like '%value%' . It works fine, but for some records some fields are null. e.g if actId is null, my whole concat_ws returns null. I don't know why, concat_ws must ignore null values. May be it's because hibernate trying to call getActNumber from null? anyway I'm trying hard to resolve this problem.

  where CONCAT_WS("_", actItemId.actId.actNumber, DATE_FORMAT(recordDate, '%d.%m.%Y'), actItemId.techniqueId.name, fzkActNumber, ....etc) like '%value%'

thanks!

score 0 · Answer 1 · answered Mar 20 '18 at 12:03

0

CONCAT_WS() is functioning as designed.

If you want it to work even when some args are NULL, do something like this to convert each possibly-NULL arg to a space.

CONCAT_WS('_',  IFNULL(cola,''), IFNULL(colb,'') ...)

answered Mar 20 '18 at 12:03

O. Jones

103,626
17
118
172

I tryed IFNULL, CASE when then IF(condition?, true, false) nothing works. I guess somehow I need to check whether actId is null or not before getting actNumber. – Sergey Dvoreckih Mar 23 '18 at 06:38

score 0 · Accepted Answer · answered Mar 27 '18 at 05:42

The reason was implicit usage of INNER JOINs in my query.

It does not select any parent rows without associated child. Solution was LEFT JOINs

select distinct(t) from InvBook as t                            +
                  "left join t.actItemId as actItem          "  +
                  "left join t.actItemId.actId as act        "  +

etc...

even if actItemId or actId is null now, CONCAT_WS ignores it and glues other fields together.

Thanks to Vlad Mihalcea https://discourse.hibernate.org/t/concat-ws-like-value/428

Hibernate concat_ws and null fielnds

2 Answers2