Обсуждение: incorrect query result using complex structures (views?)
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
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
> 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.
So ... um ... what do you consider incorrect about the results?
regards, tom lane
On Tue, 8 May 2001, Tom Lane wrote: > Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes: > > 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. > > So ... um ... what do you consider incorrect about the results? > > regards, tom lane The SELECTs give something like this: tir=> select az, (select cikk from szallitolevel_tetele_ervenyes where cikk = c.az) from cikk c limit 20; az|?column? ------+-------- 100191| 100202| 100203| 100006| 100016| 100027| 100028| 100039| 100080| 100099| 100100| 100102| 100105| 100106| 100107| 100108| 100109| 100110| 100111| 100112| (20 rows) But cikk.az and szallitolevel_tetele_ervenyes.cikk should be the same, so the correct output for this query would be like this: tir=> select c.az, cikk from cikk c, szallitolevel_tetele_ervenyes s where c.az=s.cikk limit 20; az| cikk ------+------ 100743|100743 100742|100742 101080|101080 101075|101075 101084|101084 100124|100124 100467|100467 101080|101080 101163|101163 100517|100517 101080|101080 101163|101163 100719|100719 100406|100406 101080|101080 100286|100286 100367|100367 100406|100406 101080|101080 100546|100546 (20 rows) Thanks in advance. Zoltan
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
> Thanks in advance. Zoltan
You're welcome ;-)
regards, tom lane
*** src/backend/executor/nodeAppend.c.orig Thu Mar 22 01:16:12 2001
--- src/backend/executor/nodeAppend.c Tue May 8 15:48:02 2001
***************
*** 8,14 **** * * * IDENTIFICATION
! * $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/executor/nodeAppend.c,v 1.40 2001/03/22 06:16:12
momjianExp $ * *------------------------------------------------------------------------- */
--- 8,14 ---- * * * IDENTIFICATION
! * $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/executor/nodeAppend.c,v 1.40.2.1 2001/05/08 19:48:02
tglExp $ * *------------------------------------------------------------------------- */
***************
*** 362,375 **** for (i = 0; i < nplans; i++) {
! Plan *rescanNode;
! appendstate->as_whichplan = i;
! rescanNode = (Plan *) nth(i, node->appendplans);
! if (rescanNode->chgParam == NULL) { exec_append_initialize_next(node);
! ExecReScan((Plan *) rescanNode, exprCtxt, (Plan *) node); } } appendstate->as_whichplan =
0;
--- 362,386 ---- for (i = 0; i < nplans; i++) {
! Plan *subnode;
! subnode = (Plan *) nth(i, node->appendplans);
! /*
! * ExecReScan doesn't know about my subplans, so I have to do
! * changed-parameter signaling myself.
! */
! if (node->plan.chgParam != NULL)
! SetChangedParamList(subnode, node->plan.chgParam);
! /*
! * if chgParam of subnode is not null then plan will be re-scanned by
! * first ExecProcNode.
! */
! if (subnode->chgParam == NULL) {
+ /* make sure estate is correct for this subnode (needed??) */
+ appendstate->as_whichplan = i; exec_append_initialize_next(node);
! ExecReScan(subnode, exprCtxt, (Plan *) node); } } appendstate->as_whichplan = 0;
*** src/backend/executor/nodeSubqueryscan.c.orig Thu Mar 22 01:16:13 2001
--- src/backend/executor/nodeSubqueryscan.c Tue May 8 15:48:02 2001
***************
*** 12,18 **** * * * IDENTIFICATION
! * $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/executor/nodeSubqueryscan.c,v 1.6 2001/03/22 06:16:13
momjianExp $ * *------------------------------------------------------------------------- */
--- 12,18 ---- * * * IDENTIFICATION
! * $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/executor/nodeSubqueryscan.c,v 1.6.2.1 2001/05/08
19:48:02tgl Exp $ * *------------------------------------------------------------------------- */
***************
*** 267,273 **** return; }
! ExecReScan(node->subplan, NULL, node->subplan); subquerystate->csstate.css_ScanTupleSlot = NULL; }
--- 267,284 ---- return; }
! /*
! * ExecReScan doesn't know about my subplan, so I have to do
! * changed-parameter signaling myself.
! */
! if (node->scan.plan.chgParam != NULL)
! SetChangedParamList(node->subplan, node->scan.plan.chgParam);
! /*
! * if chgParam of subnode is not null then plan will be re-scanned by
! * first ExecProcNode.
! */
! if (node->subplan->chgParam == NULL)
! ExecReScan(node->subplan, NULL, node->subplan); subquerystate->csstate.css_ScanTupleSlot = NULL; }
> You're welcome ;-) Marvellous, it works! How much time did it take for you to find what have to be changed? Thank you very much. Regards, Zoltan