[HACKERS] Q: "unknown expression type 108" ?

[Ian Barwick]

Hi

appended below is a simple database schema (which may not
be a candidate for the next Nobel Prize for SQL Database
Design, but represents enough of a production
database to demonstrate the following problem).

And that is:

under 7.3 this statement:
     SELECT foo_id, thingy_name, bar_name
       FROM foo_view, bar
      WHERE bar_id=foo_bar_id

produces the desired results.

This however:
     SELECT foo_id, thingy_name, bar_name
       FROM foo_view
 INNER JOIN bar ON bar_id=foo_bar_id

produces

 ERROR:  ExecEvalExpr: unknown expression type 108

The latter statement does however work in 7.1.3 with no
apparent problems.

Question: what does "unknown expression type 108" mean and
why should it suddenly occur in 7.3? A bit of Googling
reveals the same message occurs when using subselects
in constraints, but that doesn't seem related to this case.


Ian Barwick
[EMAIL PROTECTED]


-- sample DB for "unknown expression type 108" error

CREATE TABLE a_thingy (
  a_id INT,
  a_firstname VARCHAR(64),
  a_lastname VARCHAR(64),
  PRIMARY KEY (a_id)
);

CREATE TABLE b_thingy (
  b_id INT,
  b_name VARCHAR(64),
  PRIMARY KEY (b_id)
);

CREATE TABLE bar (
  bar_id INT,
  bar_name varchar(64),
  PRIMARY KEY (bar_id)
);

CREATE TABLE foo (
  foo_id INT,
  foo_a_id INT REFERENCES a_thingy NULL, 
  foo_b_id INT REFERENCES b_thingy NULL,
  foo_bar_id INT REFERENCES bar NOT NULL,
  PRIMARY KEY (foo_id),
  CHECK((foo_a_id IS NOT NULL AND foo_b_id IS NULL) OR
        (foo_b_id IS NOT NULL AND foo_a_id IS NULL))
);

CREATE VIEW foo_view AS
  SELECT *,
         CASE
           WHEN foo_a_id IS NOT NULL THEN 
              (SELECT a_lastname || ', ' || a_firstname
                 FROM a_thingy
                WHERE a_id=foo_a_id
              )
           WHEN foo_b_id IS NOT NULL THEN 
              (SELECT b_name
                 FROM b_thingy
                WHERE b_id=foo_b_id
              )
         END 
           AS thingy_name
    FROM foo;

INSERT INTO a_thingy VALUES
 (1, 'John', 'Doe');

INSERT INTO b_thingy VALUES
 (1, 'Megacorp');

INSERT INTO bar VALUES(1, 'squid');
INSERT INTO bar VALUES(2, 'octopus');

INSERT INTO foo VALUES (1,1,NULL,1);
INSERT INTO foo VALUES (2,NULL,1,2);

-- END


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]