JOOQ benchmark doesn't work properly on WARP

[greenlion]

Not sure about the underlying issue right now, but will look at it later.
drop table if exists parent_1;
drop table if exists parent_2;
drop table if exists child_surrogate;
drop table if exists child_natural;

create table parent_1 (id int not null );
create table parent_2 (id int not null );

create table child_surrogate (
  id int, 
  parent_1_id int not null , 
  parent_2_id int not null , 
  payload_1 int, 
  payload_2 int 
) ENGINE = WARP;
;

create table child_natural (
  parent_1_id int not null, 
  parent_2_id int not null, 
  payload_1 int, 
  payload_2 int 
) ENGINE = WARP
;


insert into parent_1 (id)
with recursive t as (
  select 1 v
  union all
  select v + 1 from t where v < 10000
)
select v
from t;

insert into parent_2 (id)
with recursive t as (
  select 1 v
  union all
  select v + 1 from t where v < 100
)
select v
from t;
set @child_id := 0;
insert into child_surrogate (id,parent_1_id, parent_2_id, payload_1, payload_2)
select @child_id := @child_id + 1, p1.id, p2.id, 1, 1
from parent_1 as p1, parent_2 as p2;

insert into child_natural (parent_1_id, parent_2_id, payload_1, payload_2)
select p1.id, p2.id, 1, 1
from parent_1 as p1, parent_2 as p2;

-- Copyright Data Geekery GmbH
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
DROP PROCEDURE IF EXISTS benchmark;
DROP TABLE IF EXISTS print;

CREATE TABLE IF NOT EXISTS print (text VARCHAR(500)) engine;
select 'after_create';
delimiter //

CREATE PROCEDURE benchmark ()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE v_ts BIGINT;
  DECLARE v_repeat INT DEFAULT 10000;
  DECLARE r, c INT;
  DECLARE a INT;

  
  DECLARE cur1 CURSOR FOR 
    SELECT c.payload_1 + c.payload_2 AS a FROM parent_1 AS p1 JOIN child_surrogate AS c ON p1.id = c.parent_1_id WHERE p1.id = 4;
    
  DECLARE cur2 CURSOR FOR 
    SELECT c.payload_1 + c.payload_2 AS a FROM parent_1 AS p1 JOIN child_natural AS c ON p1.id = c.parent_1_id WHERE p1.id = 4;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  SET r = 0;

  REPEAT
    SET v_ts = ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000);
    SET c = 0;
    REPEAT
      OPEN cur1;

      read_loop: LOOP
        FETCH cur1 INTO a;
        IF done THEN
          LEAVE read_loop;
        END IF;
	  END LOOP;
      
      CLOSE cur1;
      SET c = c + 1;
    UNTIL c >= v_repeat END REPEAT;

    INSERT INTO print VALUES (CONCAT('Run ', r, ', Statement 1 : ', ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000) - v_ts));
    
    SET v_ts = ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000);
    SET c = 0;
    REPEAT
      OPEN cur2;
	  
      read_loop: LOOP
        FETCH cur2 INTO a;
        IF done THEN
          LEAVE read_loop;
        END IF;
	  END LOOP;
      
      CLOSE cur2;
      SET c = c + 1;
    UNTIL c >= v_repeat END REPEAT;
    INSERT INTO print VALUES (CONCAT('Run ', r, ', Statement 2 : ', ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000) - v_ts));

    SET r = r + 1;
  UNTIL r >= 5 END REPEAT;
END//

delimiter ;

-- HANGS
CALL benchmark();

SELECT text
FROM print
UNION ALL 
SELECT null
UNION ALL
SELECT 'Copyright Data Geekery GmbH'
UNION ALL
SELECT 'https://www.jooq.org/benchmark';

DROP PROCEDURE IF EXISTS benchmark;

DROP TABLE IF EXISTS print;

[greenlion]

MySQL uses the PK with "const" access and doesn't do a join. WARP has to do a hash join. Will be fixed when PRIMARY KEY is supported again.

[greenlion]

WARP gets 2ms per query. That is acceptable to me right now

[greenlion]

Requires support of "VIRTUAL" constraints.
https://bugs.mysql.com/bug.php?id=105308