Can you log a Jira case for this? I think you should make your rule work for N-way Union, not just 2-way Union. And I think you should make it work whether or not a Project is present.
> On Jan 19, 2022, at 1:25 PM, Julian Hyde <jhyde.apa...@gmail.com> wrote: > > It sounds useful. > > What do you think the rule should be called? UnionFilterTransposeRule, > perhaps? > > A challenge when writing the rule will be to ensure that all of the inputs to > the Union are the same. The Volcano framework is not very good at that. > > You should be careful of the case that the conditions overlap. For example, > the rewrite > > SELECT * FROM Emp WHERE deptno < 30 > UNION ALL > SELECT * FROM Emp WHERE deptno IN (25, 35, 45) > > to > > SELECT * FROM Emp WHERE deptno < 30 OR deptno IN (25, 35, 45) > > Is not valid, because rows with deptno = 25 will appear twice in the first > query, once in the second. Maybe that problem does not occur when applied to > UNION than when applied to UNION ALL. > > There would seem to be analogous rules for INTERSECT (combine the conditions > using AND) and EXCEPT (combine the conditions using AND NOT). Perhaps one > rule could cover all set operations (see FilterSetOpTransposeRule). > > Julian > > > >> On Jan 19, 2022, at 2:38 AM, Yanjing Wang <zhuangzixiao...@gmail.com >> <mailto:zhuangzixiao...@gmail.com>> wrote: >> >> A simple example is converting SELECT a, b FROM t WHERE c = 1 UNION ALL >> SELECT a, b FROM t WHERE c = 2 to SELECT a, b FROM t WHERE c in (1, 2) >> >> Yanjing Wang <zhuangzixiao...@gmail.com <mailto:zhuangzixiao...@gmail.com>> >> 于2022年1月19日周三 18:35写道： >> Hi, community >> >> Here I recommend a new rule for converting UNION ALL sub plan to a single >> input with an OR filter, the following is its conversion diagram. >> >> >> The conversion prerequisites are >> 1. left filter range has no intersection with right filter range. >> 2. Project and Input Sub Tree must be identical. >> >> The rule will be used when Input Sub Tree is a computing-intensive or large >> IO operation. >> >> I don't know whether the community supports it or not, any suggestions will >> be appreciated. >> >> >
