Good for your job, but, in some condition it may not work well as expected

[qiukeren]

Good for your job,but , in some condition it may not work well as expected

for example, it cannot explain such SQL statement:

$query= 'select DISTINCT 1+2   c1, 1+ 2 as
`c2`, sum(c2),sum(c3) as sum_c3,"Status" = CASE
        WHEN quantity > 0 THEN \'in stock\'
        ELSE \'out of stock\'
        END case_statement
, t4.c1, (select c1+c2 from t1 inner_t1 limit 1) as subquery into @a1, @a2, @a3 from t1 the_t1 left outer join t2 using(c1,c2) join t3 as tX ON tX.c1 = the_t1.c1 join t4 t4_x using(x) where c1 = 1 and c2 in (1,2,3, "apple") and exists ( select 1 from some_other_table another_table where x > 1) and ("zebra" = "orange" or 1 = 1) group by 1, 2 having sum(c2) > 1 ORDER BY 2, c1 DESC LIMIT 0, 10 into outfile "/xyz" FOR UPDATE LOCK IN SHARE MODE';

[nijel]

See also #52.

[nijel]

We're getting closer, but still not completely parseable right now:

./bin/lint-query --query "select DISTINCT 1+2 c1, 1+ 2 as \`c2\`, sum(c2),sum(c3) as sum_c3,\"Status\" = CASE    WHEN quantity > 0 THEN 'in stock' ELSE 'out of stock'  END case_statement, t4.c1, (select c1+c2 from t1 inner_t1 limit 1) as subquery into @a1, @a2, @a3 from t1 the_t1 left outer join t2 using(c1,c2) join t3 as tX ON tX.c1 = the_t1.c1 join t4 t4_x using(x) where c1 = 1 and c2 in (1,2,3, \"apple\") and exists ( select 1 from some_other_table another_table where x > 1) and (\"zebra\" = \"orange\" or 1 = 1) group by 1, 2 having sum(c2) > 1 ORDER BY 2, c1 DESC LIMIT 0, 10 into outfile \"/xyz\" FOR UPDATE LOCK IN SHARE MODE"
#1: This type of clause was previously parsed. (near "into" at position 550)
#2: Unrecognized statement type. (near "into" at position 550)

[devenbansod]

I believe that the given query is not entirely correct in the syntax (please correct me if I am wrong).

1. Both options FOR UPDATE and LOCK IN SHARE MODE can not be used together. (and we rightly complain about it)
2. I am not sure if we can use both INTO clauses in one query. (A working example, if it really works, would go a long way in allowing me to know how to implement it)

If both the above issues are indeed true, then the only major problem is that we are currently struggling to get order check right for INTO clauses as it can appear just after select expression as well as at the end (after the LIMIT clause).

[nijel]

@devenbansod You're probably right. According to MySQL docs there is indeed just one INTO clause allowed and same with FOR UPDATE and LOCK IN SHARE MODE.

Note that in time this issue was reported, this query generated way more problems:

#1: Unrecognized keyword. (near "CASE" at position 75)
#2: Unrecognized keyword. (near "WHEN" at position 83)
#3: Unexpected token. (near "quantity" at position 88)
#4: Unexpected token. (near ">" at position 97)
#5: Unexpected token. (near "0" at position 99)
#6: Unrecognized keyword. (near "THEN" at position 101)
#7: Unexpected token. (near "'in stock'" at position 106)
#8: Unrecognized keyword. (near "ELSE" at position 117)
#9: Unexpected token. (near "'out of stock'" at position 122)
#10: Unrecognized keyword. (near "END" at position 138)
#11: Unexpected token. (near "case_statement" at position 142)
#12: Unexpected token. (near "," at position 156)
#13: Unexpected token. (near "t4" at position 158)
#14: Unexpected token. (near "." at position 160)
#15: Unexpected token. (near "c1" at position 161)
#16: Unexpected token. (near "," at position 163)
#17: Unexpected token. (near "(" at position 165)
#18: This type of clause was previously parsed. (near "select" at position 166)
#19: Unrecognized keyword. (near "as" at position 205)
#20: Unexpected token. (near "subquery" at position 208)
#21: Unexpected token. (near "," at position 225)
#22: Unexpected token. (near "@a2" at position 227)
#23: Unexpected token. (near "," at position 230)
#24: Unexpected token. (near "@a3" at position 232)
#25: This type of clause was previously parsed. (near "from" at position 236)
#26: Unrecognized statement type. (near "from" at position 236)