The riddle of the working broken query

Question

I was going through some old code that was written in years past by another developer at my organization. Whilst trying to improve this code, I discovered that the query it uses had a very bad problem.

  OdbcDataAdapter financialAidDocsQuery =
            new OdbcDataAdapter(
                @"SELECT   a.RRRAREQ_TREQ_CODE, 
                           b.RTVTREQ_SHORT_DESC, 
                           a.RRRAREQ_TRST_DESC, 
                           RRRAREQ_STAT_DATE,
                           RRRAREQ_EST_DATE,
                           a.RRRAREQ_SAT_IND, 
                           a.RRRAREQ_SBGI_CODE, 
                           b.RTVTREQ_PERK_MPN_FLAG, 
                           b.RTVTREQ_PCKG_IND, 
                           a.RRRAREQ_MEMO_IND,
                           a.RRRAREQ_TRK_LTR_IND, 
                           a.RRRAREQ_DISB_IND, 
                           a.RRRAREQ_FUND_CODE, 
                           a.RRRAREQ_SYS_IND
                  FROM     FAISMGR.RRRAREQ a, FAISMGR.RTVTREQ b
                  WHERE    a.RRRAREQ_TREQ_CODE = b.RTVTREQ_CODE
                           and a.RRRAREQ_PIDM = :PIDM
                           AND a.RRRAREQ_AIDY_CODE = :AidYear ",
                this.bannerOracle);
        financialAidDocsQuery.SelectCommand.Parameters.Add(":PIDM", OdbcType.Int, 32).Value = this.pidm;
        financialAidDocsQuery.SelectCommand.Parameters.Add(":AidYear", OdbcType.Int, 32).Value = this.aidYear;
        DataTable financialAidDocsResults = new DataTable();
        financialAidDocsQuery.Fill(financialAidDocsResults);
        FADocsGridView.DataSource = financialAidDocsResults;
        FADocsGridView.DataBind();

The problem is that the column a.RRRAREQ_TRST_DESC does not exist. A fact you learn very quickly when running it in Oracle SQL Developer.

The strange thing?

This code works.

The gridview binds successfully. (It doesn't try to bind to that field.) And it's been in production for years.

So, my question is...why? I've never seen a bad query work. I've never seen Oracle allow it or a data provider hack around it.

Does anyone have any idea what's going on here?

does it cause an exception that gets handled, and something **else** is doing a valid query and binding that? are you positive its really used? it isn't vestigal code that isn't actually referenced? — John Gardner, Oct 26 '10 at 17:23
Are you sure you are running this query on correct database or some backup db which does have that column? — Sachin Shanbhag, Oct 26 '10 at 17:27
I'm with Sachin. IF this code is executing then I the query isn't hitting the database you think it is. — NotMe, Oct 26 '10 at 17:51
Is `FAISMGR.RRRAREQ` a table or a view; either way can you post the DDL for it? Are you connected as the same user when running it in SQL Developer (yes I know the schema is specified, but still...)? And what is the actual error you get? — Alex Poole, Oct 26 '10 at 18:00
It is a table. It runs in a SGHE Banner database (Higher-Ed Campus Management), finaid module. (+1 to column names Sam). — REW, Oct 27 '10 at 04:58
I'm currently trying to figure out why this code is working in our other environments. Grabbing the dll and using reflector. I'll update as soon as I figure out what the hell this thing is doing. — clifgriffin, Oct 28 '10 at 14:29

riwalk · Accepted Answer · 2010-10-26T17:45:39.973

7

Hmmm...A few things to check:

Does this code actually run? It may seem silly to suggest this, but there may be a newer file that replaced this one.
Is an exception being squelched by your code? (Anyone who would name columns like that is definitely capable of squelching those pesky exceptions)
Is the exception being squelched by 3rd party code? (Not as likely, but sometimes 3rd party code prefers to use annoying error codes instead of exceptions).

Past those suggestions, I'm not sure.

EDIT:

Revisiting the 2nd point, if you are working in ASP.NET, check that there is no global-level exception handler that is squelching exceptions. I ran into that problem on one site that I worked on and found dozens of exceptions in a single day.

edited Oct 26 '10 at 17:45

answered Oct 26 '10 at 17:27

riwalk

14,033
6
51
68

I'm pretty sure my basis are covered on those 3 counts because I can actually see data in the gridview that is binding to that table. The code, while not being changed in years, has been pushed through our deployment process many times so I'm confident it's the same code. :) – clifgriffin Oct 26 '10 at 17:41
Even if you can see the data in the table, you are not sure that is the actual query run. Have you tried to add a dummy-column, " 'SO' as Dummy" and see if that also appears in the gridview? – Espo Oct 27 '10 at 08:54
I'm giving you credit for this since you properly identified that the issue was the code I was looking at wasn't what was running. – clifgriffin Oct 28 '10 at 14:56

score 4 · Answer 2 · answered Oct 26 '10 at 17:57

4

Try running

select * from v$sql where sql_fulltext like '%a.RRRAREQ_TRST_DESC%'

shortly after you bind the grid. That will tell you if the statement was actually seen by Oracle. Note that you should only see the above query if it was not seen by Oracle.

answered Oct 26 '10 at 17:57

RussellH

1,239
1
9
13

Similarly, you can run a trace with DBMS_MONITOR and check the query in the trace file. – Gary Myers Oct 26 '10 at 22:16

score 0 · Answer 3 · answered Oct 27 '10 at 06:27

Use ODBC trace log to see if this query is really send to database, and see what database returns. Then use any other ODBC based database tool and check if this query work from this tool. As an ultimate test you can write simple Python script. Easiest way it to use ActiveState Python 2.x with odbc module included. Test code can look like:

import odbc

connection = odbc.odbc('dnsname/user/password')
cursor = connection.cursor()
cursor.execute("select ...")
for row in cursor.fetchall():
    print '\t'.join([str(r) for r in row])

If there was no error in your program and an error in other tools then compare theirs ODBC traces.

score 0 · Answer 4 · answered Oct 27 '10 at 18:30

If I understand what the original author was trying to do, and with Banner that is never easy to figure out, then this query should be correct:

SELECT a.rrrareq_treq_code,
       b.rtvtreq_short_desc,
       c.rtvtrst_desc,
       rrrareq_stat_date,
       rrrareq_est_date,
       a.rrrareq_sat_ind,
       a.rrrareq_sbgi_code,
       b.rtvtreq_perk_mpn_flag,
       b.rtvtreq_pckg_ind,
       a.rrrareq_memo_ind,
       a.rrrareq_trk_ltr_ind,
       a.rrrareq_disb_ind,
       a.rrrareq_fund_code,
       a.rrrareq_sys_ind
FROM   faismgr.rrrareq a,
       faismgr.rtvtreq b,
       faismgr.rtvtrst c
WHERE  a.rrrareq_treq_code = b.rtvtreq_code
AND    a.rrrareq_trst_code = c.rtvtrst_code
AND    a.rrrareq_pidm = :PIDM
AND    a.rrrareq_aidy_code = :AidYear;

score 0 · Answer 5 · answered Oct 28 '10 at 14:54

Well, let's file this in the false alarm category.

I decided to have our VAT send a copy of the DLL from test. I pulled it apart with reflector and found, much to my embarrassment, that the query is right. Which makes sense.

I still can't figure out why my working copy would have one incorrect field_name. To my knowledge, I had never touched this file before this week. But, SVN doesn't have any history showing this error in previous versions.

So strange...maybe I'm losing my mind.

Thanks for all of the quality feedback on this question. I certainly learned some new trouble shooting techniques and for that I'm very appreciative. :)

Happy coding, Clif

The riddle of the working broken query

5 Answers5