# ITPUB SQL大赛之BUG（二）

ITPUB SQL

SQL> with i as
2  (select rownum i from dual connect by rownum <= :n),
3  j as
4  (select rownum j from dual connect by rownum <= :n),
5  position as
6  (select i, j
7  from i, j),
8  b as
9  (select rownum - 1 b from dual connect by rownum <= 2),
10  b_line as
11  (select replace(sys_connect_by_path(b, ','), ',', '') line
12  from b
13  where connect_by_isleaf = 1
14  connect by level <= :n),
15  lines as
16  (select line from b_line
17  where instr(line, 1, 1, :m) > 0
18  and instr(line, 0, 1, :n - :m) > 0),
19  lines_result as
20  (select replace(sys_connect_by_path(line, ','), ',', '') result
21  from lines
22  where connect_by_isleaf = 1
23  connect by level <= :n)
24  select :m M, :n N, count(*) AllCnt
25  from (
26  select result
27  from (
28  select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
29  from lines_result, position
30  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
31  group by result
32  having max(case when l is null and r is null then c end) = :m
33  and max(case when j is null and l is null then c end) = :m
34  and max(case when j is null and r is null then c end) = :m
35  );

M          N     ALLCNT
---------- ---------- ----------
2          5        931

SQL> with i as
2  (select rownum i from dual connect by rownum <= :n),
3  j as
4  (select rownum j from dual connect by rownum <= :n),
5  position as
6  (select i, j
7  from i, j),
8  b as
9  (select rownum - 1 b from dual connect by rownum <= 2),
10  b_line as
11  (select replace(sys_connect_by_path(b, ','), ',', '') line
12  from b
13  where connect_by_isleaf = 1
14  connect by level <= :n),
15  lines as
16  (select line from b_line
17  where instr(line, 1, 1, :m) > 0
18  and instr(line, 0, 1, :n - :m) > 0),
19  lines_result as
20  (select replace(sys_connect_by_path(line, ','), ',', '') result
21  from lines
22  where connect_by_isleaf = 1
23  connect by level <= :n)
24  select /*+ optimizer_features_enable('10.2.0.3') */ :m M, :n N, count(*) AllCnt
25  from (
26  select result
27  from (
28  select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
29  from lines_result, position
30  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
31  group by result
32  having max(case when l is null and r is null then c end) = :m
33  and max(case when j is null and l is null then c end) = :m
34  and max(case when j is null and r is null then c end) = :m
35  );

M          N     ALLCNT
---------- ---------- ----------
2          5         92

SQL> alter session set optimizer_features_enable = '10.2.0.4';

SQL> with i as
2  (select rownum i from dual connect by rownum <= :n),
3  j as
4  (select rownum j from dual connect by rownum <= :n),
5  position as
6  (select i, j
7  from i, j),
8  b as
9  (select rownum - 1 b from dual connect by rownum <= 2),
10  b_line as
11  (select replace(sys_connect_by_path(b, ','), ',', '') line
12  from b
13  where connect_by_isleaf = 1
14  connect by level <= :n),
15  lines as
16  (select line from b_line
17  where instr(line, 1, 1, :m) > 0
18  and instr(line, 0, 1, :n - :m) > 0),
19  lines_result as
20  (select replace(sys_connect_by_path(line, ','), ',', '') result
21  from lines
22  where connect_by_isleaf = 1
23  connect by level <= :n)
24  select :m M, :n N, count(*) AllCnt
25  from (
26  select result
27  from (
28  select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
29  from lines_result, position
30  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
31  group by result
32  having max(case when l is null and r is null then c end) = :m
33  and max(case when j is null and l is null then c end) = :m
34  and max(case when j is null and r is null then c end) = :m
35  );

M          N     ALLCNT
---------- ---------- ----------
2          5         92

SQL> with i as
2  (select rownum i from dual connect by rownum <= :n),
3  j as
4  (select rownum j from dual connect by rownum <= :n),
5  position as
6  (select i, j
7  from i, j),
8  b as
9  (select rownum - 1 b from dual connect by rownum <= 2),
10  b_line as
11  (select replace(sys_connect_by_path(b, ','), ',', '') line
12  from b
13  where connect_by_isleaf = 1
14  connect by level <= :n),
15  lines as
16  (select line from b_line
17  where instr(line, 1, 1, :m) > 0
18  and instr(line, 0, 1, :n - :m) > 0),
19  lines_result as
20  (select replace(sys_connect_by_path(line, ','), ',', '') result
21  from lines
22  where connect_by_isleaf = 1
23  connect by level <= :n)
24  select /*+ optimizer_features_enable('11.1.0.6') */ :m M, :n N, count(*) AllCnt
25  from (
26  select result
27  from (
28  select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
29  from lines_result, position
30  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
31  group by result
32  having max(case when l is null and r is null then c end) = :m
33  and max(case when j is null and l is null then c end) = :m
34  and max(case when j is null and r is null then c end) = :m
35  );
select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
*
28 行出现错误:
ORA-00976:

SQL> alter session set optimizer_features_enable = '11.1.0.6';

SQL> with i as
2  (select rownum i from dual connect by rownum <= :n),
3  j as
4  (select rownum j from dual connect by rownum <= :n),
5  position as
6  (select i, j
7  from i, j),
8  b as
9  (select rownum - 1 b from dual connect by rownum <= 2),
10  b_line as
11  (select replace(sys_connect_by_path(b, ','), ',', '') line
12  from b
13  where connect_by_isleaf = 1
14  connect by level <= :n),
15  lines as
16  (select line from b_line
17  where instr(line, 1, 1, :m) > 0
18  and instr(line, 0, 1, :n - :m) > 0),
19  lines_result as
20  (select replace(sys_connect_by_path(line, ','), ',', '') result
21  from lines
22  where connect_by_isleaf = 1
23  connect by level <= :n)
24  select :m M, :n N, count(*) AllCnt
25  from (
26  select result
27  from (
28  select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
29  from lines_result, position
30  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
31  group by result
32  having max(case when l is null and r is null then c end) = :m
33  and max(case when j is null and l is null then c end) = :m
34  and max(case when j is null and r is null then c end) = :m
35  );
select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
*
28 行出现错误:
ORA-00976:

• 博文量
1954
• 访问量
10763821