通过错误的sql来测试推理sql的解析过程(二)

 之前总结过一篇  通过错误的sql来测试推理sql的解析过程 http://blog.itpub.net/23718752/viewspace-1848816/
也算是以毒攻毒，当然也分析出来一些有意思的内容来，让原本看起来枯燥的内容有了更多的实践意义。
在后来小组内部做了一个分享总结，本来以为已经总结差不多了，但是发现真是集思广益，大家临时想出不少好的点子来，这也就是 brainstroming 的好处吧.
比如下面的错误sql，在解析的时候，会首先报错在group by的部分。在10g和11g略微有一些差别。目前以11g的为基线。
目前存在一个表test，字段情况为(id number,name varchar2(30))，里面存在1条数据。
使用如下的语句来测试一下，会发现这样的基本规律
select id1 from test1 where id1='aaa' group by id1 having1 count(*)>0 order by5 id1
                                                   *
ERROR at line 1:
ORA-00933: SQL command not properly ended

select id1 from test1 where id1='aaa' group by id1 having count(*)>0 order by5 id1
                                                                           *
ERROR at line 1:
ORA-00924: missing BY keyword

SQL> select id1 from test1 where where1 id1='aaa' group by id1 having count(*)>0 order by5 id1;
select id1 from test1 where where1 id1='aaa' group by id1 having count(*)>0 order by5 id1
                                   *
ERROR at line 1:
ORA-00920: invalid relational operator

SQL> select id1 from test1 t where1 id1='aaa' group by id1 having count(*)>0 order by5 id1;
select id1 from test1 t where1 id1='aaa' group by id1 having count(*)>0 order by5 id1
                        *
ERROR at line 1:
ORA-00933: SQL command not properly ended
可见对于这些保留字，在解析的是按照从右向左的顺序依次来解析。
如果存在数据类型的兼容性，在隐私转换的时候如果失败，会在解析的时候一并抛出，其实这个时候已经到了执行阶段了，对于数据的细节信息无从考证，使用explain plan还是能够生成执行计划来。
SQL> select id from test t where id='aaa' group by id  order by id;
select id from test t where id='aaa' group by id  order by id
                               *
ERROR at line 1:
ORA-01722: invalid number

我们清空数据，继续测试
SQL> delete from test;
1 row deleted.

SQL> commit;
Commit complete.
这个时候再次测试，发现同样的语句在这个时候就没法直接分析出来了。这种情况看起来也是一个灰色地带。
SQL> select id from test t where id='aaa' group by id  order by id;
no rows selected
那么统计信息对于sql解析有没有影响呢？
我们收集一下统计信息，让优化器能够认为存在一条数据。
SQL> exec DBMS_STATS.SET_TABLE_STATS (ownname=>'TEST',tabname=>'TEST',numrows=>1);
PL/SQL procedure successfully completed.
再次执行同样的sql语句，发现还是没有做出更进一步的校验。
SQL> select id from test t where id='aaa' group by id  order by id;
no rows selected
如果尝试让优化器识别出数据块的情况来，是不是有改善呢？
SQL> exec DBMS_STATS.SET_TABLE_STATS (ownname=>'TEST',tabname=>'TEST',numrows=>1,numblks=>1);
PL/SQL procedure successfully completed.
情况还是类似。
SQL> select id from test t where id='aaa' group by id  order by id;
no rows selected
通过上面的结果，可以简单推论是不是和数据情况有关系呢，但是看起来关系还是不大，怎么进一步验证呢。

我们继续测试隐式转换的问题。
如果插入一条记录，但是id列为null.
SQL> insert into test values(null,'aaaaa');
1 row created.
那么同样的语句会抛出错误吗？
SQL> select id from test t where id='aaa' group by id  order by id;
no rows selected
但是继续测试，插入id为2，这个时候再次运行同样的语句就会抛错，这个也是预期这种理想的情况。
SQL> insert into test values(2,'aadbdsaf');
1 row created.

SQL> select id from test t where id='aaa' group by id  order by id;
select id from test t where id='aaa' group by id  order by id
                               *
ERROR at line 1:
ORA-01722: invalid number

继续测试索引的影响。
先清空数据。
SQL> truncate table test;   
Table truncated.
然后添加主键。
SQL> alter table test modify(id primary key);
Table altered.
这个时候再次测试就会发现同样的语句就开始抛错了，看来主键的情况还是好使，能够做一些看起来的硬验证。
SQL> select id from test t where id='aaa' group by id  order by id;
select id from test t where id='aaa' group by id  order by id
                               *
ERROR at line 1:
ORA-01722: invalid number
那么我们换个角度在索引列和非索引列上测试隐式转换的情况。
SQL> select id from test t where name=111 and id='aaa' group by id  order by id;
select id from test t where name=111 and id='aaa' group by id  order by id
                                            *
ERROR at line 1:
ORA-01722: invalid number

然后删除主键
SQL> alter table test drop primary key;
Table altered.
继续测试同样的sql语句。这个时候就校验不出来数据的细节情况了。
SQL> select id from test t where name=111 and id='aaa' group by id  order by id;
no rows selected
如果我们插入一条记录。
SQL> insert into test values(1,22222);
1 row created.
然后再次验证，会发现这条语句可以从两种可能性来理解，一种是确实没有数据，没有name列相关的数据，还没有验证到id='aaa'的情况。
SQL> select id from test t where name=111 and id='aaa' group by id  order by id;
no rows selected
那么我们使用过滤条件，指向新增加的那条记录。
SQL>  select id from test t where name=22222 and id='aaa'  group by id  order by id;
 select id from test t where name=22222 and id='aaa'  group by id  order by id
                                               *
ERROR at line 1:
ORA-01722: invalid number
就会发现有意思的问题还是发生了。
后面还有一些测试的细节，后面继续解读。