虚谷数据库用 as 后的别名做where筛选，与 mysql,oracle 用列名做筛选不一致

1.背景介绍

建表语句如下：workflowactionset

create table XUGU.WORKFLOWACTIONSET(
ID integer not null,
ACTIONNAME varchar(2000),
WORKFLOWID integer,
NODEID integer,
NODELINKID integer,
ISPREOPERATOR integer,
ACTIONORDER integer,
INTERFACETYPE integer,
TYPENAME varchar(1000),
INTERFACEID varchar(2000),
ISUSED integer,
DRAWBACKFLAG integer,
UUID varchar(50) default "SYS_GUID"()
);

-- Alter Table Add PrimaryKey Constraint --
alter table XUGU.WORKFLOWACTIONSET add constraint "PK_S349916945984117437" primary key(ID);

插入数据

INSERT INTO XUGU.WORKFLOWACTIONSET 
(ID,ACTIONNAME,WORKFLOWID,NODEID,NODELINKID,
ISPREOPERATOR,ACTIONORDER,INTERFACETYPE,
 TYPENAME,INTERFACEID,ISUSED,DRAWBACKFLAG,UUID) 
 VALUES(5,'WorkflowFnaEffectNew',24,35,0,0,88888,
        3,NULL,'WorkflowFnaEffectNew',1,0,
        '1ca762e7-61a4-45b1-898c-c1881672fdfb');

2. 对比步骤

-- 1. 原始数据:  oracle  , mysql,  Xugu  正确且一致 
SELECT interfaceid,id 
from
  workflowactionset
where
  id = 5;

--   interfaceid='WorkflowFnaEffectNew', id='5'


-- 2. 别名和列名重复，被重复的列名 改为其他名称  ： oracle  , mysql 正确一致 ,  Xugu  执行报数据格式错误
select interfaceid as ID, ID as actionid
from
  workflowactionset
where
  id = 5;
-- MYSQL: 正确'WorkflowFnaEffectNew', '5'
-- Oracle: 正确 1 WorkflowFnaEffectNew    5
-- Xugu:  执行报数据格式错误

-- 3. 别名和列名重复，被重复的列名 改为其他名称 ：oracle  , mysql 找不到标识符报错 , xugu 执行正确
select interfaceid as ID, ID as actionid
from
  workflowactionset
where
   actionid = 5 ;
  -- MYSQL : 15:59:48   select interfaceid as ID, ID as actionid from   workflowactionset where   actionid = 5 LIMIT 0, 1000    Error Code: 1054. Unknown column 'actionid' in 'where clause'   0.000 sec
  -- Oracle:SQL 错误 [904] [42000]: ORA-00904: "ACTIONID": 标识符无效
   -- xugu: 执行正确 

-- 4. 别名和列名重复，被重复的列名 改为其他名称 

select interfaceid as ID, ID as actionid
from
  workflowactionset
where
  id = 'WorkflowFnaEffectNew'
  -- MYSQL： 查不来数据，
  -- Oracle:  SQL 错误 [1722] [42000]: ORA-01722: 无效数字
  -- Xugu: 执行正确 

3. 对比结论

 虚谷数据库用 as 后的别名做where筛选，与 mysql,oracle 用列名做筛选不一致。