fix(oracle): boolean syntax error in older versions of oracle

[TJDawson10]

Description of changes

Oracle did not start supporting boolean operations until very recent versions (23c). Versions of Oracle, such as 19c are still not end-of-life until 2027+, given this, adjusted the Oracle Boolean logic into a Python function to allow the use of various Oracle versions vs. just the newest 23c. If you could please consider this or some other change to allow the Oracle connection to work, it would be greatly appreciated, thanks!

[github-actions[bot]]

ACTION NEEDED

Ibis follows the Conventional Commits specification for release automation.

The PR title and description are used as the merge commit message.

Please update your PR title and description to match the specification.

[gforsyth]

Hey @TJDawson10 ! Thanks for putting this PR together!

There are a few hurdles here, but the biggest one is that sqlglot doesn't support Oracle sql without booleans, and we use sqlglot for SQL generation. Your fix here will let you load tables (which is a good thing!) but you'll find that a bunch of other operations will fail because of sqlglot assuming that bools are a thing.

There's a PR I worked on a little while back to try to tackle some of this if you want to take a look: https://github.com/ibis-project/ibis/pull/9379

We would want to have the entire test suite passing on an older version of Oracle. It would be great to have an Oracle 19 image, but I think there's only an Oracle 18 image available? I could be wrong about that.

[cpcloud]

I don't think I did a great job of documenting what exactly prevents oracle 18 from being viable, so I can take a look again and either implement it or more thoroughly catalog the reasons why we can't support it.

[TJDawson10]

@gforsyth @cpcloud that makes sense, I can also take a look into using the 18c image and see if I can get it working.

In the mean time, i'm not sure the direction you may want to take the project in regarding Oracle, but my initial thought was this type of change would allow at least basic table load/.sql() functionality with older versions of Oracle, but I agree the core dynamic SQL creation of Ibis itself would still fail for certain queries.

Depending on the direction you prefer to go, maybe something like the below would be helpful?

a) Minor Support for Older Oracle Versions: Would adding something like this/a warning regarding Ibis functionality not fully being supported be appropriate (until non-boolean handling could be addressed/End of Life is hit for Oracle versions not supporting Boolean) -- This would at least allow a user to use the Oracle connection with standard SQL queries (until they upgrade to 23c) (I think, let me know if you see other potential pitfalls of this)

or

b) Explicitly no support for older Oracle versions: Check the Oracle version (i.e SELECT * FROM v$version), if its less than 23c on connection creation, raise a error stating Ibis only supports 23c and above? Reason being, currently when you go to form the connection it just outputs a vague ORA error, which the user doesn't know the cause without looking into the source code.

Thanks!

[cpcloud]

Closing this out for now. I think we can only support versions with boolean for now unfortunately. It's a lot of maintenance to support multiple versions. Turns out not supporting a boolean type is right up there with introducing the concept of NULL in computing 😅