NH-3852 - Oracle12c dialect issue when criteria has SetMaxResults(0).

[nhibernate-bot]

Sergey Baranov created an issue — 27th February 2016, 11:04:32:

This issue was found while running NHibernate Mappings integration test in our project.
Inside the test there are similar queries for every entity:

var allClassMetadata = sessionFactory.GetAllClassMetadata();
foreach (var entry in allClassMetadata)
{
   session
      .CreateCriteria(entry.Value.GetMappedClass(EntityMode.Poco))
      .SetMaxResults(0)
      .List();
}

In current Oracle dialect it generate the following SQL:

select * from Some_Table where rownum < 0;

which is correct.

However in new Oracle12c dialect it generate the following SQL:

select * from Some_Table FETCH FIRST 0 ROWS;

According to Oracle documentation ( http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljoffsetfetch.html) the parameter must be 1 or higher. In our case the parameter is 0 and the behavior is undetermined.
In practice this leads to correct result (empty resultset) but it takes very much time to execute if table is big enough.
Proposed solution (or workaround) is transforming the SQL to the following:

select * from Some_Table FETCH FIRST 0 ROWS OFFSET 0 ROWS;

Adding "OFFSET 0 ROWS" is not fix for invalid parameter but the execution time is about 0 again.

I'm attaching our version of Oracle 12c dialect here.

---

Ricardo Peres added a comment — 28th February 2016, 15:29:56:

<~barser>: can you submit a merge request with your changes, please? This is so that they can be tested automatically, together with all the other tests, to see if anything breaks.
Thanks.

---

Sergey Baranov added a comment — 28th February 2016, 16:03:22:

<~rjperes>
Submitted pull request for two fixes: this and NH-2170.

I've tried to do all as said in CONTRIBUTING.md but there are some red tests with Oracle XE 11 Database.. These tests are not related to my changes.

---

Alexander Zaytsev added a comment — 28th February 2016, 20:47:24:

Actually failing tests are related to your changes. They expect the different string to be processed. Please fix them and also add some new tests.

---

Sergey Baranov added a comment — 28th February 2016, 21:03:11:

Ok, I'll check what I can do...
The problem with that failed test was in that I have only oracle xe 11g at home.
And this version can't work with Oracle 12c dialect. Hence there were failed tests.
I'll try to check all with appropriate version at work, thanks.

---

lnu added a comment — 8th March 2016, 8:42:26:

You can update the tests in Oracle12cDialectFixture. You don't really need to execute it on a real database.
By the way I tried it on our database (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production) and it works with FETCH FIRST 0 ROWS ONLY and OFFSET 0 ROWS.