IQ is the key to mixing up. IQ offline always complicates a simple thing, which leads to more time spent doing bad things. Recently, I happened to encounter such an experience. I wrote it down to remind myself.

First Question Feedback

Feedback from the company's technical clothes, the company's products in the process of using the wrong query results, may involve the issue of confidentiality, so that is more general, the original text is as follows:

First IQ Offline

After a brief analysis of the feedback content, I simply recalled that there was no concept of a reference cursor in my mind. Then I went to consult the help documents of the company's products and found no concept of a reference cursor. So I assumed that there was a problem in the expression of the meaning of the other party. Maybe I wanted to say that the stored procedure had parameters, and then I used this parameter in the cursor as a reference. In order to limit the conditions, but after the implementation found that the restrictions did not work, resulting in more results.
There are no use cases, so I plan to write my own use case recurrence problems, and then analyze the reasons.
For example:

create table test(col int);
insert into test values(1);
insert into test values(2);
insert into test values(3);

create or replace procedure sel(con in int) as
declare
    cursor csr for select * from test where con = col;//The col column of the query table test is consistent with the parameter con of the procedure sel
    ret sel % rowtype;
begin
    open csr;
    fetch csr into ret;
    while csr % found loop
        dbms_output.put_line(ret.col);
        fetch csr into ret;
    end loop;
    close csr;
end;

select * from test; //Expected results 3 Articles 12 3

exec sel(1); //There is only one expected result.

exec sel(2); //The expected result is only one 2

exec sel(3); //The expected result is only one 3

Simple implementation, found that the results are in line with expectations, so it is a recurrence failure, so find technical clothes, further understanding.

Second IQ Offline

After simply asking about the technical clothes, the technical clothes said that there was telephone communication, that is the string type. Therefore, the second IQ offline, simple modification of use cases, to the following use cases:

create table test(col varchar(10));
insert into test values('1');
insert into test values('2');
insert into test values('3');

create or replace procedure sel(con in varchar(10)) as
declare
    cursor csr for select * from test where con = col;//The col column of the query table test is consistent with the parameter con of the procedure sel
    ret sel % rowtype;
begin
    open csr;
    fetch csr into ret;
    while csr % found loop
        dbms_output.put_line(ret.col);
        fetch csr into ret;
    end loop;
    close csr;
end;

select * from test; //Expected results 3'1''2''3'

exec sel(1); //There is only one'1'expected result.

exec sel(2); //There is only one'2'expected result.

exec sel(3); //There is only one'3'expected result.

Use case execution, obviously, the results are still in line with expectations, recurrence fails again, carefully reflect on the process, and finally feel that the user said that the problem with a reference cursor is ignored, and finally think of Du Niang. Baidu found that the cursor with reference is real, so it found a simple use case test.
For example:

create or replace procedure a
as
  cursor b(c_id int) is select * from d where id=c_id;
  begin
    open b(111);
  end;

After experimenting with the grammar, I found that the grammar always failed, so I took it for granted again that the company product with the reference cursor that the user said did not support, so according to the user's statement, the second set of reproducing should be successful, but still failed, suspected to be the problem of some of the company's peripheral products. I would like to invite technical clothes to communicate with users again to further identify the problem.

Third IQ Offline

In the process of communication with technical clothing, technical clothing is a responsible person, so say, do not support the use of cursors? So Baidu took a look, executed the above use cases on the company products installed locally, and found that they could pass. Well, it was discovered that although the company help document did not provide instructions, it did support the function of with a reference cursor, and my local grammar was general only because I used a branch version of the product, which did not support it.
Then borrow the machine of technical clothes to construct use cases:

create table test(col varchar(10));
insert into test values('1');
insert into test values('2');
insert into test values('3');

create or replace procedure sel(con in varchar(10)) as
declare
    csr(csr_con varchar(10)) cursor is select * from test where csr_con = col;//The col column of the query table test has the same result as the parameter csr_con of the cursor csr. The value of csr_con is provided by con.
    ret sel % rowtype;
begin
    open csr(con);
    fetch csr into ret;
    while csr % found loop
        dbms_output.put_line(ret.col);
        fetch csr into ret;
    end loop;
    close csr;
end;

exec sel('1'); //Expect an outcome of'1'
exec sel('2'); //Expect an outcome of'2'
exec sel('3'); //Expect an outcome of'3'

After execution, the results still meet the expectations, indicating that the replication is still failing, feeling that the current conditions can not determine the problem, so please technical uniforms and users to communicate again.

Second question feedback

Still due to confidentiality, so this communication only got two photos and a paragraph, because no suitable map bed was found, the pictures were not posted here. The main content of the picture is the name and parameters of the process, as well as the definition of the cursor.

Fourth IQ Offline

Because use cases and data are still incomplete, but there is a rough outline, the use cases are reproduced based on these:

create table rt_ttapall(rsltype varchar(10), snum number, pname varchar(10), palia varchar(10), 
    missid number, objid number, classid varchar(10),
    subsys number);

insert into rt_tta_pall values('KA0', 0, 'KeyNo0', 'GNCC When',
    11, 1, 'AA',
    10);
insert into rt_tta_pall values('KA0', 0, 'KeyNo0', 'GNCC When',
    11, 1, 'AA',
    10);
insert into rt_tta_pall values('KA0', 0, 'KeyNo0', 'GNCC When',
    12, 1, 'AA',
    10);
insert into rt_tta_pall values('KA0', 0, 'KeyNo0', 'GNCC When',
    11, 2, 'AA',
    10);
insert into rt_tta_pall values('KA0', 0, 'KeyNo0', 'GNCC When',
    11, 1, 'BB',
    10);

create or replace procedure pro_in_telpara(t_telmissid in numeric(10, 0), t_telobjid in numeric(10, 0), t_classid in varchar(10)) as
declare
    t_rslttype varchar(10);
    t_snum number;
    t_pname varchar(10);
    t_palia varchar(10);

    cursor cur_telpara(missno number, objno number, classid varchar(10)) is
        select rsltype, snum, pname, palia
        from rt_ttapall
        where MISSID = missno and CLASSID = classid and OBJID = objno
        order by subsys, snum;
begin
    open cur_telpara(t_telmissid, t_telobjid, t_classid);
    fetch cur_telpara into t_rslttype, t_snum, t_pname, t_palia;

    while cur_telpara % found loop //Output all eligible results
        dbms_output.put_line(t_rslttype + ' ' + t_snum + ' ' + t_pname + ' ' + t_palia);
        fetch cur_telpara into t_rslttype, t_snum, t_pname, t_palia;
    end loop;
    dbms_output.put_line(cur_telpara % rowcount); //Number of output results 
    close cur_telpara;
end;

--Execution process
exec pro_in_telpara(11, 1, 'AA');

--Direct query
select rsltype, snum, pname, palia
from rt_ttapall
where MISSID = 11 and OBJID = 1 and CLASSID = 'AA' 
order by subsys, snum;

In the case above, the results of execution on both sides are expected to be identical. They are two results, i.e. two data filtered according to the three conditions of 11'AA'. However, after execution, it is found that the result of direct execution of select statement is correct, but there is one more result after execution of stored procedure, which looks very similar to the failure or loss of condition of'AA'conditional filtering. So again, I take it for granted that the user's instructions have been successfully reproduced, and I take it for granted that the results of the above two use cases should be the same. I notify the user of the technical uniform and say that it has been successfully reproduced. It is indeed a problem on our side. I will start to modify it immediately.

Fifth IQ Offline

Successful recurrence of the problem, and then began to modify, the first analysis is the cursor processing process will lose the'AA'condition, so I want to confirm whether there is this condition in the final implementation plan. However, when the process is executed, the background will not output the corresponding execution plan. There is no way to do this. I want to check the query plan of select first, and expect that the cursor mode will generate the corresponding plan to see the difference of the plan.
select's plan is very simple. In the process of execution, three values are regarded as scan key which is directly lowered to the bottom level as Const. The number of returned data bars of the operator is determined according to three conditions, and then the upper level sort operator performs the order by process. In the process of code debugging, it is found that this is true. The three conditions are Expr expressions, then Var yesterday, Const on the right, which confirms the assumption. That is to say, the number of returned results is determined by scan key. As long as the condition of scan key generated by cursor mode is checked, the breakpoint is made at the position where scan key is generated and debugged. .
During debugging, it is found that the same scan key will be generated by cursor mode, but the above conditions are not the same. The conditions of 11 and 1 are similar to the plan of select, one side is Var, the other side is Param. But just because the'AA'condition is inconsistent, it's a Null Test, that is to say, it only judges whether it's empty or not. So again, I take it for granted that the previous assumption has been validated. It is true that the'AA'condition is lost in the process of plan generation and turned into NullTest, so I step by step backwards, hoping to find the location of the problem.
The location where the plan was generated is NullTest.
2 Query generates knowledge that Expr has become empty.
3 …
While debugging was continuing, thinking that the cause of the problem would be found soon, the technical uniform received a call from the user, saying that the cursor parameter was changed to classno in the use case he executed, and the problem would disappear, there would be no problem, there would be no problem.
With a sigh of relief, how could it be possible? The problem of renaming is solved. What does it have to do with names?
So go back to the use case just now and analyze the cursor part. It is found that according to the user's operation, where condition MISSID = missno and CLASSID = classid and OBJID = objno will become MISSID = missno and CLASSID = classno and OBJID = objno. The difference is that the classid becomes classno, and the result is correct.
So what is the problem? Is the execution result also related to the field name? That's impossible. How can it be used?
After careful analysis, it will be recalled that our product is case-insensitive. That is to say, MISSID = missno and CLASSID = CLassID and OBJID = objno is actually MISSID = MISSNO and CLASSID = CLASSID and OBJID = OBJNO. The middle conditions are the same on both sides! So is it because of this that the condition becomes the field CLASSID in the table = the field CLASSID in the table, which is true, equivalent to no condition...
So we resolutely changed the cursor parameter objno to objid, and found that the condition would not work, and if the cursor parameter is not consistent with the field in the table, the execution results are correct, well, the problem is really this.
That is to say, such a long use case recurrence and debugging are doing useless work, useless work, useless work!

summary

1. When you can't get specific use cases, make sure that your environment is consistent with the user's, or that the differences will never affect the replication in the process of writing use cases or replicating them by yourself! ________ Avoid more detours.
2. Do your best to communicate with the person who raised the question. If communication is inconvenient, then everything should be based on the statement of the person who raised the question. Of course, the problem poser may also make mistakes. In addition to the objective and practical errors, all the descriptions of the believers can make you understand others'meanings as much as possible, without misinterpreting others' meanings, leading to detours.
3. When getting the user's use case, we must carefully analyze the desired results of the use case, and determine whether the use case will get the desired results according to the products of our company.

Epilogue

The problems described in this paper are the actual record of a problem encountered in the work, which is recorded here to summarize the reasons and give yourself an alarm.