5

I've a table like below:

wordId     |   word      
---------------------------------
1          |   axxe
2          |   test word
3          |   another test word

I'm trying to run the query below to find the records beginning with the letters "ax".

SELECT * FROM `words` WHERE word LIKE 'ax%'

MySQL cannot find anything.

But, if I try one of the queries below I can see the correct record (the word "axxe") on the results.

SELECT * FROM `words` WHERE word='axxe'

SELECT * FROM `words` WHERE word LIKE '%ax%'

SELECT * FROM `words` WHERE word LIKE 'a%'

Why can't MySQL find the correct value for the first query? I've tried to run this both on the command line and phpMyAdmin but the result is the same.

This is SHOW CREATE TABLE output:

CREATE TABLE `words` (
 `wordId` int(11) NOT NULL auto_increment,
 `word` text collate utf8_turkish_ci NOT NULL
 PRIMARY KEY  (`word`)
) ENGINE=MyISAM AUTO_INCREMENT=2853 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci
Martin
  • 22,212
  • 11
  • 70
  • 132
amone
  • 3,712
  • 10
  • 36
  • 53
  • 2
    Works for me: http://sqlfiddle.com/#!9/53090/1 – gen_Eric May 09 '17 at 18:35
  • Maybe your table is case sensitive? – gen_Eric May 09 '17 at 18:35
  • What are you asking? This seems to work as expected. – Matt Clark May 09 '17 at 18:35
  • 1
    I would surmise your 'words.word' column has preceding values (non display characters such as tab or space or enter. so does `SELECT * FROM words WHERE trim(word) LIKE 'ax%'` give you wordid 1? if so you have leading spaces if not you have a non display character in word preceding the a in ax. since %ax% works it's a reasonable assumption. – xQbert May 09 '17 at 18:36
  • Is it possible you have a space in front of the axxe entry? – D.L. May 09 '17 at 18:36
  • @Rocket Hazmat how can I learn this? + words that I try completely consist of lowercase chars. – amone May 09 '17 at 18:38
  • 2
    Could you copy-paste the four words from the actual table? If `axxe` happens to be `аххе` (looks the same, but it is in Cyrillic) MySql wouldn't be able to find it. – Sergey Kalinichenko May 09 '17 at 18:38
  • @D.L No, I am sure it doesn't. – amone May 09 '17 at 18:38
  • @xQbert I've thought this first. But also "WHERE word='axxe'" works too and added the values by hand. – amone May 09 '17 at 18:40
  • how are you talking to MySQL, is it CLI or PHP or WorkBench, etc? – Martin May 09 '17 at 18:40
  • @Martin PhpMyAdmin and CLI. trying on both of them. – amone May 09 '17 at 18:40
  • ok wierd... `='axxe'` works but `like 'ax%'` doesn't.... and like `%ax%'` works... yeh.... I just don't see how that possible on the same table in the same database without changes in between. – xQbert May 09 '17 at 18:40
  • @xQbert wierdest thing I've seen. I tried to think it is due to server configuration or something. – amone May 09 '17 at 18:41
  • Ok try this: `SELECT * FROM words WHERE word LIKE CONCAT('ax','%')` – Martin May 09 '17 at 18:41
  • @Martin no it doesn't either. – amone May 09 '17 at 18:42
  • 2
    same session/window? one window using a different connection and uncommitted and the other uncommitted but on the same connection so transaction still open; thus one works other doesn't? I'm graspin because it should work; so this is just the odd stuff I start thinking about. – xQbert May 09 '17 at 18:42
  • @xQbert. No. I encountered with this error while working on my project. After hundreds of failed try, I've started to try it on phpMyAdmin and CLI to understand what is the problem. – amone May 09 '17 at 18:44
  • @xQbert The table has 4-5 thousands rows. Is that number huge for a MySql? Could this be the problem? – amone May 09 '17 at 18:45
  • 1
    no, that is a trivial sized table for MySQL – Martin May 09 '17 at 18:45
  • @Martin then where should I look at? Could wrong configuration or something cause this kind of problem? I've no idea anymore. – amone May 09 '17 at 18:46
  • can you edit your question and show the output of `SHOW CREATE TABLE`, cheers – Martin May 09 '17 at 18:47
  • 1
    Well we know it works elsewhere so it must be a config/environment settings now the question is what the hell could it it be... – xQbert May 09 '17 at 18:48
  • I would redirect the select output to a file and take a look at it using "od -a file". Something not quite right with the actual contents. – D.L. May 09 '17 at 18:48
  • if it was a data issue (extra characters/white space etc) `='axxe'` wouldn't return a record; but it does. – xQbert May 09 '17 at 18:49
  • Did you check if the word "axxe" does not have an extra withe space? Like " axxe"? – developer_hatch May 09 '17 at 18:49
  • @xQbert - Unless his client is somehow encoding the 'axxe'... – D.L. May 09 '17 at 18:50
  • said he tried on command line I don't see how that would happen; but shrug – xQbert May 09 '17 at 18:51
  • 1
    @Martin I've added the output of SHOW CREATE TABLE. – amone May 09 '17 at 18:51
  • Maybe... http://stackoverflow.com/questions/32583922/mysql-table-collation-utf8-turkish-ci-to-utf8-general-ci problem with Collation ? No... same collation on table and data entry... – xQbert May 09 '17 at 18:52
  • @D.L I've thought it too. I've deleted everything on the table and re-entered by hand. The result is the same. I am sure this is not a data error. – amone May 09 '17 at 18:52
  • I think @xQbert might be on the right track with the encoding. – D.L. May 09 '17 at 18:55
  • Is X a different letter in Turkish? (honest question) – Jacob H May 09 '17 at 18:56
  • https://en.wikipedia.org/wiki/Turkish_alphabet Wikipedia doesn't even list X as a letter in Turkish... – Jacob H May 09 '17 at 18:57
  • 3
    maybe `where word like ('ax%' COLLATE utf8_turkish_ci)`? based on :http://stackoverflow.com/questions/25428529/effects-of-updating-a-table-with-rows-from-utf8-turkish-ci-to-utf8-general-ci since table column word is defined in utf8_turkish_ci @RocketHazmat can you update your fiddle to turkish collation and try see if we can reproduce? – xQbert May 09 '17 at 18:57
  • @Jacob H As a turkish speaking person, I know. But the table also contains turkish chars. – amone May 09 '17 at 18:59
  • Yes, but how does SQL know how to compare 'AX' when X does not exist in the collation? Try what @xQbert is suggesting with COLLATE – Jacob H May 09 '17 at 19:03
  • @JacobH You're right. But I tried. It doesn't work either. Also the result is same with the turkish chars too. – amone May 09 '17 at 19:04
  • Maybe try `ascii(substring(word, 2, 1))` to confirm what character 'x' really is? – Jerrad May 09 '17 at 19:07
  • What coolation was the database created using?https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html I'm not an expert in this area so I'll back out for now but this is where I would spend my time; since we know it works in mysql with default settings and the use of utf8_turkish_ci is not the default. – xQbert May 09 '17 at 19:08
  • @xQbert I am trying it now. thank you – amone May 09 '17 at 19:11
  • 1
    Known bug perhaps: https://bugs.mysql.com/bug.php?id=24921 nope that's falcon engine :( – xQbert May 09 '17 at 19:13
  • Also, you might check the database version and see if you see the same behavior in a newer version, – D.L. May 09 '17 at 19:13
  • 1
    Seems others have similar issue: http://stackoverflow.com/questions/22454999/mysql-query-select-like-with-diacritic-turkish-letters or http://stackoverflow.com/questions/11216079/turkish-character-encoding-with-mysql? – xQbert May 09 '17 at 19:15
  • This many comments and only one upvote on the question (sigh) its a great question and people we are all stuggling with it! – xQbert May 09 '17 at 19:18
  • @xQbert yes, I guess most of people don't believe this could happen :) by the way, thanks for links. I'm working on it. – amone May 09 '17 at 19:20
  • I didn't think it could at first then when I realized = 'axxe' did work; I know it was a config/environment thing just needed to figure out what it could be and collation seemed to make the most sense. Other stack posts and articles lead me to believe it was likely the culprit; I just don't know how to fix it. Maybe: http://stackoverflow.com/questions/10444004/mysql-turkish-character – xQbert May 09 '17 at 19:23
  • Any chance you could use regular expressions as a workaround? Just a thought if you need a quick solution...And assuming you don't see similar behavior there. https://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html. SELECT * FROM words WHERE word REGEXP '^ax.*' – D.L. May 09 '17 at 19:27
  • 1
    Maybe this is crazy but... cast both as binary? – Jacob H May 09 '17 at 19:27
  • 1
    Just some more good reading: http://stackoverflow.com/questions/34582519/php-mysql-turkish-character-encoding-and-comparison – xQbert May 09 '17 at 19:28
  • The issue appears to be the MySQL understanding of the character collation, `utf8_turkish_ci`. A solution could be to change the table and column collation type to `utf8mb4_unicode_ci`. Let me know if this works. MySQL [has a well documented](http://stackoverflow.com/a/29929677/3536236) issue with standard `utf8_` collations *not* being full UTF-8 complient. – Martin May 09 '17 at 21:02
  • Or possibly try `utf8mb4_turkish_ci`, if the unicode general collation doesn't have all the characters (apparently it may not) – Martin May 09 '17 at 21:06

1 Answers1

0

TL;DR:

Update your MySQL version.

I created a simulation of your problem here:

Create table:

CREATE TABLE `turky` (
 `id` int(5) NOT NULL AUTO_INCREMENT,
 `word` text COLLATE utf8_turkish_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci

Insert data:

INSERT INTO `turky` (`id`, `word`) VALUES
(1, 'axxe'),
(2, 'test word'),
(3, 'axxxxxe'),
(4, 'another test word');

Run test query (that works):

SELECT * FROM `turky` WHERE `word`='axxe'

Result:

1, 'axxe',

Run test query 2 (that works):

SELECT * FROM `turky` WHERE word LIKE '%ax%'

Result:

1, 'axxe',
3, 'axxxxxe',

Run test query 3 (that works):

SELECT * FROM `turky` WHERE word LIKE 'a%'

Result:

1, 'axxe',
3, 'axxxxxe',
4, 'another test word';

Run test query 4 (that does not work originally):

SELECT * FROM `turky` WHERE `word` LIKE 'ax%'

Result:

1, 'axxe',
3, 'axxxxxe',

This works in MySQL, using PHPMyAdmin.

Versions:
MySQL: 5.6.35
PHPMyAdmin: 4.6.6

The current Turkish alphabet doesn't contain the letter "x" so this fact may [probably not] be causing some obscure interference with the SQL sorting process (as in a lack of language guidance when looking for this character).

Web searching Turkish language bugs in MySQL and while there are half a dozen none of them appear to be for your specific instance.

But the only option here that I can see from my own testing (above), using the table and SQL details you've given us, is that you have an older version of MySQL that includes some turkish language bugs.

If your MySQL version is up to date

(or at least, more recent than mine)

Then the issue seems to be specific to your setup and your data, so I highly doubt we can find and reproduce this issue :-(

More Diagnostic stuff:
As commented by Jacob H, see if this issue still occurs after casting to binary:

SELECT * FROM `turky` WHERE BINARY `word` LIKE CONCAT(BINARY 'ax','%');

Result:

1, 'axxe',
3, 'axxxxxe',

Community
  • 1
  • 1
Martin
  • 22,212
  • 11
  • 70
  • 132