同样的sql语句在MySQL中能查出数据，openGauss返回值为空[zyzx]

标题描述】:
创建枚举类型指定字符集时，无法创建

【操作系统和硬件信息】(查询命令: cat /etc/system-release, uname -a):
openEuler 20.03（LTS-SP3）

【测试环境】(单机/1主x备x级联备):
1主2备

【被测功能】:
查询

【数据库版本】(查询命令: gaussdb -V):
openGauss 5.0.1

【操作步骤】(请填写详细的操作步骤):
1、MySQL查出的结果值
![输入图片说明](https://foruda.gitee.com/images/1710931701313872435/8b6b6187_12182695.png "屏幕截图")

2、openGauss查出的结果值
![输入图片说明](https://foruda.gitee.com/images/1710931722912743335/34976dd3_12182695.png "屏幕截图")

3、以下为建表语句，插入数据，以及查询语句

CREATE TABLE `side_concenoper_statistics` (
  `id` bigint NOT NULL,
  `title` varchar(200) DEFAULT NULL,
  `running_id` varchar(50) DEFAULT NULL,
  `activity_id` varchar(255) DEFAULT NULL,
  `supporttype` varchar(20) DEFAULT NULL,
  `satisEvaluate` varchar(20) DEFAULT NULL,
  `status` int DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `finish_time` datetime DEFAULT NULL,
  `is_confirm_reject` tinyint DEFAULT NULL,
`delete_flag` tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `create_time` (`create_time`),
  KEY `finish_time` (`finish_time`),
  KEY `supporttype` (`supporttype`),
  KEY `satisEvaluate` (`satisEvaluate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO side_concenoper_statistics (id, title, running_id, activity_id, supporttype, satisEvaluate, status, create_time, update_time, finish_time, delete_flag) VALUES (417, 'dsfgergsg', 'COP2003000417', 'FinishActivity', NULL, '满意', 2, '2020-03-02 16:54:44', '2020-03-02 17:43:20', '2020-03-02 17:43:20', 0);

CREATE TABLE `side_concenoper_statistics_other` (
  `id` bigint NOT NULL,
  `title` varchar(200) DEFAULT NULL,
  `running_id` varchar(50) DEFAULT NULL,
  `activity_id` varchar(255) DEFAULT NULL,
  `supporttype` varchar(20) DEFAULT NULL,
  `satisEvaluate` varchar(20) DEFAULT NULL,
  `status` int DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `finish_time` datetime DEFAULT NULL,
  `delete_flag` tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `create_time` (`create_time`),
  KEY `finish_time` (`finish_time`),
  KEY `supporttype` (`supporttype`),
  KEY `satisEvaluate` (`satisEvaluate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO side_concenoper_statistics_other (id, title, running_id, activity_id, supporttype, satisEvaluate, status, create_time, update_time, finish_time, delete_flag) VALUES (1322, 'dsfwefs', 'NIPDI2005001322', 'Distribution IPActivity', NULL, NULL, 1, '2020-05-13 14:35:25', '2020-05-13 14:45:15', NULL, 0);

select u1.createTime,u1.num,u1.supporttype from (SELECT t1.createTime, COALESCE(t2.num, 0) AS num,t2.supporttype FROM( SELECT @cdate:= DATE_ADD(@cdate, INTERVAL - 1 DAY) AS createTime FROM (SELECT @cdate:= DATE_ADD('2023-01-01', INTERVAL + 1 DAY) FROM side_concenoper_statistics) tmp1 WHERE @cdate >'2022-01-01' ) t1 LEFT JOIN( SELECT DATE_FORMAT(m.create_time,'%Y-%m-%d') AS createTime,supporttype,COUNT(*) AS num FROM side_concenoper_statistics AS m WHERE m.create_time >= '2022-01-01 00:00:00' AND m.create_time < '2024-03-18 00:00:00' AND m.delete_flag = 0 GROUP BY DATE_FORMAT(m.create_time,'%Y-%m-%d'),supporttype ) t2 ON t1.createTime = t2.createTime ORDER BY t1.createTime,t2.supporttype) u1 UNION SELECT u2.createTime,u2.num,u2.supporttype from ( SELECT t1.createTime as createTime, COALESCE(t2.num, 0) AS num,t2.supporttype as supporttype FROM( SELECT @cdate2:= DATE_ADD(@cdate2, INTERVAL - 1 DAY) AS createTime FROM (SELECT @cdate2:= DATE_ADD('2023-01-01', INTERVAL + 1 DAY) FROM side_concenoper_statistics_other) tmp1 WHERE @cdate2 > '2022-01-01' ) t1 LEFT JOIN( SELECT DATE_FORMAT(m.create_time,'%Y-%m-%d') AS createTime,supporttype,COUNT(*) AS num FROM side_concenoper_statistics_other AS m WHERE m.create_time >= '2020-04-01' and m.create_time < '2024-03-18 00:00:00' AND m.delete_flag = 0 GROUP BY DATE_FORMAT(m.create_time,'%Y-%m-%d'),supporttype ) t2 ON t1.createTime = t2.createTime ORDER BY t1.createTime,t2.supporttype) u2;

GVP openGauss/openGauss-server

内容风险标识

评论 (7)

GVPopenGauss/openGauss-server

内容风险标识