Receiving SQL Error -76, Cardinality Mismatch, even though query has no INTO

Question

Question

Laura Cavanaugh · Oct 3, 2018

I'm running this code in our application, and in the terminal:

>set query="SELECT * FROM PMG_Data_Private.RemitInstance WHERE (VoucherNumber %INLIST ? SIZE ((10)))"

>set tRS = ##class(%ResultSet).%New("%DynamicQuery:SQL")

>set ok=tRS.Prepare(query)

>d $system.Status.DisplayError(ok)

ERROR #5540: SQLCODE: -76 Message: Cardinality mismatch between the SELECT-list and INTO-list
SQLTEXT: SELECT * FROM PMG_Data_Private.RemitInstance WHERE (VoucherNumber %INLIST ? SIZE ((10)))

It's not really a problem with the query, since there's no INTO keyword int he query, and I'm not getting it on QA or a production server -- using the same query etc. Another programmer tends to delete cached queries when this happens.

However, I purged cached queries for this statement, this table, and for this namespace, and still receive this error (respectively).

What could be causing this error? It's also appearing in out application, so I don't want to move anything up to production until I know that this won't happen.

Should I try to use the newer %SQL.Statement class? (this is older code)

Thanks,

Laura

Discussion (5)1

Log in or sign up to continue

Wolf Koelling · Oct 5, 2018

I think Kev is right: the upgrade froze all query plans. In your DEV environment the class definition might have moved on, probably added a new field - which should cause the plan to become unfrozen but there was (is?) a bug in Caché with that. The fix is

DPV5125 - SQL: Correct frozen plan error condition of SELECT * validation

We had a patch for that for v2017.1 and it is supposed to be included in 2017.2.2, although I'm not too sure as we recently upgraded to that version and had the same problem afterwards.

1 0

Kyle Baxter · Oct 10, 2018

Hey Laura!

OK so if Frozen Query Plans was your problem (and it's a good hypothesis), then unfreezing them all gets you back to the old behavior (you'll need to recompile and purge cached queries). There were some problems with the 'old' dynamic SQL classes, but I think those were solved, so you shouldn't have any problems going forward. So let's talk about this a bit.

What are Frozen Query Plans?
If you've ever upgraded Caché and had a critical query degrade in performance, you'll agree this technology is pretty cool. What it does, is to lock down your query execution plan during upgrades (or it can be user [you're the user] initiated).

Benefits: No bad surprises during upgrades
Drawback: No benefits from core improvements to SQL engine

Is it a problem to Unfreeze all plans?

No! Of course not. You will absorb some risk, but if you usually don't have problems then you will continue to not usually have problems. If you unfreeze all plans you'll need to compile your application and/or purge cached queries. To do this you can execute the following in your application namespace:

w $SYSTEM.SQL.FreezePlans(0,1,,.err)
d $SYSETM.SQL.Purge()

You can obviously look this up in the docs for more information. If you have embedded SQL queries those would need to be recompiled.

Dynamic SQL Implementation
We (InterSystems) recommend using %SQL.Statement in lieu of %ResultSet for dynamic SQL. When you ask if you should change, the answer is 'yes'. However, this only fixes your problem because when you use %SQL.Statement it ends up being a slightly different query according to our Frozen Query Plan implementation. It fixes your problem, but not the root of the problem. %SQL.Statement is also fine to use for 'older' code. Of course, if you don't want to change your code ("if it ain't broke"), that's understandable.

SELECT * queries

Running a SELECT * query is a bit of an edge case. It is frequently better to list out your fields so your are only showing your users the data they are interested in. Indeed, you can only run into this problem if it is a SELECT * query. In general, list out your fields.

So that's that. If you have any further questions, feel free to let me know :-)

0 0

score 0 · Answer 1 · 2018-10-03T14:59:29-04:00

Update: using the %SQL.Statement class did help, and I had to change only a few lines of code (the Execute, Next, and Data methods).

Still curious as to why this was happening on our DEV server but not other servers, where the other servers still have the original code using %ResultSet class. And why now -- we've been using this code for years, and upgraded to 2017 in July.

Thanks,

Laura

score 1 · Answer 2 · 2018-10-04T12:14:26-04:00

we had this problem, the issue was "frozen queries"

look at frozen queries in the docs, and then from in the SQL portal, you can "un-freeze" them and remove cached queries

score 0 · Answer 3 · 2018-10-09T11:44:36-04:00

I think the frozen plans from our fairly recent upgrade was indeed the problem, especially taking into account what Wolf said about adding new properties to a table. I had done that recently, which makes the "SELECT-list and INTO-list mismatch" error more plausible. We upgraded to 2017.2.2.

I coudln't really test unfreezing the plan (which was in a Frozen/Upgrade status) because I had already changed the code to use a new object, %SQL.Statement. And, this also makes sense, since using a new object probably created a new plan ... ?

We are not that sophisticated -- we don't have a DBA -- I'd prefer to let InterSystems code optimize the queries. We never optimize and freeze plans. We just add Indexes to make queries faster. I'll probably leave the queries frozen until we come across this problem again. Unless I hear that it's OK to simply unfreeze them all. What's the downside to that?

Thanks for the help,

Laura