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?

Answer 1

Hmmm...A few things to check:

1. Does this code actually run? It may seem silly to suggest this, but there may be a newer file that replaced this one.

2. Is an exception being squelched by your code? (Anyone who would name columns like that is definitely capable of squelching those pesky exceptions)

3. 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.

Answer 2

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.

Answer 3

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.

Answer 4

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;

Answer 5

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