Undoing a CTE (Common Table Expression)

Question

Another developer wrote a query against this for a MariaDB (MySQL Version 10.x) and not the MySQL database is should have been written for (MySQL Version 5.6). They are no longer available to have them rewrite it for MySQL 5.6.

Can someone assist with reverse engineering this thing?

WITH temptable (column1, column2, column3) 
     AS (SELECT t3.column1, 
                t3.column2, 
                CASE 
                  WHEN t3.column3 = 1 
                       AND t2.column3 = 1 THEN 2 
                  ELSE COALESCE(t2.column3, 0) 
                END AS column3 
         FROM   table1 t1 
                JOIN table2 t2 
                  ON t1.column5 = t2.column5 
                     AND t1.column6 = t2.column6 
                JOIN table3 t3 
                  ON t3.column1 = t2.column1 
         WHERE  t1.column4 = :var1 
                AND t1.column6 = :var2 
                AND t3.column7 = 0)
SELECT column2, 
       column3 
FROM   temptable 
UNION 
SELECT t3.column2, 
       t3.column3 
FROM   table3 t3 
WHERE  t3.column7 = -1 
UNION 
SELECT t3.column2, 
       0 AS column3 
FROM   table3 t3 
       LEFT JOIN temptable temp 
              ON temp.column2 = t3.column2 
WHERE  temp.action IS NULL 
       AND t3.column7 = 0;

The tables and columns have been changed to protect the innocent.

Just replace any occurrence of `temptable` in the lower part of your query with the cte code definition (so copy and paste your code twice). See e.g. [How do you use the “WITH” clause in MySQL?](https://stackoverflow.com/a/1382578/6248528) — Solarflare, Jun 19 '18 at 22:37
There's no column named `action` returned by the CTE; but the outer query is referencing a column named `action`... i suspect that got missed in the renaming. The question is, is there a guarantee that the `action` column value from the CTE is guaranteed to be non-NULL. Is that third query (after the second UNION) supposed to be an anti-join? There's also no table or table alias `p`... we expect that's supposed to be a reference to `t3`? — spencer7593, Jun 19 '18 at 22:40
Maybe it is time to upgrade to MariaDB so you don't run into the problem in future? — Jonathan Leffler, Jun 19 '18 at 23:00
@Solarflare I actually ran across that SO question but stopped reading after the first comment about "dumbing down the query" b/c I thought the thread was going into the realm of a debate and no actually answering the OP's question. I went back and read the thread and there is some really good information in there. — HPWD, Jun 19 '18 at 23:44

spencer7593 · Accepted Answer · 2018-06-20T14:21:26.247

The "easy button" fix is to take the definition of the CTE and use it as an inline view, in place of references to temptable in the outer query. (This isn't necessarily the best fix, or the best way to write the query.)

Chop out the beginning of the query, this part,

WITH temptable 
( column1
, column2
, column3
) 
AS
( SELECT t3.column1
       , t3.column2
       , CASE
           WHEN t3.column3 = 1 AND t2.column3 = 1 THEN 2
           ELSE COALESCE(t2.column3, 0)
         END AS column3 
    FROM table1 t1
    JOIN table2 t2
      ON t1.column5   = t2.column5
     AND t1.column6   = t2.column6
    JOIN table3 t3
      ON t3.column1   = t2.column1
   WHERE t1.column4   = :var1
     AND t1.column6   = :var2 
     AND t3.column7   = 0
)

leaving just this:

SELECT a.column2
     , a.column3 
  FROM temptable a

 UNION 

SELECT b.column2
     , b.column3 
  FROM table3 b
 WHERE b.column7 = -1

 UNION 

SELECT p.column2
     , 0 AS column3
  FROM table3 p
  LEFT
  JOIN temptable q
    ON q.column2 = p.column2
 WHERE q.action IS NULL
   AND p.column7 = 0

(As noted in a comment on the question, the reference to action is invalid, because there's no column named action from temptable.)

Then replace the references to the CTE temptable with the inline view definition.

In the query, this would be aliaas a and q.

Like this:

SELECT a.column2
     , a.column3 
  FROM -- temptable
       ( 
         SELECT t3.column1
              , t3.column2
              , CASE
                  WHEN t3.column3 = 1 AND t2.column3 = 1 THEN 2
                  ELSE COALESCE(t2.column3, 0)
                END AS column3 
           FROM table1 t1
           JOIN table2 t2
             ON t1.column5   = t2.column5
            AND t1.column6   = t2.column6
           JOIN table3 t3
             ON t3.column1   = t2.column1
          WHERE t1.column4   = :var1
            AND t1.column6   = :var2 
            AND t3.column7   = 0
       ) a

 UNION

SELECT b.column2
     , b.column3 
  FROM table3 b
 WHERE b.column7 = -1

 UNION 

SELECT p.column2
     , 0 AS column3
  FROM table3 p
  LEFT
  JOIN -- temptable 
       (
         SELECT t3.column1
              , t3.column2
              , CASE
                  WHEN t3.column3 = 1 AND t2.column3 = 1 THEN 2
                  ELSE COALESCE(t2.column3, 0)
                END AS column3 
           FROM table1 t1
           JOIN table2 t2
             ON t1.column5   = t2.column5
            AND t1.column6   = t2.column6
           JOIN table3 t3
             ON t3.column1   = t2.column1
          WHERE t1.column4   = :var1
            AND t1.column6   = :var2 
            AND t3.column7   = 0
       ) q
    ON q.column2 = p.column2
 WHERE q.action IS NULL
   AND p.column7 = 0

EDIT

Oh, also... the references to :var1 and :var2 placeholders in the second occurrence of the inline view definition will probably need to be changed to be unique ... :var1b and :var2b (at least, this is the case with named placeholders using PDO, they have to be unique)

Copies of the values provided for for :var1 and :var2 will need to be provided for the new bind placeholders.

FOLLOWUP

Q: This query is ... hit a lot. You mention an "easy fix" but at what expense?

A: In the "easy button" fix, the two inline views a and q (replacement for references to the CTE) are being materialized separately. The inline view query is executed two times, and results are materialized into two separate derived tables. (The EXPLAIN output will show two separate derived tables, a and q).

The original query has been edited, yes, it was missed on the renaming. This query is performing some permissions checks so it is hit a lot. You mention an "easy fix" but at what expense? — HPWD, Jun 19 '18 at 23:28
Thanks for the `-- EDIT` comment as well regarding the unique placeholders. That is a very good point. — HPWD, Jun 19 '18 at 23:29
In the "easy button" fix, the two inline views `a` and `q`, replacements for the references to the CTE) are being materialized separately. The inline view query is being execute two times, and results are materialized into two separate derived tables. (The EXPLAIN output will show two separate derived tables, `a` and `q`). — spencer7593, Jun 20 '18 at 14:21
got it - thanks for clearing that up for me. Assuming the content doesn't change often, then I assume it'd be safe to cache this query. — HPWD, Jun 20 '18 at 18:19
That's a good question, I'm going to assume you're asking about the MySQL query cache. I don't have a lot of experience with that. But I do know that some queries are not cacheable, restrictions documented in the MySQL Reference Manual. Our configuration is `query_cache_type=2`, which means that queries that don't include the `SQL_CACHE` directive don't get cached. We enable caching of specific queries by starting them `SELECT SQL_CACHE`. If you are talking about an application level cache, then by all means. (We just want to consider invalidating cache entries. Use a time limit, or ... — spencer7593, Jul 03 '18 at 21:00

Undoing a CTE (Common Table Expression)

1 Answers1