Thread: BUG #5594: Nested views working on same set of data on 8.1.21 but not on 8.4.4
The following bug has been logged online: Bug reference: 5594 Logged by: Fathi Email address: fathibn@yahoo.fr PostgreSQL version: 8.4.4 Operating system: Linux 2.6.18-194.8.1.el5xen #1 SMP x86_64 Description: Nested views working on same set of data on 8.1.21 but not on 8.4.4 Details: Hello, I have migrated a 5 years old database from 8.1.21 running on top of centos 5.5 on an i686 to 8.4.4 running on top of centos 5.5 on an x86_64 architecture. I have some nested views and i am still able to get the results on 8.1.21 but when it comes to 8.4.4, I can get the results only from views that references other tables but when the view references other views, I get an empty result set. I also noticed that a view (not nested) gives an empty result set on 8.4.4 while it gives exactly what it is expected to on 8.1.21. This is a simple view for giving some pseudo-random data for an exam. it's definition is as follows: -- View: examen CREATE OR REPLACE VIEW examen AS SELECT DISTINCT substr(t_valeurs_stock.article::text, 4) AS substr, round(t_valeurs_stock.consommation_triennale::numeric, 0) AS consommation_triennale, t_valeurs_stock.min_reappro, t_valeurs_stock.max_reappro, t_valeurs_stock.dernier_prix_achat, t_valeurs_stock.devise, sum(t_stocks.quantite_stock) AS stock_reseau, round((sum(t_ressources.quantite) / 4::double precision)::numeric, 0) AS total_ressources, max(t_stocks.date_derniere_sortie) AS dds, md5(t_articles_stock.nom::text) AS cle FROM t_valeurs_stock t_valeurs_stock JOIN t_stocks t_stocks ON t_valeurs_stock.article = t_stocks.article JOIN t_ressources t_ressources ON t_valeurs_stock.article = t_ressources.article JOIN t_articles_stock t_articles_stock ON t_valeurs_stock.article = t_articles_stock.article WHERE t_articles_stock.nom::text <> ''::text AND t_valeurs_stock.devise = 'USD'::bpchar AND t_valeurs_stock.consommation_triennale > 10::double precision AND t_stocks.date_derniere_sortie::timestamp without time zone > '1971-12-24 00:00:00'::timestamp without time zone AND char_length(t_articles_stock.nom::text) > 10 AND char_length(t_articles_stock.nom::text) < 40 GROUP BY t_valeurs_stock.article, t_valeurs_stock.consommation_triennale, t_valeurs_stock.min_reappro, t_valeurs_stock.max_reappro, t_valeurs_stock.dernier_prix_achat, t_valeurs_stock.devise, t_articles_stock.nom ORDER BY md5(t_articles_stock.nom::text), substr(t_valeurs_stock.article::text, 4), round(t_valeurs_stock.consommation_triennale::numeric, 0), t_valeurs_stock.min_reappro, t_valeurs_stock.max_reappro, t_valeurs_stock.dernier_prix_achat, t_valeurs_stock.devise, sum(t_stocks.quantite_stock), round((sum(t_ressources.quantite) / 4::double precision)::numeric, 0), max(t_stocks.date_derniere_sortie) LIMIT 26; What other data should I post to get some help? There is no confidential data on the database so I can send schema definition and data but this is a huge amount for a web form.
Re: BUG #5594: Nested views working on same set of data on 8.1.21 but not on 8.4.4
From
Tom Lane
Date:
"Fathi" <fathibn@yahoo.fr> writes: > What other data should I post to get some help? You need to provide enough information for somebody else to reproduce the problem. Please read http://www.postgresql.org/docs/8.4/static/bug-reporting.html The usual way to report this type of issue is to create a SQL script that will reproduce it starting from an empty database. You can start from a pg_dump script and then try to trim out anything that's confidential or irrelevant, and/or reduce the amount of data, if the script seems too large to mail to the list. regards, tom lane