dual的作用

客户现场：
有个客户现场遇到一个报错：
select open_acct_org_id,acct_instit_id from (
select  a.open_acct_org_id,a.acct_instit_id,suma,
if(@open_acct_org_id =a.open_acct_org_id,@rownum :=@rownum+1,@rownum :=1) as rn,
@open_acct_org_id :=a.open_acct_org_id from 
(select open_acct_org_id,acct_instit_id,count(1) as suma  from cmm_dep_acct_info 
where open_acct_org_id in (select org_id from org_core_org where org_status_cd in ('1003','1004'))
and dep_acct_status_cd = '0' group by open_acct_org_id,acct_instit_id order by open_acct_org_id,suma desc)a,(select @rownum :=0,@open_acct_org_id :=null)b
)a;
整理成执行sql形式：
gbase> select open_acct_org_id,acct_instit_id 
    -> from (
    -> select  
    -> a.open_acct_org_id,a.acct_instit_id,suma,
    -> if(@open_acct_org_id =a.open_acct_org_id,@rownum :=@rownum+1,@rownum :=1) as rn,
    -> @open_acct_org_id :=a.open_acct_org_id 
    -> from (select open_acct_org_id,acct_instit_id,count(1) as suma  from cmm_dep_acct_info 
    -> where open_acct_org_id in (select org_id from org_core_org where org_status_cd in ('1003','1004'))
    -> and dep_acct_status_cd = '0' 
    -> group by open_acct_org_id,acct_instit_id 
    -> order by open_acct_org_id,suma desc) a,
    -> (select @rownum :=0,@open_acct_org_id :=null) b
    -> )a;

SQL 错误 [1149] [42000]: (GBA-02SC-1001) The query includes syntax that is not supported by the gcluster

排查过程：
原先误以为报错的点是8a不支持变量、子查询（select org_id from org_core_org where org_status_cd in ('1003','1004')）是一个独立的不带from子句的子查询。。。。;
但实际上错误的点是：GBase 8a 不允许 FROM 子句中出现“不基于任何物理表”的子查询，包括纯常量查询、纯变量赋值查询。

验证如下：
在mysql中，简单执行SELECT * FROM (SELECT @x := 100) AS init;可以得出结果：
mysql> SELECT * FROM (SELECT @x := 100) AS init;
+--------------+
|  @x := 100   |
+--------------+
|        100   |
+--------------+

但是该语句在gbase 8a数据库中却会报错：
gbase> SELECT * FROM (SELECT @x := 100) AS init;
ERRPR 1149 (42000): (GBA-02SC-1001) The query includes syntax that is not supported by the gcluster.
很明显，跟前面客户跑任务遇到的问题的一样的。
因此修改语句的内容：使其能正常在gbase和Mysql执行：
gbase> set @x = 100;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> select @x;
+-------+
|  @x   |
+-------+
|   100 |
+-------+
1 row in set (Elapsed: 00:00:00.00)
在mysql执行：
mysql> set @x = 100;
Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
+-------+
|  @x   |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

就上面的结论，结合我模拟的两张表，以及客户的sql语句，内容如下：
正常加上select @rownum :=0,@open_acct_org_id :=null)b，执行后会报错：
gbase> select a.age,a.user_name
    -> from(
    -> select
    -> a.age,a.user_name,a.address,suma,
    -> if(@age = a.age,@rownum := @rownum + 1, @rownum := 1) AS rn,
    -> @age := a.age
    -> from (select user_name,age,address,count(1) as suma from test
    -> where ID in (select ID from test_2 where ID in ('5','8'))
    -> and age > 15
    -> group by age,user_name,address
    -> order by age,suma desc ) a,
    -> (select @rownum :=0,@age :=null) b
    -> )a;
ERRPR 1149 (42000): (GBA-02SC-1001) The query includes syntax that is not supported by the gcluster.

不加select @rownum :=0,@open_acct_org_id :=null)b，正常可以查询到结果，且符合sql的内容
gbase> select a.age,a.user_name
    -> from(
    -> select
    -> a.age,a.user_name,a.address,suma,
    -> if(@age = a.age,@rownum := @rownum + 1, @rownum := 1) AS rn,
    -> @age := a.age
    -> from (select user_name,age,address,count(1) as suma from test
    -> where ID in (select ID from test_2 where ID in ('5','8'))
    -> and age > 15
    -> group by age,user_name,address
    -> order by age,suma desc ) a
    -> )a;
+-----------------------+
| age  | username       |
+-----------------------+
|   17 | Maria Garcia   |
+-----------------------+
|    81 | Jason Williams |
+-----------------------+
|    25 | Lisa Smith     |
+-----------------------+
|    82 | Betty Lewis    |
+-----------------------+

最后为了完全符合客户的想法，还是把定义变量默认值记上去，得到的结果也是一样的：
gbase> SET @rownum = 0;SET @age = NULL;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> select a.age,a.user_name
    -> from(
    -> select
    -> a.age,a.user_name,a.address,suma,
    -> if(@age = a.age,@rownum := @rownum + 1, @rownum := 1) AS rn,
    -> @age := a.age
    -> from (select user_name,age,address,count(1) as suma from test
    -> where ID in (select ID from test_2 where ID in ('5','8'))
    -> and age > 15
    -> group by age,user_name,address
    -> order by age,suma desc ) a
    -> )a;
+-----------------------+
| age  | username       |
+-----------------------+
|   17 | Maria Garcia   |
+-----------------------+
|    81 | Jason Williams |
+-----------------------+
|    25 | Lisa Smith     |
+-----------------------+
|    82 | Betty Lewis    |
+-----------------------+

因为最后将客户发过来的sql整改为：
gbase> SET @rownum = 0;SET @open_acct_org_id = NULL;
gbase> select open_acct_org_id,acct_instit_id 
    -> from (
    -> select  
    -> a.open_acct_org_id,a.acct_instit_id,suma,
    -> if(@open_acct_org_id =a.open_acct_org_id,@rownum :=@rownum+1,@rownum :=1) as rn,
    -> @open_acct_org_id :=a.open_acct_org_id 
    -> from (select open_acct_org_id,acct_instit_id,count(1) as suma  from cmm_dep_acct_info 
    -> where open_acct_org_id in (select org_id from org_core_org where org_status_cd in ('1003','1004'))
    -> and dep_acct_status_cd = '0' 
    -> group by open_acct_org_id,acct_instit_id 
    -> order by open_acct_org_id,suma desc) a,
    -> )a;

gbase> SET @rownum = 0;SET @open_acct_org_id = NULL;
gbase> select open_acct_org_id,acct_instit_id from (
select  a.open_acct_org_id,a.acct_instit_id,suma,
if(@open_acct_org_id =a.open_acct_org_id,@rownum :=@rownum+1,@rownum :=1) as rn,
@open_acct_org_id :=a.open_acct_org_id from 
(select open_acct_org_id,acct_instit_id,count(1) as suma  from cmm_dep_acct_info 
where open_acct_org_id in (select org_id from org_core_org where org_status_cd in ('1003','1004'))
and dep_acct_status_cd = '0' group by open_acct_org_id,acct_instit_id order by open_acct_org_id,suma desc)a )a;

即可正常得出结果。

还有另个更简单的解决办法，就是在select @rownum :=0,@open_acct_org_id :=null 后面加上 from dual，因为gbase跟oracle一样，可以通过dual来实现不基于物理表的查询，同时也规避了很多类似的问题：
gbase> select a.age,a.user_name
    -> from(
    -> select
    -> a.age,a.user_name,a.address,suma,
    -> if(@age = a.age,@rownum := @rownum + 1, @rownum := 1) AS rn,
    -> @age := a.age
    -> from (select user_name,age,address,count(1) as suma from jinghai
    -> where ID in (select ID from hq where parentID in ('5','8'))
    -> and age > 15
    -> group by age,user_name,address
    -> order by age,suma desc ) a,
    -> (select @rownum :=0,@age :=null from dual) b
    -> )a;
+-----------------------+
| age  | username       |
+-----------------------+
|   17 | Maria Garcia   |
+-----------------------+
|    81 | Jason Williams |
+-----------------------+
|    25 | Lisa Smith     |
+-----------------------+
|    82 | Betty Lewis    |
+-----------------------+

客户的sql整改：
select open_acct_org_id,acct_instit_id from (
select  a.open_acct_org_id,a.acct_instit_id,suma,
if(@open_acct_org_id =a.open_acct_org_id,@rownum :=@rownum+1,@rownum :=1) as rn,
@open_acct_org_id :=a.open_acct_org_id from 
(select open_acct_org_id,acct_instit_id,count(1) as suma  from cmm_dep_acct_info 
where open_acct_org_id in (select org_id from org_core_org where org_status_cd in ('1003','1004'))
and dep_acct_status_cd = '0' group by open_acct_org_id,acct_instit_id order by open_acct_org_id,suma desc)a,(select @rownum :=0,@open_acct_org_id :=null from dual)b
)a;