选中的sql其实是多余的,但是却直接影响了数据结果,实际应该是3行数据,但是这段sql一会儿3行,一会儿27行
把left join 改成 inner join结果就是对的
麻烦提供下版本信息,测试sql文本,内部测验下。
原始SQL如下,截图是为了方便查看、诊断,把中间的一个子查询创建了视图
SELECT t0.PJXXUUID AS Z,
t0.BXUUID AS BXUUID,
t0.BXSXUUID AS BXSXUUID,
t1.项目名称 AS XMMC,
t1.开票时间 AS KPRQ,
t1.金额 AS JEXX,
t1.税额 AS SE,
t0.BXLX AS BXLX1,
t1.上传时间 AS CREATE_TIME1,
t2.FJID AS FJID,
t2.FJ_DX AS FJ_DX,
t2.FJ_MC AS FJ_MC,
t2.FJ_LX AS FJ_LX,
t2.FJ AS FJ,
t0.HJJE AS HJJE,
t0.BXJE AS BXJE
FROM szhfpgl.fact_bx_bxsxmxb_cf t0
LEFT JOIN (SELECT t0.PJXXUUID AS 票据信息UUID,
t0.CREATE_TIME AS 上传时间,
CASE
WHEN t0.SJLY = 'x-manual input' AND t0.XMMC IS NULL THEN
'未填写'
WHEN t0.SJLY IN ('taxi_receipt',
'train_ticket',
'vat_invoice',
'roll_normal_invoice',
'quoto_invoice',
'air_ticket') AND t0.XMMC IS NULL THEN
'未识别到有关信息'
ELSE
t0.XMMC
END AS 项目名称,
str_to_date(t0.KPRQ, '%Y-%m-%d') AS 开票时间,
t0.SE AS 税额,
t0.JE AS 金额
FROM (SELECT t0.PJXXUUID AS PJXXUUID,
t0.FPDM AS FPDM,
t0.FPHM AS FPHM,
t0.PJLX AS PJLX,
t0.CREATE_TIME AS CREATE_TIME,
t0.LRR AS LRR,
t0.LRRQ AS LRRQ,
t0.BXLX AS BXLX,
t0.XGR AS XGR,
t0.XMMC AS XMMC,
t0.KPRQ AS KPRQ,
t0.SE AS SE,
t0.GFMC AS GFMC,
t0.GFSBH AS GFSBH,
t0.XFSBH AS XFSBH,
t0.XFMC AS XFMC,
t0.SJLY AS SJLY,
t0.JE AS JE,
t0.CRSJ AS CRSJ,
t0.RWSCUUID AS RWSCUUID
FROM (SELECT t0.pjxxuuid AS PJXXUUID,
t0.fpdm AS FPDM,
t0.fphm AS FPHM,
t0.pjlx AS PJLX,
t0.create_time AS CREATE_TIME,
t0.lrr AS LRR,
t0.lrrq AS LRRQ,
t0.bxlx AS BXLX,
t0.xgr AS XGR,
t0.xmmc AS XMMC,
t0.kprq AS KPRQ,
t0.se AS SE,
t0.gfmc AS GFMC,
t0.gfsbh AS GFSBH,
t0.xfsbh AS XFSBH,
t0.xfmc AS XFMC,
t0.sjly AS SJLY,
t0.je AS JE,
t0.crsj AS CRSJ,
t0.rwscuuid AS RWSCUUID
FROM szhfpgl.fact_pj_ocrsbjbxxhzb t0
WHERE (t0.lrr = 'lijj')
UNION ALL
SELECT t0.PJXXUUID AS PJXXUUID,
t0.FPDM AS FPDM,
t0.FPHM AS FPHM,
t0.PJMX AS PJMX,
t0.create_time AS CREATE_TIME,
t0.LRR AS LRR,
t0.LRRQ AS LRRQ,
t0.BXLX AS BXLX,
t0.XGR AS XGR,
CASE
WHEN t0.BZ IS NULL THEN
'该票据未填写备注'
ELSE
t0.BZ
END AS BZ,
t0.KPRQ AS KPRQ,
t0.HJSE AS HJSE,
null AS GFMC,
null AS GFSBH,
null AS XFSBH,
null AS XFMC,
'x-manual input' AS SJLY,
cast(t0.JSHJ AS decimal(30, 8)) AS JSHJ,
NULL AS CRSJ,
null AS RWSCUUID
FROM szhfpgl.fact_pj_typjxxb t0
WHERE (t0.LRR = 'lijj')) t0) t0
LEFT JOIN szhfpgl.fact_pj_fj t1
ON (t0.PJXXUUID = t1.PJXXUUID)
LEFT JOIN (SELECT t0.BXUUID AS BXUUID, t0.PJXXUUID AS PJXXUUID
FROM (SELECT t0.BXUUID AS BXUUID,
t0.PJXXUUID AS PJXXUUID
FROM szhfpgl.fact_bx_bxsxmxb_cf t0
WHERE (t0.LRR = 'lijj')
UNION ALL
SELECT t0.BXUUID AS BXUUID,
t0.PJXXUUID AS PJXXUUID
FROM szhfpgl.fact_bx_clxcmxb_cf t0
WHERE (t0.LRR = 'lijj')) t0) t2
ON (t0.PJXXUUID = t2.PJXXUUID)) t1
ON (t0.PJXXUUID = t1.票据信息UUID)
LEFT JOIN szhfpgl.fact_pj_fj t2
ON (t0.PJXXUUID = t2.PJXXUUID)
WHERE (t0.BXUUID = '5242ed77c1094158ae68af3845ba2942' AND
t0.BXSXUUID IN
('1gtetknftt0pq1xv59b570hged', '9d316ciyw2k14z0umf29t37x'))
AND (t0.LRR = 'lijj')
ORDER BY t0.PJXXUUID
这里第一个LEFT JOIN改成INNER JOIN是没问题的,因为WHERE条件并没有使用左表,改成INNER JOIN也是符合业务需求的,其结果集就是准确的3条,不会变。所以怀疑是LEFT JOIN的底层原理是不是存在什么不对的地方
问下这是哪个版本呢?
OceanBase4.0单机版Mysql租户
CREATE TABLE `t_fact_bx_bxsxmxb_cf` (
`PJXXUUID` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`BXUUID` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`BXSXUUID` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0;
CREATE TABLE `t_v_tmp_zhyz` (
`PJXXUUID` varchar(255) NOT NULL,
`SJLY` varchar(255) DEFAULT NULL,
`fpdm` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`项目名称` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0;
CREATE TABLE `t_fact_pj_fj` (
`PJXXUUID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '票据信息uuid',
`FJID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '附件id',
PRIMARY KEY (`FJID`),
KEY `IDXZIPPD3016155212` (`PJXXUUID`) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0;
insert into t_fact_bx_bxsxmxb_cf values('489bf40db7e84245a0dff8d831857705','aac8a5d2671e4087aaa9c580c81bb2e3','1mbpt3v4mqfhbrg7h6zmfrijb');
insert into t_fact_bx_bxsxmxb_cf values('1c9c707d97d948e08796e3fea948454a','aac8a5d2671e4087aaa9c580c81bb2e3','219g2ofugbmfyruyzq82vpon');
insert into t_fact_bx_bxsxmxb_cf values('111d5413f5634037906f548c5d128295','aac8a5d2671e4087aaa9c580c81bb2e3','219g2ofugbmfyruyzq82vpon');
insert into t_v_tmp_zhyz values('111d5413f5634037906f548c5d128295','vat_invoice','011002200711','*运输服务*客运服务费');
insert into t_v_tmp_zhyz values('19614c36effb411dbddd46b5a6d6aafb','vat_invoice','042002200211','(详见销货清单)');
insert into t_v_tmp_zhyz values('1c9c707d97d948e08796e3fea948454a','vat_invoice','042002200411','*运输服务*客运服务费');
insert into t_v_tmp_zhyz values('207402b4e3744dd4a902f334a432edb2','vat_invoice','034001700111','*运输服务*客运服务费');
insert into t_v_tmp_zhyz values('2d3161f7ee204dfea18a16595fad0208','vat_invoice','042002200111','*餐饮服务*餐饮费');
insert into t_v_tmp_zhyz values('3b1dcb10973248e8ae7bb50f8862a3a3','vat_invoice','042002200311','*体育用品*川崎(KAWASAKI)羽毛球拍双拍超轻碳素耐打对拍KD-1蓝红(已穿线+6羽毛球+2手胶+拍包)');
insert into t_v_tmp_zhyz values('3b38b896a306467f96e58502b2e0cd24','vat_invoice','042002200511','*餐饮服务*餐饮服务、餐饮费');
insert into t_v_tmp_zhyz values('3fc3adb139474ef4aaa151d0813e7a64','vat_invoice','033002200511','*体育用品*健身器材');
insert into t_v_tmp_zhyz values('4394d49d06b74838b59a41bfcd94f5e1','vat_invoice','012002100611','*运输服务*客运服务费');
insert into t_v_tmp_zhyz values('4768827068ab4e989287f561eb6b0c0c','vat_invoice','042002200411','*焙烤食品*软点、*焙烤食品*软点');
insert into t_v_tmp_zhyz values('489bf40db7e84245a0dff8d831857705','vat_invoice','031002000611','*美容护肤品*【立即付定金】娇兰蜜润修护精粹水滋润150ml*2、*美容护肤品*【立即付定金】娇兰蜜润修护精粹水滋润150ml*2');
insert into t_v_tmp_zhyz values('5a5ee065940c4556a362324261a7f1a4','medical_receipt',null ,'224.00-3684001530792022120医疗费收据');
insert into t_v_tmp_zhyz values('5bcf0cfd97d743fcadd0ef1784e3cb29','medical_receipt',null ,'160.00-3682002667302022111医疗费收据');
insert into t_v_tmp_zhyz values('65c698d1a66e4a88896751dda5d6ca07','vat_invoice','042002200111','(详见销货清单)');
insert into t_v_tmp_zhyz values('80ffd3df99894450b7f0d0b448983dc0','vat_invoice','032002100611','*运输服务*客运服务费、*运输服务*客运服务费');
insert into t_v_tmp_zhyz values('81b6c3cf021445478138ffa8f1429297','vat_invoice','014002200111','*运输服务*客运服务费');
insert into t_v_tmp_zhyz values('a229c6710ef84f1ca937d6f9225875f4','vat_invoice','042002200311','*运输服务*客运服务费');
insert into t_v_tmp_zhyz values('a24b33fc4efe4d758bb84ecc58878515','vat_invoice','042002200211','(详见销货清单)');
insert into t_v_tmp_zhyz values('a9ca337aec5a4d158063b3cf5f8abff5','vat_invoice','032002200211','*运输服务*客运服务费');
insert into t_v_tmp_zhyz values('b45d8c0d6c9b4c9f8d15b35498bf3007','vat_invoice','011002200711','*旅游服务*代订车服务费');
insert into t_v_tmp_zhyz values('d6aa2a115fea434482573ef44da4a496','vat_invoice','042002200311','*体育用品*京东京造哑铃男士杠铃家用可调节可拆卸包胶壶铃运动锻炼健身器材组合套装30KG、*体育用品*京东京造哑铃男士杠铃家用可调节可拆卸包胶壶铃运动锻炼健身器材组合套装30KG');
insert into t_v_tmp_zhyz values('dbad83cfd8ff4160aa698f7a6064f32a','vat_invoice','037002200111','*口腔清洁护理品*牙膏');
insert into t_v_tmp_zhyz values('e7b42698b4b44b69921f2b78ef02dc66','vat_invoice','042002200311','*体育用品*川崎Kawasaki羽毛球比赛训练耐打鹅毛球12个装10号球');
insert into t_v_tmp_zhyz values('e85cadc3cd944f089771d600ee682d7f','vat_invoice','042002200211','(详见销货清单)');
insert into t_v_tmp_zhyz values('eaa48c33cc354967ad6c1e6292277ecc','vat_invoice','050002100311','*其他贵金属*足银银条20克');
insert into t_v_tmp_zhyz values('ee60e274558547fc97ab58030023037f','vat_invoice','034001700111','*运输服务*客运服务费');
insert into t_v_tmp_zhyz values('f682e09def794289a1cf43a412460e24','vat_invoice','031002100211','*焙烤食品*蛋糕');
insert into t_v_tmp_zhyz values('fba126aa9c0048719e1c85f4b6dc37de','vat_invoice','042002200211','(详见销货清单)');
insert into t_fact_pj_fj values('489bf40db7e84245a0dff8d831857705','10832128df034a3fa73a7024f59930e1');
insert into t_fact_pj_fj values('2d3161f7ee204dfea18a16595fad0208','142163f3347c42d8bc5285752795c53e');
insert into t_fact_pj_fj values('eaa48c33cc354967ad6c1e6292277ecc','1bb5fb57979c4fa598d805be32d6b702');
insert into t_fact_pj_fj values('81b6c3cf021445478138ffa8f1429297','2ae805ffe5b3416fbe7440ef701f0a38');
insert into t_fact_pj_fj values('e85cadc3cd944f089771d600ee682d7f','2ba3e46110db411fa5ceff0ca15ebb43');
insert into t_fact_pj_fj values('65c698d1a66e4a88896751dda5d6ca07','2eea0e713b484f0c941e341759acbc37');
insert into t_fact_pj_fj values('207402b4e3744dd4a902f334a432edb2','39acab655d384f64bd81d1ff22e3260a');
insert into t_fact_pj_fj values('a9ca337aec5a4d158063b3cf5f8abff5','44a658228ca946e8aa17877f771a8aac');
insert into t_fact_pj_fj values('80ffd3df99894450b7f0d0b448983dc0','451a5929e90a4c5587c47d1f191d51c6');
insert into t_fact_pj_fj values('111d5413f5634037906f548c5d128295','46477f6484cf4e49b038b43562b542cd');
insert into t_fact_pj_fj values('f682e09def794289a1cf43a412460e24','47cc29ea1aa1492497b53945c8c6a205');
insert into t_fact_pj_fj values('19614c36effb411dbddd46b5a6d6aafb','4c8f41bdc9464eccb17e62495ec08caa');
insert into t_fact_pj_fj values('b45d8c0d6c9b4c9f8d15b35498bf3007','5012beb63de146e6bed8972ffcf38e37');
insert into t_fact_pj_fj values('5a5ee065940c4556a362324261a7f1a4','542f085e1cfd4f8f86ed337249868ed9');
insert into t_fact_pj_fj values('1c9c707d97d948e08796e3fea948454a','6646defd4f1241c3b5b586e4a9c9abf3');
insert into t_fact_pj_fj values('dbad83cfd8ff4160aa698f7a6064f32a','69bd3a04779c4850b119b0ec7e888683');
insert into t_fact_pj_fj values('3fc3adb139474ef4aaa151d0813e7a64','72aff74f72ea4fa4ad0fa31d5adc7694');
insert into t_fact_pj_fj values('a229c6710ef84f1ca937d6f9225875f4','736f27f52399402ab6038d44f81b7012');
insert into t_fact_pj_fj values('3b1dcb10973248e8ae7bb50f8862a3a3','808b53a25a4d4a2c82e889ef3323e35a');
insert into t_fact_pj_fj values('3b38b896a306467f96e58502b2e0cd24','8da38e005c5f4d049a0ea529a021fcfd');
insert into t_fact_pj_fj values('fba126aa9c0048719e1c85f4b6dc37de','94aa4ebc0e6548569c746384575cadf9');
insert into t_fact_pj_fj values('e7b42698b4b44b69921f2b78ef02dc66','96e7836a9425418980ba8cc30f7577cd');
insert into t_fact_pj_fj values('4768827068ab4e989287f561eb6b0c0c','b0b233c6fe014701a59ce8d510938f5c');
insert into t_fact_pj_fj values('5bcf0cfd97d743fcadd0ef1784e3cb29','ba7889a7487d4c8ebecf51d6d3c756b1');
insert into t_fact_pj_fj values('ee60e274558547fc97ab58030023037f','c505e29f59504d4aa1a4b6a58369b02e');
insert into t_fact_pj_fj values('4394d49d06b74838b59a41bfcd94f5e1','c71d5f1c86564c90893699e84b82075d');
insert into t_fact_pj_fj values('d6aa2a115fea434482573ef44da4a496','d325ebe56eae4c5a9013dd37af4a703a');
insert into t_fact_pj_fj values('a24b33fc4efe4d758bb84ecc58878515','f60f706e8c6a481696232247672137e2');
SELECT t0.PJXXUUID AS Z,
t0.BXUUID AS BXUUID,
t0.BXSXUUID AS BXSXUUID,
t1.项目名称 AS XMMC,
t1.FPDM
FROM t_fact_bx_bxsxmxb_cf t0
LEFT JOIN (select t0.PJXXUUID,t0.SJLY,t0.FPDM,t0.项目名称
from t_v_tmp_zhyz t0
LEFT JOIN szhfpgl.t_fact_pj_fj t1
ON (t0.PJXXUUID = t1.PJXXUUID)
) t1
ON t0.PJXXUUID = t1.PJXXUUID
ORDER BY t0.PJXXUUID
DDL语句,INSERT语句,以及精简版复现SQL语句见上面两条回复。
你好,目前已复现出来,应该是个bug,内部分析后进行反馈,非常感谢。
你好,这个bug,发现是开启向量化时,MERGE OUTER JOIN算子的执行结果有问题,目前代码已经在修复了,预计下个发布版本修复。
能预计出下下个版本发布大概什么时候吗
我在5.7.25-OceanBase_CE-v4.0.0.0 也重现了,这个bug 有点可怕,希望尽快修复。
另外楼主贴的SQL 需要修改一下:
社区版4.1 发布,不知道这个问题解决没有,我会空了安装一下试试。