Greetings,
We recently converted an app of ours to the new(ish) Managed driver. All worked well, with very minor changes needed. However, we did notice one of our queries was returning 0 results with the managed driver while the unmanaged driver returned the correct number of results. The issue seemed to boil down to the use of parentheses and the UNION operator. When parentheses were used to surround each subquery that was being unioned, the managed driver returned 0 rows. When the parentheses were removed, the managed driver returned the correct results. A reproducible example is below:
var badQuery = @"
(SELECT 3 FROM DUAL)
UNION
(SELECT 2 FROM DUAL)
";
var goodQuery = @"
SELECT 3 FROM DUAL
UNION
SELECT 2 FROM DUAL
";
using (var conn = new OracleConnection(connString))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = badQuery; // change query here
using (var reader = cmd.ExecuteReader())
{
var table = new DataTable();
table.Load(reader, LoadOption.OverwriteChanges);
table.Rows.Count.Dump(); // returns 2 rows with goodQuery, 0 rows with bad query
}
}
This seems odd to me, given that both queries should be proper SQL. Any ideas why the so-called badQuery doesn't work?