通過錯誤的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
就會發現有意思的問題還是發生了。
後面還有一些測試的細節，後面繼續解讀。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1988095/，如需轉載，請註明出處，否則將追究法律責任。