DROP DATABASE IF EXISTS jk_cte_test;

CREATE DATABASE jk_cte_test;

USE jk_cte_test;

CREATE TABLE EventTypes
(
  EventTypeId     INT(11) AUTO_INCREMENT PRIMARY KEY,
  Name             VARCHAR(25) NOT NULL
);

INSERT INTO EventTypes (Name) VALUES ('MyType'), ('IrrelevantType');

CREATE TABLE WeightedScores
(
  Id               INT(11) AUTO_INCREMENT PRIMARY KEY,
  AttributeName    VARCHAR(25) NOT NULL,
  WeightedScore    INT(2) NOT NULL
);

INSERT INTO WeightedScores (AttributeName, WeightedScore) VALUES ('MyType', 50);

CREATE TABLE LogEvents
(
  LogEventId         INT(11) AUTO_INCREMENT PRIMARY KEY,
  EventTypeId        INT(11) NOT NULL,
  CONSTRAINT `FK_Events_EventTypeId` FOREIGN KEY (EventTypeId) REFERENCES EventTypes (EventTypeId)
);

INSERT INTO LogEvents (EventTypeId) VALUES (1), (2);

DROP FUNCTION IF EXISTS getScore;

DELIMITER /

CREATE FUNCTION getScore()
  RETURNS INT
BEGIN
  DECLARE val INT;
  
  WITH RelevantEventTypes AS (
    SELECT  et.EventTypeId
           ,ws.WeightedScore
      FROM EventTypes et
           INNER JOIN WeightedScores ws
             ON ot.Name = ws.AttributeName
  ),
  MatchingEvents AS (
    SELECT le.LogEventId
      FROM LogEvents le
           INNER JOIN RelevantEventTypes ret
             ON le.EventTypeId = ret.EventTypeId
  )
  SELECT SUM(ret.WeightedScore)
    INTO val
    FROM MatchingEvents me
         INNER JOIN RelevantEventTypes ret
           ON me.EventTypeId = ret.EventTypeId;
  
  RETURN val;
END
/

DELIMITER ;

-- Fails with error code 1146:
SELECT getScore();

-- Works, returns 50:
WITH RelevantEventTypes AS (
  SELECT  et.EventTypeId
         ,ws.WeightedScore
    FROM EventTypes et
         INNER JOIN WeightedScores ws
           ON ot.Name = ws.AttributeName
),
MatchingEvents AS (
  SELECT le.LogEventId
    FROM LogEvents le
         INNER JOIN RelevantEventTypes ret
           ON le.EventTypeId = ret.EventTypeId
)
SELECT SUM(ret.WeightedScore) AS Score
  FROM MatchingEvents me
       INNER JOIN RelevantEventTypes ret
         ON me.EventTypeId = ret.EventTypeId;
