Order of statement evaluation and variable assignment in MySQL UNIONs

Question

Question: In the UNIONized query below, how can I force @col to be assigned before the dependent derived queries are evaluated? Requirement: it needs to be done in one query.

CREATE TABLE tbl (col CHAR(1) NOT NULL UNIQUE);

INSERT INTO tbl (col) VALUES ('a'), ('b'), ('c'), ('d'), ('e'), ...;

-- Now, for some value of "col", fetch that record and the
-- immediately preceding and following records as ordered by "col"
--
-- If you care to test this, be sure to SET @col := NULL before
-- subsequent executions to simulate a "fresh" MySQL session...
--
SELECT @col := col AS col    -- Fetch particular record given a value of
  FROM tbl                   -- "col".
 WHERE col = 'd'
UNION ALL
SELECT col                   -- Fetch the immediately preceding record,
  FROM (  SELECT col         -- ordered by "col"
            FROM tbl
           WHERE col < @col
        ORDER BY col DESC
          LIMIT 1) preceding
UNION ALL
SELECT col                   -- Fetch the immediately following record,
  FROM (  SELECT col         -- ordered by "col"
            FROM tbl
           WHERE col > @col
        ORDER BY col ASC
          LIMIT 1) following
ORDER BY col ASC;

Background: From the UNIONized query above I expected to obtain three records: a record matching an exact and unique "col" value, and the immediately preceding and following records as ordered by "col".

However, the first run of the query yields just one record, the one matching the user-supplied value for "col". Subsequent runs give me the three I expect. My inference is that @col is not assigned until after the derived queries preceding and following are evaluated — that was not the left-to-right-top-to-bottom evaluation order I'd expected.

(I was attempting to refine an answer to this question, but ran in to this difficulty.)

score 2 · Accepted Answer · answered Dec 20 '11 at 16:18

Don't UNION the assignment of @col with your other queries.

Have one query to assign a value to @col, and a separate query to include that record in your results.

SELECT @col := col AS col    -- Fetch particular record given a value of
  FROM tbl                   -- "col", assigning the identifier to @col.
 WHERE col = 'd'



SELECT col                   -- Now include the above record in the
  FROM tbl                   -- Final result-set
WHERE col = @col

UNION ALL

SELECT col                   -- Fetch the immediately preceding record,
  FROM (  SELECT col         -- ordered by "col"
            FROM tbl
           WHERE col < @col
        ORDER BY col DESC
          LIMIT 1) preceding

UNION ALL

SELECT col                   -- Fetch the immediately following record,
  FROM (  SELECT col         -- ordered by "col"
            FROM tbl
           WHERE col > @col
        ORDER BY col ASC
          LIMIT 1) following
ORDER BY col ASC;

Yes, thanks, a distinct query for assignment was my first approach. However, the requirement here is to "do it all in one query" and my question is, "how?" (Now, whether that requirement holds water is a separate issue... +1 for a working solution, though.) — pilcrow, Dec 20 '11 at 16:38

score 1 · Answer 2 · answered Dec 20 '11 at 16:19

SELECT @col := col AS col    -- Fetch particular record given a value of
  FROM tbl                   -- "col".
 WHERE col = 'd'
UNION ALL
SELECT col                   -- Fetch the immediately preceding record,
  FROM

(@colx:=col), -- Assigne it here

       (  SELECT col         -- ordered by "col"
            FROM tbl
           WHERE col < @colx
        ORDER BY col DESC
          LIMIT 1) preceding
UNION ALL
SELECT col                   -- Fetch the immediately following record,
  FROM

(@colx:=col), -- Assign it here also

       (  SELECT col         -- ordered by "col"
            FROM tbl
           WHERE col > @colx
        ORDER BY col ASC
          LIMIT 1) following
ORDER BY col ASC;

*"You have an error in your SQL syntax"*. That first `@colx` is causing trouble. (Do you need to make that a proper SELECTing derived query with an alias?) — pilcrow, Dec 20 '11 at 16:40
Also, do you mean to "col" or `@col` as an rvalue in that assignment? — pilcrow, Dec 20 '11 at 16:42

Order of statement evaluation and variable assignment in MySQL UNIONs

2 Answers2

Linked