Thread: BUG #15882: Select .... UNION ALL
The following bug has been logged on the website:
Bug reference: 15882
Logged by: Vincenzo Campanella
Email address: v.campanella47@gmail.com
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
(v.campanella@tin.it)
On Tuesday, July 2, 2019, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 15882
Logged by: Vincenzo Campanella
Email address: v.campanella47@gmail.com
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.
On Tue, 2 Jul 2019 at 21:22, David G. Johnston <david.g.johnston@gmail.com> 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