使用dbisql和isql执行相同查询但返回不同结果问题的解决

      最近遇到一个问题，用户使用dbisql和isql工具分别执行相同一个查询时返回的结果不同。在这里跟大家分享这个问题的解决过程，希望大家在开发、运行程序时能够避免类似问题的产生。

1. 问题再现
    (1) 创建测试表
    create table test1(id int, name varchar(8), primary key(id))
    (2) 插入测试数据
    insert into test1 values (1,'aaaa'), (2,null), (3,'cccc'), (4,''), (5,null) 
    commit
    (3) 测试查询
    select * from test1 where name not in ('',null)
 
     (4) 使用dbisql和isql工具连接IQ，分别执行这个查询，返回结果不同：
    -- dbisql执行结果
(DBA)> select * from test1 where name not in ('',null)
          id name    
--------------------

(0 rows)

Execution time: 0.14 seconds

    
    --isql执行结果
:~> isql -UDBA -Psql -Siqdemo
1> select * from test1 where name not in ('',null)
2> go
 id          name                    
 ----------- ------------------------
           1 aaa                     
           3 ccc

     我们看到，两个工具执行的结果是不同的：即isql的执行结果是正确的，而dbisql执行是错误的。
  
2. 问题分析
     这个语句中where条件中出现了空值 (null)，会不会与此相关呢？如果把上面sql语句中not in中的null去掉的话，那么dbisql和isql执行结果就完全相同了。
     为什么加上null结果就不一样了呢？根据以往的经验，很可能与不同工具连接时使用的协议不同，从而导致IQ为不同协议下建立的连接设置了不同的数据库选项。执行如下语句可以看到所建连采用的协议：
      select connection_property('ClientLibrary',@@spid)
      dbisql返回的是"CmdSeq"，而isql返回的是"CT-Library"

      上面的查询可以证明这个假设了。那么，到底是哪个数据库选项导致的呢？既然跟null相关，那么我们就分别在dbisql和isql中执行如下语句：
       select * from sp_iqcheckoptions() where option_name like '%null%'

       看看他们的输出有什么不同，从而寻找线索。通过执行这个语法，发现dbisql没有找到记录，而isql输出如下记录：
User_name    Option_name    Current_value    Default_value        Option_type 
DBA           ansinull          Off                On               Temporary 
      看来很可能是与数据库选项"ansinull"相关了，通过查询IQ参考手册，确定应该是这个选项导致的。下面是手册中对这个选项的解释：
       *  With ANSINULL ON, results of comparisons with NULL using '=' or '!=' are

       *  Setting ANSINULL to OFF allows comparisons with NULL to yield results that
are not unknown

3. 问题解决
    在dbisql执行问题查询之前，先把ANSINULL数据库选项设置为OFF，再执行那个查询，结果就与isql相同了。具体如下：
(DBA)> set temporary option ansinull='off'
Execution time: 0.007 seconds

(DBA)> select * from test1 where name not in ('',null);
         id name    
--------------------
          1 aaa     
          3 ccc     

(2 rows)

Execution time: 0.074 seconds

说明：
    * ANSINULL用于控制字段与null比较时的行为。ANSI标准要求判断字段是否为空值时使用 column is null或 column is not null 这样的语法；Sybase ASE可以使用=nulll或 !=null这样的写法实现判断。
     * isql连接到IQ时，为了保持与ASE的兼容性，IQ会把ANSINULL设置为'OFF'；而dbisql连接到IQ时采用标准的ANSI行为。对于not in (null)这种写法，应该等同与!=null，所以会导致这样的结果不一致问题。