ExecInitIndexScan: both left and right ops are rel-vars
And here is an old nemesis, thought to have been fixed in 7.0,
reproducible on 7.0beta3 with the following:
DROP TABLE foo;
CREATE TABLE foo (d date);
CREATE UNIQUE INDEX date_uidx ON foo(d);
CREATE UNIQUE INDEX datetime_uidx ON foo(datetime(d));
INSERT INTO foo (d) VALUES ('17-Jun-1995');
DROP TABLE bar;
DROP SEQUENCE bar_id_seq;
CREATE TABLE bar (
id SERIAL,
start_time DATETIME,
duration FLOAT
);
INSERT INTO bar (start_time, duration) VALUES ('17-Jun-1995', 3);
DROP TABLE baz;
DROP SEQUENCE baz_id_seq;
CREATE TABLE baz (
id SERIAL,
bar_id INTEGER,
duration FLOAT
);
INSERT INTO baz (bar_id, duration) SELECT id, duration FROM bar;
-- Here's the offending query...
SELECT f.d, r.start_time::date, r.duration AS "r_dur", z.duration AS
"z_dur"
FROM foo f, bar r, baz z
WHERE r.id = 2
AND z.bar_id = 2
AND f.d = (r.start_time - '1 day'::interval)::date ;
Ed Loehr wrote:
And here is an old nemesis, thought to have been fixed in 7.0,
reproducible on 7.0beta3 with the following:DROP TABLE foo;
CREATE TABLE foo (d date);
CREATE UNIQUE INDEX date_uidx ON foo(d);
CREATE UNIQUE INDEX datetime_uidx ON foo(datetime(d));
INSERT INTO foo (d) VALUES ('17-Jun-1995');DROP TABLE bar;
DROP SEQUENCE bar_id_seq;
CREATE TABLE bar (
id SERIAL,
start_time DATETIME,
duration FLOAT
);
INSERT INTO bar (start_time, duration) VALUES ('17-Jun-1995', 3);DROP TABLE baz;
DROP SEQUENCE baz_id_seq;
CREATE TABLE baz (
id SERIAL,
bar_id INTEGER,
duration FLOAT
);
INSERT INTO baz (bar_id, duration) SELECT id, duration FROM bar;
A final clue: if I run 'VACUUM ANALYZE' at this point in the script,
before the select, the error disappears.
Regards,
Ed Loehr
Show quoted text
-- Here's the offending query...
SELECT f.d, r.start_time::date, r.duration AS "r_dur", z.duration AS
"z_dur"
FROM foo f, bar r, baz z
WHERE r.id = 2
AND z.bar_id = 2
AND f.d = (r.start_time - '1 day'::interval)::date ;
