Shulou(Shulou.com)06/01 Report--

When I was working on a project, I stumbled upon a bug on the page. Record here:

First of all, manually copy the sql statement output from the background into the oracle to troubleshoot the error. Comparing the result set of the first 10 items in the following view with the result set of 10 to 20 items, it is found that most of the records are duplicated. The SQL statement is as follows:

-the first 10 records select * from (select row_.*, rownum rownum_ from (select t.idcard, count (1) from sampling.v_unvoucher_blacklist t where 1 = 1 group by t.idcard order by count (1) asc) row_ where rownum 0 -Article 11-Article 20 records select * from (select row_.*, rownum rownum_ from (select t.idcard, count (1) from sampling.v_unvoucher_blacklist t where 1 = 1 group by t.idcard order by count (1) asc) row_ where rownum 10

When the above errors occur, we first repeatedly add / delete the conditions after where, and keep testing, and seem to find some clues. The error records all occurred at the same value of count (1), so we searched the Internet for a while, but still did not find a solution.

There was no way. Finally, I went to find some demo on the official website to observe the usage. After exploring and groping, I got the following version, which can return the correct result set after testing. After modification, it is as follows:

Select * from (select row_1.*, rownum rownum_ from (select * from (select t.idcard idcard, count (1) total from sampling.v_unvoucher_blacklist t where 1 = 1 group by t.idcard) order by total,rownum asc) row_1 where rownum 0

Summary: order by statements should follow the principle of conditional uniqueness, otherwise oracle does not guarantee that the result set of each query will be the same.

For more information, please refer to oracle's official website:

Http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.