why coerce the column instead of a constant?

  • Filter
  • Time
  • Show
Clear All
new posts

  • why coerce the column instead of a constant?

    So if I have a table

    CREATE TABLE examples ( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, external_id CHAR(12) NOT NULL, data INT(11), INDEX (external_id)) Engine=InnoDB;

    and I query it like so:

    SELECT * FROM examples WHERE external_id = 1

    I can't use the index, because MySQL thinks it must coerce the external_id column to an INT in order to evaluate the where clause. Is there any good reason why it just doesn't coerce the constant into a CHAR(12)?

    Just curious.

  • #2
    It cannot simply use the index, because external_id = 'asdf1' would also match your where clause. Either convert external_id to an integer field, or use external_id = '1'.


    • #3
      gmouse is right. Strings are not compared the way you might think.

      '1 ' == '1' == '1e0' == '1.0000' == '1 day I wrote a forum post'


      • #4
        Actually my comparison was wrong because it only looks at prefixes. And I believe MySQL could use an index, it should just make sure that the first character is a 1, and the second character is non-numeric.


        • #5
          If that's what you believe, then keep on believing )


          • #6
            I appreciate the answers, but I think you guys are misreading my question. I understand the behavior, I'm asking about the reasoning behind it or some insight into the implementation. This is purely a curiosity on my part, I understand that avoiding the situation I described is the right way to go.

            My assumption is that when it sees the restriction external_id = 1, it understands that it must compare two different data types, a CHAR and an INT. These cannot be compared to directly, so I assume one of two things can happen: external_id is coerced into an integer and compared to 1, or 1 can be coerced to a string and compared to external_id.

            To put it in SQL, why does this statement:

            SELECT 1 = '1asdf';

            roughly equal this statement:

            SELECT 1 = CAST('1asf' AS SIGNED);

            instead of this:

            SELECT CAST(1 AS CHAR(12)) = '1asdf';

            I'm sure this decision is completely arbitrary, but if it's not, I'd love to hear the rationale behind it.


            • #7
              xaprb wrote on Fri, 05 February 2010 03:11

              If that's what you believe, then keep on believing )

              Because I am right? )


              • #8
                gmouse, sorry I thought the OP was "believing" things ) I read it as "it should theoretically be possible so I insist that it is really possible".

                vtatto, there is a page in the MySQL manual that explains this behavior and why it happens, but I can't remember where that is. See if you can find it by searching.


                • #9
                  the page is http://dev.mysql.com/doc/refman/5.0/...onversion.html