search

frioux / DBIx-Class-Helpers

https://metacpan.org/pod/DBIx::Class::Helpers
20 stars 38 forks source link

Adjust exists subquery for Oracle #105

Closed ribasushi closed 3 years ago

ribasushi commented 4 years ago

I believe this is all that's needed to address https://github.com/frioux/DBIx-Class-Helpers/issues/104. @abraxxa please test when time permits.

coveralls commented 4 years ago

Coverage Status

Coverage decreased (-0.05%) to 98.517% when pulling 9a9a92b1d96f97e82706945b816ae074dc607eae on ribasushi:fix_oracle_exists into 1c6913047228f681a6eae345fc0581c138b81126 on frioux:main.

abraxxa commented 4 years ago

I tested the changed code and sadly it didn't change the error message.

ribasushi commented 4 years ago

I tested the changed code and sadly it didn't change the error message.

If the error message is exactly the same => this means that the query that executed is exactly the same => which means the conditional that I added failed => which should not be possible

Either you are not testing against the correct code, or there is a secondary bug of some sort lurking somewhere obscuring the correct sqlt_type. You will have to debug this further locally.

abraxxa commented 4 years ago

The error message doesn't include the full sql query just the start so it is the same. Yes DBIC_TRACE showed that 'FROM DUAL' has been appended.

Oracle has no 'SELECT EXISTS' as far as I see here: https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702

I only found 'exists' here https://docs.oracle.com/database/121/SQLRF/conditions013.htm#SQLRF52167

ribasushi commented 4 years ago

Oracle has no 'SELECT EXISTS'

You are mis-interpreting the query we are issuing ( EXISTS is a standalone operator, not a qualifier for SELECT: think SELECT column1, function2(...), value3 ). You need to try to dig harder and apply more thought.

I can try to setup the CI portion locally, and figure this out, but this is fiddly and I definitely won't be able to look until the weekend or beyond.

abraxxa commented 4 years ago

The in Oracles error messages haven't failed me in the past to point at the correct location of the query and it points at the EXISTS right after SELECT. As shown in the stackoverflow I've linked to `select count() from dual where exists ($subquery)` should be a valid statement. I don't know EXISTS at all and haven't used it before so can't help but testing queries.

ribasushi commented 3 years ago

lack of available expertise, closing until such materializes