南大通用GBase 8a里面兼容oracle的dual表

Oracle里，dual是一个只有一行数据的虚表，目的只是为了SQL语句结构的完整性，本文介绍在GBase 8a数据库集群里实现的dual。

用法

默认是可以不用写from dual的。如下是对照的例子。

gbase> select 1+1;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (Elapsed: 00:00:00.00)

gbase> select 1+1 from dual;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (Elapsed: 00:00:00.03)

gbase> select now() from dual;
+---------------------+
| now()               |
+---------------------+
| 2021-02-24 10:03:52 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-02-24 10:03:58 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

元数据

在GBase 8a里，内部是通过在gclusterdb.dual表来实现的。

如下例子，如果单独写dual会兼容oracle的虚表，为了当成表名字，可以加上库名或者加上界定符，比如`，或者双引号 "。其实质就是一个有一行数据的复制表。

警告：请不要人工修改系统元数据表，否则会造成执行失败或结果集错误。

gbase> use gclusterdb;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select * from dual;
ERROR 1096 (HY000): No tables used
gbase>
gbase> select * from `dual`;
+-------+
| dummy |
+-------+
| X     |
+-------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select * from gclusterdb.dual;
+-------+
| dummy |
+-------+
| X     |
+-------+
1 row in set (Elapsed: 00:00:00.00)

gbase> show create table gclusterdb.dual;
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| dual  | CREATE TABLE "dual" (
  "dummy" varchar(1) DEFAULT NULL
) ENGINE=EXPRESS REPLICATED  DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

注意事项

如果是新建用户，需要授权gclusterdb.dual表的select权限，否则查询dual会报访问拒绝的错误。ERROR 1142 (42000): SELECT command denied to user for table 'dual'

gbase> select 1+1 from dual;
ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'dual'
gbase> ^CAborted
[gbase@rh6-1 ~]$ gccli gclusterdb

GBase client 8.6.2.43-R28 .125499. Copyright (c) 2004-2021, GBase.  All Rights Reserved.

gbase> grant select on gclusterdb.dual to test;
Query OK, 0 rows affected (Elapsed: 00:00:00.05)

gbase> ^CAborted
[gbase@rh6-1 ~]$ gccli -utest -ptest

GBase client 8.6.2.43-R28 .125499. Copyright (c) 2004-2021, GBase.  All Rights Reserved.

gbase> select 1+1 from dual;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (Elapsed: 00:00:00.01)