-5849 The sub-query flattening settings for the query plan and the current value of NO_SUBQF environment variable do not match.

Hi! We are getting -5849 error from our Informix server (14.10FC9W1). The error description mentioned the NO_SUBQF environment variable. Where can I find some information about this environment variable? The sub-query flattening settings for the query plan and the current value of NO_SUBQF environment variable do not match. .PROBLEM The query plan was created with a different value of the NO_SUBQF environment variable than the current value of the NO_SUBQF environment variable. .ACTION Change the value of the NO_SUBQF environment variable or run UPDATE STATISTICS to recreate the routine query plan. There was an APAR earlyer in 12.10 xC1-xC4. IT03174: RUNNING A PROCEDURE WHICH CONTAINS MULTIPLE SUB-QUERIES ON VIEWS CAN RETURN ERROR -5849 OR -640 WITH NO_SUBQF SET ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------

Gabor, From the lab: The only valid values are 1 or 0. 1 turns off the subquery flattening and allows the subquery to stay as a subquery instead of getting it transformed into a join. 0(default) allows query sub-flattening. Server boot is not needed to make changes take effect. Scott Pickett IBM Informix Product Management spickett@us.ibm.com 617-899-7549

Gabor, From the lab: Upon further search it seems like it is a server environment variable and used with SETENV. It is not a session environment variable and therefore SET ENVIRONMENT statement is not valid. What were your settings (if any) when you encountered the error? Scott Pickett IBM Informix Product Management spickett@us.ibm.com 617-899-7549

Gabor, An example using it, from the lab: Some more information. Here is how a query uses it: -server is running -setenv NO_SUBQF 1 - dbaccess to run a query will NOT flatten the subquery -setenv NO_SUBQF 0 - dbaccess to run a query will flatten the subquery Ex: create table A(a int); create table B(b int); insert into A values(1); insert into B values(1); set explain on; select a from A where a in (select b from B where a = b); ---- correlated subqueryrunning the above query with NO_SUBQF 1 will have: 1) uaymak1.a: SEQUENTIAL SCAN Filters: uaymak1.a.a = ANY <subquery> Subquery: --------- 1) uaymak1.b: SEQUENTIAL SCAN Filters: uaymak1.b.b = uaymak1.a.a Running it without: 1) uaymak1.a: SEQUENTIAL SCAN 2) uaymak1.b: SEQUENTIAL SCAN (First Row) Filters: uaymak1.a.a = uaymak1.b.b NESTED LOOP JOIN (Semi Join) Scott Pickett IBM Informix Product Management spickett@us.ibm.com 617-899-7549

This error seems to indicate that the OP was executing a stored routine that had been compiled without NO_SUBQF set but was executed with that environment variable set (whether in the engine or in the application calling the routine) or vice-versa that it was compiled with NO_SUBQF set but was executed without that flag set. The solution is to recompile the routine (with UPDATE STATISTICS FOR ROUTINE ...) while having the environment set correctly so that the stored query plan will agree with the runtime environment. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Scott! Thank you for your reply. We haven't setup the NO_SUBQF Enviroment variable anywhere in our system, neither on the server nor on the client machines. We didn't know about the NO_SUBQF until now. I didn't found I set a trap for the 5849 errno and I got a similar stack as in the mentioned APAR 15:09:50 15:09:50 IBM Informix Dynamic Server Version 14.10.FC9W1 15:09:50 Assert Failed: found errno 5849 in session 47013 15:09:50 Who: Session(47013, zeussys@labifx6, 2566013, 0x50e2fca8) Thread(766471, sqlexec, 50df8d68, 9) File: rsdebug.c Line: 3064 15:09:50 Action: Use onmode -I to stop verbose error trapping. 15:09:50 SHM Globals and Master Pool/Master Block Adresses: 15:09:50 shmcb = 0x000000004422c698 15:09:50 rhead = 0x0000000044299800 15:09:50 pool list = 0x000000004422c770 15:09:50 block pool list = 0x0000000044293f78 15:09:50 TRANSP = 0x0000000050e27ea8 15:09:50 PARTP = 0x0000000000000000 15:09:50 PARTNP = 0x0000000000000000 15:09:50 OPENP = 0x000000008a658028 15:09:50 FILEP = 0x000000008ac409d8 15:09:50 Raw hex dump of stack located in /opt/app/informix/14.10.FC9W1/tmp/af.b5efa61d.rawstk 15:09:50 Stack for thread: 766471 sqlexecbase: 0x0000000095053000 len: 266240 pc: 0x00000000014dc4fd tos: 0x000000009508ba70 state: running vp: 9 0x00000000014dc4fd (oninit) afstack 0x00000000014d9f69 (oninit) afhandler 0x00000000014d6ca2 (oninit) affail_interface 0x0000000000efa0a8 (oninit) check_traperror 0x0000000000765fa8 (oninit) sqerr1 0x0000000000bccad2 (oninit) d_conblock 0x0000000000bcc8f8 (oninit) d_qplan 0x000000000070061e (oninit) qd_getqplan 0x00000000006e097b (oninit) ip_evalexpr 0x00000000006dd520 (oninit) runproc 0x00000000006f1589 (oninit) udrlm_spl_execute 0x0000000000b10ef0 (oninit) udrlm_exec_routine 0x000000000072e03d (oninit) udr_execute 0x0000000000739ff6 (oninit) exroutine 0x00000000008fa9d1 (oninit) geval 0x00000000016ce29e (oninit) ev_geval 0x00000000016c4f7f (oninit) new_eval 0x00000000006e017a (oninit) ip_evalexpr 0x00000000006dd520 (oninit) runproc 0x00000000006f1589 (oninit) udrlm_spl_execute 0x0000000000b10ef0 (oninit) udrlm_exec_routine 0x000000000072e03d (oninit) udr_execute 0x0000000000739ff6 (oninit) exroutine 0x00000000008fa9d1 (oninit) geval 0x00000000016ce29e (oninit) ev_geval 0x00000000016c4f7f (oninit) new_eval 0x00000000006e017a (oninit) ip_evalexpr 0x00000000006dd520 (oninit) runproc 0x00000000006f1589 (oninit) udrlm_spl_execute 0x0000000000b10ef0 (oninit) udrlm_exec_routine 0x000000000072e03d (oninit) udr_execute 0x0000000000739ff6 (oninit) exroutine 0x00000000008fa9d1 (oninit) geval 0x00000000016ce29e (oninit) ev_geval 0x00000000016c4f7f (oninit) new_eval 0x00000000006e017a (oninit) ip_evalexpr 0x00000000006dd520 (oninit) runproc 0x00000000006f1589 (oninit) udrlm_spl_execute 0x0000000000b10ef0 (oninit) udrlm_exec_routine 0x000000000072e03d (oninit) udr_execute 0x0000000000739ff6 (oninit) exroutine 0x00000000008fa9d1 (oninit) geval 0x00000000016ce29e (oninit) ev_geval 0x00000000016c4f7f (oninit) new_eval 0x00000000006e017a (oninit) ip_evalexpr 0x00000000006dd520 (oninit) runproc 0x00000000006f1589 (oninit) udrlm_spl_execute 0x0000000000b10ef0 (oninit) udrlm_exec_routine 0x000000000072e03d (oninit) udr_execute 0x00000000006e921b (oninit) ip_fetch 0x0000000000875d2b (oninit) getrow 0x0000000000875c6e (oninit) fetchrow 0x0000000000608954 (oninit) exfetch 0x0000000000ab89ad (oninit) sql_nfetch 0x0000000000ab8284 (oninit) sq_nfetch 0x0000000000b96cee (oninit) sqmain 0x0000000001610499 (oninit) spawn_thread 0x00000000014c9633 (oninit) th_init_initgls 0x000000000151220f (oninit) startup /opt/app/informix/14.10.FC9W1/bin/onstat -g sql 47013: IBM Informix Dynamic Server Version 14.10.FC9W1 -- On-Line -- Up 33 days 07:18:59 -- 1630992 Kbytes 2023-07-12 15:09:56Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 47013 capsys CR Wait 3 -5849 0 9.24 Off Current Role : ZeusSys Stored procedure stack : context proc-counter opcode name ------------------------------ 0x000000008a658498 0x95d9ef68+0x0010 EXPR capsys:skv_pnz_reszlet 0x0000000097ae0058 0x9ba13010+0x0010 EXPR capsys:skv_pnz 0x0000000073453b68 0x8617d9a0+0x0010 EXPR capsys:skv_pnzvez 0x0000000078d72498 0x85e98200+0x0010 EXPR capsys:sor_elemfeldolg 0x0000000071205058 0x6b4e28c8+0x0010 EXPR capsys:sor_feldolg Current SQL statement (161627) in procedure capsys:skv_pnz_reszlet proc-counter 0x95d9ef68 opcode EXPR (and (<> penz, 0), (in szszazonpar, (select szsz_azon from szsz_ekxw))) Last parsed SQL statement : execute procedure sor_feldolg('log',0)This is our spl code part witch is cause the issue. I uploaded the szsz_ekxw view source code. if penz != 0 and szszazonpar in ( select szsz_azon from szsz_ekxw ) then let osszdij = osszdij + penz; let tranzdij = tranzdij + penz; end if; ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest

Art Kagel had a thought more recent than this thread. See if you think that might apply to you. Scott Pickett IBM Informix Product Management spickett@us.ibm.com 617-899-7549

Hi I tested the solution what was mentioned by Art. I set the NO_SUBQF=1 for an user on a server and I executed a UPDATE STATISTICS FOR ROUTINE to every procedure what was created by us. Our first problematic query was executed fine. But after this some other procedure had a same problem. I found one more suggestion elsewhere. IT04651: WRONG RESULTS RETURNED IF LEFT OUTER JOIN ANSI SQL SYNTAX USED WITH SUBQUERY FLATTENING (ibm.com) To avoid sub query flattening, set NO_SUBQF=1 from the client application before running the query. Also, you can disable sub query flattening for statements with (Not Exists) in them by setting the onconfig parameter SQL_FEAT_CTRL 0x00008000. I will try this solution and test it for a few days. We'll see what we experience. [informix@labifx6 ~]$ onmode -wm SQL_FEAT_CTRL=0x00008000 SQL_FEAT_CTRL modified: Old value:0x8008 New value:0x8000 The errno trap will be active for a -5849 I tested our procedure on IBM Informix Dynamic Server Version 14.10.FC3 and it's work fine. I'm looking for a root cause why this behavor come up with 14.10.FC9W1 ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------

Hi Gábor, this looks to be a slight misconception of how these bit field parameters work: your SQL_FEAT_CTRL setting apparently already contained 0x8000, so no need to change it unless you want to turn the bit off. What the command you ran achieved was turning off bit 0x8 which might have unexpected consequences elsewhere. You always first look at the parameter's current value, see whether the target bit is on or off, and then, only if that bit doesn't already have the desired state, use onmode to set the entire parameter value to one that has the bit in the desired state. And no, these bits are not documented, for good reason as they're meant to be used/changed only on very exceptional basis, typically to turn off a certain defect fix in case of undesired side effects, at the direction of tech support. As for this particular bit, does the query in question use ANSI left outer join? And, what the APAR doesn't mention, any NOT EXISTS clause (resulting in a sub-query)? BR, Andreas ------------------------------ Andreas Legner ------------------------------

Regarding the APAR mentioned (IT03174), it indicates a known issue in Informix 12.10 xC1-xC4 related to running procedures containing multiple sub-queries on views, which can result in error -5849 or -640 when the NO_SUBQF environment variable is set. ------------------------------ Youssef Sbai Idrissi Software Engineer ------------------------------

Hi Andreas Yes I changed back the SQL_FEAT_CTRL 0x8 value. When the first error was occured its come from this query . if penz != 0 and szszazonpar in ( select szsz_azon from szsz_ekxw )then The szszs_ekxw is a view create view "zeus1000".szsz_ekxw ( szsz_azon, szsz_strukod, szsz_levelx, szsz_nev, szsz_jelleg, szsz_elojelx, szsz_kijelezx, szsz_tiltx, szsz_initx, szsz_megj) as select x0.szsz_azon, x0.szsz_strukod, x0.szsz_levelx, x0.szsz_nev, x0.szsz_jelleg, x0.szsz_elojelx, x0.szsz_kijelezx, x0.szsz_tiltx, x0.szsz_initx, x0.szsz_megj from "zeus1000".szmlszamok as x0 left join "zeus1000".szmlszamok as x1 on x1.szsz_strukod [1,4] = x0.szsz_strukod [1,4] where (x1.szsz_azon = 'EKX') or ( x0.szsz_azon = 'EKB'); The second error come from this f oreach select sel_iazon into iazon from szmlertlog join instrumentum on sel_iazon = i_azon where sel_tazon = _tazon andsel_ttip = _ttip and sel_darab > 0 and exists( select 1 from insttip join itellszml on ite_itazon = it_azon where it_azon = i_itazon and ite_uazonell = sel_uazon and ite_sasazonell = sel_sasazon and ite_szkazonell = sel_szkazon ) .. end foreach; The last time we modified this code was before 2013. So they work well over a decade. if this queries executed by a serverside background process with NO_SUBQF=1 its work fine. If we want to execute this queries from a workstation side we can't setup NO_SUBQF variable with setnet32. ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------

Apart from the fact that all this seems to warrant a support case, why do you think you can't setup NO_SUBQF variable with setnet32? Have you tried? ------------------------------ Andreas Legner ------------------------------

Yes, we are thinking about opening a support case. I tried with the setnet32 client and I can only modify the existing variables. I tried add it with loading from .ifx config file. I can't validate a changes because i didn't see in a list. I will try add it with direct regedit like this: [HKEY_CURRENT_USER\\SOFTWARE\\Informix\\Environment] "NO_SUBQF"="1" [HKEY_LOCAL_MACHINE\\SOFTWARE\\Informix\\Environment] "NO_SUBQF"="1" ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------