BUG #15882: Select .... UNION ALL

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15882: Select .... UNION ALL

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      15882
Logged by:          Vincenzo Campanella
Email address:      [hidden email]
PostgreSQL version: 11.4
Operating system:   windows 10 build 1903
Description:        

cSelect = "select 1 as prog,sum(imp_asl) as Importo_Asl,Count(Arc_Ref.n_Rif)
as nRicette,sum(n_Pre) as nPrelievi  " +
                                                                        "from " + MyFunc.cSchema + "Arc_Ref " +
                                                                        "where data >= '" + cAnno + "-01-01' and data <= '" + cAnno +
"-01-31' and T_MPR='M' " +
                                                                        "union all " +
                                                                        "select 2 as prog,sum(imp_asl) as Importo_Asl,Count(Arc_Ref.n_Rif)
as nRicette,sum(n_Pre) as nPrelievi  " +
                                                                        "from " + MyFunc.cSchema + "Arc_Ref " +
                                                                        "where data >= '" + cAnno + "-02-01' and data <= '" + cAnno +
"-02-" + MyFunc.Ultimo_Giorno_Mese(2, Converti.ToInt32(cAnno)) + "' and
T_MPR='M' " +
                                                                        "union all " +
                                                                        "select 3 as prog,sum(imp_asl) as Importo_Asl,Count(Arc_Ref.n_Rif)
as nRicette,sum(n_Pre) as nPrelievi  " +
                                                                        "from " + MyFunc.cSchema + "Arc_Ref " +
                                                                        "where data >= '" + cAnno + "-03-01' and data <= '" + cAnno +
"-03-31' and T_MPR='M' " +
                                                                        "union all " +
                                                                        "select 4 as prog,sum(imp_asl) as Importo_Asl,Count(Arc_Ref.n_Rif)
as nRicette,sum(n_Pre) as nPrelievi  " +
                                                                        "from " + MyFunc.cSchema + "Arc_Ref " +
                                                                        "where data >= '" + cAnno + "-04-01' and data <= '" + cAnno +
"-04-30' and T_MPR='M' " +
                                                                        "union all " +
                                                                        "select 5 as prog,sum(imp_asl) as Importo_Asl,Count(Arc_Ref.n_Rif)
as nRicette,sum(n_Pre) as nPrelievi  " +
                                                                        "from " + MyFunc.cSchema + "Arc_Ref " +
                                                                        "where data >= '" + cAnno + "-05-01' and data <= '" + cAnno +
"-05-31' and T_MPR='M' " +
                                                                        "union all " +
                                                                        "select 6 as prog,sum(imp_asl) as Importo_Asl,Count(Arc_Ref.n_Rif)
as nRicette,sum(n_Pre) as nPrelievi  " +
                                                                        "from " + MyFunc.cSchema + "Arc_Ref " +
                                                                        "where data >= '" + cAnno + "-06-01' and data <= '" + cAnno +
"-06-30' and T_MPR='M' " +
                                                                        "union all " +
                                                                        "select 7 as prog,sum(imp_asl) as Importo_Asl,Count(Arc_Ref.n_Rif)
as nRicette,sum(n_Pre) as nPrelievi  " +
                                                                        "from " + MyFunc.cSchema + "Arc_Ref " +
                                                                        "where data >= '" + cAnno + "-07-01' and data <= '" + cAnno +
"-07-31' and T_MPR='M' " +
                                                                        "union all " +
                                                                        "select 8 as prog,sum(imp_asl) as Importo_Asl,Count(Arc_Ref.n_Rif)
as nRicette,sum(n_Pre) as nPrelievi  " +
                                                                        "from " + MyFunc.cSchema + "Arc_Ref " +
                                                                        "where data >= '" + cAnno + "-08-01' and data <= '" + cAnno +
"-08-31' and T_MPR='M' " +
                                                                        "union all " +
                                                                        "select 9 as prog,sum(imp_asl) as Importo_Asl,Count(Arc_Ref.n_Rif)
as nRicette,sum(n_Pre) as nPrelievi  " +
                                                                        "from " + MyFunc.cSchema + "Arc_Ref " +
                                                                        "where data >= '" + cAnno + "-09-01' and data <= '" + cAnno +
"-09-30' and T_MPR='M' " +
                                                                        "union all " +
                                                                        "select 10 as prog,sum(imp_asl) as Importo_Asl,Count(Arc_Ref.n_Rif)
as nRicette,sum(n_Pre) as nPrelievi  " +
                                                                        "from " + MyFunc.cSchema + "Arc_Ref " +
                                                                        "where data >= '" + cAnno + "-10-01' and data <= '" + cAnno +
"-10-31' and T_MPR='M' " +
                                                                        "union all " +
                                                                        "select 11 as prog,sum(imp_asl) as Importo_Asl,Count(Arc_Ref.n_Rif)
as nRicette,sum(n_Pre) as nPrelievi  " +
                                                                        "from " + MyFunc.cSchema + "Arc_Ref " +
                                                                        "where data >= '" + cAnno + "-11-01' and data <= '" + cAnno +
"-11-30' and T_MPR='M' " +
                                                                        "union all " +
                                                                        "select 12 as prog,sum(imp_asl) as Importo_Asl
,Count(Arc_Ref.n_Rif) as nRicette,sum(n_Pre) as nPrelievi " +
                                                                        "from " + MyFunc.cSchema + "Arc_Ref " +
                                                                        "where data >= '" + cAnno + "-12-01' and data <= '" + cAnno +
"-12-31' and T_MPR='M' " +
                                                                        "union all " +
                                                                        "select 13 as prog,sum(imp_asl) as Importo_Asl,Count(Arc_Ref.n_Rif)
as nRicette,sum(n_Pre) as nPrelievi  " +
                                                                        "from " + MyFunc.cSchema + "Arc_Ref " +
                                                                        "where data >= '" + cAnno + "-01-01' and data <= '" + cAnno +
"-12-31' and T_MPR='M' ";
This produces an unordered but random table.
I put the prog field and then make the ascending order on this field.
In previous versions the order was in the typed sequence.
Greetings
Enzo Campanella
([hidden email])

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15882: Select .... UNION ALL

David G Johnston
On Tuesday, July 2, 2019, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      15882
Logged by:          Vincenzo Campanella
Email address:      [hidden email]
PostgreSQL version: 11.4
Operating system:   windows 10 build 1903
Description:       

cSelect = "select 1 as prog,sum(imp_asl) as Importo_Asl,Count(Arc_Ref.n_Rif)
as nRicette,sum(n_Pre) as nPrelievi  " +
                                                                        "from " + MyFunc.cSchema + "Arc_Ref " +
                                                                        "where data >= '" + cAnno + "-01-01' and data <= '" + cAnno +
"-01-31' and T_MPR='M' " +
                                                                        "union all " +
                                                                        "select 2 as prog,sum(imp_asl) as Importo_Asl,Count(Arc_Ref.n_Rif)
as nRicette,sum(n_Pre) as nPrelievi  " +
                                                                        "from " + MyFunc.cSchema + "Arc_Ref " +
                                                                        "where data >= '" + cAnno + "-02-01' and data <= '" + cAnno +
"-02-" + MyFunc.Ultimo_Giorno_Mese(2, Converti.ToInt32(cAnno)) + "' and
T_MPR='M' " +
[...]
 
This produces an unordered but random table.

unordered but random?
 
I put the prog field and then make the ascending order on this field.
In previous versions the order was in the typed sequence.

I too would expect the unioned rows to be produced in the order listed but this is not bug because the only way PostgreSQL is required to maintain record order in a query is if you specify an order by clause.

It occurs to me you are likely benefitting from the new parallel query infrastructure here.

David J.
 
Reply | Threaded
Open this post in threaded view
|

Re: BUG #15882: Select .... UNION ALL

David Rowley-3
On Tue, 2 Jul 2019 at 21:22, David G. Johnston
<[hidden email]> wrote:
> It occurs to me you are likely benefitting from the new parallel query infrastructure here.

Yeah, most likely.

It can be disabled globally by changing enable_parallel_append = off
in postgresql.conf. However, relying on the query outputting rows in
some order without an ORDER BY clause is asking for trouble. It looks
as though there are only 13 rows to sort, so I don't think an ORDER BY
will cost much.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services