question Impala bug with nested arrays of structures where some of the fields contains null in Support Questions

question Impala bug with nested arrays of structures where some of the fields contains null in Support Questions https://community.cloudera.com/t5/Support-Questions/Impala-bug-with-nested-arrays-of-structures-where-some-of/m-p/78507#M12592 <DIV>Hi All,</DIV><DIV> </DIV><DIV>We found a case where Impala returns incorrect values from simple query. Our data contains nested array of structures and structures contains other structures.</DIV><DIV>We generated minimal sample data allowing to reproduce the issue.</DIV><DIV> </DIV><DIV>SQL to create a table:</DIV><DIV><DIV><FONT face="monospace, monospace">CREATE TABLE plat_test.test_users (</FONT></DIV><DIV><FONT face="monospace, monospace">  id INT,</FONT></DIV><DIV><FONT face="monospace, monospace">  name STRING,   </FONT></DIV><DIV><FONT face="monospace, monospace">  devices ARRAY<</FONT></DIV><DIV><FONT face="monospace, monospace">    STRUCT<</FONT></DIV><DIV><FONT face="monospace, monospace">      id:STRING,</FONT></DIV><DIV><FONT face="monospace, monospace">      device_info:STRUCT<</FONT></DIV><DIV><FONT face="monospace, monospace">        model:STRING</FONT></DIV><DIV><FONT face="monospace, monospace">      ></FONT></DIV><DIV><FONT face="monospace, monospace">    ></FONT></DIV><DIV><FONT face="monospace, monospace">  ></FONT></DIV><DIV><FONT face="monospace, monospace">)</FONT></DIV><DIV><FONT face="monospace, monospace">STORED AS PARQUET</FONT></DIV></DIV><DIV> </DIV><DIV>Please put attached parquet file to the location of the table and refresh the table.</DIV><DIV>In sample data we have 2 users, one with 2 devices, second one with 3. Some of the devices.device_info.model fields are NULL.</DIV><DIV> </DIV><DIV>When I issue a query:</DIV><DIV><DIV><FONT face="monospace, monospace">SELECT<SPAN> </SPAN><A href="http://u.name/" target="_blank">u.name</A>, d.device_info.model as model</FONT></DIV><DIV><FONT face="monospace, monospace">FROM test_users u,</FONT></DIV><DIV><FONT face="monospace, monospace">u.devices d;</FONT></DIV></DIV><DIV> </DIV><DIV>I'm expecting to get 5 records in results, but getting only one<span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="1.png" style="width: 545px;"><img src="https://community.cloudera.com/t5/image/serverpage/image-id/4761iE915712E88965565/image-size/large?v=v2&px=999" role="button" title="1.png" alt="1.png" /></span></DIV><DIV><DIV><DIV>If I change query to:</DIV><DIV><FONT face="monospace, monospace">SELECT<SPAN> </SPAN><A href="http://u.name/" target="_blank">u.name</A>, d.device_info.model as model</FONT></DIV><DIV><FONT face="monospace, monospace">FROM test_users u</FONT></DIV><DIV><FONT face="monospace, monospace">LEFT OUTER JOIN u.devices d;</FONT></DIV></DIV><DIV> </DIV><DIV>I'm getting two records in the results, but still not as it should be:</DIV></DIV><DIV> </DIV><DIV><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="2.png" style="width: 553px;"><img src="https://community.cloudera.com/t5/image/serverpage/image-id/4762iBFC7AE9CA2964983/image-size/large?v=v2&px=999" role="button" title="2.png" alt="2.png" /></span></DIV><DIV><DIV>We found some workaround to this problem. If we add to the result columns<SPAN> </SPAN><A href="http://device.id/" target="_blank">device.id</A><SPAN> </SPAN>we will get all records from parquet file:</DIV><DIV><DIV><FONT face="monospace, monospace">SELECT<SPAN> </SPAN><A href="http://u.name/" target="_blank">u.name</A>,<SPAN> </SPAN><A href="http://d.id/" target="_blank">d.id</A>, d.device_info.model as model</FONT></DIV><DIV><FONT face="monospace, monospace">FROM test_users u</FONT></DIV><DIV><FONT face="monospace, monospace">, u.devices d</FONT></DIV></DIV><DIV> </DIV><DIV>And result is</DIV></DIV><DIV> </DIV><DIV><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="3.png" style="width: 539px;"><img src="https://community.cloudera.com/t5/image/serverpage/image-id/4764i32CE6F19446C7014/image-size/large?v=v2&px=999" role="button" title="3.png" alt="3.png" /></span></DIV><DIV> </DIV><DIV><DIV>But we can't rely on this workaround, because we don't need<SPAN> </SPAN><A href="http://device.id/" target="_blank">device.id</A><SPAN> </SPAN>in all queries and Impala optimizes it, and as a result we are getting unpredicted results.</DIV><DIV> </DIV><DIV>I tested Hive query on this table and it returns expected results:</DIV><DIV><DIV><FONT face="monospace, monospace">SELECT<SPAN> </SPAN><A href="http://u.name/" target="_blank">u.name</A>, d.device_info.model</FONT></DIV><DIV><FONT face="monospace, monospace">FROM test_users u</FONT></DIV><DIV><FONT face="monospace, monospace">lateral view outer inline (u.devices) d;</FONT></DIV></DIV><DIV> </DIV><DIV>results:</DIV><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="4.png" style="width: 556px;"><img src="https://community.cloudera.com/t5/image/serverpage/image-id/4763i54304DFF2616F74A/image-size/large?v=v2&px=999" role="button" title="4.png" alt="4.png" /></span></DIV><DIV><DIV>Please advice if it's a problem in Impala engine or we did some mistake in our query.</DIV><DIV> </DIV><DIV><A title="test_users_131786401297925138_0.parquet" href="https://drive.google.com/file/d/1ApPLVJpqebe8puuQZvU3m6857knIDq1L/view?usp=sharing" target="_blank">test_users_131786401297925138_0.parquet</A></DIV><DIV> </DIV><DIV>Best regards,</DIV><DIV>Come2Play team.</DIV></DIV> Fri, 16 Sep 2022 13:35:20 GMT Yurii 2022-09-16T13:35:20Z