Search code examples
selectmariadbcomments

Question about the result with the presence of a comment in MySql

Doubt about the result with the presence of a comment in MySql

Recently I've got an issue with a code where I've got a strange result. After some minutes, just by casualty, I could find the result. I made this simple test to check that it was not a problem with my query:

select
NOW()as date_1,
'4: 33.32%' as string_1, 
--- comment,
NOW() as date_2, 
--- comment
'4: 33.32%' as string_2

I've got this result:

enter image description here

By accident, I made this little change for both comments:

select
NOW()as date_1,
'4: 33.32%' as string_1, 
--- comment,
NOW() as date_2, 
--- comment
'4: 33.32%' as string_2, 
-- - comment,
NOW() as date_3, 
-- - comment
'4: 33.32%' as string_3

With this, I have the right answer:

enter image description here

However, I've got doubt about the comments present before the second date and string. Why the third consecutive hyphen affected the result and what was the calculation it forced to performed.

version: 10.3.8-MariaDB

Solution

  • A SQL comment begins with --<space>. When you write ---<space>, the first - is not part of the comment, it's a minus sign before the comment. So it's like you wrote

    select
NOW()as date_1,
'4: 33.32%' as string_1, 
- -- comment,
NOW() as date_2, 
- -- comment
'4: 33.32%' as string_2, 
-- - comment,
NOW() as date_3, 
-- - comment
'4: 33.32%' as string_3

    And when you remove the comments, this is equivalent to

    select
NOW()as date_1,
'4: 33.32%' as string_1, 
- NOW() as date_2, 
- '4: 33.32%' as string_2, 
NOW() as date_3, 
'4: 33.32%' as string_3

    The - operator converts its operand to a number first, so the date in NOW() is converted to the number 20190621200233, and the string '4: 33.32%' is converted to the number 4.000. Then the - operator returns the negative of these numbers, so you see -20190621200233 and -4.000 in the results.