oracle listagg函数bug记录

一时失言乱红尘 2021-09-29 01:14 375阅读 0赞

**测试数据在文章的最后给出**

select *
    from test_190619_lagg_bug_1 t1,
         (select t2.sub_id,
                 listagg(t2.val || '.' ) within group(order by t2.val) option_info
            from test_190619_lc2_2 t2
           where t2.id = '1'
           group by t2.sub_id) t2
    where t1.sub_id = t2.sub_id(+)
     and t1.id = '1'
    ;

![在这里插入图片描述][20190619171854851.png]  
可以看到，因为是左外链接，所以，第一行中的option\_info列应该是为null的，但是却有了值。

通过查看执行计划可以发现一些问题  
![在这里插入图片描述][20190619172431718.png]  
有经验的人应该瞬间明白发生了什么吧。

我在网上搜索了一圈后并没有发现相关信息。个人推测是，oracle对于带有listagg函数的sql的执行计划的解析出了问题。  
这样会导致的一个问题是，同样的数据，换一个表，换一个顺序，稍微改一点数，或者重建一下表，都可能会导致不同的结果。事实也正是如此，我换着建了N张表，造了上千条数据，也还是没能找到必定能重现这个bug的数据。

至于解决方法，到是很简单，在外层用decode处理一下，或者在内部加一个无关的聚合函数来改变执行计划都是可行的。

select *
    from test_190619_lagg_bug_1 t1,
         (select t2.sub_id,
         avg(1),
                 listagg(t2.val || '.' ) within group(order by t2.val) option_info
            from test_190619_lc2_2 t2
           where t2.id = '1'
           group by t2.sub_id) t2
    where t1.sub_id = t2.sub_id(+)
     and t1.id = '1'
    ;

上述sql中加了一个avg(1)，这会诱导oracle选择正确的执行计划  
![在这里插入图片描述][2019061917325344.png]

测试数据（你使用这个测试数据不一定能重现这个bug，甚至连我自己都不能！）

create table test_190619_lagg_bug_1(id varchar2(50), sub_id varchar2(50));
    
    insert into test_190619_lagg_bug_1(id, sub_id) values('1', '1');
    insert into test_190619_lagg_bug_1(id, sub_id) values('1', '2');
    insert into test_190619_lagg_bug_1(id, sub_id) values('1', '3');
    insert into test_190619_lagg_bug_1(id, sub_id) values('1', '4');
    insert into test_190619_lagg_bug_1(id, sub_id) values('1', '5');
    insert into test_190619_lagg_bug_1(id, sub_id) values('1', '6');
    insert into test_190619_lagg_bug_1(id, sub_id) values('1', '7');
    insert into test_190619_lagg_bug_1(id, sub_id) values('1', '0');
    
    create table test_190619_lc2_2(id varchar2(50), sub_id varchar2(50), val varchar2(500));
    
    insert into test_190619_lc2_2(id, sub_id, val) values('1','1','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','1','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','1','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','1','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','11','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','11','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','11','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','11','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','12','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','12','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','12','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','12','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','12','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','13','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','13','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','13','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','13','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','14','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','14','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','14','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','14','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','14','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','2','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','2','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','2','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','2','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','2','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','3','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','3','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','3','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','4','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','4','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','4','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','4','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','4','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','5','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','5','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','5','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','5','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','6','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','6','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','6','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','6','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','6','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','63','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','63','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','63','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','63','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','63','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','7','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','7','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','7','C');
    
    insert into test_190619_lc2_2(id, sub_id, val) values('1','7','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','8','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','8','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','8','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','8','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','8','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','9','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','9','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','9','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('1','9','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','35','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','35','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','35','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','35','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','36','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','36','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','36','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','36','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','36','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','39','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','39','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','39','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','39','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','40','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','40','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','40','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','40','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','41','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','41','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','41','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','41','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','42','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','42','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','42','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','42','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','43','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','43','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','43','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','43','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','44','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','44','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','44','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('10','44','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','45','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','45','B');
    
    insert into test_190619_lc2_2(id, sub_id, val) values('11','45','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','45','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','46','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','46','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','46','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','46','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','47','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','47','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','47','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','47','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','48','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','48','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','48','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','48','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','49','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','49','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','49','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','49','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','50','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','50','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','50','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','50','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','60','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','60','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','60','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','60','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','60','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','63','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','63','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','63','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('11','63','B');
    
    insert into test_190619_lc2_2(id, sub_id, val) values('12','1','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','1','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','1','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','1','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','2','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','2','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','2','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','2','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','2','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','4','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','4','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','4','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','4','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','5','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','5','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','5','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','5','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','51','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','51','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','51','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','51','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','52','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','52','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','52','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','52','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','53','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','53','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','53','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','53','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','54','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','54','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','54','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('12','54','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','32','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','32','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','32','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','32','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','33','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','33','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','33','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','33','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','34','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','34','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','34','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','34','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','55','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','55','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','55','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','55','C');
    
    insert into test_190619_lc2_2(id, sub_id, val) values('13','56','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','56','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','56','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','56','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','56','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','57','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','57','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','57','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','57','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','58','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','58','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','58','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','58','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','59','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','59','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','59','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('13','59','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('14','22','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('14','22','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('14','22','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('14','22','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('14','23','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('14','23','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('14','23','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('14','23','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('14','23','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('14','25','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('14','25','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('14','25','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('14','25','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('15','26','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('15','26','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('15','26','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('15','26','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('15','26','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('15','51','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('15','51','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('15','51','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('15','51','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('15','65','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('15','65','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('15','65','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('15','65','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('16','40','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('16','40','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('16','40','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('16','40','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('16','41','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('16','41','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('16','41','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('16','41','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('16','41','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('16','44','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('16','44','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('16','44','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('16','44','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('17','56','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('17','56','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('17','56','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('17','56','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('17','57','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('17','57','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('17','57','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('17','57','B');
    
    insert into test_190619_lc2_2(id, sub_id, val) values('17','67','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('17','67','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('17','67','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('17','67','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('17','67','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('2','52','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('2','52','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('2','52','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('2','52','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('2','55','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('2','55','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('2','55','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('2','55','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('2','66','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('2','66','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('2','66','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('2','66','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('2','66','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','16','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','16','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','16','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','17','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','17','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','17','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','17','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','17','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','18','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','18','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','18','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','18','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','19','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','19','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','19','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','19','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','19','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','20','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','20','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','20','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','20','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','21','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','21','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','21','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','21','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','21','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','34','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','34','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','34','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','34','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','35','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','35','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','35','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','35','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','35','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','36','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','36','C');
    
    insert into test_190619_lc2_2(id, sub_id, val) values('3','36','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','36','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','37','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','37','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','37','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','37','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','37','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','38','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','38','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','38','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','38','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','39','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','39','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','39','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','39','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','39','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','40','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','40','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','40','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','40','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','41','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','41','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','41','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','41','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','41','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','59','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','59','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','59','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','59','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','68','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','68','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','68','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','68','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','69','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','69','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','69','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','69','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('3','69','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('4','60','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('4','60','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('4','60','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('4','60','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('4','61','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('4','61','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('4','61','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('4','61','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('4','61','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('4','62','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('4','62','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('4','62','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('4','62','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('5','45','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('5','45','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('5','45','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('5','45','A');
    
    insert into test_190619_lc2_2(id, sub_id, val) values('5','45','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('5','47','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('5','47','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('5','47','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('5','47','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('5','64','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('5','64','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('5','64','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('5','64','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('6','49','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('6','49','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('6','49','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('6','49','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('6','65','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('6','65','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('6','65','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('6','65','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('6','66','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('6','66','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('6','66','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('6','66','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('6','66','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','68','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','68','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','68','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','68','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','69','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','69','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','69','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','69','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','69','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','70','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','70','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','70','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','71','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','71','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','71','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','71','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','71','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','72','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','72','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','72','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','72','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','73','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','73','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','73','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','73','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','73','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','74','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','74','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','74','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','74','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','75','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','75','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','75','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','75','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','75','B');
    
    insert into test_190619_lc2_2(id, sub_id, val) values('7','76','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','76','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','76','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','76','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','77','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','77','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','77','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','77','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','77','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','78','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','78','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','78','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','78','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','79','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','79','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','79','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','79','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','79','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','80','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','80','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','80','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','80','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','81','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','81','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','81','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','81','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('7','81','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','10','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','10','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','10','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','10','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','11','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','11','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','11','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','11','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','12','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','12','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','12','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','12','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','13','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','13','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','13','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','13','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','14','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','14','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','14','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','14','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','15','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','15','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','15','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','15','C');
    
    insert into test_190619_lc2_2(id, sub_id, val) values('8','6','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','6','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','6','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','6','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','7','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','7','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','7','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','7','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('8','7','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','23','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','23','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','23','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','23','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','24','E');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','24','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','24','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','24','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','24','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','26','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','26','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','26','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','26','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','27','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','27','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','27','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','27','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','28','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','28','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','28','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','28','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','29','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','29','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','29','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','29','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','30','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','30','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','30','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','30','A');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','31','D');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','31','C');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','31','B');
    insert into test_190619_lc2_2(id, sub_id, val) values('9','31','A');

[20190619171854851.png]: /images/20210923/c3f58b3e98d543d79cae9f7f3f3835af.png
[20190619172431718.png]: /images/20210923/6c66aad01078425092971c2f2ddbfbef.png
[2019061917325344.png]: /images/20210923/b6236181913f4d82a1453c88dd480c19.png