pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: ExecEvalExpr: unknown expression type 108
4+ messages / 3 participants
[nested] [flat]
* ERROR: ExecEvalExpr: unknown expression type 108
@ 2003-06-30 16:10 Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 2 replies; 4+ messages in thread
From: Markus Bertheau @ 2003-06-30 16:10 UTC (permalink / raw)
To: pgsql-sql; +Cc: Horst Schwarz <schwarz@bab24.de>
Hi,
datetest=# select version();
version
----------------------------------------------------------------------
PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC gcc (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 time zone |
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-01 12: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:00
2000-01-01 11:00:00
2000-01-01 12:00:00
2000-01-01 14:00:00
2000-01-01 15:00:00
2000-01-01 16: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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ERROR: ExecEvalExpr: unknown expression type 108
@ 2003-06-30 16:41 Rod Taylor <rbt@rbt.ca>
parent: Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 1 reply; 4+ messages in thread
From: Rod Taylor @ 2003-06-30 16:41 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql; Horst Schwarz <schwarz@bab24.de>
> 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-01 12: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, compiled by GCC 2.95.4
(1 row)
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ERROR: ExecEvalExpr: unknown expression type 108
@ 2003-06-30 16:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-06-30 16:50 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql; Horst Schwarz <schwarz@bab24.de>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ERROR: ExecEvalExpr: unknown expression type 108
@ 2003-06-30 17:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rod Taylor <rbt@rbt.ca>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-06-30 17:04 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: Markus Bertheau <twanger@bluetwanger.de>; pgsql-sql; Horst Schwarz <schwarz@bab24.de>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-06-30 17:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-30 16:10 ERROR: ExecEvalExpr: unknown expression type 108 Markus Bertheau <twanger@bluetwanger.de>
2003-06-30 16:41 ` Rod Taylor <rbt@rbt.ca>
2003-06-30 17:04 ` Re: ERROR: ExecEvalExpr: unknown expression type 108 Tom Lane <tgl@sss.pgh.pa.us>
2003-06-30 16:50 ` Re: ERROR: ExecEvalExpr: unknown expression type 108 Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox