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

[PATCH] Query parser tansforms MICROSECOND into SECOND_FRAC, which does not work

    Details

    • Sprint:
      5.5.48-0

      Description

      This works:

      MariaDB LABS localhost heartbeat_p > SELECT CASE WHEN file like 'db1052%' THEN 's1' WHEN file like 'db1024%' THEN 's2' WHEN file like 'db1038%' THEN 's3' WHEN file like 'db1040%' THEN 's4' WHEN file like 'db1058%' THEN 's5' WHEN file like 'db1023%' THEN 's6' WHEN file like 'db1033%' THEN 's7' ELSE 'unknown' END as `shard`, TIMESTAMPDIFF(MICROSECOND,ts,UTC_TIMESTAMP(6)) AS `lag` FROM heartbeat.heartbeat;
      +-------+-------+
      | shard | lag   |
      +-------+-------+
      | s6    | 18588 |
      | s2    | 18738 |
      | s7    | 18798 |
      | s3    | 18788 |
      | s4    | 18568 |
      | s1    | 18928 |
      | s5    | 19068 |
      +-------+-------+
      7 rows in set (0.00 sec)

      This doesn't:

      MariaDB LABS localhost heartbeat_p > CREATE VIEW `heartbeat` AS SELECT CASE WHEN file like 'db1052%' THEN 's1' WHEN file like 'db1024%' THEN 's2' WHEN file like 'db1038%' THEN 's3' WHEN file like 'db1040%' THEN 's4' WHEN file like 'db1058%' THEN 's5' WHEN file like 'db1023%' THEN 's6' WHEN file like 'db1033%' THEN 's7' ELSE 'unknown' END as `shard`, TIMESTAMPDIFF(MICROSECOND,ts,UTC_TIMESTAMP(6)) AS `lag` FROM heartbeat.heartbeat;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB LABS localhost heartbeat_p > SELECT * FROM heartbeat;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SECOND_FRAC,`heartbeat`.`heartbeat`.`ts`,utc_timestamp()) AS `lag` from `heartbe' at line 1

      The reason why:

      MariaDB LABS localhost heartbeat_p > EXPLAIN EXTENDED SELECT CASE WHEN file like 'db1052%' THEN 's1' WHEN file like 'db1024%' THEN 's2' WHEN file like 'db1038%' THEN 's3' WHEN file like 'db1040%' THEN 's4' WHEN file like 'db1058%' THEN 's5' WHEN file like 'db1023%' THEN 's6' WHEN file like 'db1033%' THEN 's7' ELSE 'unknown' END as `shard`, TIMESTAMPDIFF(MICROSECOND,ts,UTC_TIMESTAMP(6)) AS `lag` FROM heartbeat.heartbeat\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: heartbeat
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 7
           filtered: 100.00
              Extra: 
      1 row in set, 1 warning (0.00 sec)
       
      MariaDB LABS localhost heartbeat_p > SHOW WARNINGS\G
      *************************** 1. row ***************************
        Level: Note
         Code: 1003
      Message: select (case when (`heartbeat`.`heartbeat`.`file` like 'db1052%') then 's1' when (`heartbeat`.`heartbeat`.`file` like 'db1024%') then 's2' when (`heartbeat`.`heartbeat`.`file` like 'db1038%') then 's3' when (`heartbeat`.`heartbeat`.`file` like 'db1040%') then 's4' when (`heartbeat`.`heartbeat`.`file` like 'db1058%') then 's5' when (`heartbeat`.`heartbeat`.`file` like 'db1023%') then 's6' when (`heartbeat`.`heartbeat`.`file` like 'db1033%') then 's7' else 'unknown' end) AS `shard`,timestampdiff(SECOND_FRAC,`heartbeat`.`heartbeat`.`ts`,utc_timestamp()) AS `lag` from `heartbeat`.`heartbeat`
      1 row in set (0.00 sec)

      Please note the the SECOND_FRAC, which shoudln't be there (it is deprecated).

        Attachments

          Activity

            People

            • Assignee:
              serg Sergei Golubchik
              Reporter:
              jcrespo Jaime Crespo
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: