[HACKERS] incorrect query result using complex structures (views?)

[Kovacs Zoltan]

Hi,

I cannot decide if this is a serious bug or not --- some queries from
complex views may give strange results. The next few days I will try to
find the point where the problem is but now I can only include the data
structure and the SELECT statements which don't give the correct result. A
lot of rows (contained by the database) should be downloaded from

http://www.math.u-szeged.hu/~kovzol/rows.pgsql.gz (25K, uncompressed 305K)

if you want to check this error.

Here are the definitions (rels-views.pgsql) and a RUNME.pgsql file (which
must be loaded with \i in psql), it contains the SELECTs.

I tried it with 7.1beta4 and 7.1.

There ARE workarounds. I am using SQL functions instead of subSELECTs now.

Regards,
Zoltan

CREATE TABLE cikk (
        az SERIAL PRIMARY KEY,
        nev varchar(80) NOT NULL,
        mennyisegi_egyseg int4 NOT NULL,
        szin int4 NOT NULL,
        tipus int4 NOT NULL DEFAULT 1,
        megjegyzes varchar(250),
        felvitel_allapot int4  NOT NULL DEFAULT 1);

CREATE TABLE keretrendeles (
  az SERIAL PRIMARY KEY,
  szallito int4 NOT NULL,
  megrendelo int4 NOT NULL,
  CHECK (szallito <> megrendelo),
  az_jel varchar(20) NOT NULL,
  megjegyzes varchar(250),
  kezdes date NOT NULL,
  befejezes date NOT NULL DEFAULT '3000-01-01',
  CHECK (kezdes<=befejezes)
);

CREATE TABLE megrendeles (
  az SERIAL PRIMARY KEY,
  szallito int4 NOT NULL,
  megrendelo int4 NOT NULL,
  az_jel varchar(10),
  keretrendeles int4 REFERENCES keretrendeles(az),
  teljesites date NOT NULL,
  teljesites_kezdete date,
  allapot int4 NOT NULL,
  utolso_lezart_modositas int4
  );

CREATE TABLE megrendeles_modositasa (
  megrendeles int4 NOT NULL REFERENCES megrendeles(az),
  sorszam int4 check (sorszam >= 0) DEFAULT 0,
  PRIMARY KEY (megrendeles, sorszam),
  kelt date check ( (not kelt is null) or (not lezarva) ),
  beerkezett date NOT NULL DEFAULT now(),
  kezdemenyezo int4,
  leiras text,
  lezarva bool DEFAULT 'f',
  lezarta int4,
  megnyito int4,
  lezaras_idopontja timestamp);

CREATE TABLE megrendeles_tetele (
  megrendeles int4 NOT NULL REFERENCES megrendeles(az),
  modositas int4 NOT NULL,
  FOREIGN KEY (megrendeles, modositas) REFERENCES
      megrendeles_modositasa(megrendeles, sorszam),
  tetelszam int4 NOT NULL,
  archiv bool default 'f',
  PRIMARY KEY (megrendeles, tetelszam, modositas, archiv),
  sorrend int4 NOT NULL,
  kulso_cikk int4 NOT NULL,
  cikk int4 NOT NULL,
  minoseg int4 DEFAULT 1 NOT NULL,
  teljesites date NOT NULL,
  mennyiseg numeric(14,4) NOT NULL, 
  mettol int4
);

create view megrendeles_tetele_eddigi as select
mt.megrendeles, mt.tetelszam, mt.sorrend, mt.kulso_cikk, mt.cikk, mt.minoseg,
mt.teljesites, mt.mennyiseg, mm.sorszam
from megrendeles_tetele as mt, megrendeles_modositasa as mm
where (mm.sorszam < mettol and mm.sorszam >= modositas and archiv)
or (mm.sorszam >= modositas and not archiv)
and mt.megrendeles = mm.megrendeles
group by mt.megrendeles, tetelszam, sorrend, kulso_cikk, cikk, minoseg,
  teljesites, mennyiseg, sorszam;

create view megrendeles_tetele_ervenyes as 
select mr.az, mr.az_jel, mr.allapot, mr.megrendelo,
mr.szallito, (select keretrendeles.az_jel from
keretrendeles where az=mr.keretrendeles) as keretrendeles,
mr.keretrendeles as keretrendeles_az,
mt.teljesites, mt.cikk, mt.kulso_cikk, mt.minoseg, mt.mennyiseg
from megrendeles mr, megrendeles_tetele_eddigi mt
where mr.az = mt.megrendeles
and mt.sorszam = mr.utolso_lezart_modositas;


\i rels-views.pgsql 
\i rows.pgsql 
select az, (select mennyiseg from megrendeles_tetele_ervenyes where cikk=cikk.az) from 
cikk;
select az, (select az from megrendeles_tetele_ervenyes where cikk=cikk.az) from cikk;
select az, (select cikk.az from megrendeles_tetele_ervenyes where cikk=cikk.az) from 
cikk;


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl