OK, here's a real bug, just encountered

Consider the following piece of SQL (against a stupid schema that someone else designed, natch):

\n            SELECT /* /dashboard/scorecard/ */\n                TO_CHAR(v.date_dm, 'YYYY-MM-DD') as "Week"\n              , v.visitors as "Unique Visitors"\n              , v.registered_visitors as "Registered Visitors"\n            FROM (\n                SELECT TRUNC(q.date_dm, 'DY') as date_dm\n                  , NVL(SUM(DECODE(q.type, 15, q.value, NULL)), 0)\n                    as visitors\n                  , NVL(SUM(DECODE(q.type, 16, q.value, NULL)), 0)\n                    as registered_visitors\n                FROM vw_ext_quickstats q\n                WHERE q.date_dm >= TRUNC(TO_DATE(:1, 'YYYYMMDD'), 'DY')\n                  AND q.date_dm <= TRUNC(TO_DATE(:2, 'YYYYMMDD'), 'DY')\n                  AND q.subtype = 1\n                GROUP BY TRUNC(q.date_dm, 'DY')\n              ) v\n

When I substitute in '20040404' and '20040504' in for :1 and :2 I get numbers for visitors and registered_visitors. When I execute the exact same SQL passing in parameters for placeholders, the date field is populated and everything else has 0s in it. I know that the parameters are actually being passed in properly because if they were wrong, then I wouldn't have all of the right dates in the output. (Plus I'm running the query through a well-tested piece of code, parameter passing darned well should work.)

I don't need help solving it, I already have a DBA working on the problem. Furthermore if it can't be fixed, then I'm sure I can find a workaround. (Heck, "substitute in actual values" is a decent workaround in this case - in fact it is what I'll probably do.) But I just wanted to give you a reality check demonstrating that I really do encounter bugs from time to time in 10G.

Cheers,
Ben

Post #254,470 by ben_tilly 5/4/06 10:10:54 PM Reply	OK, here's a real bug, just encountered Consider the following piece of SQL (against a stupid schema that someone else designed, natch): \n SELECT /* /dashboard/scorecard/ /\n TO_CHAR(v.date_dm, 'YYYY-MM-DD') as "Week"\n , v.visitors as "Unique Visitors"\n , v.registered_visitors as "Registered Visitors"\n FROM (\n SELECT TRUNC(q.date_dm, 'DY') as date_dm\n , NVL(SUM(DECODE(q.type, 15, q.value, NULL)), 0)\n as visitors\n , NVL(SUM(DECODE(q.type, 16, q.value, NULL)), 0)\n as registered_visitors\n FROM vw_ext_quickstats q\n WHERE q.date_dm >= TRUNC(TO_DATE(:1, 'YYYYMMDD'), 'DY')\n AND q.date_dm <= TRUNC(TO_DATE(:2, 'YYYYMMDD'), 'DY')\n AND q.subtype = 1\n GROUP BY TRUNC(q.date_dm, 'DY')\n ) v\n When I substitute in '20040404' and '20040504' in for :1 and :2 I get numbers for visitors and registered_visitors. When I execute the exact same SQL passing in parameters for placeholders, the date field is populated and everything else has 0s in it. I know that the parameters are actually being passed in properly because if they were wrong, then I wouldn't have all of the right dates in the output. (Plus I'm running the query through a well-tested piece of code, parameter passing darned well should* work.) I don't need help solving it, I already have a DBA working on the problem. Furthermore if it can't be fixed, then I'm sure I can find a workaround. (Heck, "substitute in actual values" is a decent workaround in this case - in fact it is what I'll probably do.) But I just wanted to give you a reality check demonstrating that I really do encounter bugs from time to time in 10G. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #254,493 by admin 5/5/06 8:18:31 AM Reply	I'll have to give that one a whirl. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #254,510 by ben_tilly 5/5/06 11:01:32 AM Reply	It can probably be simplified further... And I don't know how general it is - just that it hit the database I was working against. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)

Welcome to IWETHEY!