The following bug has been logged online:
Bug reference: 1260
Logged by: Blazej
Email address: blajan@poczta.fm
PostgreSQL version: 7.4.3
Operating system: Red Hat 9.0 (without updates)
Description: standard compilation
Details:
Posiible bug in contrib/intarray package (function idx)
For test I use FoodMart Database from Mondrian Project (mondrian data):
http://sourceforge.net/projects/mondrian/
build table (for rank):
===== BEGIN QUER =====
CREATE TABLE rank_test
(
idx serial NOT NULL,
week_of_year int4,
the_year int4,
rank int4[],
CONSTRAINT rank_test_pkey PRIMARY KEY (idx)
)
WITH OIDS;
===== END QUERY =====
Insert to table rank index of customers:
===== BEGIN QUER =====
INSERT INTO rank_test(week_of_year, the_year, rank)
SELECT t1.week_of_year, t1.the_year, array_accum(t1.customer_id) FROM
(SELECT sum(store_sales*unit_sales), week_of_year, the_year, customer_id
FROM
(
SELECT *
FROM
sales_fact_1997 sf97 LEFT OUTER JOIN time_by_day td ON (sf97.time_id =
td.time_id)
UNION
SELECT *
FROM
sales_fact_1998 sf98 LEFT OUTER JOIN time_by_day td ON (sf98.time_id =
td.time_id)
UNION
SELECT *
FROM
sales_fact_dec_1998 sf98d LEFT OUTER JOIN time_by_day td ON (sf98d.time_id
= td.time_id)
) AS sf_all
GROUP BY week_of_year, the_year, customer_id
ORDER BY the_year, week_of_year, sum, customer_id
) AS t1
GROUP BY t1.week_of_year, t1.the_year
ORDER BY t1.the_year, t1.week_of_year
and when I run:
SELECT idx, idx(rank, 4676), icount(rank) FROM rank_test WHERE 4767 = ANY
(rank)
===== END QUERY =====
I get this:
idx;idx;icount
2;286;605
10;59;294
11;0;303 <- wrong
18;189;285
20;377;505
21;200;323
25;0;227 <- wrong
27;0;332 <- wrong
28;412;422
31;308;347
33;0;348 <- wrong
34;0;470 <- wrong
43;311;480
48;330;463
49;0;400 <- wrong
56;488;1035
61;247;798
64;236;642
68;413;434
69;701;738
70;497;636
80;692;784
84;0;925 <- wrong
89;395;579
92;0;567 <- wrong
93;541;768
95;338;651
101;0;578 <- wrong
102;117;851
103;160;694
104;0;70 <- wrong
106;286;605
114;59;294
115;0;303 <- wrong
122;189;285
124;377;505
125;200;323
129;0;227 <- wrong
131;0;332 <- wrong
132;412;422
135;308;347
137;0;348 <- wrong
138;0;470 <- wrong
147;311;480
152;330;463
153;0;400 <- wrong
160;488;1035
165;247;798
168;236;642
172;413;434
173;701;738
174;497;636
184;692;784
188;0;925 <- wrong
193;395;579
196;0;567 <- wrong
197;541;768
199;338;651
205;0;578 <- wrong
206;117;851
207;160;694
208;0;70 <- wrong
210;286;605
218;59;294
219;0;303 <- wrong
226;189;285
228;377;505
229;200;323
233;0;227 <- wrong
235;0;332 <- wrong
236;412;422
239;308;347
241;0;348 <- wrong
242;0;470 <- wrong
251;311;480
256;330;463
257;0;400 <- wrong
264;488;1035
269;247;798
272;236;642
276;413;434
277;701;738
278;497;636
288;692;784
292;0;925 <- wrong
297;395;579
300;0;567 <- wrong
301;541;768
303;338;651
309;0;578 <- wrong
310;117;851
311;160;694
312;0;70 <- wrong
*** BUG ***
for example in this output idx=11 (idx from rank_test table, 11 row)
function return idx(rank, 4676)=0 ?????? <- here BUG
Blazej
