Commented by: @asfernandes

Cannot reproduce in HEAD:

SQL> create or alter procedure PROC1 returns (proc char(50), acto char(50))
CON> as
CON> begin
CON> proc = '1R1oK3qxdM';
CON> acto = '1R1oK3qxdM';
CON> suspend;
CON> end!
SQL>
SQL> select * from (select cast("ACTO" as character(100)) as "D_COL1" from "PROC1" where "PROC"='1R1oK3qxdM') AA!

D_COL1

1R1oK3qxdM

Please create a reproducible test case.

Commented by: Daniel (danyschaer)

Hi Adriano; I found the problem is when working with a view, but not if I work with tables:

CREATE TABLE PROC(
PROC Char(10) NOT NULL,
GRUP Char(1) NOT NULL,
TPRO Char(10) NOT NULL,
ACTO Varchar(200) NOT NULL,
DEMA Varchar(200) NOT NULL,
OBSE Varchar(120) NOT NULL,
CARP Char(18) NOT NULL,
INIC Char(8) NOT NULL,
FINA Char(8) NOT NULL,
FCAP Char(8) NOT NULL,
CAPI Varchar(19) NOT NULL,
PRES Varchar(19) NOT NULL,
DOCO Char(10) NOT NULL,
OJUD Char(10) NOT NULL,
INST Char(1) NOT NULL,
EXP1 Char(18) NOT NULL,
EXP2 Char(18) NOT NULL,
EXP3 Char(18) NOT NULL,
EXP4 Char(18) NOT NULL,
SUPE Char(1) NOT NULL,
MIEM Char(10) NOT NULL,
AUX1 Varchar(40) NOT NULL,
AUX2 Varchar(40) NOT NULL,
AUX3 Varchar(40) NOT NULL,
AUX4 Varchar(40) NOT NULL,
AUX5 Varchar(40) NOT NULL,
CONSTRAINT PROC_PK PRIMARY KEY (PROC)
);
CREATE INDEX PROC_GRUP ON PROC (GRUP);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON PROC TO LEX WITH GRANT OPTION;

CREATE TABLE TPRO(
TPRO Char(10) NOT NULL,
OWNR Char(10) NOT NULL,
ORDE Char(10) NOT NULL,
DSCR Varchar(120) NOT NULL,
CONSTRAINT TPRO_PK PRIMARY KEY (TPRO)
);
CREATE INDEX TPRO_OWNR ON TPRO (OWNR);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON TPRO TO LEX WITH GRANT OPTION;

CREATE VIEW PROC1 (PROC, ACTO, DEMA, OBSE, D_TPRO)
AS SELECT "PROC"."PROC","PROC"."ACTO","PROC"."DEMA","PROC"."OBSE","TPRO"."DSCR" AS "D_TPRO" FROM "PROC" left outer join "TPRO" on "PROC"."TPRO"="TPRO"."TPRO";
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON PROC1 TO LEX WITH GRANT OPTION;

If I do:

select * from (select cast("ACTO" as character(100)) as "D_COL1" from "PROC1" where "PROC"='1R1oK3qxdM') AA

returns NULL, but if I do:

select * from (select cast("ACTO" as character(100)) as "D_COL1" from "PROC" where "PROC"='1R1oK3qxdM') AA

returns the correct value. It also returns fine if I do:

select * from (select "ACTO" as "D_COL1" from "PROC1" where "PROC"='1R1oK3qxdM') AA

... so I think the view is working fine; it seams the problem becomes when casting the fields from the view.

Dany

Commented by: @asfernandes

Ok. Simple reproducable metadata and data for the test:
--------------
CREATE TABLE PROC(
PROC Char(10) NOT NULL,
TPRO Char(10) NOT NULL,
ACTO Varchar(200) NOT NULL,
CONSTRAINT PROC_PK PRIMARY KEY (PROC)
);

CREATE TABLE TPRO(
TPRO Char(10) NOT NULL,
CONSTRAINT TPRO_PK PRIMARY KEY (TPRO)
);

CREATE VIEW PROC1 (PROC, ACTO)
AS SELECT "PROC"."PROC","PROC"."ACTO" FROM "PROC" left outer join "TPRO" on "PROC"."TPRO"="TPRO"."TPRO";

insert into proc values ('1R1oK3qxdM', '1', '2');
insert into tpro values ('1');
--------------

Commented by: Daniel (danyschaer)

Thank you. Did you see the problem now?.

Commented by: @asfernandes

Yes, will look at it soon.

Commented by: @asfernandes

Please test the fix on a new build.

Commented by: @pcisar

QA test added.