Hi Sadegh, If I recall well the behavior of the string concatenation is defined by the SQL standard (ISO/IEC 9075).
Section 6.29: General rules 2.b.i: If at least one of S1 and S2 is the null value, then the result of the <concatenation> is the null value. Hive as well as many other DBMS systems strive to remain as close to the standard as possible. Best, Stamatis On Sun, May 18, 2025 at 3:36 PM Sadegh Abedi <sadeghab...@live.com> wrote: > > Dear Hive User Group, > > I hope this email finds you well. > > I’m writing to inquire about the behavior of string concatenation with NULL > values in Hive. I’ve observed that when concatenating a string with a NULL > value using the || operator or concat function, Hive returns NULL. > > For example: > ```sql > beeline> select “a” || null; > ±------+ > | _c0 | > ±------+ > | NULL | > ±------+ > ``` > My understanding is that in some other database systems, such as Oracle or > PostgreSQL, a similar operation (e.g., `"a" || NULL` or `'a' || NULL`) would > typically return the non-`NULL` string itself (i.e., "a"), effectively > treating the `NULL` as an empty string or ignoring it for the concatenation. > > I'm curious to understand the rationale behind Hive's approach to propagate > `NULL` in this scenario. While I understand that `NULL` often means "unknown" > and operations involving it can result in `NULL`, I find that in the context > of string building, the behavior of ignoring `NULL`s (as seen in other > RDBMSs) can sometimes be more intuitive and convenient, potentially reducing > the need for explicit `COALESCE` calls. > > From my perspective, it seems that if Hive were to ignore `NULL` values in > `||` concatenation (similar to Oracle/Postgres), it might lead to more > straightforward query writing in certain scenarios, particularly when dealing > with potentially `NULL`able columns that need to be combined. > > Could you please shed some light on why this design choice was made for Hive? > Are there specific advantages or standards that Hive is adhering to with this > behavior? > > Thank you for your time and insights. I appreciate the work done by the Hive > community and would be happy to contribute to any discussions or > documentation if needed. > > Best regards, > Sadegh Abedi
