Styjun

As matter approaches a black hole, does it speed up?

What is the name of this hexagon/pentagon polyhedron?

How does this change to the opportunity attack rule impact combat?

Can there be a single technologically advanced nation, in a continent full of non-technologically advanced nations?

Can a nothic's Weird Insight action discover secrets about a player character that the character doesn't know about themselves?

How should I tell my manager I'm not paying for an optional after work event I'm not going to?

Fitch Proof Question

Verb "geeitet" in an old scientific text

Should I mention being denied entry to UK due to a confusion in my Visa and Ticket bookings?

What to use instead of cling film to wrap pastry

Can my company stop me from working overtime?

Can Infinity Stones be retrieved more than once?

What does this colon mean? It is not labeling, it is not ternary operator

I drew a randomly colored grid of points with tikz, how do I force it to remember the first grid from then on?

How was the quadratic formula created?

Which module had more 'comfort' in terms of living space, the Lunar Module or the Command module?

On which topic did Indiana Jones write his doctoral thesis?

What is the difference between 'unconcealed' and 'revealed'?

Can an isometry leave entropy invariant?

What was the design of the Macintosh II's MMU replacement?

Point of the the Dothraki's attack in GoT S8E3?

Why doesn't WotC use established keywords on all new cards?

Can hackers enable the camera after the user disabled it?

Does a card have a keyword if it has the same effect as said keyword?

MySQL issue - querying the database attributes with 'string_value' returns 0 for some attributes and ALL rows for others why?

Can I concatenate multiple MySQL rows into one field?What's the best practice for primary keys in tables?How do I limit the number of rows returned by an Oracle query after ordering?How do I add indices to MySQL tables?What are the options for storing hierarchical data in a relational database?Why does this MySQL Query returns two rows?MySQL Delete all rows from table and reset ID to zeroMySQL Error 1215: Cannot add foreign key constraintReset identity seed after deleting records in SQL ServerMySQL Auto-Increment query is returning two rows

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

1

1

I have a table with the following format.

|id | int_col|
--------------
1 | 0 |
2 | 0 |
--------------

The DDL is defined below:

id - is the primary key - it is also set to auto increment

int_col - is an attribute

I tried the below queries:

Select * from table_name where id='string_value';

Returns 0 rows.

Select * from table_name where int_col = 'string_value';

Returns all rows

I am not sure as to why it has returned all rows. I expected it to return 0 rows for both queries.

mysql sql

share|improve this question

edited Mar 23 at 13:09

n00b

asked Mar 22 at 22:20

n00bn00b

83

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  Can't reproduce this behaviour.
  
  – tadman
  Mar 22 at 22:33
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I have edited my question with a reproducible example.
  
  – n00b
  Mar 23 at 13:07
  
  

  
  
  
  

add a comment | 

1

1

I have a table with the following format.

|id | int_col|
--------------
1 | 0 |
2 | 0 |
--------------

The DDL is defined below:

id - is the primary key - it is also set to auto increment

int_col - is an attribute

I tried the below queries:

Select * from table_name where id='string_value';

Returns 0 rows.

Select * from table_name where int_col = 'string_value';

Returns all rows

I am not sure as to why it has returned all rows. I expected it to return 0 rows for both queries.

mysql sql

share|improve this question

edited Mar 23 at 13:09

n00b

asked Mar 22 at 22:20

n00bn00b

83

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  Can't reproduce this behaviour.
  
  – tadman
  Mar 22 at 22:33
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I have edited my question with a reproducible example.
  
  – n00b
  Mar 23 at 13:07
  
  

  
  
  
  

add a comment | 

I have a table with the following format.

|id | int_col|
--------------
1 | 0 |
2 | 0 |
--------------

The DDL is defined below:

id - is the primary key - it is also set to auto increment

int_col - is an attribute

I tried the below queries:

Select * from table_name where id='string_value';

Returns 0 rows.

Select * from table_name where int_col = 'string_value';

Returns all rows

I am not sure as to why it has returned all rows. I expected it to return 0 rows for both queries.

mysql sql

share|improve this question

edited Mar 23 at 13:09

n00b

asked Mar 22 at 22:20

n00bn00b

83

|id | int_col|
--------------
1 | 0 |
2 | 0 |
--------------

Select * from table_name where id='string_value';

Select * from table_name where int_col = 'string_value';

share|improve this question

edited Mar 23 at 13:09

n00b

asked Mar 22 at 22:20

n00bn00b

83

share|improve this question

edited Mar 23 at 13:09

n00b

asked Mar 22 at 22:20

n00bn00b

83

asked Mar 22 at 22:20

n00bn00b

83

asked Mar 22 at 22:20

n00bn00b

83

1 Answer
1

active

oldest

votes

0

To make it short: do not compare strings and integers in MySQL. This can lead to unpredictable results such as those you are seeing.

As per MySQL conversion rules, comparing a string and an integer actually results in both values being under the hood converted to floating point numbers, and then compared. The documentation warns :

Comparisons that use floating-point numbers [...] are approximate because such numbers are inexact. This might lead to results that appear inconsistent.

Further in the doc, another disclaimers can be found that specifically relates to integer/string comparison :

Furthermore, the conversion from string to floating-point and from integer to floating-point do not necessarily occur the same way. The integer may be converted to floating-point by the CPU, whereas the string is converted digit by digit in an operation that involves floating-point multiplications.

The results shown will vary on different systems, and can be affected by factors such as computer architecture or the compiler version or optimization level.

Finally, here is an example of (possible) conversion discrepancy, also from MySQL documentation:

mysql> SELECT '18015376320243458' = 18015376320243458;
 -> 1
mysql> SELECT '18015376320243459' = 18015376320243459;
 -> 0

share|improve this answer

edited Mar 22 at 23:49

answered Mar 22 at 22:54

GMBGMB

22.1k61128

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Which version are you using? I can't reproduce your example.
  
  – Paul Spiegel
  Mar 22 at 23:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @PaulSpiegel: the example comes straight from the documentation. I just now tested this in a DB Fiddle and I am not able to reproduce it either, on any version of MySQL supported in the fiddle...
  
  – GMB
  Mar 22 at 23:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Documentation says: The results shown will vary on different systems, and can be affected by factors such as computer architecture or the compiler version or optimization level.... Might explain why we are not able to reproduce...
  
  – GMB
  Mar 22 at 23:48
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  "In all other cases, the arguments are compared as floating-point (real) numbers." - This is sooooooooo wrong. And also not (always) true. If I have id as INT PK, where id = '1234567890' will use the index. But that wouldn't be possible if the engine would cast the id to float/double.
  
  – Paul Spiegel
  Mar 23 at 0:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @PaulSpiegel: I do agree that MySQL conversion rules, as explained in the (latest !) docs, are, to say the least, simplistic (and to say the worst : wrong). And I can see also some cases where implicit conversion works properly... Bottom line, I understand that the docs basically discourages it.
  
  – GMB
  Mar 23 at 0:14
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
);



);

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%2fstackoverflow.com%2fquestions%2f55308513%2fmysql-issue-querying-the-database-attributes-with-string-value-returns-0-for%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

To make it short: do not compare strings and integers in MySQL. This can lead to unpredictable results such as those you are seeing.

As per MySQL conversion rules, comparing a string and an integer actually results in both values being under the hood converted to floating point numbers, and then compared. The documentation warns :

Comparisons that use floating-point numbers [...] are approximate because such numbers are inexact. This might lead to results that appear inconsistent.

Further in the doc, another disclaimers can be found that specifically relates to integer/string comparison :

Furthermore, the conversion from string to floating-point and from integer to floating-point do not necessarily occur the same way. The integer may be converted to floating-point by the CPU, whereas the string is converted digit by digit in an operation that involves floating-point multiplications.

The results shown will vary on different systems, and can be affected by factors such as computer architecture or the compiler version or optimization level.

Finally, here is an example of (possible) conversion discrepancy, also from MySQL documentation:

mysql> SELECT '18015376320243458' = 18015376320243458;
 -> 1
mysql> SELECT '18015376320243459' = 18015376320243459;
 -> 0

share|improve this answer

edited Mar 22 at 23:49

answered Mar 22 at 22:54

GMBGMB

22.1k61128

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Which version are you using? I can't reproduce your example.
  
  – Paul Spiegel
  Mar 22 at 23:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @PaulSpiegel: the example comes straight from the documentation. I just now tested this in a DB Fiddle and I am not able to reproduce it either, on any version of MySQL supported in the fiddle...
  
  – GMB
  Mar 22 at 23:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Documentation says: The results shown will vary on different systems, and can be affected by factors such as computer architecture or the compiler version or optimization level.... Might explain why we are not able to reproduce...
  
  – GMB
  Mar 22 at 23:48
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  "In all other cases, the arguments are compared as floating-point (real) numbers." - This is sooooooooo wrong. And also not (always) true. If I have id as INT PK, where id = '1234567890' will use the index. But that wouldn't be possible if the engine would cast the id to float/double.
  
  – Paul Spiegel
  Mar 23 at 0:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @PaulSpiegel: I do agree that MySQL conversion rules, as explained in the (latest !) docs, are, to say the least, simplistic (and to say the worst : wrong). And I can see also some cases where implicit conversion works properly... Bottom line, I understand that the docs basically discourages it.
  
  – GMB
  Mar 23 at 0:14
  
  

  
  
  
  

add a comment | 

0

To make it short: do not compare strings and integers in MySQL. This can lead to unpredictable results such as those you are seeing.

As per MySQL conversion rules, comparing a string and an integer actually results in both values being under the hood converted to floating point numbers, and then compared. The documentation warns :

Comparisons that use floating-point numbers [...] are approximate because such numbers are inexact. This might lead to results that appear inconsistent.

Further in the doc, another disclaimers can be found that specifically relates to integer/string comparison :

Furthermore, the conversion from string to floating-point and from integer to floating-point do not necessarily occur the same way. The integer may be converted to floating-point by the CPU, whereas the string is converted digit by digit in an operation that involves floating-point multiplications.

The results shown will vary on different systems, and can be affected by factors such as computer architecture or the compiler version or optimization level.

Finally, here is an example of (possible) conversion discrepancy, also from MySQL documentation:

mysql> SELECT '18015376320243458' = 18015376320243458;
 -> 1
mysql> SELECT '18015376320243459' = 18015376320243459;
 -> 0

share|improve this answer

edited Mar 22 at 23:49

answered Mar 22 at 22:54

GMBGMB

22.1k61128

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Which version are you using? I can't reproduce your example.
  
  – Paul Spiegel
  Mar 22 at 23:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @PaulSpiegel: the example comes straight from the documentation. I just now tested this in a DB Fiddle and I am not able to reproduce it either, on any version of MySQL supported in the fiddle...
  
  – GMB
  Mar 22 at 23:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Documentation says: The results shown will vary on different systems, and can be affected by factors such as computer architecture or the compiler version or optimization level.... Might explain why we are not able to reproduce...
  
  – GMB
  Mar 22 at 23:48
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  "In all other cases, the arguments are compared as floating-point (real) numbers." - This is sooooooooo wrong. And also not (always) true. If I have id as INT PK, where id = '1234567890' will use the index. But that wouldn't be possible if the engine would cast the id to float/double.
  
  – Paul Spiegel
  Mar 23 at 0:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @PaulSpiegel: I do agree that MySQL conversion rules, as explained in the (latest !) docs, are, to say the least, simplistic (and to say the worst : wrong). And I can see also some cases where implicit conversion works properly... Bottom line, I understand that the docs basically discourages it.
  
  – GMB
  Mar 23 at 0:14
  
  

  
  
  
  

add a comment | 

To make it short: do not compare strings and integers in MySQL. This can lead to unpredictable results such as those you are seeing.

As per MySQL conversion rules, comparing a string and an integer actually results in both values being under the hood converted to floating point numbers, and then compared. The documentation warns :

Comparisons that use floating-point numbers [...] are approximate because such numbers are inexact. This might lead to results that appear inconsistent.

Further in the doc, another disclaimers can be found that specifically relates to integer/string comparison :

Furthermore, the conversion from string to floating-point and from integer to floating-point do not necessarily occur the same way. The integer may be converted to floating-point by the CPU, whereas the string is converted digit by digit in an operation that involves floating-point multiplications.

The results shown will vary on different systems, and can be affected by factors such as computer architecture or the compiler version or optimization level.

Finally, here is an example of (possible) conversion discrepancy, also from MySQL documentation:

mysql> SELECT '18015376320243458' = 18015376320243458;
 -> 1
mysql> SELECT '18015376320243459' = 18015376320243459;
 -> 0

share|improve this answer

edited Mar 22 at 23:49

answered Mar 22 at 22:54

GMBGMB

22.1k61128

mysql> SELECT '18015376320243458' = 18015376320243458;
 -> 1
mysql> SELECT '18015376320243459' = 18015376320243459;
 -> 0

share|improve this answer

edited Mar 22 at 23:49

answered Mar 22 at 22:54

GMBGMB

22.1k61128

answered Mar 22 at 22:54

GMBGMB

22.1k61128

answered Mar 22 at 22:54

GMBGMB

22.1k61128