Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Fix bool column behavior in Oracle queries #1540

Merged
merged 1 commit into from
Aug 3, 2022

Conversation

maio
Copy link
Contributor

@maio maio commented Jun 22, 2022

Fix how bool values are sent to Oracle DB. Previously BooleanColumnType
didn't do any conversion so real boolean value did reach JDBC and it has
been converted to numeric value of 0 or 1.

This behavior is not correct as bool columns in Exposed are CHAR(1) in Oracle.
We need to pass bool as a string instead of a number otherwise Oracle will not use
indexes when such column is used in WHERE clause.

Example:

create table foo (boolColumn char(1));
create index foo_idx on foo (boolColumn);

With numeric value:

select 1 from foo where boolColumn = 1;    // results in full table scan (ignores foo_idx)

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     3 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| FOO  |     1 |     3 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

"   1 - filter(TO_NUMBER(""BOOLCOLUMN"")=1)"

With string value:

select 1 from foo where boolColumn = '1';  // correctly uses foo_idx

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| FOO_IDX |     1 |     3 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

"   1 - access(""BOOLCOLUMN""='1')"

Fix how bool values are sent to Oracle DB. Previously BooleanColumnType
didn't do any conversion so real boolean value did reach JDBC and it has
been converted to numeric value of 0 or 1.

This behavior is not correct as bool columns in Exposed are CHAR(1) in Oracle.
We need to pass bool as a string instead of a number otherwise Oracle will not use
indexes when such column is used in WHERE clause.

Example:

create table foo (boolColumn char(1));
create index foo_idx on foo (boolColumn);

select 1 from foo where boolColumn = 1;    // results in full table scan (ignores foo_idx)

    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |     3 |     2   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| FOO  |     1 |     3 |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    "   1 - filter(TO_NUMBER(""BOOLCOLUMN"")=1)"

select 1 from foo where boolColumn = '1';  // correctly uses foo_idx

    ----------------------------------------------------------------------------
    | Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |         |     1 |     3 |     1   (0)| 00:00:01 |
    |*  1 |  INDEX RANGE SCAN| FOO_IDX |     1 |     3 |     1   (0)| 00:00:01 |
    ----------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    "   1 - access(""BOOLCOLUMN""='1')"
@Tapac Tapac merged commit 53bcb6f into JetBrains:master Aug 3, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging this pull request may close these issues.

2 participants