title: Hive Coalesce function isn't returning expected results after performing concat_ws and and collect_list
body: <p>I need to pivot column servicename in table services and then coalesce values.</p>
  
  <pre><code>services:
  userid servicename
  1      A
  2      B
  2      C
  3      B
  1      C
  4
  5      C
  </code></pre>
  
  <p>The query I am trying is </p>
  
  <pre><code>SELECT userid,
         COALESCE(a,b,c) AS servicename
  FROM (SELECT userid,
               concat_ws('',a) AS a,
               concat_ws('',b) AS b,
               concat_ws('',c) AS c
        FROM (SELECT userid,
                     collect_list(a.group_map['A']) AS a,
                     collect_list(a.group_map['B']) AS b,
                     collect_list(a.group_map['C']) AS c
              FROM (SELECT userid,
                           MAP(servicename,servicename) AS group_map
                    FROM (SELECT userid, servicename, servicename FROM services) AS table1) AS table2) AS table3) AS table4
  </code></pre>
  
  <p>And here is the breakdown of each <code>SELECT</code> query with it's result:</p>
  
  <p>Query 1:</p>
  
  <pre><code>SELECT userid,
         servicename,
         servicename
  FROM services
  </code></pre>
  
  <p>Result 1:</p>
  
  <pre><code>userid servicename servicename
     1      A           A
     2      B           B
     2      C           C
     3      B           B
     1      C           C
     4 
     5      C           C 
  </code></pre>
  
  <p>Query 2:</p>
  
  <pre><code>SELECT userid,
         MAP(servicename,servicename) AS group_map
  FROM table1
  </code></pre>
  
  <p>Result 2:</p>
  
  <pre><code>userid    group_map
      1      {"A":"A"}
      2      {"B":"B"}
      2      {"C":"C"}
      3      {"B":"B"}
      1      {"C":"C"}
      4      {"null":"null"}
      5      {"C":"C"}
  </code></pre>
  
  <p>Query 3:</p>
  
  <pre><code>SELECT userid,
         collect_list(a.group_map['A']) AS a,
         collect_list(a.group_map['B']) AS b,
         collect_list(a.group_map['C']) AS c
  FROM table2
  </code></pre>
  
  <p>Result 3:</p>
  
  <pre><code>userid    a      b      c
  1       ["A"]         ["C"]
  2              ["B"]  ["C"]
  3              ["B"]
  4           
  5                     ["C"]
  </code></pre>
  
  <p>The following concat_ws function is to convert array to String</p>
  
  <p>Query 4:</p>
  
  <pre><code>SELECT userid,
         concat_ws('',a) AS a,
         concat_ws('',b) AS b,
         concat_ws('',c) AS c
  FROM table3
  </code></pre>
  
  <p>Result 4:</p>
  
  <pre><code>userid    a    b   c
  1         A        C
  2              B   C
  3              B
  4           
  5                  C
  </code></pre>
  
  <p>And in the end using COALESCE function in the query to get results as below:</p>
  
  <p>Query 5</p>
  
  <pre><code>SELECT userid,
         COALESCE(a,b,c) AS servicename
  FROM table4
  </code></pre>
  
  <p>And below is the result which I expect to see</p>
  
  <p>Result 5:</p>
  
  <pre><code>userid  servicename
  1       A
  2       B
  3       B
  4
  5       C
  </code></pre>
  
  <p>However, I get only <code>Service A</code> or null like below:</p>
  
  <pre><code>userid  servicename
      1       A
      2       
      3       
      4
      5       
  </code></pre>
  
  <p>Does anyone suspect any issue? Is there casting required? </p>
  
  <p>I have been trying different things and tried to search on web as well, but no luck yet. </p>
  
  <p>Thanks in advance!</p>