Обсуждение: strange query results

hi guys
I am trying out some relativly simple queries against my database..

select distinct site_section as "distinct site sections" from exhibit_distributions ;
distinct site sections
------------------------
ARCHIVED
ARTETC
CALENDAR
GALLERY
POSTCARD
(5 rows)


select site_section, count(*) from exhibit_distributions group by site_section;
site_section | count
--------------+-------
    |   352
      |    45
    |     1
     |   166
    |     2

The second query is not priniting out site_section column.. This is
happening in 3 seperate dbs (702 and 703).. Can any one point out the
mistake.

This is proving to be a show stopper .. We arent able to select rows for
a particular site_section..

Thanks for your response
Anand

Anand Raman <araman@india-today.com> writes:
> The second query is not priniting out site_section column.

Odd.  What is the exact definition of table exhibit_distributions?
Does it have any indices?  What plan is printed by EXPLAIN for the
problem query?

            regards, tom lane

Hi tom
The table description is as follows

arttoday=> \d exhibit_distributions
                                    Table "exhibit_distributions"
          Attribute           |     Type      |                       Modifier
------------------------------+---------------+------------------------------------------------------
 exhibit_distribution_id      | integer       | not null default nextval('sq_exhibit_dist_id'::text)
 exhibit_id                   | integer       | not null
 created_by                   | integer       | not null
 creation_date                | timestamp     | not null default "timestamp"('now'::text)
 last_update_date             | timestamp     |
 last_updated_by              | integer       |
 exhibit_type_id              | integer       | not null
 medium                       | varchar(100)  |
 image_path_small             | varchar(50)   |
 image_path_big               | varchar(50)   |
 length                       | numeric(7,2)  |
 breadth                      | numeric(7,2)  |
 width                        | numeric(7,2)  |
 diameter                     | numeric(7,2)  | default 8
 dimensional_aspect           | varchar(50)   | default 'Dimensions'
 unframed_volume_weight       | numeric(10,2) |
 framed_volume_weight         | numeric(10,2) |
 override_vw_computation      | boolean       | default 'f'::bool
 exhibit_code                 | varchar(25)   |
 exhibit_options              | varchar(10)   |
 unframed_exhibit_restriction | varchar(25)   | default 'WORLD'
 framed_exhibit_restriction   | varchar(25)   | default 'WORLD'
 up_for_sale                  | char(1)       | not null default 'T'
 gallery_id                   | integer       |
 site_section                 | varchar(20)   | not null default 'GALLERY'
Index: exhibit_distributions_pkey
Constraints: ((up_for_sale = 'T'::bpchar) OR (up_for_sale = 'F'::bpchar))
             (length > '0'::"numeric")
             (breadth > '0'::"numeric")
             (width > '0'::"numeric")
             (((unframed_exhibit_restriction = 'WORLD'::"varchar") OR (unframed_exhibit_restriction =
'INDIA'::"varchar"))OR (unframed_exhibit_restriction = 'ONLY_WORLD'::"varchar")) 
             (((framed_exhibit_restriction = 'WORLD'::"varchar") OR (framed_exhibit_restriction = 'INDIA'::"varchar"))
OR(framed_exhibit_restriction = 'ONLY_WORLD'::"varchar")) 
             (((exhibit_options = 'FRAMED'::"varchar") OR (exhibit_options = 'UNFRAMED'::"varchar")) OR
(exhibit_options= NULL::"varchar")) 

Explain plan results in the following
arttoday=> explain select site_section, count(*) from exhibit_distributions group by site_section;
NOTICE:  QUERY PLAN:

Aggregate  (cost=69.83..74.83 rows=100 width=12)
  ->  Group  (cost=69.83..72.33 rows=1000 width=12)
          ->  Sort  (cost=69.83..69.83 rows=1000 width=12)
                        ->  Seq Scan on exhibit_distributions
                        (cost=0.00..20.00 rows=1000 width=12)

The problem still persists.. I will a drop and recreation of the db just
in case..

Thanks for the help
Anand
On Mon, Feb 12, 2001 at 11:04:55AM -0500, Tom Lane wrote:
>Anand Raman <araman@india-today.com> writes:
>> The second query is not priniting out site_section column.
>
>Odd.  What is the exact definition of table exhibit_distributions?
>Does it have any indices?  What plan is printed by EXPLAIN for the
>problem query?
>
>            regards, tom lane

Hi tom

Thanks for your time..

No all of the columns were there right from the start..
Even if i added a few columns i always went thru the process of dropping
and recreating the entire db..

However a few days back there was a instance of index curroption and things came
to a halt.. A vaccum of the database notified of the possible curroption
and i recreated a index which didnt belong to this table..

I havent tried vaccuming the table now.. Will it help??

Thanks
Anand

On Tue, Feb 13, 2001 at 10:20:53AM -0500, Tom Lane wrote:
>Anand Raman <araman@india-today.com> writes:
>> The table description is as follows
>
>Hmm ... nothing obviously funny here.  Is there anything unusual about
>the history of this table?  (For example, were site_section or any other
>columns added via ALTER TABLE, rather than being there all along?)
>
>            regards, tom lane

Anand Raman <araman@india-today.com> writes:
> The table description is as follows

Hmm ... nothing obviously funny here.  Is there anything unusual about
the history of this table?  (For example, were site_section or any other
columns added via ALTER TABLE, rather than being there all along?)

            regards, tom lane

HI tom
A few days back i had bugged this list about the seemingly impossible
select queries results..

##RECAP##
select distinct site_section as "distinct site sections" from
exhibit_distributions ;
distinct site sections
------------------------
ARCHIVED
ARTETC
CALENDAR
GALLERY
POSTCARD
(5 rows)


select site_section, count(*) from exhibit_distributions group by
site_section;
site_section | count
--------------+-------
    |   352
    |    45
    |     1
    |   166
    |     2

##RECAP##

On going thru the flat files which we used to uplaod the database we
noticed a few fields had '' characters to signify '.. This was creating
problems in some jdbc queries.

One changing them to single ' and reloading all the data, the problem
simply disappered..

Thanks for the help
Anand Raman
On Tue, Feb 13, 2001 at 10:20:53AM -0500, Tom Lane wrote:
>Anand Raman <araman@india-today.com> writes:
>> The table description is as follows
>
>Hmm ... nothing obviously funny here.  Is there anything unusual about
>the history of this table?  (For example, were site_section or any other
>columns added via ALTER TABLE, rather than being there all along?)
>
>            regards, tom lane