Thread: Select & Tables X Select & Views
Hello all!, Using the following query : select * from taba, tabb; The result will be taba's number of rows * tabb's number of rows result = rows(taba) * rows(tabb) 1.ex.: taba : 1 2 3 tabb a b select * from taba, tabb; : 1 | a 2 | a 3 | a 1 | b 2 | b 3 | b This occurred because I didn't use the clause Join and Where... But, using a View instead of tabb(table), the number of rows was the same of the view 2. ex.: taba : 1 2 3 viewb a b select * from taba, viewb; : 1 | a 1 | b Why this occurred? I would like this second example returned the same as the first Thanks a lot, Rodrigo C. Rezende
Rodrigo -
Are you reporting a bug, or looking for advice? Here's how it works
for me:
I ran this script:
create table taba (a1 int);
create table tabb (b1 char);
insert into taba (1);
insert into taba values (1);
insert into taba values (2);
insert into taba values (3);
insert into tabb values ('a');
insert into tabb values ('b');
create view viewb as select * from tabb;
And here are the tests:
test=> select version();
version
--------------------------------------------------------------
PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3
(1 row)
test=> select * from taba, tabb;
a1|b1
--+--1|a 2|a 3|a 1|b 2|b 3|b
(6 rows)
test=> select * from taba, viewb;
a1|b1
--+--1|a 2|a 3|a 1|b 2|b 3|b
(6 rows)
test=>
So, it looks like your view definition is broken, or your using an
old version of pgsql.
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
I'm sorry,
the real case is :
1.) create the table func_preench
create table func_preench ( codfuncionario int4, codest_preench int4, data date
);
2.) I put the data :
codfuncionario codest_preench data
-------------- -------------- ----------
108 2 1999-08-01
109 2 1999-08-01
110 2 1999-08-01
111 2 1999-08-01
112 2 1999-08-01
113 2 1999-08-01
114 2 1999-08-01
115 1 1999-10-01
116 2 1999-08-01
117 2 1999-08-01
118 2 1999-08-01
119 2 1999-08-01
120 2 1999-08-01
121 2 1999-08-01
122 1 1999-08-01
123 1 1999-08-01
124 1 1999-08-01
125 1 1999-08-01
126 1 1999-08-01
127 1 1999-08-01
128 1 1999-08-01
128 0 1999-10-01
129 1 1999-08-01
130 1 1999-08-01
131 1 1999-08-01
132 1 1999-08-01
133 1 1999-08-01
134 1 1999-08-01
135 1 1999-08-01
136 1 1999-08-01
137 1 1999-08-01
138 1 1999-08-01
139 1 1999-08-01
140 1 1999-08-01
141 1 1999-08-01
142 1 1999-08-01
143 1 1999-08-01
144 1 1999-08-01
145 1 1999-08-01
146 3 1999-10-01
147 1 1999-08-01
148 1 1999-08-01
149 2 1999-08-01
150 1 1999-08-01
151 2 1999-08-01
152 2 1999-08-01
153 1 1999-08-01
154 1 1999-08-01
155 1 1999-08-01
156 1 1999-08-01
157 1 1999-08-01
158 1 1999-08-01
159 1 1999-08-01
160 1 1999-08-01
161 1 1999-08-01
162 1 1999-08-01
163 1 1999-08-01
164 1 1999-08-01
165 1 1999-08-01
166 1 1999-08-01
167 1 1999-08-01
168 1 1999-08-01
169 1 1999-08-01
170 1 1999-08-01
171 1 1999-08-01
172 1 1999-08-01
173 1 1999-08-01
174 1 1999-08-01
175 1 1999-08-01
176 1 1999-08-01
177 1 1999-08-01
178 1 1999-08-01
183 1 1999-08-01
184 1 1999-08-01
184 0 1999-09-01
185 1 1999-08-01
186 1 1999-08-01
187 1 1999-08-01
188 1 1999-08-01
189 1 1999-08-01
190 1 1999-08-01
191 1 1999-08-01
191 0 1999-12-01
192 1 1999-08-01
193 1 1999-08-01
194 2 1999-08-01
195 1 1999-10-01
198 0 1999-07-01
199 1 2000-01-01
202 1 1999-12-01
203 1 2000-01-01
166 0 2000-01-01
(92 rows)
The view :
create view func_aux_stat as select fp.codfuncionario, max(fp.data) as
data
from func_preench as fp group by fp.codfuncionario ;
running : select * from func_aux_stat; the result have 88 rows affected
The problem :
running:
select * from func_preench, func_aux_stat ;
codfuncionario codest_preench data codfuncionario data
-------------- -------------- ---------- -------------- ----------
108 2 1999-08-01 108 1999-08-01
108 2 1999-08-01 109 1999-08-01
108 2 1999-08-01 110 1999-08-01
108 2 1999-08-01 111 1999-08-01
108 2 1999-08-01 112 1999-08-01
108 2 1999-08-01 113 1999-08-01
108 2 1999-08-01 114 1999-08-01
108 2 1999-08-01 115 1999-10-01
108 2 1999-08-01 116 1999-08-01
108 2 1999-08-01 117 1999-08-01
108 2 1999-08-01 118 1999-08-01
108 2 1999-08-01 119 1999-08-01
108 2 1999-08-01 120 1999-08-01
108 2 1999-08-01 121 1999-08-01
108 2 1999-08-01 122 1999-08-01
108 2 1999-08-01 123 1999-08-01
108 2 1999-08-01 124 1999-08-01
108 2 1999-08-01 125 1999-08-01
108 2 1999-08-01 126 1999-08-01
108 2 1999-08-01 127 1999-08-01
108 2 1999-08-01 128 1999-10-01
108 2 1999-08-01 129 1999-08-01
108 2 1999-08-01 130 1999-08-01
108 2 1999-08-01 131 1999-08-01
108 2 1999-08-01 132 1999-08-01
108 2 1999-08-01 133 1999-08-01
108 2 1999-08-01 134 1999-08-01
108 2 1999-08-01 135 1999-08-01
108 2 1999-08-01 136 1999-08-01
108 2 1999-08-01 137 1999-08-01
108 2 1999-08-01 138 1999-08-01
108 2 1999-08-01 139 1999-08-01
108 2 1999-08-01 140 1999-08-01
108 2 1999-08-01 141 1999-08-01
108 2 1999-08-01 142 1999-08-01
108 2 1999-08-01 143 1999-08-01
108 2 1999-08-01 144 1999-08-01
108 2 1999-08-01 145 1999-08-01
108 2 1999-08-01 146 1999-10-01
108 2 1999-08-01 147 1999-08-01
108 2 1999-08-01 148 1999-08-01
108 2 1999-08-01 149 1999-08-01
108 2 1999-08-01 150 1999-08-01
108 2 1999-08-01 151 1999-08-01
108 2 1999-08-01 152 1999-08-01
108 2 1999-08-01 153 1999-08-01
108 2 1999-08-01 154 1999-08-01
108 2 1999-08-01 155 1999-08-01
108 2 1999-08-01 156 1999-08-01
108 2 1999-08-01 157 1999-08-01
108 2 1999-08-01 158 1999-08-01
108 2 1999-08-01 159 1999-08-01
108 2 1999-08-01 160 1999-08-01
108 2 1999-08-01 161 1999-08-01
108 2 1999-08-01 162 1999-08-01
108 2 1999-08-01 163 1999-08-01
108 2 1999-08-01 164 1999-08-01
108 2 1999-08-01 165 1999-08-01
108 2 1999-08-01 166 2000-01-01
108 2 1999-08-01 167 1999-08-01
108 2 1999-08-01 168 1999-08-01
108 2 1999-08-01 169 1999-08-01
108 2 1999-08-01 170 1999-08-01
108 2 1999-08-01 171 1999-08-01
108 2 1999-08-01 172 1999-08-01
108 2 1999-08-01 173 1999-08-01
108 2 1999-08-01 174 1999-08-01
108 2 1999-08-01 175 1999-08-01
108 2 1999-08-01 176 1999-08-01
108 2 1999-08-01 177 1999-08-01
108 2 1999-08-01 178 1999-08-01
108 2 1999-08-01 183 1999-08-01
108 2 1999-08-01 184 1999-09-01
108 2 1999-08-01 185 1999-08-01
108 2 1999-08-01 186 1999-08-01
108 2 1999-08-01 187 1999-08-01
108 2 1999-08-01 188 1999-08-01
108 2 1999-08-01 189 1999-08-01
108 2 1999-08-01 190 1999-08-01
108 2 1999-08-01 191 1999-12-01
108 2 1999-08-01 192 1999-08-01
108 2 1999-08-01 193 1999-08-01
108 2 1999-08-01 194 1999-08-01
108 2 1999-08-01 195 1999-10-01
108 2 1999-08-01 198 1999-07-01
108 2 1999-08-01 199 2000-01-01
108 2 1999-08-01 202 1999-12-01
108 2 1999-08-01 203 2000-01-01
88 Row(s) affected
Why this operation presented 88 rows if the correct is 8096 ?
Thanks a lot,
Rodrigo C. Rezende
reedstrm@wallace.ece.rice.edu wrote:
> Rodrigo -
> Are you reporting a bug, or looking for advice? Here's how it works
> for me:
>
> I ran this script:
>
> create table taba (a1 int);
> create table tabb (b1 char);
> insert into taba (1);
> insert into taba values (1);
> insert into taba values (2);
> insert into taba values (3);
> insert into tabb values ('a');
> insert into tabb values ('b');
> create view viewb as select * from tabb;
>
> And here are the tests:
> test=> select version();
> version
> --------------------------------------------------------------
> PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3
> (1 row)
>
> test=> select * from taba, tabb;
> a1|b1
> --+--
> 1|a
> 2|a
> 3|a
> 1|b
> 2|b
> 3|b
> (6 rows)
>
> test=> select * from taba, viewb;
> a1|b1
> --+--
> 1|a
> 2|a
> 3|a
> 1|b
> 2|b
> 3|b
> (6 rows)
>
> test=>
>
> So, it looks like your view definition is broken, or your using an
> old version of pgsql.
>
> Ross
> --
> Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
> NSBRI Research Scientist/Programmer
> Computer and Information Technology Institute
> Rice University, 6100 S. Main St., Houston, TX 77005
Rodrigo Rezende <Rodrigo.Rezende@hortolandia.marelli.it> writes:
> The view :
> create view func_aux_stat as select fp.codfuncionario, max(fp.data) as
> data from func_preench as fp group by fp.codfuncionario ;
Ah. I'm afraid GROUP BY doesn't work very well in views --- it's OK
for simply selecting from the grouped view, but not for joining it
against other tables. This is because the rule rewriter can only
revise your query into some other query that's still expressible in
SQL, and there's no way to group only one table in a query in the
subset of SQL that we support.
We hope to fix this in 7.1, but for now it's not going to work.
You might think about trying to recast your problem to use subselects,
or some other approach that doesn't involve joining a grouped table
against an ungrouped one.
regards, tom lane