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

Emulate the PL/SQL BOOLEAN type in SQL functions in Oracle 12c #8522

Open
lukaseder opened this issue Apr 13, 2019 · 12 comments

Comments

Projects
None yet
2 participants
@lukaseder
Copy link
Member

commented Apr 13, 2019

With #365, jOOQ supports the PL/SQL BOOLEAN type in standalone stored procedure and function calls by translating 1/0/NULL to TRUE/FALSE/NULL in an anonymous PL/SQL block. The same trick is in fact also possible when using the WITH clause to define a bridge function that bridges between numbers and booleans.

Consider this function:

CREATE OR REPLACE FUNCTION f_bool (p BOOLEAN DEFAULT TRUE) RETURN VARCHAR2 IS
BEGIN
  RETURN CASE 
    WHEN p THEN 'TRUE'
    WHEN NOT p THEN 'FALSE'
    ELSE 'NULL'
  END;
END f_bool;
/

First off, if the function has a default value for the boolean parameter, and the user does not supply a value for that parameter, then we can call it as follows:

SELECT f_bool FROM dual;

If, however, the user wants to pass a value to the parameter, then a working emulation is this:

WITH
  FUNCTION f_bool_ (i NUMBER) RETURN VARCHAR2 IS
  BEGIN
    RETURN f_bool(NOT i = 0);
  END f_bool_;
SELECT f_bool_(1) FROM dual;

jOOQ should generate that synthetic bridge method and translate between NUMBER and BOOLEAN transparently for the user.

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Apr 13, 2019

If the function returns a BOOLEAN, and we're not passing that BOOLEAN to another function (in case of which no translation is needed), then we have to translate the BOOLEAN back to a number in the auxiliary function:

WITH
  FUNCTION f_bool__ (i NUMBER) RETURN NUMBER IS
    r BOOLEAN;
  BEGIN
    r := pls_objects.f_bool(NOT i = 0);
    
    RETURN CASE WHEN r THEN 1 WHEN NOT r THEN 0 ELSE NULL END;
  END f_bool__;
SELECT f_bool__(1) FROM dual;
/
@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Apr 13, 2019

and we're not passing that BOOLEAN to another function (in case of which no translation is needed)

In fact, the SQL engine cannot handle such intermediary BOOLEAN values. We have to wrap all the calls

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Apr 13, 2019

Seems like nesting with function in a correlated subquery isn't possible, which would be the easiest way to implement this:

-- Doesn't work
select (
  with 
    function f return number is
    begin 
      return 1; 
    end f; 
  select f 
  from dual
) from dual;

Without such a local auxiliary function, we will have to emit a CTE to the top level select: #3607, which is a very useful feature per se, but not trivial to implement. In other words, whenever the renderer encounters a PL/SQL boolean function argument or return value, it should render the auxiliary call instead of itself, and notify some hypothetical CTE collector of the presence of the auxiliary function declaration(s).

So, this feature depends on #3607

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Apr 13, 2019

In the presence of other CTE, the function must be prepended:

with   
  function f return number is
  begin 
    return 1; 
  end f; 
  x(a) as (select 1 from dual)
select f(), a
from x;
@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Apr 13, 2019

The function call may also originate from within another CTE:

with   
  function f return number is
  begin 
    return 1; 
  end f; 
  x(a) as (select f() from dual)
select a
from x;
@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Apr 13, 2019

Supporting these in DML statement may not be possible...

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Apr 13, 2019

A first working draft is implemented:
https://twitter.com/lukaseder/status/1117160235846840320

Will fix edge cases and clean up and check in on Monday. This feature will definitely ship for jOOQ 3.12!

@knutwannheden

This comment has been minimized.

Copy link
Contributor

commented Apr 16, 2019

Would an alternative to wrapping the methods be to have dedicated conversion functions and apply those to the parameters and return value? The only advantages I see with that is that the conversion functions could be declared as DETERMINISTIC (probably mostly irrelevant) and the PL/SQL call stacks (e.g. when logging errors) wouldn't include the synthetic wrapper.

Also, can CASE WHEN i = 1 THEN TRUE WHEN i = 0 THEN FALSE ELSE NULL END be condensed to i = 1? Only difference would be that result would also be FALSE for all other numbers than 0 (like 2).

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Apr 16, 2019

@knutwannheden Would an alternative to wrapping the methods [...]

There are a variety of jOOQ emulations that would profit from the generation of synthetic schema level objects, this being one of them. There's a pending feature request to add support for a SystemConnectionProvider, which provides jOOQ with a "system" connection on which we can create such auxiliary types and objects: #8194

Without such an API, it would be unwise to start creating such objects. We could, of course, document the required objects and rely on users setting them up for us, and then configuring jOOQ with their real world names and qualifiers, but that is not very user friendly.

So, right now, a lot of these emulations simply work through generating inline, anonymous PL/SQL either in a BEGIN .. END block, or with this issue also in WITH clauses of SQL statements.

Also, can CASE WHEN i = 1 THEN TRUE WHEN i = 0 THEN FALSE ELSE NULL END be condensed to i = 1? Only difference would be that result would also be FALSE for all other numbers than 0 (like 2).

Yes, indeed. Or rather, when NOT i = 0, as that would make 0 the same as false, all the other numbers true, and null stays null

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Apr 16, 2019

I'll fix all my comments to use NOT i = 0 or equivalent.

lukaseder added a commit that referenced this issue Apr 16, 2019

[#3607] [#8522] Support PL/SQL BOOLEAN types in SQL functions
[#8522] Emulate the PL/SQL BOOLEAN type in SQL functions in Oracle 12c
[#3607] Allow for emitting common table expression declarations to RenderContext
@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Apr 16, 2019

A first implementation draft has been added. The following things need to be integration tested, still:

  • Extract public API from the new function generation (implemented separately: #8526)
  • Combining boolean functions with CTE (the emulation functions must syntactically precede the CTE, see #8522 (comment))
  • Multiple distinct boolean function calls
  • Boolean function calls in derived tables
  • All possible function parameter types (e.g. make sure varchar2 has no length)
  • Chained boolean function calls
  • Conflicts when people use the data type rewriting feature on functions
  • Boolean functions in DML (see https://twitter.com/connor_mc_d/status/1118493000852578304)
  • Generated bind variable indexes
  • Benchmark the current scope stack solution
  • Test if Settings.renderScalarSubqueriesForStoredFunctions works correctly

knutwannheden added a commit that referenced this issue Apr 16, 2019

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Apr 16, 2019

Thanks, @knutwannheden for adding the forgotten files

lukaseder added a commit that referenced this issue Apr 16, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.