MySQL not equal doesn't work

Question

I have the following MySQL code:

SELECT db.chr FROM jp_lists_kanji db, (SELECT list.chr,
                                     FLOOR(1 + RAND() * x.m_id) 'rand_ind'
                                FROM jp_stats_kanji list,
                                     (SELECT MAX(t.id) - 1 'm_id'
                                        FROM jp_stats_kanji t) x
                               WHERE user_id = '12345678'
                                 AND chr != '〇'
                            ORDER BY rand_ind
                               LIMIT 17) random WHERE db.id = random.chr

How is it possible that the result is 〇? Shouldn't AND chr != '〇' prevent that?

WOW!!! you can draw such picture as well in `MySQL`? didn't knew that. — Rahul, Jun 28 '15 at 23:42
This query is incredibly messy. You should really learn to use `JOIN` syntax as it looks like you are producing a Cartesian product here. — DavidG, Jun 28 '15 at 23:45
@DavidG The query is based on http://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand — Philip Seyfi, Jun 28 '15 at 23:49
@DavidG If you have a better, equally fast version, I'm sure everyone would appreciate an update. That question has been favourited 19 times and pops up on Google for many relevant queries. — Philip Seyfi, Jun 28 '15 at 23:52
I had a suggestion, use `JOIN`! Look at your query, you join `x` with `list` but have no where clause. — DavidG, Jun 28 '15 at 23:53
@DavidG That cross join is intentional, but also unnecessary as the entire block code be used as a subquery in the calculation :) — jpw, Jun 29 '15 at 00:05
Note there is a special character inside '' after chr != which one wouldn't see just looking at screen (without editing question) — Drew, Jun 29 '15 at 00:06
@jpw: and subquery is entirely unnecessary. `ORDER BY RAND()` would give equivalent result just as efficiently. (The query that OP query is based on, in an answer to this question ... [http://stackoverflow.com/questions/1823306/...](http://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand) only obfuscates the ORDER BY RAND(); it's not any better or faster. (We might use an expression like that if we had a requirement to return some random "integer" value in a given range. But as it used in the query, it's just unnecessary crap. — spencer7593, Jun 29 '15 at 00:12
@spencer7593 I guess so - I didn't analyze the query further than too notice that the cross join could be eliminated. I just assumed there was some good reason to introduce a constant :) — jpw, Jun 29 '15 at 00:15
@jpw: This query should gives equivalent result: [http://pastebin.com/7yHN0BrA](http://pastebin.com/7yHN0BrA). (To get an identical result, for testing, we'd need to initialize RAND() with a seed, so it returns a consistent set of values.) — spencer7593, Jun 29 '15 at 00:18

score 0 · Accepted Answer · answered Jun 28 '15 at 23:53

0

The join condition for the field returned is WHERE db.id = random.chr. Unless db.id always = db.chr, '〇' is a valid result.

answered Jun 28 '15 at 23:53

grahamj42

2,752
3
25
34

Thank you! That was a stupid mistake. Changing to `db.id = random.chr AND db.chr != '〇'` solved the issue. – Philip Seyfi Jun 28 '15 at 23:54

MySQL not equal doesn't work

1 Answers1