Thread: ERROR: ExecEvalExpr: unknown expression type 108

ERROR: ExecEvalExpr: unknown expression type 108

From
Markus Bertheau
Date:
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



Re: ERROR: ExecEvalExpr: unknown expression type 108

From
Rod Taylor
Date:
> 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?

Yes.  Regardless of whether this is a valid SQL statement, it shouldn't
be caught in this manner.

SubSelects have undergone a large simplification by Tom while
implementing read-only plans in the executor.  This seems to have
accidentally fixed this case.

rbt=# create table daten
rbt-# ( id serial not null primary key
rbt(# , menge integer
rbt(# , von timestamp(0) without time zone
rbt(# , bis timestamp(0) without time zone
rbt(# );
NOTICE:  CREATE TABLE will create implicit sequence 'daten_id_seq' for
SERIAL column 'daten.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'daten_pkey' for table 'daten'
CREATE TABLE
rbt=#
rbt=# INSERT INTO daten
rbt-#      VALUES (DEFAULT, 2, '2000-01-01 10:00:00', '2000-01-01
12:00:00');
INSERT 17132 1
rbt=#
rbt=#
rbt=# INSERT INTO daten
rbt-#      VALUES (DEFAULT, 3, '2000-01-01 11:00:00', '2000-01-01
14:00:00');
INSERT 17133 1
rbt=#
rbt=#
rbt=# INSERT INTO daten
rbt-#      VALUES (DEFAULT, 1, '2000-01-01 14:00:00', '2000-01-01
15:00:00');
INSERT 17134 1
rbt=#
rbt=# INSERT INTO daten
rbt-#      VALUES (DEFAULT, 8, '2000-01-01 12:00:00', '2000-01-01
16:00:00');
INSERT 17135 1
rbt=#
rbt=#
rbt=# INSERT INTO daten
rbt-#      VALUES (DEFAULT, 4, '2000-01-01 10:00:00', '2000-01-01
11:00:00');
INSERT 17136 1
rbt=#
rbt=# select * from (select (select count(1) from (select von from
rbt(# daten union select bis as von from daten) as d1 where d1.von <
d2.von)
rbt(# as number, von from (select von from daten union select bis as von
from
rbt(# daten) d2) as bar join (select (select count(1)+1 from (select von
from
rbt(# daten union select bis as von from daten) as d1 where d1.von <
d2.von)
rbt(# as number, von from (select von from daten union select bis as von
from
rbt(# daten) d2) as foo using (number);number |         von         |         von
--------+---------------------+---------------------     1 | 2000-01-01 11:00:00 | 2000-01-01 10:00:00     2 |
2000-01-0112:00:00 | 2000-01-01 11:00:00     3 | 2000-01-01 14:00:00 | 2000-01-01 12:00:00     4 | 2000-01-01 15:00:00
|2000-01-01 14:00:00     5 | 2000-01-01 16:00:00 | 2000-01-01 15:00:00 
(5 rows)
rbt=# select version();                               version
------------------------------------------------------------------------PostgreSQL 7.4devel on i386-unknown-freebsd4.8,
compiledby GCC 2.95.4 
(1 row)


--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Re: ERROR: ExecEvalExpr: unknown expression type 108

From
Tom Lane
Date:
Markus Bertheau <twanger@bluetwanger.de> writes:
>  PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2

> 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

7.3 has a problem with subselects referenced as join outputs.  There is
a fix in 7.3.1, and a better fix in place for 7.4.
        regards, tom lane


Re: ERROR: ExecEvalExpr: unknown expression type 108

From
Tom Lane
Date:
Rod Taylor <rbt@rbt.ca> writes:
> SubSelects have undergone a large simplification by Tom while
> implementing read-only plans in the executor.  This seems to have
> accidentally fixed this case.

Nothing accidental about it.
http://archives.postgresql.org/pgsql-general/2002-12/msg00375.php
        regards, tom lane