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

Conversion from TIMESTAMP to VARCHAR SP variables does not work well on fractional digits

Details

    Description

      DROP PROCEDURE IF EXISTS p1;
      		 
      DELIMITER $$
      		 
      CREATE PROCEDURE p1()
      		 
      BEGIN
      		 
        DECLARE ts10 TIMESTAMP(1) DEFAULT NOW();
      		 
        DECLARE ts16 TIMESTAMP(1) DEFAULT NOW(6);
      		 
        DECLARE dt10 DATETIME(1) DEFAULT NOW();
      		 
        DECLARE dt16 DATETIME(1) DEFAULT NOW(6);
      		 
       
      		 
        DECLARE vts10 VARCHAR(32) DEFAULT ts10;
      		 
        DECLARE vts16 VARCHAR(32) DEFAULT ts16;
      		 
        DECLARE vdt10 VARCHAR(32) DEFAULT dt10;
      		 
        DECLARE vdt16 VARCHAR(32) DEFAULT dt16;
      		 
       
      		 
        SELECT vts10, vts16, vdt10, vdt16;
      		 
      END;
      		 
      $$
      		 
      DELIMITER ;
      		 
      CALL p1\G


      vts10: 2018-12-07 16:19:32
      		 
      vts16: 2018-12-07 16:19:32.900000
      		 
      vdt10: 2018-12-07 16:19:32.0
      		 
      vdt16: 2018-12-07 16:19:32.9

      Notice:

      Columns vdt10 and vdt16 returned correct results.

      Columns vts10 and vts16 returned wrong results.

      • vts10 does not have fractional digits. The expected result is '2018-12-07 16:19:32.0', like it vdt10.
      • vts16 has six fractional digits. The expected result is '2018-12-07 16:19:32.9', like it vdt16.

      Attachments

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13995 MAX(timestamp) returns a wrong result near DST change

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            Rank Ranked higher
            bar Alexander Barkov made changes -
            Description {code}
            DROP PROCEDURE IF EXISTS p1;
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE ts10 TIMESTAMP(1) DEFAULT NOW();
              DECLARE ts16 TIMESTAMP(1) DEFAULT NOW(6);
              DECLARE dt10 DATETIME(1) DEFAULT NOW();
              DECLARE dt16 DATETIME(1) DEFAULT NOW(6);

              DECLARE vts10 VARCHAR(32) DEFAULT ts10;
              DECLARE vts16 VARCHAR(32) DEFAULT ts16;
              DECLARE vdt10 VARCHAR(32) DEFAULT dt10;
              DECLARE vdt16 VARCHAR(32) DEFAULT dt16;

              SELECT vts10, vts16, vdt10, vdt16;
            END;
            $$
            DELIMITER ;
            CALL p1\G
            {code}
            {noformat}
            vts10: 2018-12-07 16:19:32
            vts16: 2018-12-07 16:19:32.900000
            vdt10: 2018-12-07 16:19:32.0
            vdt16: 2018-12-07 16:19:32.9
            {noformat}
            Notice:

            Columns {{vdt10}} and {{vdt16}} returned correct results.

            Columns {{vts10}} and {{vts16}} returned wrong results.
            - {{vts10}} does not have fractional digits. The expected result is {{2018-12-07 16:19:32.0}}, like it {{vdt10}}.
            - {{vts16}} has six fractional digits. The expected result is {{2018-12-07 16:19:32.9}}, like it {{vdt16}}.

            		{code:sql}
            DROP PROCEDURE IF EXISTS p1;
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE ts10 TIMESTAMP(1) DEFAULT NOW();
              DECLARE ts16 TIMESTAMP(1) DEFAULT NOW(6);
              DECLARE dt10 DATETIME(1) DEFAULT NOW();
              DECLARE dt16 DATETIME(1) DEFAULT NOW(6);

              DECLARE vts10 VARCHAR(32) DEFAULT ts10;
              DECLARE vts16 VARCHAR(32) DEFAULT ts16;
              DECLARE vdt10 VARCHAR(32) DEFAULT dt10;
              DECLARE vdt16 VARCHAR(32) DEFAULT dt16;

              SELECT vts10, vts16, vdt10, vdt16;
            END;
            $$
            DELIMITER ;
            CALL p1\G
            {code}
            {noformat}
            vts10: 2018-12-07 16:19:32
            vts16: 2018-12-07 16:19:32.900000
            vdt10: 2018-12-07 16:19:32.0
            vdt16: 2018-12-07 16:19:32.9
            {noformat}
            Notice:

            Columns {{vdt10}} and {{vdt16}} returned correct results.

            Columns {{vts10}} and {{vts16}} returned wrong results.
            - {{vts10}} does not have fractional digits. The expected result is {{2018-12-07 16:19:32.0}}, like it {{vdt10}}.
            - {{vts16}} has six fractional digits. The expected result is {{2018-12-07 16:19:32.9}}, like it {{vdt16}}.

            bar Alexander Barkov made changes -
            Description {code:sql}
            DROP PROCEDURE IF EXISTS p1;
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE ts10 TIMESTAMP(1) DEFAULT NOW();
              DECLARE ts16 TIMESTAMP(1) DEFAULT NOW(6);
              DECLARE dt10 DATETIME(1) DEFAULT NOW();
              DECLARE dt16 DATETIME(1) DEFAULT NOW(6);

              DECLARE vts10 VARCHAR(32) DEFAULT ts10;
              DECLARE vts16 VARCHAR(32) DEFAULT ts16;
              DECLARE vdt10 VARCHAR(32) DEFAULT dt10;
              DECLARE vdt16 VARCHAR(32) DEFAULT dt16;

              SELECT vts10, vts16, vdt10, vdt16;
            END;
            $$
            DELIMITER ;
            CALL p1\G
            {code}
            {noformat}
            vts10: 2018-12-07 16:19:32
            vts16: 2018-12-07 16:19:32.900000
            vdt10: 2018-12-07 16:19:32.0
            vdt16: 2018-12-07 16:19:32.9
            {noformat}
            Notice:

            Columns {{vdt10}} and {{vdt16}} returned correct results.

            Columns {{vts10}} and {{vts16}} returned wrong results.
            - {{vts10}} does not have fractional digits. The expected result is {{2018-12-07 16:19:32.0}}, like it {{vdt10}}.
            - {{vts16}} has six fractional digits. The expected result is {{2018-12-07 16:19:32.9}}, like it {{vdt16}}.

            		{code:sql}
            DROP PROCEDURE IF EXISTS p1;
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE ts10 TIMESTAMP(1) DEFAULT NOW();
              DECLARE ts16 TIMESTAMP(1) DEFAULT NOW(6);
              DECLARE dt10 DATETIME(1) DEFAULT NOW();
              DECLARE dt16 DATETIME(1) DEFAULT NOW(6);

              DECLARE vts10 VARCHAR(32) DEFAULT ts10;
              DECLARE vts16 VARCHAR(32) DEFAULT ts16;
              DECLARE vdt10 VARCHAR(32) DEFAULT dt10;
              DECLARE vdt16 VARCHAR(32) DEFAULT dt16;

              SELECT vts10, vts16, vdt10, vdt16;
            END;
            $$
            DELIMITER ;
            CALL p1\G
            {code}
            {noformat}
            vts10: 2018-12-07 16:19:32
            vts16: 2018-12-07 16:19:32.900000
            vdt10: 2018-12-07 16:19:32.0
            vdt16: 2018-12-07 16:19:32.9
            {noformat}
            Notice:

            Columns {{vdt10}} and {{vdt16}} returned correct results.

            Columns {{vts10}} and {{vts16}} returned wrong results.
            - {{vts10}} does not have fractional digits. The expected result is '{{2018-12-07 16:19:32.0}}', like it {{vdt10}}.
            - {{vts16}} has six fractional digits. The expected result is '{{2018-12-07 16:19:32.9}}', like it {{vdt16}}.

            bar Alexander Barkov made changes -
            Description {code:sql}
            DROP PROCEDURE IF EXISTS p1;
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE ts10 TIMESTAMP(1) DEFAULT NOW();
              DECLARE ts16 TIMESTAMP(1) DEFAULT NOW(6);
              DECLARE dt10 DATETIME(1) DEFAULT NOW();
              DECLARE dt16 DATETIME(1) DEFAULT NOW(6);

              DECLARE vts10 VARCHAR(32) DEFAULT ts10;
              DECLARE vts16 VARCHAR(32) DEFAULT ts16;
              DECLARE vdt10 VARCHAR(32) DEFAULT dt10;
              DECLARE vdt16 VARCHAR(32) DEFAULT dt16;

              SELECT vts10, vts16, vdt10, vdt16;
            END;
            $$
            DELIMITER ;
            CALL p1\G
            {code}
            {noformat}
            vts10: 2018-12-07 16:19:32
            vts16: 2018-12-07 16:19:32.900000
            vdt10: 2018-12-07 16:19:32.0
            vdt16: 2018-12-07 16:19:32.9
            {noformat}
            Notice:

            Columns {{vdt10}} and {{vdt16}} returned correct results.

            Columns {{vts10}} and {{vts16}} returned wrong results.
            - {{vts10}} does not have fractional digits. The expected result is '{{2018-12-07 16:19:32.0}}', like it {{vdt10}}.
            - {{vts16}} has six fractional digits. The expected result is '{{2018-12-07 16:19:32.9}}', like it {{vdt16}}.

            		{code:sql}
            DROP PROCEDURE IF EXISTS p1;
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE ts10 TIMESTAMP(1) DEFAULT NOW();
              DECLARE ts16 TIMESTAMP(1) DEFAULT NOW(6);
              DECLARE dt10 DATETIME(1) DEFAULT NOW();
              DECLARE dt16 DATETIME(1) DEFAULT NOW(6);

              DECLARE vts10 VARCHAR(32) DEFAULT ts10;
              DECLARE vts16 VARCHAR(32) DEFAULT ts16;
              DECLARE vdt10 VARCHAR(32) DEFAULT dt10;
              DECLARE vdt16 VARCHAR(32) DEFAULT dt16;

              SELECT vts10, vts16, vdt10, vdt16;
            END;
            $$
            DELIMITER ;
            CALL p1\G
            {code}
            {noformat}
            vts10: 2018-12-07 16:19:32
            vts16: 2018-12-07 16:19:32.900000
            vdt10: 2018-12-07 16:19:32.0
            vdt16: 2018-12-07 16:19:32.9
            {noformat}
            Notice:

            Columns {{vdt10}} and {{vdt16}} returned correct results.

            Columns {{vts10}} and {{vts16}} returned wrong results.
            - {{vts10}} does not have fractional digits. The expected result is '*{{2018-12-07 16:19:32.0}}*', like it {{vdt10}}.
            - {{vts16}} has six fractional digits. The expected result is '*{{2018-12-07 16:19:32.9}}*', like it {{vdt16}}.

            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2018-12-08 15:26:39.0 2018-12-08 15:26:39.66
            bar Alexander Barkov made changes -
            Fix Version/s 10.4.1 [ 23228 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 91101 ] MariaDB v4 [ 155318 ]

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.