Ygthkb

Is the symmetric product of an abelian variety a CY variety?

Boss asked me to sign a resignation paper without a date on it along with my new contract

What is an efficient way to digitize a family photo collection?

Co-worker sabotaging/undoing my work. (Software Development)

What species should be used for storage of human minds?

How do I avoid the "chosen hero" feeling?

Why didn't Tom Riddle take the presence of Fawkes and the Sorting Hat as more of a threat?

Where does documentation like business and software requirement spec docs fit in an agile project?

How do dictionaries source attestation?

What is a good way to explain how a character can produce flames from their body?

How to fly a direct entry holding pattern when approaching from an awkward angle?

Does the US government have any planning in place to ensure there's no shortages of food, fuel, steel and other commodities?

Is it really OK to use "because of"?

How to deal with an underperforming subordinate?

What are some ways of extending a description of a scenery?

Does it take energy to move something in a circle?

Rigorous justification for non-relativistic QM perturbation theory assumptions?

Can me and my friend spend the summer in Canada (6 weeks) at 16 years old without an adult?

How to extract specific values/fields from the text file?

XOR-free sets: Maximum density?

Remove isolated elements of a vector

Is .NET Framework 3.5 still needed with a SQL Server 2017 installation to utilize Database Mail?

RS485 using USART or UART port on STM32

Kernel and image of matrix: What are they? Why do they exist?

10.3.9-MariaDB can not query equal operator with special character

MariaDB not caching any queriesmariadb: setting AUTO_INCREMENT using SELECTWhy is MySQL MariaDB GREATEST(timestamp,NOW()) padding appending results with zeros?Matching string with LIKE derived from sub-querySetting up replication with MariaDB 10.3.4 docker imagesMySQL Workbench Visual Explain not working with MariaDB 10.3.9How to enable NULL values in MySQL timestamp fields?MariaDB special Charactersdatabase is not responding for hours trying to import a 2mb csv with MariaDBMariadb (MySQL) On Windows- problem entering non-ASCII characters in a query

0

I have a MariaDB database that has a filed TagName with value ~!@#$%^&*()_+|}{":?><./';[]=-`

I had set mode SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_BACKSLASH_ESCAPES');

When I try to created DB in MySQL 8.0.13, this query work well and return 1 row

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

But in 10.3.9-MariaDB, the query

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

can not return any result.

Is this an MariaDB issue or I missed some configuration?

mysql mariadb mariadb-10.3

share|improve this question

edited Feb 20 at 4:59

Hien Nguyen

asked Feb 20 at 1:45

Hien NguyenHien Nguyen

1012

New contributor

Hien Nguyen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you create a fiddle like this one so we can switch between mysql-8.0.13 and mariadb-10.3
  
  – danblack
  Feb 20 at 2:08
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I updated value %~!@#$%^&*()_+|}{":?><./'';[]\=-`%
  
  – Hien Nguyen
  Feb 20 at 5:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Test SELECT field, HEX(field), 'literal', HEX('literal'), field = 'literal', HEX(field) = HEX('literal') FROM .. on both servers in desured mode. Look carefully where is a problem.
  
  – Akina
  Feb 20 at 5:37
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tried this query select * from taginfo where HEX(TagName) = HEX('~!@#$%^&*()_+|}{":?><./'';[]\=-`'); but it is still not correct
  
  – Hien Nguyen
  Feb 20 at 5:39
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You must search not what, but why.
  
  – Akina
  Feb 20 at 6:18
  

  
  
  
  

add a comment | 

0

I have a MariaDB database that has a filed TagName with value ~!@#$%^&*()_+|}{":?><./';[]=-`

I had set mode SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_BACKSLASH_ESCAPES');

When I try to created DB in MySQL 8.0.13, this query work well and return 1 row

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

But in 10.3.9-MariaDB, the query

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

can not return any result.

Is this an MariaDB issue or I missed some configuration?

mysql mariadb mariadb-10.3

share|improve this question

edited Feb 20 at 4:59

Hien Nguyen

asked Feb 20 at 1:45

Hien NguyenHien Nguyen

1012

New contributor

Hien Nguyen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you create a fiddle like this one so we can switch between mysql-8.0.13 and mariadb-10.3
  
  – danblack
  Feb 20 at 2:08
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I updated value %~!@#$%^&*()_+|}{":?><./'';[]\=-`%
  
  – Hien Nguyen
  Feb 20 at 5:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Test SELECT field, HEX(field), 'literal', HEX('literal'), field = 'literal', HEX(field) = HEX('literal') FROM .. on both servers in desured mode. Look carefully where is a problem.
  
  – Akina
  Feb 20 at 5:37
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tried this query select * from taginfo where HEX(TagName) = HEX('~!@#$%^&*()_+|}{":?><./'';[]\=-`'); but it is still not correct
  
  – Hien Nguyen
  Feb 20 at 5:39
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You must search not what, but why.
  
  – Akina
  Feb 20 at 6:18
  

  
  
  
  

add a comment | 

I have a MariaDB database that has a filed TagName with value ~!@#$%^&*()_+|}{":?><./';[]=-`

I had set mode SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_BACKSLASH_ESCAPES');

When I try to created DB in MySQL 8.0.13, this query work well and return 1 row

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

But in 10.3.9-MariaDB, the query

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

can not return any result.

Is this an MariaDB issue or I missed some configuration?

mysql mariadb mariadb-10.3

share|improve this question

edited Feb 20 at 4:59

Hien Nguyen

asked Feb 20 at 1:45

Hien NguyenHien Nguyen

1012

New contributor

Hien Nguyen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

share|improve this question

edited Feb 20 at 4:59

Hien Nguyen

asked Feb 20 at 1:45

Hien NguyenHien Nguyen

1012

New contributor

Hien Nguyen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited Feb 20 at 4:59

Hien Nguyen

asked Feb 20 at 1:45

Hien NguyenHien Nguyen

1012

New contributor

Hien Nguyen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked Feb 20 at 1:45

Hien NguyenHien Nguyen

1012

New contributor

Hien Nguyen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked Feb 20 at 1:45

Hien NguyenHien Nguyen

1012

1 Answer
1

active

oldest

votes

0

According to the documentation about sql_mode NO_BACKSLASH_ESCAPES:

Disables using the backslash character as an escape character within
strings, making it equivalent to an ordinary character.

Your string has a double backslash in it. In the NO_BACKSLASH_ESCAPES sql_mode this will be interpreted as a double backslash whereas in the default sql_mode it's interpreted as a single backslash.

Below are some tests with MariaDB 10.3.13 using the following table:

CREATE TABLE taginfo1 (id int unsigned PRIMARY KEY, TagName varchar(200));

Default sql_mode

INSERT INTO taginfo1 (id, TagName) VALUES (1, '~!@#$%^&*()_+|}{":?><./'';[]\=-`');

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

+----+---------------------------------+

| id | TagName                         |

+----+---------------------------------+

|  1 | ~!@#$%^&*()_+|}{":?><./';[]=-` |

+----+---------------------------------+

Note that one backslash has disappeared.

sql_mode with NO_BACKSLASH_ESCAPES

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

Empty set (0.000 sec)

Let's remove one backslash from the WHERE clause:

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]=-`';

+----+---------------------------------+

| id | TagName                         |

+----+---------------------------------+

|  1 | ~!@#$%^&*()_+|}{":?><./';[]=-` |

+----+---------------------------------+

Then let's insert a second row while we're in NO_BACKSLASH_ESCAPES mode:

INSERT INTO taginfo1 (id, TagName) VALUES (2, '~!@#$%^&*()_+|}{":?><./'';[]\=-`');



SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

+----+----------------------------------+

| id | TagName                          |

+----+----------------------------------+

|  2 | ~!@#$%^&*()_+|}{":?><./';[]\=-` |

+----+----------------------------------+

As far as I can tell, this all works as per the documentation. If you really get different results in 10.3.9, then presumably there was a bug that's been fixed.

share

answered 4 mins ago

dbdemondbdemon

3,0622625

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Hien Nguyen is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230198%2f10-3-9-mariadb-can-not-query-equal-operator-with-special-character%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

According to the documentation about sql_mode NO_BACKSLASH_ESCAPES:

Disables using the backslash character as an escape character within
strings, making it equivalent to an ordinary character.

Your string has a double backslash in it. In the NO_BACKSLASH_ESCAPES sql_mode this will be interpreted as a double backslash whereas in the default sql_mode it's interpreted as a single backslash.

Below are some tests with MariaDB 10.3.13 using the following table:

CREATE TABLE taginfo1 (id int unsigned PRIMARY KEY, TagName varchar(200));

Default sql_mode

INSERT INTO taginfo1 (id, TagName) VALUES (1, '~!@#$%^&*()_+|}{":?><./'';[]\=-`');

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

+----+---------------------------------+

| id | TagName                         |

+----+---------------------------------+

|  1 | ~!@#$%^&*()_+|}{":?><./';[]=-` |

+----+---------------------------------+

Note that one backslash has disappeared.

sql_mode with NO_BACKSLASH_ESCAPES

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

Empty set (0.000 sec)

Let's remove one backslash from the WHERE clause:

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]=-`';

+----+---------------------------------+

| id | TagName                         |

+----+---------------------------------+

|  1 | ~!@#$%^&*()_+|}{":?><./';[]=-` |

+----+---------------------------------+

Then let's insert a second row while we're in NO_BACKSLASH_ESCAPES mode:

INSERT INTO taginfo1 (id, TagName) VALUES (2, '~!@#$%^&*()_+|}{":?><./'';[]\=-`');



SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

+----+----------------------------------+

| id | TagName                          |

+----+----------------------------------+

|  2 | ~!@#$%^&*()_+|}{":?><./';[]\=-` |

+----+----------------------------------+

As far as I can tell, this all works as per the documentation. If you really get different results in 10.3.9, then presumably there was a bug that's been fixed.

share

answered 4 mins ago

dbdemondbdemon

3,0622625

add a comment | 

0

According to the documentation about sql_mode NO_BACKSLASH_ESCAPES:

Disables using the backslash character as an escape character within
strings, making it equivalent to an ordinary character.

Your string has a double backslash in it. In the NO_BACKSLASH_ESCAPES sql_mode this will be interpreted as a double backslash whereas in the default sql_mode it's interpreted as a single backslash.

Below are some tests with MariaDB 10.3.13 using the following table:

CREATE TABLE taginfo1 (id int unsigned PRIMARY KEY, TagName varchar(200));

Default sql_mode

INSERT INTO taginfo1 (id, TagName) VALUES (1, '~!@#$%^&*()_+|}{":?><./'';[]\=-`');

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

+----+---------------------------------+

| id | TagName                         |

+----+---------------------------------+

|  1 | ~!@#$%^&*()_+|}{":?><./';[]=-` |

+----+---------------------------------+

Note that one backslash has disappeared.

sql_mode with NO_BACKSLASH_ESCAPES

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

Empty set (0.000 sec)

Let's remove one backslash from the WHERE clause:

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]=-`';

+----+---------------------------------+

| id | TagName                         |

+----+---------------------------------+

|  1 | ~!@#$%^&*()_+|}{":?><./';[]=-` |

+----+---------------------------------+

Then let's insert a second row while we're in NO_BACKSLASH_ESCAPES mode:

INSERT INTO taginfo1 (id, TagName) VALUES (2, '~!@#$%^&*()_+|}{":?><./'';[]\=-`');



SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

+----+----------------------------------+

| id | TagName                          |

+----+----------------------------------+

|  2 | ~!@#$%^&*()_+|}{":?><./';[]\=-` |

+----+----------------------------------+

As far as I can tell, this all works as per the documentation. If you really get different results in 10.3.9, then presumably there was a bug that's been fixed.

share

answered 4 mins ago

dbdemondbdemon

3,0622625

add a comment | 

According to the documentation about sql_mode NO_BACKSLASH_ESCAPES:

Disables using the backslash character as an escape character within
strings, making it equivalent to an ordinary character.

Your string has a double backslash in it. In the NO_BACKSLASH_ESCAPES sql_mode this will be interpreted as a double backslash whereas in the default sql_mode it's interpreted as a single backslash.

Below are some tests with MariaDB 10.3.13 using the following table:

CREATE TABLE taginfo1 (id int unsigned PRIMARY KEY, TagName varchar(200));

Default sql_mode

INSERT INTO taginfo1 (id, TagName) VALUES (1, '~!@#$%^&*()_+|}{":?><./'';[]\=-`');

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

+----+---------------------------------+

| id | TagName                         |

+----+---------------------------------+

|  1 | ~!@#$%^&*()_+|}{":?><./';[]=-` |

+----+---------------------------------+

Note that one backslash has disappeared.

sql_mode with NO_BACKSLASH_ESCAPES

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

Empty set (0.000 sec)

Let's remove one backslash from the WHERE clause:

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]=-`';

+----+---------------------------------+

| id | TagName                         |

+----+---------------------------------+

|  1 | ~!@#$%^&*()_+|}{":?><./';[]=-` |

+----+---------------------------------+

Then let's insert a second row while we're in NO_BACKSLASH_ESCAPES mode:

INSERT INTO taginfo1 (id, TagName) VALUES (2, '~!@#$%^&*()_+|}{":?><./'';[]\=-`');



SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

+----+----------------------------------+

| id | TagName                          |

+----+----------------------------------+

|  2 | ~!@#$%^&*()_+|}{":?><./';[]\=-` |

+----+----------------------------------+

As far as I can tell, this all works as per the documentation. If you really get different results in 10.3.9, then presumably there was a bug that's been fixed.

share

answered 4 mins ago

dbdemondbdemon

3,0622625

CREATE TABLE taginfo1 (id int unsigned PRIMARY KEY, TagName varchar(200));

INSERT INTO taginfo1 (id, TagName) VALUES (1, '~!@#$%^&*()_+|}{":?><./'';[]\=-`');

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

+----+---------------------------------+

| id | TagName                         |

+----+---------------------------------+

|  1 | ~!@#$%^&*()_+|}{":?><./';[]=-` |

+----+---------------------------------+

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

Empty set (0.000 sec)

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]=-`';

+----+---------------------------------+

| id | TagName                         |

+----+---------------------------------+

|  1 | ~!@#$%^&*()_+|}{":?><./';[]=-` |

+----+---------------------------------+

INSERT INTO taginfo1 (id, TagName) VALUES (2, '~!@#$%^&*()_+|}{":?><./'';[]\=-`');



SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';

+----+----------------------------------+

| id | TagName                          |

+----+----------------------------------+

|  2 | ~!@#$%^&*()_+|}{":?><./';[]\=-` |

+----+----------------------------------+

share

answered 4 mins ago

dbdemondbdemon

3,0622625

answered 4 mins ago

dbdemondbdemon

3,0622625

answered 4 mins ago

dbdemondbdemon

3,0622625