Beautifier still locks up on UNION statements

[Clouds]

Hi there,

The beautifier is still not able to handle queries with UNION statements.
When there are a couple of union statements, the beautifier takes way too much time.

In cases where quite some UNIONS are used (let's say 25) the beautifier apparently locks up for hours (I don't wait that long; I killed the process after 90 minutes).

This problem has been around for quite some time now...is it going to be solved soon?

Best regards!

 

[jlcox]

Same issue here, sometimes I forget about this and hit the beautifier button on a package containing multiple unions in a query and have to end up killing the application.

 

[Marco Kalter]

Can you send me a source file to reproduce this?

 

[jlcox]

Here's an extreme example, but it's an actual example, with the field names obfuscated. This will bring PLS to its knees if you hit the beautifier button:

select sales_date eom_dt,
       sum(abcd) abcd,
       sum(bcde) bcde,
       sum(cdef) cdef,
       sum(defg) defg,
       sum(efgh) efgh,
       sum(fghi) fghi,
       sum(ghij) ghij,
       sum(hijk) hijk,
       sum(ijkl) ijkl,
       sum(jklm) jklm,
       sum(klmn) klmn,
       sum(lmno) lmno,
       sum(mnop) mnop,
       sum(nopq) nopq,
       sum(opqr) opqr,
       sum(pqrs) pqrs
  from (select sales_date, /*abcd*/
               actual_value abcd,
               0 bcde,
               0 cdef,
               0 can_syn,
               0 defg,
               0 efgh,
               0 fghi,
               0 ghij,
               0 hijk,
               0 ijkl,
               0 jklm,
               0 klmn,
               0 lmno,
               0 mnop,
               0 nopq,
               0 opqr,
               0 pqrs
          from table1 t
         where t.sales_account = 'Z54'
           and t.account_number = 98700001
        union all
        select sales_date, /*bcde*/
               0,
               actual_value,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0
          from table1 t
         where t.sales_account = 'Z55'
           and t.account_number = 98700002
        union all
        select sales_date, /*cdef*/
               0,
               0,
               actual_value,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0
          from table1 t
         where t.sales_account = 'Z54'
           and t.account_number = 98700005
        union all
        select sales_date, /*can_syn*/
               0,
               0,
               0,
               actual_value,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0
          from table1 t
         where t.sales_account = 'Z55'
           and t.account_number = 98700402
        union all
        select sales_date,/*defg*/
               0,
               0,
               0,
               0,
               actual_value,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0
          from table1 t
         where t.sales_account = 'Z54'
           and t.account_number = 98700081
        union all
        select sales_date, /*efgh*/
               0,
               0,
               0,
               0,
               0,
               actual_value,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0
          from table1 t
         where t.sales_account = 'Z55'
           and t.account_number = 98700098
        union all
        select sales_date, /*fghi*/
               0,
               0,
               0,
               0,
               0,
               0,
               actual_value,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0
          from table1 t
         where t.sales_account = 'N221'
           and t.account_number = 98700002
        union all
        select sales_date, /*ghij*/
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               actual_value,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0
          from table1 t
         where t.sales_account = 'Y32'
           and t.account_number = 98700098
        union all
        select sales_date, /*hijk*/
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               sum(actual_value),
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0
          from (select sales_date,
                       actual_value
                  from table1 t
                 where t.sales_account = 'XZAS'
                   and t.account_number = 98700002
                union all
                select sales_date,
                       actual_value
                  from table1 t
                 where t.sales_account = 'ZAS'
                   and t.account_number = 98700002)
         group by sales_date
        union all
        select sales_date, /*ijkl*/
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               actual_value,
               0,
               0,
               0,
               0,
               0,
               0,
               0
          from table1 t
         where t.sales_account = 'YZAS'
           and t.account_number = 98700002
        union all
        select sales_date, /*jklm*/
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               actual_value,
               0,
               0,
               0,
               0,
               0,
               0
          from table1 t
         where t.sales_account = 'Y32'
           and t.account_number = 98720000
        union all
        select sales_date, /*klmn*/
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               actual_value,
               0,
               0,
               0,
               0,
               0
          from table1 t
         where t.sales_account = 'ZAS'
           and t.account_number = 98780001
        union all
        select sales_date, /*lmno*/
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               actual_value,
               0,
               0,
               0,
               0
          from table1 t
         where t.sales_account in ('ZAS', 'YZAS')
           and t.account_number = 98800002
        union all
        select sales_date, /*mnop*/
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               actual_value,
               0,
               0,
               0
          from table1 t
         where t.sales_account = 'Z54'
           and t.account_number = 98800005
        union all
        select sales_date, /*nopq*/
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               actual_value,
               0,
               0
          from table1 t
         where t.sales_account = 'Z54'
           and t.account_number in (98800006, 98800007)
         union all
         select sales_date, /*opqr*/
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               actual_value,
               0
          from table1 t
         where t.sales_account = 'Z55'
           and t.account_number = 98700407
         union all
         select sales_date, /*pqrs*/
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               0,
               actual_value
          from table1 t
         where t.sales_account in ('Z54', 'N266')
           and t.account_number = 98700407)
 group by sales_date;