BUG #5594: Nested views working on same set of data on 8.1.21 but not on 8.4.4

From: "Fathi" <fathibn(at)yahoo(dot)fr>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5594: Nested views working on same set of data on 8.1.21 but not on 8.4.4
Date: 2010-08-03 07:53:37
Message-ID: 201008030753.o737rbVh098491@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 5594
Logged by: Fathi
Email address: fathibn(at)yahoo(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Greg Stark 2010-08-03 10:10:09 Re: BUG #5592: list of integer undefined behaviors
Previous Message Fathi 2010-08-03 07:52:14 BUG #5593: Nested views working on same set of data on 8.1.21 but not on 8.4.4