Ingres Community Forum

kuonirat · 2010-07-27

Hi!

This:

Code:

DROP TABLE u;
CREATE TABLE u (
  id int NOT NULL,
  birth_date timestamp(0),
  sex char(1)
) WITH STRUCTURE = VECTORWISE;

DROP TABLE nwt;
CREATE TABLE nwt (
  user_id int NOT NULL,
  added timestamp(0) NOT NULL,
  n int NOT NULL,
  status tinyint
) WITH STRUCTURE = VECTORWISE;

DROP TABLE pur;
CREATE TABLE pur (
  id int NOT NULL,
  datetime timestamp(0) NOT NULL,
  user_id int NOT NULL
) WITH STRUCTURE = VECTORWISE;

SELECT nwt.* FROM (
    SELECT 
        (YEAR(t.added) - YEAR(birth_date) - (
            CASE WHEN
                MONTH(t.added) > MONTH(birth_date)
                OR MONTH(t.added) = MONTH(birth_date)
                AND DAY(t.added) >= DAY(birth_date)
            THEN 0 ELSE 1 END)
        ) AS age
    FROM nwt t
    INNER JOIN u ON (t.user_id = u.id)
    GROUP BY age
) nwt
INNER JOIN (

    SELECT 
        (YEAR(pur.datetime) - YEAR(birth_date) - (
            CASE WHEN
                MONTH(pur.datetime) > MONTH(birth_date)
                OR MONTH(pur.datetime) = MONTH(birth_date)
                AND DAY(pur.datetime) >= DAY(birth_date)
            THEN 0 ELSE 1 END)
        ) AS age 

    FROM pur
    INNER JOIN u ON (pur.user_id = u.id)
    GROUP BY age
) nwpp ON (
    nwt.age = nwpp.age
)

\g

Makes an error visible in vectorwise.log:

Code:

2010-07-27 13:51:47 PID  9318: INFO:X100SERVER:received query: [Project (Aggr (Project (HashJoinN ( Aggr (HashJoinN ( Aggr (MScan ( _t = '_nwt', [ '_user_id', '_added'] ) [ 'est_card' = '1' ] , [ _t._user_id, _t._added], []), [ _t._user_id ], Aggr (MScan ( _u = '_u', [ '_id', '_birth_date'] ) [ 'est_card' = '1' ] , [ _u._id, _u._birth_date], []) , [ _u._id ], 0 ) [ 'est_card' = '1' ] , [ _t._added, _u._birth_date, FA0], []), [ FA0 ], Aggr (HashJoinN ( Aggr (MScan ( _pur = '_pur', [ '_user_id', '_datetime'] ) [ 'est_card' = '1' ] , [ _pur._user_id, _pur._datetime], []), [ _pur._user_id ], Aggr (MScan ( _u000 = '_u', [ '_id', '_birth_date'] ) [ 'est_card' = '1' ] , [ _u000._id, _u000._birth_date], []) , [ _u000._id ], 0 ) [ 'est_card' = '1' ] , [ _u000._birth_date, _pur._datetime, FA1], []) , [ FA1 ], 0 ) [ 'est_card' = '1' ] , [_TRSDM_3 = +(+(year(_t._added), -(year(_u._birth_date))), -(ifthenelse( || (>(month(_t._added),month(_u._birth_date)), && (==(month(_t._added),month(_u._birth_date)), >=(day(_t._added),day(_u._birth_date)))),  sint('0'),  sint('1')))), _TRSDM_2 = +(+( -(year(_u000._birth_date)),year(_pur._datetime)), -(ifthenelse( || (>(month(_pur._datetime),month(_u000._birth_date)), && (==(month(_pur._datetime),month(_u000._birth_date)), >=(day(_pur._datetime),day(_u000._birth_date)))),  sint('0'),  sint('1'))))] ), [_TRSDM_2, _TRSDM_3] , [ ]), [_age_1 = _TRSDM_3] )]
2010-07-27 13:51:47 PID  9318:ERROR:REWRITER:rewriter(type): could not find column FA0

Though I think, it should work fine or a syntax error (if any) should point, what exactly is wrong with the query.

Best regards!

makni01 · 2010-07-27

Hi Konrad

Thanks for reporting this. I have opened Mantis #900 to track this. This works in regular Ingres so it shouldn't be something hard to fix.
Nick

zelaine · 4 Hours Ago

This issue has been fixed in the now available 1.0/11402 build.