Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5702

Incorrect results are returned with NULLIF()

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 5.5.35, 10.0.8
    • 5.5.38, 10.0.11
    • None
    • 10.0.8-MariaDB-1~precise mariadb.org binary distribution (64Bit Ubuntu 12.04)

      5.5.35-MariaDB-log MariaDB Server (64Bit Centos 6.5)


    Description

      Hi

      the attached SQL script shows the problem.

      Results in "bad" column, of the last SQL statement, differ from "good" column results, while "bad_len" and "good_len" are the same.

      Cast is used for "good" results. No cast for "bad".

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          s4uliu5,
          Thanks for the report.

          This is an upstream (MySQL) bug, reproducible on MySQL 5.5+. In such cases, we also report the bug in bugs.mysql.com, to inform upstream about the issue. Are you willing to do it, or should we do it on your behalf?

          bar,

          Even though it's an upstream bug, could you please take a quick look, if it's related to the temporal types area that you've been working on, maybe it will be easier to fix it than to wait for the upstream fix.

          Here is even smaller test case (although the original one is also quite compact):

          SET NAMES utf8;
          		 
           
          		 
          DROP TABLE IF EXISTS t1;
          		 
          CREATE TABLE t1 (d DATE) ENGINE=MyISAM;
          		 
           
          		 
          INSERT INTO t1 VALUES ('1999-11-11'),('2014-02-04');
          		 
           
          		 
          SELECT DISTINCT
          		 
            d, 
            cast(d as char), 
            nullif(d,"2000-01-01") bad, 
            nullif(cast(d as char),"2000-01-01") good
          		 
          FROM t1;
          		 
           
          		 
          DROP TABLE t1;

          Result:

          +------------+-----------------+------+------------+
          		 
          | d          | cast(d as char) | bad  | good       |
          		 
          +------------+-----------------+------+------------+
          		 
          | 1999-11-11 | 1999-11-11      | 199  | 1999-11-11 |
          		 
          | 2014-02-04 | 2014-02-04      | 201  | 2014-02-04 |
          		 
          +------------+-----------------+------+------------+

          elenst Elena Stepanova added a comment - s4uliu5 , Thanks for the report. This is an upstream (MySQL) bug, reproducible on MySQL 5.5+. In such cases, we also report the bug in bugs.mysql.com, to inform upstream about the issue. Are you willing to do it, or should we do it on your behalf? bar , Even though it's an upstream bug, could you please take a quick look, if it's related to the temporal types area that you've been working on, maybe it will be easier to fix it than to wait for the upstream fix. Here is even smaller test case (although the original one is also quite compact): SET NAMES utf8;   DROP TABLE IF EXISTS t1; CREATE TABLE t1 (d DATE ) ENGINE=MyISAM;   INSERT INTO t1 VALUES ( '1999-11-11' ),( '2014-02-04' );   SELECT DISTINCT d, cast (d as char ), nullif (d, "2000-01-01" ) bad, nullif ( cast (d as char ), "2000-01-01" ) good FROM t1;   DROP TABLE t1; Result: +------------+-----------------+------+------------+ | d | cast(d as char) | bad | good | +------------+-----------------+------+------------+ | 1999-11-11 | 1999-11-11 | 199 | 1999-11-11 | | 2014-02-04 | 2014-02-04 | 201 | 2014-02-04 | +------------+-----------------+------+------------+
          s4uliu5 Saulius Gurklys added a comment -

          Elena Stepanova,

          Just submitted bug report - http://bugs.mysql.com/71777

          Thanks.

          s4uliu5 Saulius Gurklys added a comment - Elena Stepanova, Just submitted bug report - http://bugs.mysql.com/71777 Thanks.
          bar Alexander Barkov added a comment -

          Pushed into 5.5.

          bar Alexander Barkov added a comment - Pushed into 5.5.
          s4uliu5 Saulius Gurklys added a comment -

          Hello,

          just tested on 10.0.13, the issue is gone.

          Not tested on latest 5.5 as I do not have one.

          Regards,
          Saulius

          s4uliu5 Saulius Gurklys added a comment - Hello, just tested on 10.0.13, the issue is gone. Not tested on latest 5.5 as I do not have one. Regards, Saulius
          s4uliu5 Saulius Gurklys added a comment -

          Just got a chance to test on 5.5.39, the issue is gone too.

          I think this issue could be closed.

          Regards,
          Saulius

          s4uliu5 Saulius Gurklys added a comment - Just got a chance to test on 5.5.39, the issue is gone too. I think this issue could be closed. Regards, Saulius

          People

            bar Alexander Barkov
            s4uliu5 Saulius Gurklys
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.