Re: BUG #5924: bug or feature?

On 03/11/11 12:18 AM, Ferenc Lutischan wrote:
> The following bug has been logged online:
>
> Bug reference:      5924
> Logged by:          Ferenc Lutischan
> Email address:      yoursoft@freemail.hu
> PostgreSQL version: 8.3.14 - 9.0.3
> Operating system:   Linux
> Description:        bug or feature?
> Details:
>
> Dear Developers,
>
> Inconsecutive view handling?:
>

Its totally unclear to me what you're showing with the follwing sequence
of operations.

could you explain it perhaps ?


> CREATE TABLE ifck.test_tipus
> (
>    tipus character varying(2) NOT NULL,
>    tipus_kod character varying(2) NOT NULL,
>    CONSTRAINT pk_test_kod PRIMARY KEY (tipus)
> )
> WITH (
>    OIDS=TRUE
> );
>
> insert into ifck.test_tipus values ('GX', 'GG');
> insert into ifck.test_tipus values ('GY', 'GG');
> insert into ifck.test_tipus values ('EX', 'EE');
>
> CREATE TABLE ifck.test
> (
>    tipus character varying(2) NOT NULL,
>    status boolean NOT NULL,
>    id character varying(20) NOT NULL,
>    datum character varying(8) NOT NULL,
>    CONSTRAINT fk_test_tipus FOREIGN KEY (tipus)
>        REFERENCES ifck.test_tipus (tipus) MATCH SIMPLE
>        ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> WITH (
>    OIDS=TRUE
> );
>
> insert into ifck.test values ('EX', true, '123456', '');
>
> insert into ifck.test values ('GX', true, '123456', '20000101');
>
> insert into ifck.test values ('GY', true, '123456', '20000101');
>
> insert into ifck.test values ('GX', true, '123454', '19991231');
>
> insert into ifck.test values ('GY', false, '123454', '');
>
> insert into ifck.test values ('GY', true, '123454', '19991231');
>
> CREATE OR REPLACE VIEW ifck.v_test AS
>   SELECT t.*, tt.tipus_kod from ifck.test t
>    JOIN ifck.test_tipus tt ON t.tipus::text = tt.tipus::text and status;
>
> CREATE OR REPLACE VIEW ifck.v_testom ifck.v_test
>    where tipus_kod='GG' ;
>
> CREATE OR REPLACE VIEW ifck.v_test3 AS
>   SELECT * from ifck.v_test
>    where tipus like 'G%'  and status;
>
> /*
> select * from ifck.v_test2 where id='123456';
>
> result:
> "GX";t;"123456";"20000101";"GG"
> "GY";t;"123456";"20000101";"GG"
> */
>
> /*
> select * from ifck.v_test3 where id='123456';
>
> result:
> "GX";t;"123456";"20000101";"GG"
> "GY";t;"123456";"20000101";"GG"
> */
>
> /*
> select * from ifck.v_test2 where id='123454';
> result:
> "GX";t;"123454";"19991231";"GG"
> "GY";t;"123454";"19991231";"GG"
> */
>
> /*
> select * from ifck.v_test3 where id='123454';
>
> result:
> "GX";t;"123454";"19991231";"GG"
> "GY";t;"123454";"19991231";"GG"
> */
>
> /*
> select * from ifck.v_test2 where datum::date=datum::date and id='123456';
>
> result:
> ERROR:  invalid input syntax for type date: ""
> ********** Error **********
> ERROR: invalid input syntax for type date: ""
> SQL state: 22007
> */
>
> /*
> select * from ifck.v_test2 where datum::date=datum::date and id='123454';
>
> result:
> "GX";t;"123454";"19991231";"GG"
> "GY";t;"123454";"19991231";"GG"
> */
>
> /*
> select * from ifck.v_test3 where datum::date=datum::date and id='123454';
>
> result:
> "GX";t;"123454";"19991231";"GG"
> "GY";t;"123454";"19991231";"GG"
> */
>
> Regards,
>       Ferenc Lutischan
>