User variable in nested subquery not getting picked up - in complex sort of 2 tables with gaps

Question

I have just recently learned about user variables (ie, @myvar) and am trying to create a complex sorting query that takes 2 tables where the second is missing a column which then gets filled in by a subquery. In this subquery I need to generate a number by doing a lookup on the first table and modifying the result.

More specifically, I have a table A (id, date), and table B (id, weekday) and I need to return a date for each row of B where it is the next date occurring on the same weekday where such a date does not occur in A, all starting from a specific minimum date, where entries in table A are all higher (ie, later) than that date. Table B is sorted by staggered weekday (First Monday, First Tuesday... Second Monday, Second Thursday..) and then these get "slotted in" (by assigning "date") where that date in A does not occur. So if the two tables look something like:

 A 
id | date
a-a 2014年11月11日
a-b 2014年11月13日
a-c 2014年11月18日
 B
id | weekday
b-a Tuesday
b-b Tuesday
b-c Wednesday

Then the resulting sort (if start date is 2014年11月10日, a Monday) would be:

 sorted
id | date
a-a 2014年11月11日
b-c 2014年11月12日 (Wednesday)
a-b 2014年11月13日
a-c 2014年11月18日
b-a 2014年11月25日 (Tuesday)
b-b 2014年12月02日 (Tuesday)

I have been working on this query for a while now, and almost had it done, but then I couldn't find a way to pass the "start date" to the subquery that allocates dates to table B. The docs say that

Subqueries in the FROM clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) before evaluating the outer query, so they cannot be evaluated per row of the outer query.

So I reimplemented the subquery to move the variable to a WHERE clause, using JOINs, but this still didn't seem to help, since I couldn't figure out how to return just the single row needed without an outer query to do that. So here is what I have, in the hopes someone can help me figure this out:

 SELECT
 @next_date as next_date,
 @tmp_i := CASE w.weekday + 0
 WHEN 0 THEN @idxw_U:=@idxw_U+1
 WHEN 1 THEN @idxw_M:=@idxw_M+1
 WHEN 2 THEN @idxw_T:=@idxw_T+1
 WHEN 3 THEN @idxw_W:=@idxw_W+1
 WHEN 4 THEN @idxw_R:=@idxw_R+1
 WHEN 5 THEN @idxw_F:=@idxw_F+1
 WHEN 6 THEN @idxw_S:=@idxw_S+1
 END as idxW,
 @idxw_offset := (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxw_offset,
 @idxw_corr := (@tmp_i *7)-7 + (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxW_corr,
 w.weekday as weekday,
 @dayofweek:= w.weekday+0 as dweekday,
 @TMP_date:= DATE_ADD(@next_date, INTERVAL (@idxw_corr) DAY) as tmp_date,
 @clash_offset:= IF((SELECT 1 FROM t_date WHERE date = @TMP_date),
 IFNULL(
 (
 SELECT next_slot FROM (
 SELECT 
 -- DAYOFWEEK(d.date) as weekdaynum,
 -- dayname(d.date) as weekday,
 -- d.date,
 -- MIN(d2.date) as min_d2,
 DATE_ADD(MIN(d2.date), INTERVAL 1 WEEK) as next_slot,
 -- DATEDIFF(d2.date, d.date) as datediff,
 -- DATEDIFF(d2.date, d.date) DIV 7 as weeksdiff,
 -- DATEDIFF(MIN(d3.date), d2.date) DIV 7 as nextdiff
 FROM t_date as d
 JOIN t_date as d2
 JOIN t_date as d3
 WHERE d.date >= @TMP_date
 and DAYOFWEEK(@TMP_date) = DAYOFWEEK(d.date) = DAYOFWEEK(d2.date)
 = DAYOFWEEK(d3.date)
 and d2.date > d.date
 and d3.date > d2.date
 and DATEDIFF(d2.date, d.date) = 7
 GROUP BY d.date
 ORDER BY d.date ASC
 ) as t
 -- these 3 lines below are the ones I need to figure out how to accomplish without an outer query
 -- ie, find the first (lowest date) result that has a nextdiff greater than 1 (a gap of more than 1 week between results)
 WHERE nextdiff >1
 ORDER BY date ASC
 LIMIT 1
 )
 , CONCAT('dow: ',@dayofweek,' ',@TMP_date) )
 -- , 1) -- commented this out while debugging, debug output is line above; in final, if return is null, that means offset is one week
 ,
 0
 ) as clash_offset,
 @NEW_date:= (SELECT DATE_ADD((SELECT @TMP_date), INTERVAL @clash_offset WEEK)) as date,
 w.extra
FROM `t_weekday` as w
JOIN (SELECT
 @idxw := 0, @idxw_U := 0, @idxw_M := 0, @idxw_T := 0, @idxw_W := 0, @idxw_R := 0, @idxw_F := 0, @idxw_S := 0,
 @next_date := DATE_ADD((SELECT t.date FROM `t` as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY) 
) as varrs
ORDER BY date DESC, weekday, id ASC

@TMP_date is the starting (minimum) date. The subquery is the one inside the IFNULL, and tested outside of this, as its own query, it works perfectly, when specifying a literal value (or setting the variable in a JOIN()).

As a test, I did:

 SELECT
 @next_date as next_date,
 @tmp_i := CASE w.weekday + 0
 WHEN 0 THEN @idxw_U:=@idxw_U+1
 WHEN 1 THEN @idxw_M:=@idxw_M+1
 WHEN 2 THEN @idxw_T:=@idxw_T+1
 WHEN 3 THEN @idxw_W:=@idxw_W+1
 WHEN 4 THEN @idxw_R:=@idxw_R+1
 WHEN 5 THEN @idxw_F:=@idxw_F+1
 WHEN 6 THEN @idxw_S:=@idxw_S+1
 END as idxW,
 @idxw_offset := (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxw_offset,
 @idxw_corr := (@tmp_i *7)-7 + (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxW_corr,
 w.weekday as weekday,
 @dayofweek:= w.weekday+0 as dweekday,
 @TMP_date:= DATE_ADD(@next_date, INTERVAL (@idxw_corr) DAY) as tmp_date,
 @clash_offset:= IF((SELECT 1 FROM t_date WHERE date = @TMP_date),
 IFNULL(:= IF((SELECT 1 FROM t_date WHERE show_after = @TMP_show_after),
 IFNULL(
 (
 -- SELECT f FROM ( -- uncommenting this line and the ftmp one results in NULL, with them commented out, @TMP_date is picked up no problem
 SELECT @TMP_date as f
 LIMIT 1
 -- ) as ftmp
 )
 , CONCAT('dow: ',@dayofweek,' ',@TMP_show_after) )
 -- , 1)
 ,
 0
 ) as clash_offset,
 @NEW_date:= (SELECT DATE_ADD((SELECT @TMP_date), INTERVAL @clash_offset WEEK)) as date,
 w.extra
FROM `t_weekday` as w
JOIN (SELECT
 @idxw := 0, @idxw_U := 0, @idxw_M := 0, @idxw_T := 0, @idxw_W := 0, @idxw_R := 0, @idxw_F := 0, @idxw_S := 0,
 @next_date := DATE_ADD((SELECT t.date FROM `t` as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY) 
) as varrs
ORDER BY date DESC, weekday, id ASC

Which confirms what the docs say. However, I don't know how to modify my query (the first one) to get the result I want.

---

Edit: I found an error in the logic, I need to return the highest result (date) in a series of nextdiffs if all return 1, or the first higher-than-1 result. It currently only would return the latter. So that needs fixing too.

• Rick James: Can't you use COUNT(*) ... GROUP BY weekday (instead of that case)?
• insaner: @RickJames, no, because they are staggered, and I need to increment per day but also if there are any entries (could be more than one successive entry) in t_date for that weekday (plus the next weekday, etc) I need to keep track of that as well. At least, I don't at this point see another way of doing it.

Answer 1

Ok, I solved it. I could not solve the actual problem of using the user variable nested in the subquery, as I think there is simply no way to do this (please comment, or answer if there is), but this is how I solved it, in case anyone is curious:

SELECT @idx:=@idx+1 as idx,
 date, weekday, id, added_on
 , from_table
FROM (
 SELECT show_after,
 DAYNAME(date) as weekday,
 id,
 added_on,
 'date' as from_table
 FROM `t_date` as d
 UNION
 SELECT show_after,
 DAYNAME(date) as weekday,
 id,
 added_on,
 'weekday' as from_table
 FROM (
 SELECT
 @next_date as next_date,
 @tmp_i := CASE w.weekday + 0
 WHEN 0 THEN @idxw_U:=@idxw_U+1
 WHEN 1 THEN @idxw_M:=@idxw_M+1
 WHEN 2 THEN @idxw_T:=@idxw_T+1
 WHEN 3 THEN @idxw_W:=@idxw_W+1
 WHEN 4 THEN @idxw_R:=@idxw_R+1
 WHEN 5 THEN @idxw_F:=@idxw_F+1
 WHEN 6 THEN @idxw_S:=@idxw_S+1
 END as idxW,
 @idxw_corr := (@tmp_i *7)-7 + (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxW_corr,
 w.weekday as weekday,
 @TMP_date:= DATE_ADD(@next_date, INTERVAL (@idxw_corr) DAY) as tmp_date,
 @clash_offset:= IF( (SELECT 1 FROM t_date WHERE date = @TMP_date),
 DATEDIFF(
 ( SELECT 
 @next_slot:= DATE_ADD(d.date, INTERVAL 1 WEEK) as next_slot
 FROM t_date as d
 WHERE d.date >= @TMP_date
 and DAYOFWEEK(d.date) = DAYOFWEEK(@TMP_date)
 and NOT EXISTS (SELECT date FROM t_date as dx WHERE date = DATE_ADD(d.date, INTERVAL 1 WEEK))
 LIMIT 1
 ),
 @TMP_date
 ) DIV 7
 , 0
 ) as clash_offset,
 @NEW_show_after:= (SELECT DATE_ADD((SELECT @TMP_date), INTERVAL @clash_offset WEEK)) as date,
 w.id,
 added_on
 FROM `t_weekday` as w
 JOIN (SELECT
 @idxw := 0, @idxw_U := 0, @idxw_M := 0, @idxw_T := 0, @idxw_W := 0, @idxw_R := 0, @idxw_F := 0, @idxw_S := 0,
 @next_date := DATE_ADD((SELECT t.date FROM `t` as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY) 
 ) as varrs
 ORDER BY date, weekday, added_on ASC
 ) as weekday_calc
) as main
JOIN (SELECT 
 @next_date := DATE_ADD((SELECT t.date FROM `t` as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY) as date_slot
 , @idx:=0
 ) as main_vars
ORDER BY date

So, in case you missed it, the clue was to use NOT EXISTS with a subquery in the WHERE clause to detect gaps, which simplified the entire approach.

• Drew: check out an answer of mine here that references precedence/order of via greatest(), least(), coalesce() tricks and the Obligatory Doc
• insaner: Did you mean to link to this: stackoverflow.com/questions/37940132/… instead?
• insaner: So, if I understood correctly so far, using greatest(@var:=..) ensures that the assignment occurs before any access to that result?
• Drew: That is Baron Schwartz's contention in the doc we all seem to reference for safety. A month ago I went out again hunting for a precedence list and it was nebulous once again. We realize we are walking on thin ice until the next decade's mysql releases perhaps. But it seems all we have.
• insaner: Reading through the obligatory doc, really interesting. Thanks. By the way, maybe you have some useful feedback for me here: stackoverflow.com/questions/38092259/…