@Christian EL HAKIM, thanks for reporting this. First of all, it seems there is indeed a problem in your environment, since plan is not optimized by CBO as indicated in the explain. If you explore the log files of HS2, you should see the actual reason why CBO is not optimizing the query. Concerning the mismatch between number of rows in statistics and actual results, the statistics only represent an estimate of the actual data coming out of the operators. Thus, there is nothing wrong there. In addition, I tried running query2 with CBO in my environment and it seems that for your specific example, CBO is not pushing the predicates either. I have created HIVE-17668 and I have a fix for that (this will be part of HDP-2.6.x). However, note that in your example query2, predicates are not being pushed through windowing functions: in fact, columns created by windowing functions are pruned out from the query since they are not used by the top-outer query, and then filter predicates can be pushed down. That is why if your top-outer query is _select *..._ (query3), Hive and SQLServer do not push the predicates down, since this would create wrong results (e.g. the result coming out of ROW_NUMBER would be different if the filter predicate is pushed). Concerning the semantics of query4, it seems instead of using ROW_NUMBER + filter to get first row, you could use FIRST_VALUE.
... View more
