ITPub博客

首页 > 数据库 > Oracle > 通过错误的sql来测试推理sql的解析过程(二)

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

原创 Oracle 作者:jeanron100 时间:2016-02-12 23:14:04 0 删除 编辑
 之前总结过一篇  通过错误的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
就会发现有意思的问题还是发生了。
后面还有一些测试的细节,后面继续解读。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23718752/viewspace-1988095/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 新年庙会归来
请登录后发表评论 登录
全部评论
技术文章每天更新,阵地已转移到微信公众号端。 公众号:jianrong-notes

注册时间:2012-05-14

  • 博文量
    1498
  • 访问量
    14641549