zhouqingqing / qpmodel

A Relational Optimizer and Executor
MIT License
66 stars 18 forks source link

enable optimize_.enable_subquery_unnest_ = false #270

Closed 9DemonFox closed 3 years ago

9DemonFox commented 3 years ago

The SQL below will trigger this bug

  sql = "select a2/2, count(*) from (select a2 from a where exists (select * from a b where b.a3>=a.a1+b.a1+1) or a2>2) b group by a2/2;";
                TU.ExecuteSQL(sql, "0,1;1,2", out phyplan, option);

because it will remove from and not let it be added to subQueries, i.e., option.optimize_.removefrom and option.optimize_.enable_subqueryunnest are not indepwndent

                    var plan = qref.query_.CreatePlan();
**                    if (qref is FromQueryRef && queryOpt_.optimize_.remove_from_)
**                        from = plan;
                    else
                    {
                        string alias = null;
                        if (qref is CTEQueryRef cq)
                            alias = cq.alias_;
                        else if (qref is FromQueryRef fq)
                            alias = fq.alias_;
                        var key = new NamedQuery(qref.query_, alias);
                        from = new LogicFromQuery(qref, plan);
                        subQueries_.Add(key);

                        // if from CTE, then it could be duplicates
                        if (!fromQueries_.ContainsKey(key))
                            fromQueries_.Add(key, from as LogicFromQuery);
                    }