Обсуждение: 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