BUG #5924: bug or feature?

Started by yoursoft@freemail.huabout 15 years ago3 messagesbugs
Jump to latest
#1yoursoft@freemail.hu
yoursoft@freemail.hu

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?:

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_test2 AS
SELECT * from 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

#2John R Pierce
pierce@hogranch.com
In reply to: yoursoft@freemail.hu (#1)
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 ?

Show quoted text

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

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: yoursoft@freemail.hu (#1)
Re: BUG #5924: bug or feature?

"Ferenc Lutischan" <yoursoft@freemail.hu> writes:

Description: bug or feature?

Feature. If we weren't willing to push qualifications down into views,
they'd be far too slow to be useful.

regards, tom lane