3

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.

  • Too broad. Fun but too broad. – Drew Jul 10 '16 at 0:21
  • @drew, I've almost tackled it, I had to make an almost complete departure of approach. – insaner Jul 10 '16 at 0:53
  • These oddly are about the only questions I seem to like – Drew Jul 10 '16 at 0:54
  • @drew, ok, I've solved it and posted my solution below. Let me know what you think. – insaner Jul 10 '16 at 18:41
  • Can't you use COUNT(*) ... GROUP BY weekday (instead of that case)? – Rick James Jul 13 '16 at 0:17
0

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.

  • check out an answer of mine here that references precedence/order of via greatest(), least(), coalesce() tricks and the Obligatory Doc – Drew Jul 10 '16 at 18:51
  • saying that because of your line @idxw_corr := (@tmp_i *7)-7 + ... and its use of @tmp_i set a few lines above. See also No Guarantees so we try to stack the odds in our favor with the tricks. – Drew Jul 10 '16 at 19:00
  • Did you mean to link to this: stackoverflow.com/questions/37940132/… instead? – insaner Jul 10 '16 at 19:18
  • there was a link at the bottom to that one, sure. – Drew Jul 10 '16 at 19:19
  • So, if I understood correctly so far, using greatest(@var:=..) ensures that the assignment occurs before any access to that result? – insaner Jul 10 '16 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.