Обсуждение: UNION syntax different for 7.1.2

Hello,

    from 7.0.2 to 7.0.3, the following script works fine, but in 7.1.2 I must remove the rr. from ORDER BY clause. This
happensonly when I have a UNION between the selects, if I try the order by rr.dtsolicitacao in both selects everything
goesok. I know you guys can suggest me to change this whole script to a single outer join, but I cant because my app
stillaccess some old 7.0.3 databases spread over all my country. The question is : Is it some kind of bug or I need to
changeall my scripts that uses UNION ? 

Best regards,

José Vilson de Mello de Farias
Dígitro Tecnologia Ltda - Brazil

SELECT
  rr.*, rm.identificacao, rr.tipo_arquivo,
  ru.cod_operador, ru.cod_operador as nome,
  ru.flg_responsavel, ri.flg_estado as flg_estimp,
  ri.msg_erro as msg_erroimp
FROM
  rel__relatorio rr, rel__modelo rm,
  rel__usuarios ru, rel__impressao ri
WHERE
  rr.cod_cad_relatorio = 1 AND
  rr.cod_modelo = rm.cod_modelo  AND
  rr.cod_cad_relatorio = ru.cod_cad_relatorio AND
  rr.cod_relatorio = ru.cod_relatorio AND
  ru.cod_cad_relatorio = ri.cod_cad_relatorio AND
  ru.cod_relatorio = ri.cod_relatorio AND
  ru.cod_operador = ri.cod_operador AND
  rr.cod_terminal = 'dgtac' AND
  ri.dtimpressao IS NULL AND    ru.cod_operador = 'SYSADM'
UNION ALL
SELECT
  rr.*, rm.identificacao, rr.tipo_arquivo,
  ru.cod_operador, ru.cod_operador AS nome,
  ru.flg_responsavel, NULL, NULL
FROM
  rel__relatorio rr, rel__modelo rm,
  rel__usuarios ru
WHERE
  rr.cod_cad_relatorio = 1 AND
  rr.cod_modelo = rm.cod_modelo  AND
  rr.cod_cad_relatorio = ru.cod_cad_relatorio AND
  rr.cod_relatorio = ru.cod_relatorio AND    ru.cod_operador = 'SYSADM'  AND
  rr.cod_terminal = 'dgtac' AND
  NOT EXISTS (SELECT * FROM rel__impressao ri WHERE
    ru.cod_cad_relatorio = ri.cod_cad_relatorio AND
    ru.cod_relatorio = ri.cod_relatorio AND
    ru.cod_operador = ri.cod_operador) AND    ru.cod_operador = 'SYSADM'

ORDER BY rr.dtsolicitacao

See http://fts.postgresql.org/db/mw/msg.html?mid=1024361

You need to change your query slightly.

Regards,
Ed Loehr

Vilson farias wrote:
>
> Hello,
>
>     from 7.0.2 to 7.0.3, the following script works fine, but in 7.1.2 I must remove the rr. from ORDER BY clause.
Thishappens only when I have a UNION between the selects, if I try the order by rr.dtsolicitacao in both selects
everythinggoes ok. I know you guys can suggest me to change this whole script to a single outer join, but I cant
becausemy app still access some old 7.0.3 databases spread over all my country. The question is : Is it some kind of
bugor I need to change all my scripts that uses UNION ? 
>
> Best regards,
>
> José Vilson de Mello de Farias
> Dígitro Tecnologia Ltda - Brazil
>
> SELECT
>   rr.*, rm.identificacao, rr.tipo_arquivo,
>   ru.cod_operador, ru.cod_operador as nome,
>   ru.flg_responsavel, ri.flg_estado as flg_estimp,
>   ri.msg_erro as msg_erroimp
> FROM
>   rel__relatorio rr, rel__modelo rm,
>   rel__usuarios ru, rel__impressao ri
> WHERE
>   rr.cod_cad_relatorio = 1 AND
>   rr.cod_modelo = rm.cod_modelo  AND
>   rr.cod_cad_relatorio = ru.cod_cad_relatorio AND
>   rr.cod_relatorio = ru.cod_relatorio AND
>   ru.cod_cad_relatorio = ri.cod_cad_relatorio AND
>   ru.cod_relatorio = ri.cod_relatorio AND
>   ru.cod_operador = ri.cod_operador AND
>   rr.cod_terminal = 'dgtac' AND
>   ri.dtimpressao IS NULL AND    ru.cod_operador = 'SYSADM'
> UNION ALL
> SELECT
>   rr.*, rm.identificacao, rr.tipo_arquivo,
>   ru.cod_operador, ru.cod_operador AS nome,
>   ru.flg_responsavel, NULL, NULL
> FROM
>   rel__relatorio rr, rel__modelo rm,
>   rel__usuarios ru
> WHERE
>   rr.cod_cad_relatorio = 1 AND
>   rr.cod_modelo = rm.cod_modelo  AND
>   rr.cod_cad_relatorio = ru.cod_cad_relatorio AND
>   rr.cod_relatorio = ru.cod_relatorio AND    ru.cod_operador = 'SYSADM'  AND
>   rr.cod_terminal = 'dgtac' AND
>   NOT EXISTS (SELECT * FROM rel__impressao ri WHERE
>     ru.cod_cad_relatorio = ri.cod_cad_relatorio AND
>     ru.cod_relatorio = ri.cod_relatorio AND
>     ru.cod_operador = ri.cod_operador) AND    ru.cod_operador = 'SYSADM'
>
> ORDER BY rr.dtsolicitacao
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

"Vilson farias" <vilson.farias@digitro.com.br> writes:
> from 7.0.2 to 7.0.3, the following script works fine, but in 7.1.2 I
> must remove the rr. from ORDER BY clause.

Yup.  That's an intentional change.  The ORDER BY applies to the output
columns of the UNION, not to whatever tables might have been referenced
in the arms of the UNION.

            regards, tom lane