A nhibernate bug.

I. BACKGROUND

We are now doing projects that use NHibernate to implement data access layers.

When visiting data, some database tables are deterministic: There are clear table names, field names. This is how you can: establish a database table-to-class mapping. Use HQL to read and write databases.

However, there are data access questions about which database tables are not deterministic and which fields of the database tables are identified during the execution phase.

Database tables and fields are not deterministic, and naturally there is no way to suggest o-r mappings, just to construct SQL statements.

Now that we have used nhibernate, we still use nhibernate when we use SQL to access the database. The main point is to use the session it manages, as well as support for paging queries: the ability to specify the start line. There is also a need for the total number of rows.

Because of greed, this is cheap. There's been a problem.

Second, error

Use SQL instead of HQL to access the database. And with access to the range (starting line or total number of rows), there are sometimes strange problems: some fields, in the database clearly have values, but is not read out.

Like what. There is a database table, the definition of the table is roughly: MyTable (Id, Name, Frompoint).

Now you need to query the top 10 records, using the following SQL statement:

Select Id, Name, frompoint from MyTable

After creating the SQL query, set the query scope:

... var query = session. Createsqlquery (SQL); query. Setfirstresult (0); query. Setmaxresults (10);..

For running results, it is expected that each record has three fields. But in fact, just return the value of the first two fields, the third field, the value of Frompoint. No return.

To view the NHibernate log, the resulting SQL is:

Select ID, name from (select ID, name, frompoint from MyTable) where rownum<=10

This is really confusing.

Third, Reason

Search on the Internet. The reason could not be found.

Just a good sacrifice of the last killer: tracking source.

The conclusion is that NHibernate has a problem parsing SQL statements. Results in filtering out the columns that should not be filtered out.

In detail, there are examples of the following code in the method Extractcolumnoraliasnames of class NHibernate.Dialect.Dialect:

if (token. Startswithcaseinsensitive ("select")) continue;if (token. Startswithcaseinsensitive ("distinct")) continue;if (token. Startswithcaseinsensitive (",")) Continue;if (token. Startswithcaseinsensitive ("from"));

The intent of this code. is to not use SQL reserved words such as SELECT, distinct, and so on as columns. And once you encounter the from reserved word. That means the column is over.

The problem is that it uses startswith, not the whole word inference. The fields that begin with these keyword are thus accidentally injured. And once there are fields starting with from, the subsequent fields are filtered out.

Iv. Ways of

The problem method extractcolumnoraliasnames is static internal. No change of opportunity.

We are only good detour: we add rownum filter conditions by ourselves.

I was going to use nhibernate to implement cross-database, and I've been telling my little friends to avoid using database management system-specific SQL syntax such as Oracle, SQL Server, and so on.

Many program apes will instinctively think of a solution: since it is open source, and the cause of the error has been found, take over to change it. Compile a new version number with OK. I do not agree with this, on the one hand. This will take away from the development of the NHibernate mainline version number. On the one hand, the reference to the component is also inconvenient: we are using spring.net to manage NHibernate. Build a version number of its own. To set up a heap of meta-information. Just think about the big head.

V. Scope

As I now know, the scenario for this bug is as follows:

1. Use native SQL to access the database;
2. Set the Firstresult, maxresults and other query results range;
3. The earliest introduced version number is unknown, the latest version number. Inferred from the source. This problem persists.
4. We are using an Oracle database and other database management systems are unknown.

??

A nhibernate bug.