ERROR: ExecEvalExpr: unknown expression type 108 - Mailing list pgsql-sql

Hi,

datetest=# select version();                              version
----------------------------------------------------------------------PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by
GCCgcc (GCC) 3.2.2 
(1 запись)

datetest=# \d daten                                    Таблица "public.daten"Колонка |           Тип            |
       
Модификаторы
----------------+-----------------------------+-------------------------------------------------------id             |
integer                    | not null default 
nextval('public.daten_id_seq'::text)menge          | integer                     | von            | timestamp without
timezone | bis            | timestamp without time zone |  
Индексы: daten_pkey ключевое поле btree (id)

datetest=# select * from daten;id | menge |         von         |         bis
----+-------+---------------------+--------------------- 1 |     2 | 2000-01-01 10:00:00 | 2000-01-01 12:00:00 2 |
3| 2000-01-01 11:00:00 | 2000-01-01 14:00:00 3 |     1 | 2000-01-01 14:00:00 | 2000-01-01 15:00:00 4 |     9 |
2000-01-0112:00:00 | 2000-01-01 16:00:00 5 |     4 | 2000-01-01 10:00:00 | 2000-01-01 11:00:00 
(записей: 5)

datetest=# select * from (select (select count(1) from (select von from
daten union select bis as von from daten) as d1 where d1.von < d2.von)
as number, von from (select von from daten union select bis as von from
daten) d2) as bar join (select (select count(1)+1 from (select von from
daten union select bis as von from daten) as d1 where d1.von < d2.von)
as number, von from (select von from daten union select bis as von from
daten) d2) as foo using (number);
ERROR:  ExecEvalExpr: unknown expression type 108

To explain what I'm actually trying to do, I'll at first visualize the
data:

10    11    12    13    14    15    16    17    t
--------------------------------------------------->
[-----2------]     [---------3--------]                       [---1--]           [----------9-------------]
[---4--]

This displays use periods for microphones, with the number of
microphones for each period of time. So from 10 to 12, one guy needs 2
microphones, and from 11 to 14 another guy needs 3, and so on.
Now let's suppose a third guy asks, if we have enough microphones for
his event from 10 to 17. I know how many microphones there are at all.
So I want to know the maximum number of microphones that are used at a
point in time within that period 10-17. That number is 12 obviously.

It's easy to find out how many microphones are in use at a certain point
in time:

datetest=# select sum(menge) from daten where von < '2000-01-01 10:30'
and bis > '2000-01-01 10:30';sum
-----  6
(1 запись)

So I could ask the maximum of sum for each point in time from 10 to 17.
This is a lot of points in time. So if two points in time don't differ
with respect to the number of microphones in use, I want to look at only
one of them. For that I query the points in time where something
changes:

datetest=# select von from daten union select bis as von from daten;        von
---------------------2000-01-01 10:00:002000-01-01 11:00:002000-01-01 12:00:002000-01-01 14:00:002000-01-01
15:00:002000-01-0116:00:00 
(записей: 6)

Now I want the point in time in the middle between each two subsequent
points in time, i.e. 10:30, 11:30, 12:30, 14:30, 15:30. These would be
the points in time I have to query the sum of microphones in use, then I
have to take the maximum of these sums. To achieve that, I insert a
record counter, topdog from IRC kindly showed me how to do that:

datetest=# select (select count(1) from (select von from daten union
select bis as von from daten) as d1 where d1.von < d2.von) as number,
von from (select von from daten union select bis as von from daten) d2;number |         von
--------+---------------------     0 | 2000-01-01 10:00:00     1 | 2000-01-01 11:00:00     2 | 2000-01-01 12:00:00
3| 2000-01-01 14:00:00     4 | 2000-01-01 15:00:00     5 | 2000-01-01 16:00:00 
(записей: 6)

And another one:

datetest=# select (select count(1) + 1 from (select von from daten union
select bis as von from daten) as d1 where d1.von < d2.von) as number,
von from (select von from daten union select bis as von from daten) d2;number |         von
--------+---------------------     1 | 2000-01-01 10:00:00     2 | 2000-01-01 11:00:00     3 | 2000-01-01 12:00:00
4| 2000-01-01 14:00:00     5 | 2000-01-01 15:00:00     6 | 2000-01-01 16:00:00 
(записей: 6)

Now I want to join these using number and then calculate the average of
both von columns:

datetest=# select * from (select (select count(1) from (select von from
daten union select bis as von from daten) as d1 where d1.von < d2.von)
as number, von from (select von from daten union select bis as von from
daten) d2) as table1 join (select (select count(1) + 1 from (select von
from daten union select bis as von from daten) as d1 where d1.von <
d2.von) as number, von from (select von from daten union select bis as
von from daten) d2) as table2 using (number);
ERROR:  ExecEvalExpr: unknown expression type 108

Is that a bug?

Thanks

--
Markus Bertheau
Cenes Data GmbH
Berlin, Germany