Re: [SQL] Select & Tables X Select & Views - Mailing list pgsql-sql
| From | Rodrigo Rezende |
|---|---|
| Subject | Re: [SQL] Select & Tables X Select & Views |
| Date | |
| Msg-id | 38B132D3.5CEB990A@hortolandia.marelli.it Whole thread Raw |
| In response to | Re: [SQL] Select & Tables X Select & Views ("Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>) |
| Responses |
Re: [SQL] Select & Tables X Select & Views
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-sql |
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