SQLAlchemy 0.6.1 should use MINUS instead of EXCEPT with Oracle

[Former user]

Oracle does not support EXCEPT, but supports the functionally identically MINUS.

Currently (0.6.1_beta1), SQLAlchemy provides an except_() expression that uses EXCEPT even when connected to an Oracle (10.2.0.4) database. This results in non-working SQL code when except_() is used in SQLAlchemy code when speaking to Oracle.

Recommendation:

Have except_() expression use MINUS instead of EXCEPT when speaking to an Oracle database. In addition, add minus() expression/generative method that is functionally identical to except_(). This will allow except_() or minus() to work interchangeably on Oracle or any other database. The user will not be forced to work around this incompatibility by modifying the SQLAlchemy source code or bypassing SQLAlchemy's auto-SQL generation and manually writing a query.

Tested:

        def getFreeIP(self):
                myips = IP.query.filter_by(pool=self)
                allocated = session.query(IP).select_from(join(IP, HostIP)).filter(IP.pool==self)
                myfree=myips.except_(allocated)
                return myfree.first()

This function fails under SQLAlchemy 0.6.1_beta1 and Elixir 0.7.1 due to EXCEPT being passed to Oracle.

If SQLAlchemy's sql/expression.py is modified around line 573 so that def except_(): uses "MINUS" rather than "EXCEPT", Oracle happily handles the above query.

Change:

    #return _compound_select('EXCEPT', *selects, **kwargs)
    return _compound_select('MINUS', *selects, **kwargs)

This code would need to be modified in order to selectively use MINUS when speaking to Oracle. In addition, I'd suggest throwing an exception if the database in question doesn't support MINUS or EXCEPT. This would be preferable to passing incorrect SQL to the database, which may or may not display a coherent error message (the error message Oracle spit out when it saw the unrecognized "EXCEPT" was "missing right parenthesis").

[Mike Bayer]

Having Oracle dialect render "MINUS" is fine. The "except" string would need to become a token which is parsed by the compiler.py as well as base.py in oracle as appropriate.

As far as a "minus()" method, we already have "except_()" and we don't add multiple ways of doing the same thing if at all avoidable. The names in expression.py are generally based around ANSI SQL standards.

As far as exceptions, we don't reinvent database exceptions. If we took it upon ourselves to reinvent the many thousands of potential SQL errors that might appear "incoherent", we would be creating an unmaintainable monstrosity that nobody would appreciate, particularly when a new version of a database comes out and they can't use it's new "EXCEPT" feature with SQLAlchemy because we haven't yet released a new version with revised rules. We do rethrow DBAPI exceptions within wrappers standard to SQLAlchemy to standardize the procedure of catching them, but we generally don't interpret them beyond that.

[Former user]

I will be quite happy with any fix that will allow Oracle to work without tweaks. Since ANSI SQL has EXCEPT, I guess it makes sense to just stick with except_.

Regarding the exception suggestion, I am not asking you and rethrow your own "easy to understand" version of the DBAPI exception.

I am saying that if there are known cases where invalid code will be sent to a database, it would make sense for SQLAlchemy to throw an IncompatibilityError or similar exception before sending out invalid SQL.

If new versions of the underlying database become available that supported these previously broken features - which would be and extremely rare occurrence - it would be very easy to remove the exception from SQLAlchemy and re-test the code. You would simply commment out the test and the "raise IncompatibiltyError" line. Trivial fix for an end-developer to make.

Honestly, I don't know how many of these unresolvable issues actually exist, but as I continue using SQLAlchemy with Oracle, it would be helpful if SQLAlchemy would inform me when I am asking something from it that is beyond the database's known capabilities.

I think this would be in the best interests of the project as it allows developers to traverse these potentially tricky issues without any time wasted on google.com trying to understand why SQLAlchemy is showing them a traceback under Oracle when the code works fine under SQLite. One of the benefits of SQLAlchemy is the retargetable nature of the toolkit, so anything that may detract, in perception or reality, from this promise should be carefully mitigated, in my opinion.

So this is purely my opinion, of course, but from the perspective of a developer working on a software product for a client, I never want to be left with a cryptic traceback when I could have seen an informative one instead. If the developer is in the dark as to the cause of a traceback then it can impact the timely delivery of software, which can negatively impact the developer's experience with the toolkit.

So when I am using a retargetable database toolkit, having easy-to-understand exceptions thrown to alert me when I am asking SQLAlchemy to do things that will fail would be a great benefit.

[Mike Bayer]

Replying to guest:

I think this would be in the best interests of the project as it allows developers to traverse these potentially tricky issues without any time wasted on google.com trying to understand why SQLAlchemy is showing them a traceback under Oracle when the code works fine under SQLite. One of the benefits of SQLAlchemy is the retargetable nature of the toolkit, so anything that may detract, in perception or reality, from this promise should be carefully mitigated, in my opinion.

There's just no practicality to this. Trapping the vast myriad of edge cases that each database can and cannot support is a far more vast task than you may realize (such as. Did you know that MySQL cannot handle a phrase of the form "select * from table1, table2 JOIN table3 ON <onclause>" ? Should we catch that ? There's hundreds of little cases like that on every backend. MySQL, OTOH, can handle a GROUP BY that doesn't mention every non-aggregate column in the SELECT list. So should we then catch all invalid GROUP BY usages on all other backends ? Even though they all raise perfectly fine error messages ? ).

The complexity, performance overhead, and maintenance responsibility is simply not something we can support. There is also little difference between googling for an ORA-XXXX result code and a SQLAlchemy error. SQLAlchemy still cannot raise the exception until the code is run on the target backend, so the goal of writing "platform neutral" code is not achieved here. Most databases have very clear error messages so I am sorry that a particular Oracle error message was hard for you to understand but its out of our scope to reinvent the database's capability system.

What's missing here, and which nobody has ever been able to specify for me is, what is a good criterion for deciding which errors we should circumvent, and which we should not ? We cannot reimplement thousands of SQL error condition checks. Until a very good and easily implementable rule of thumb is devised I am simply not sold on this feature.

[Former user]

OK, the approach I suggested is not feasible.

I think the key thing is that the developer needs to know the core SQLAlchemy features that are "safe" and those that should be avoided depending on what database(s) are being used.

That way, the developer is not forced to discover the features that do and do not work via experimentation, during the middle of a software development project, which works against the productivity benefits of using SQLAlchemy in the first place.

If it's too complex to handle in code, then it would be good to address more thoroughly in the documentation. If sample breakages were documented along with workarounds, that could be very useful. At the end of the day, the job of SQLAlchemy is to use it to connect to a database and successfully perform database operations, so handling it thru docs should be within the project's scope.

[Mike Bayer]

I am all for as big a directory as we can hold of every kind of error message from within SQLAlchemy as well as from database drivers, and full discussion of each one including techniques to work around and such. I've considered having numerical codes within SQLA and DBAPI error messages as well, or some kind of reproducible hashcode that leads right to the wiki (probably using a redirect similar to a bit.ly type of thing). It's a big job and would need a serious effort to get started. But I think having some kind of linkable resource in the exception itself would catch on really quick.

[Former user]

Something like that would be great. Thanks very much for all your replies and for developing SQLAlchemy - it is clear that a lot of hard work has and is going into this project.

[Mike Bayer]

the tokenization of set operators and the usage of MINUS for Oracle is in 811ece13974504685e1e2add7c91b2b88d213322.

[Mike Bayer]

Removing milestone: 0.6.0 (automated comment)