castor

SQLServer documentation not recommanding the use of *= for left outer join

Details

  • Type: Improvement Improvement
  • Status: Closed Closed
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: 0.9.6
  • Fix Version/s: 1.0.1
  • Component/s: JDO queries
  • Labels:
    None
  • Environment:
    SQLServer 2000, Windows 2000
  • Number of attachments :
    1

Description

From the SQL Server Books online help:
"In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the = and = operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax."

As mentioned in the excerpt, future version of SQL Server may not support the *=. Are there any plans in a future release to modify the SQLServerQueryExpression class to use LEFT OUTER JOIN in the FROM clause instead of *= in the WHERE clause?

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Ralf Joachim added a comment -

We are planing a big refactoring of the SQL generation for the 1.1 release. At that point we'll take care on all known aspects of the SQL generation. If you like to see such changes to apear in an earlier release we are open to review your patch .

Having said that we search for someone how will run our CTF test suite of CVS head against MSSQL server after big changes have been commited.

Show
Ralf Joachim added a comment - We are planing a big refactoring of the SQL generation for the 1.1 release. At that point we'll take care on all known aspects of the SQL generation. If you like to see such changes to apear in an earlier release we are open to review your patch . Having said that we search for someone how will run our CTF test suite of CVS head against MSSQL server after big changes have been commited.
Hide
Permalink
Werner Guttmann added a comment -

If you really wanted a patch to be included earlier than for a 1.1 release, a little bit of your time wrt running the CTF test suite even now might cause wonders to happen .. ;.-).

Show
Werner Guttmann added a comment - If you really wanted a patch to be included earlier than for a 1.1 release, a little bit of your time wrt running the CTF test suite even now might cause wonders to happen .. ;.-).
Hide
Permalink
Werner Guttmann added a comment -

Marc, is there any chance that we could get some of your help ? E.g .with running tests against MSSQL ?

Show
Werner Guttmann added a comment - Marc, is there any chance that we could get some of your help ? E.g .with running tests against MSSQL ?
Hide
Permalink
Werner Guttmann added a comment -

Ralf, just looking at SQLQueryExpression.getStandardStatement(), I think it should not be too difficult to provide the requested feature. Apart from two non-standard features (LIMIT clause and HOLDLOCK), one could simply delegate to the getStandardStatement() method to create the SQL statement. Given that this is not the case, one could simply take he body of that method, and modify it accordingly. Which I agree is not the most beautiful solution, but should do the trick until we ship 1.1.

Show
Werner Guttmann added a comment - Ralf, just looking at SQLQueryExpression.getStandardStatement(), I think it should not be too difficult to provide the requested feature. Apart from two non-standard features (LIMIT clause and HOLDLOCK), one could simply delegate to the getStandardStatement() method to create the SQL statement. Given that this is not the case, one could simply take he body of that method, and modify it accordingly. Which I agree is not the most beautiful solution, but should do the trick until we ship 1.1.
Hide
Permalink
Gregory Block added a comment -

As we're an MSSQL shop, we'll take the hit on this one and make sure that it works with 2005; however, note that we won't be doing the 2005 upgrade until it's actually released.

While this may not be optimal (or even perfect) SQL, I don't think it's actually broken in 2005.

Wasn't there going to be a major bit of work done on the SQL query engine at some point, with some work already in progress? Let's just link to that bug and mark it as blocking this one - We'll fix this more forcibly if we find out that it's broken in 2005, but as I said, to the best of my knowledge... it isn't.

Show
Gregory Block added a comment - As we're an MSSQL shop, we'll take the hit on this one and make sure that it works with 2005; however, note that we won't be doing the 2005 upgrade until it's actually released. While this may not be optimal (or even perfect) SQL, I don't think it's actually broken in 2005. Wasn't there going to be a major bit of work done on the SQL query engine at some point, with some work already in progress? Let's just link to that bug and mark it as blocking this one - We'll fix this more forcibly if we find out that it's broken in 2005, but as I said, to the best of my knowledge... it isn't.
Hide
Permalink
Werner Guttmann added a comment -

Yes, there will be major work in this area with 1.1. But I thought that one could quite 'easily' provide value added service by incl. a (hopefully) small patch ...

Show
Werner Guttmann added a comment - Yes, there will be major work in this area with 1.1. But I thought that one could quite 'easily' provide value added service by incl. a (hopefully) small patch ...
Hide
Permalink
Werner Guttmann added a comment -

Marc ? Any comments ?

Show
Werner Guttmann added a comment - Marc ? Any comments ?
Hide
Permalink
Werner Guttmann added a comment -

Initial patch, changing SQLServerFactory to use JDBCQueryExpression rather than SQLServerQueryExpression if a property is set.

Show
Werner Guttmann added a comment - Initial patch, changing SQLServerFactory to use JDBCQueryExpression rather than SQLServerQueryExpression if a property is set.
Hide
Permalink
Werner Guttmann added a comment -

Just to clarify a few things; yes, there's still a major refactoring planned/scheduled for SQL generation .. . But as there's more and more requests showing from the users, I think something should be done. And as the request is mostly about ANSI-SQL-92 compliance, I just thought I'd introduce a property (again .. ) that enabled folks to use the standard JDBCQueryExpression .. as what's provided there should be sufficient. If that's not the case ... well, tough luck. I am not really eager to refactor JDNCQueryStatement to break it apart into more meaningful chunks, and rebuild SQLServerQueryExpression() to partially use this broken-apart code.

Show
Werner Guttmann added a comment - Just to clarify a few things; yes, there's still a major refactoring planned/scheduled for SQL generation .. . But as there's more and more requests showing from the users, I think something should be done. And as the request is mostly about ANSI-SQL-92 compliance, I just thought I'd introduce a property (again .. ) that enabled folks to use the standard JDBCQueryExpression .. as what's provided there should be sufficient. If that's not the case ... well, tough luck. I am not really eager to refactor JDNCQueryStatement to break it apart into more meaningful chunks, and rebuild SQLServerQueryExpression() to partially use this broken-apart code.
Hide
Permalink
Werner Guttmann added a comment -

Committing patch as is. I wonder, though, whether you'd be able to help us a bit with running the Castor JDO testing suite against SQLServer (as we don't have access to this RDBMS) ?

Show
Werner Guttmann added a comment - Committing patch as is. I wonder, though, whether you'd be able to help us a bit with running the Castor JDO testing suite against SQLServer (as we don't have access to this RDBMS) ?

People

Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Codehaus. Try JIRA - bug tracking software for your team.