一、先跑个脚本把问题复现出来很多刚从Oracle迁到金仓KES的DBA都遇到过这种情况SQL在测试环境跑得好好的一上生产就出幺蛾子。不是报错就是查出来的数据不对更诡异的是——同一个会话里手动执行能查出数据脚本跑就不行。我今天就把这个问题的完整复现过程写下来。你直接在KES环境里跑下面这套脚本就能亲眼看到那个让人抓狂的Bug长什么样-3。1.1 建一张业务表先创建一张账户余额表用来存客户的账户信息-3-- -- 脚本段 1创建业务表 account_balance -- DROP TABLE IF EXISTS account_balance; CREATE TABLE account_balance ( acct_id NUMBER(10) PRIMARY KEY, cust_id NUMBER(10) NOT NULL, balance NUMBER(15, 2) DEFAULT 0.00, acct_status VARCHAR2(20) DEFAULT NORMAL, update_time DATE DEFAULT SYSDATE ); COMMENT ON TABLE account_balance IS 账户余额表; COMMENT ON COLUMN account_balance.acct_status IS 状态NORMAL-正常, FROZEN-冻结, CLOSED-销户;1.2 往里插几条测试数据-- -- 脚本段 2插入测试数据 -- INSERT INTO account_balance (acct_id, cust_id, balance, acct_status) VALUES (10001, 101, 5000.00, NORMAL); INSERT INTO account_balance (acct_id, cust_id, balance, acct_status) VALUES (10002, 102, 3000.50, NORMAL); INSERT INTO account_balance (acct_id, cust_id, balance, acct_status) VALUES (10003, 101, 8000.00, FROZEN); INSERT INTO account_balance (acct_id, cust_id, balance, acct_status) VALUES (10004, 103, 1200.00, NORMAL); COMMIT;注意这里的数据客户101有两条记录一条正常一条冻结-3。这个细节在后面复现问题的时候会用到。1.3 创建那个“惹祸”的Package接下来是重头戏。创建一个包里面放一个会话级的全局变量再加一对set和get函数-3-12-- -- 脚本段 3创建带全局变量的 Package -- CREATE OR REPLACE PACKAGE pkg_session_data IS -- 全局变量存储当前操作的客户ID -- 注意这个变量是会话隔离的只要连接不断值就一直存在 g_cust_id NUMBER(10); -- 设置函数修改全局变量返回状态码 FUNCTION set_cust_id(p_cust_id IN NUMBER) RETURN NUMBER; -- 获取函数读取全局变量 FUNCTION get_cust_id RETURN NUMBER; -- 清理函数重置状态用于测试 PROCEDURE reset_context; END pkg_session_data; / CREATE OR REPLACE PACKAGE BODY pkg_session_data IS FUNCTION set_cust_id(p_cust_id IN NUMBER) RETURN NUMBER IS BEGIN IF p_cust_id IS NULL THEN g_cust_id : NULL; RETURN 0; ELSE g_cust_id : p_cust_id; RETURN 1; -- 返回成功标志 END IF; END; FUNCTION get_cust_id RETURN NUMBER IS BEGIN RETURN g_cust_id; END; PROCEDURE reset_context IS BEGIN g_cust_id : NULL; END; END pkg_session_data; /1.4 看一眼数据长什么样-- -- 脚本段 4查看初始数据 -- SELECT acct_id, cust_id, balance, acct_status FROM account_balance ORDER BY acct_id; -- 预期结果 -- 10001 | 101 | 5000.00 | NORMAL -- 10002 | 102 | 3000.50 | NORMAL -- 10003 | 101 | 8000.00 | FROZEN -- 10004 | 103 | 1200.00 | NORMAL二、问题SQL长什么样下面这条SQL就是当年在Oracle里跑了多年、迁到KES之后出问题的那条-2-1-- -- 脚本段 5有问题的SQL依赖函数执行顺序 -- SELECT * FROM account_balance WHERE cust_id pkg_session_data.get_cust_id() AND pkg_session_data.set_cust_id(101) 1;开发人员的意图很明确先用set_cust_id(101)把会话变量设为101再用get_cust_id()把这个值取出来去过滤account_balance表查出客户101的所有账户。他的理由是“在KES里WHERE子句是从左到右执行的所以set一定先于get执行没问题。”-2听起来有道理对吧但现实是这条SQL在不同的数据库里跑出来的结果完全不一样。三、在Oracle里跑是什么结果先看Oracle。Oracle的优化器是出了名的“有主见”——它不保证WHERE子句里多个函数的执行顺序-2-1。优化器可能基于以下原因调整执行顺序-2-11谓词重排根据过滤率和代价模型重新排列条件尽早过滤掉不合格的行短路优化如果一个条件已经能决定整个表达式的真假后面的直接跳过并行执行并行查询时不同分片可能在不同线程上各跑各的在Oracle里跑上面那条SQL结果完全不可预测。运气好优化器按从左到右执行先跑set再跑get能查出数据。运气不好优化器觉得get_cust_id()的过滤率更高先执行它——但此时变量还是空的get返回NULL条件为FALSE短路评估直接跳过右边的set整个查询返回空集-。Oracle官方社区对这个问题的态度非常明确WHERE子句中函数的执行顺序没有任何保证-。你今天测出来的顺序明天执行计划一变就可能反过来。四、在KES里跑是什么结果金仓KES在这个问题上走了另一条路KES严格按WHERE子句中表达式的书写顺序从左到右依次执行无论等式还是不等式--2-1。所以在KES里跑上面那条SQLset_cust_id(101)一定会先执行变量被赋值为101然后get_cust_id()读到101查询返回客户101的两条记录。看起来一切正常对吧但事情远没有这么简单。五、为什么说依赖顺序仍然不安全5.1 先看第一个坑会话污染我刚才说了g_cust_id是会话级变量。在测试环境里开发人员手动执行SQL的时候往往是先执行一遍正确的写法再执行别的测试用例——但会话一直开着变量已经被赋过值了-2-1。来跑一下下面这几条SQL感受一下什么叫“测试幻觉”-- -- 脚本段 6复现测试幻觉 -- -- 第一步先执行一个正确的查询set在前 SELECT * FROM account_balance WHERE cust_id pkg_session_data.get_cust_id() AND pkg_session_data.set_cust_id(101) 1; -- 返回客户101的两条记录 ✓ -- 第二步再执行一个错误的查询get在前但没写set SELECT * FROM account_balance WHERE cust_id pkg_session_data.get_cust_id(); -- 猜猜返回什么 -- 因为g_cust_id还残留着101的值居然也能查出数据 -- 这就是测试幻觉——看起来SQL怎么写都能跑通看到了吗第二次执行的时候明明没有调用set_cust_id但因为变量里还留着第一次执行时赋的值查询依然能返回结果-2。到了生产环境应用服务器用连接池管理数据库连接。每次从池里拿出来的连接可能是全新的会话变量是空的也可能是被复用过的里面残留着上一个请求设的值-1。结果就是同一个SQL有时候能查出数据有时候查不出来全看命-1。更可怕的是这种问题不会报错。语法没错函数调用也没抛异常就是数据不对。日志里什么都看不到-3。5.2 再看第二个坑短路评估就算KES保证了从左到右执行短路评估仍然是个坑。-- -- 脚本段 7短路评估的陷阱 -- -- 假设变量当前是空的 EXEC pkg_session_data.reset_context(); -- 这条SQLget在前面返回NULL条件为FALSE -- 短路评估直接跳过右边的setset根本没执行 SELECT * FROM account_balance WHERE cust_id pkg_session_data.get_cust_id() -- 返回NULLFALSE AND pkg_session_data.set_cust_id(101) 1; -- 被跳过了 -- 返回空集在AND逻辑里如果第一个条件是FALSE第二个条件压根不会被执行-2。你指望set_cust_id去设置变量但它连跑的机会都没有。5.3 再看第三个坑优化器的等价变换前面说了等价变换是优化器在逻辑优化阶段的核心工作——在保证结果不变的前提下把SQL重写成更高效的形式-19。比如谓词下推这是优化器最核心的变换手段之一--19-- -- 脚本段 8谓词下推示例 -- -- 原始SQL过滤条件在外层 SELECT emp.*, dept.dept_name FROM emp JOIN dept ON emp.dept_id dept.dept_id WHERE dept.dept_name 研发部; -- 优化器等价改写为谓词下推 SELECT emp.*, sub.dept_name FROM emp JOIN ( SELECT dept_id, dept_name FROM dept WHERE dept_name 研发部 ) sub ON emp.dept_id sub.dept_id;原始写法需要全量扫描两张表完成关联再过滤改写后先过滤dept表只留研发部数据再跟emp关联关联计算量天差地别-19。还有子查询提升-19-- -- 脚本段 9子查询提升示例 -- -- 原始SQL SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.a 1 OR t1.b 10); -- 等价扩展改写 SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.a 1) OR t1.b 10;拆分后t1.b10可以直接过滤外层表不用遍历t2表-19。还有常量折叠-19-- -- 脚本段 9子查询提升示例 -- -- 原始SQL SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.a 1 OR t1.b 10); -- 等价扩展改写 SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.a 1) OR t1.b 10;这些变换本身没问题都是为了性能。但如果你的WHERE条件里调用了有副作用的函数——优化器在做等价变换的时候可能会改变函数调用的位置和时机-。金仓KES的等价变换有一套安全校验机制每一条变换都要过两层校验-19。但再怎么校验也架不住你在条件里放一个会改状态的函数——因为优化器的等价变换是基于“函数是无副作用的纯函数”这个假设来做的。六、怎么验证你的SQL有没有问题6.1 用EXPLAIN看执行计划-- -- 脚本段 11查看执行计划 -- EXPLAIN ANALYZE SELECT * FROM account_balance WHERE cust_id pkg_session_data.get_cust_id() AND pkg_session_data.set_cust_id(101) 1;看执行计划里的Filter顺序可以看到各个条件实际执行的顺序和耗时-。6.2 写个测试脚本验证顺序-- -- 脚本段 12验证函数执行顺序 -- -- 先重置状态 EXEC pkg_session_data.reset_context(); -- 执行查询观察返回结果 SELECT * FROM account_balance WHERE cust_id pkg_session_data.get_cust_id() AND pkg_session_data.set_cust_id(101) 1; -- 如果返回空集说明get先执行了或者set被短路跳过了 -- 如果返回数据说明set先执行了七、正确的写法应该是什么样7.1 方案一把状态设置和查询分开这是最推荐的做法——把“改状态”和“查数据”彻底解耦--- -- 脚本段 13正确的写法方案一 -- -- 第一步先设置状态 SELECT pkg_session_data.set_cust_id(101) FROM DUAL; -- 第二步再执行查询 SELECT * FROM account_balance WHERE cust_id pkg_session_data.get_cust_id();这样写逻辑清晰不依赖任何执行顺序在任何数据库里行为都是一致的。7.2 方案二用普通变量代替函数如果场景简单直接用变量-- -- 脚本段 14正确的写法方案二 -- DECLARE v_cust_id NUMBER : 101; BEGIN SELECT * FROM account_balance WHERE cust_id v_cust_id; END; /7.3 方案三纯读取函数声明为STABLE/IMMUTABLE如果函数确实不修改状态纯读取在数据库里把它声明成STABLE或IMMUTABLE-。这能帮助优化器更好地理解函数行为做更积极的等价变换。-- -- 脚本段 15声明函数属性 -- -- 纯读取函数不修改任何状态 CREATE OR REPLACE FUNCTION get_cust_id_safe RETURN NUMBER STABLE -- 告诉优化器这个函数在同一个事务中返回相同结果 IS BEGIN RETURN pkg_session_data.g_cust_id; END; /八、总结这篇文章的核心观点其实就一句话永远不要在WHERE子句里依赖函数执行顺序来实现业务逻辑。为了佐证这个观点我们跑了一套完整的脚本——建表、插数、建Package、写函数、执行有问题的SQL、分析原因、给出修复方案。整个过程你可以在KES环境里完整复现-3。不管用的是Oracle还是金仓KES不管优化器是自由调度还是严格按顺序执行——在WHERE里放有副作用的函数把业务正确性押在执行顺序上都是在给自己埋雷-2-1。SQL是声明式语言不是过程式语言。逻辑归逻辑查询归查询。把状态变更塞进查询语句里不仅违背了数据库的设计初衷还会埋下极难排查的生产隐患。