-- drop procedure users;
CREATE TABLE "informix".users(
    id SERIAL
  , name NVARCHAR( 80 )
  , category INT
)

ALTER TABLE users
  ADD CONSTRAINT
    PRIMARY KEY(id)
    CONSTRAINT "informix".pk_users;

CREATE INDEX "informix".idx_users_name
  ON users(name);
  
CREATE INDEX "informix".idx_users_category
  ON users(category);

--drop procedure users_fill;
CREATE PROCEDURE "informix".users_fill(
) 
  
  DEFINE i INT;
  
  FOR i =  1  TO  10000 
     INSERT INTO users(name, category)
       VALUES (i, i /  20 );
  END FOR;
  
   
END PROCEDURE;

EXECUTE PROCEDURE users_fill();
 
-- DROP PROCEDURE user_permissions;

CREATE PROCEDURE "informix".user_permissions(
  p_user_id INT
) RETURNING
  SMALLINT
  ;
  DEFINE i INT;
  
  FOR i =  1  TO  10000 
  
  END FOR;
  RETURN  1 ;
  
END PROCEDURE;

UPDATE STATISTICS HIGH
  FOR TABLE users;

SELECT {+EXPLAIN} *
  FROM users
    WHERE category IN ( 1 ,  2 ,  3 )
      AND name LIKE '%LOH%' 
      AND user_permissions(id) =  1 ; 

SELECT {+EXPLAIN} *
  FROM users
    WHERE category IN ( 1 ,  2 ,  3 )
      AND user_permissions(id) =  1  
      AND name LIKE '%LOH%';  

SELECT {+EXPLAIN} *
  FROM users
    WHERE category IN ( 1 ,  2 ,  3 )
      AND name LIKE '%LOH%';

\nSELECT {+EXPLAIN} *\n  FROM users\n    WHERE category IN ( 1 ,  2 ,  3 )\n      AND name LIKE \'%LOH%\' \n      AND user_permissions(id) IN ( 1 );\n

\nSELECT {+EXPLAIN} *\n  FROM users\n    WHERE category IN ( 1 ,  2 ,  3 )\n      AND name LIKE \'%LOH%\' \n      AND user_permissions(id) -  1  =  0 ;\n

\nSELECT {+EXPLAIN} *\n  FROM users\n    WHERE category IN ( 1 ,  2 ,  3 )\n      AND name LIKE \'%LOH%\' \n      AND user_permissions(id)/ 2  BETWEEN  0 . 49  AND  5 . 1 ;\n

informix.users.name LIKE \'%LOH%\'

\nCREATE PROCEDURE "informix".clear_counter(\n)\n\n  DEFINE GLOBAL G_user_permissions_calls INT DEFAULT  0 ;\n\n  LET G_user_permissions_calls =  0 ;\n\nEND PROCEDURE; \n\nDROP PROCEDURE user_permissions;\n\nCREATE PROCEDURE "informix".user_permissions(\n   p_user_id INT\n , p_max_cnt INT -- ругаться при вызове больше p_max_cnt раз!\n) RETURNING\n  SMALLINT\n  ;\n  DEFINE i INT;\n\n  DEFINE GLOBAL G_user_permissions_calls INT DEFAULT  0 ;\n\n  LET G_user_permissions_calls = G_user_permissions_calls +  1 ;\n  IF G_user_permissions_calls > p_max_cnt THEN\n    RAISE EXCEPTION - 746 ,  0 , \'2 раза - это 2 раза! (с) Не Я\';\n  END IF; \n  \n  FOR i =  1  TO  10000 \n  \n  END FOR;\n  RETURN  1 ;\n  \nEND PROCEDURE;\n

\nINSERT INTO users(name, category)\n       VALUES (\'таки кто-то LOH\',  2 );\n\n

\nEXECUTE PROCEDURE "informix".clear_counter();\n\nSELECT {+EXPLAIN} *\n  FROM users\n    WHERE category IN ( 1 ,  2 ,  3 )\n      AND name LIKE \'%LOH%\' \n      AND user_permissions(id,  1 )/ 2  BETWEEN  0 . 49  AND  5 . 1 ;\n

\nEXECUTE PROCEDURE "informix".clear_counter();\n\nSELECT {+EXPLAIN} *\n  FROM users\n    WHERE category IN ( 1 ,  2 ,  3 )\n      AND name LIKE \'%LOH%\' \n      AND user_permissions(id,  2 )/ 2  BETWEEN  0 . 49  AND  5 . 1 ;\n

  WITH (NOT VARIANT)

WITH (VARIANT)