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

CONNECT returns error 174 on query involving timestamp column when the condition is given as a date literal

    XMLWordPrintable

Details

    Description

      When querying a CONNECT table for a MS SQL Server table. If the where clause involves a columns that is of type timestamp and the matching condition is given as a date literal ('2015-03-25') instead of a timestamp literal ('2015-03-25 00:00:00') CONNECT will return error 174.

      The same query (with the date liternal) works on a InnoDB table. As well as on MariaDB version 10.0.15

      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 3
      Server version: 10.0.17-MariaDB mariadb.org binary distribution
       
      Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> use test
      Database changed
      MariaDB [test]> show create table test\G
      *************************** 1. row ***************************
             Table: test
      Create Table: CREATE TABLE `test` (
        `col1` int(10) DEFAULT NULL,
        `col2` varchar(255) DEFAULT NULL,
        `col3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAM
      P
      ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=CCRDB01;uid=user;pwd=password' `TABLE_TYPE`='ODBC' `BLOCK_SIZE`=10
      1 row in set (0.00 sec)
       
      MariaDB [test]> select * from test\G
      *************************** 1. row ***************************
      col1: 1
      col2: one
      col3: 2015-01-01 00:00:00
      *************************** 2. row ***************************
      col1: 2
      col2: two
      col3: 2015-01-02 00:00:00
      *************************** 3. row ***************************
      col1: 3
      col2: three
      col3: 2015-01-03 00:00:00
      3 rows in set (0.09 sec)
       
      MariaDB [test]> select * from test where col1=2\G
      *************************** 1. row ***************************
      col1: 2
      col2: two
      col3: 2015-01-02 00:00:00
      1 row in set (0.04 sec)
       
      MariaDB [test]> select * from test where col3='2015-01-02'\G
      ERROR 1296 (HY000): Got error 174 '[Microsoft][SQL Server Native Client 11.0][SQ
      L Server]Executing SQL directly; no cursor.' from CONNECT
      MariaDB [test]> select * from test where col3='2015-01-02 00:00:00'\G
      *************************** 1. row ***************************
      col1: 2
      col2: two
      col3: 2015-01-02 00:00:00
      1 row in set (0.08 sec)
       
      MariaDB [test]> create table foo select * from test\G
      Query OK, 3 rows affected (0.08 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from foo\G
      *************************** 1. row ***************************
      col1: 1
      col2: one
      col3: 2015-01-01 00:00:00
      *************************** 2. row ***************************
      col1: 2
      col2: two
      col3: 2015-01-02 00:00:00
      *************************** 3. row ***************************
      col1: 3
      col2: three
      col3: 2015-01-03 00:00:00
      3 rows in set (0.00 sec)
       
      MariaDB [test]> show create table foo\G
      *************************** 1. row ***************************
             Table: foo
      Create Table: CREATE TABLE `foo` (
        `col1` int(10) DEFAULT NULL,
        `col2` varchar(255) DEFAULT NULL,
        `col3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.00 sec)
       
      MariaDB [test]> select * from foo where col3='2015-01-02'\G
      *************************** 1. row ***************************
      col1: 2
      col2: two
      col3: 2015-01-02 00:00:00
      1 row in set (0.00 sec)
       
      MariaDB [test]>

      Attachments

        Activity

          People

            bertrandop Olivier Bertrand
            Tuco Tuco
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.