Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.17
-
Windows 7/ Windows Server 2008 querying MS SQL Server 2012
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
Indeed using the ODBC escape syntax seems to fail when the date constant is not exactly what is expected. Now when building the where clause in cond push, CONNECT makes the exact required syntax.
Note that if you need to have an application working waiting for the fix, a workaround is to set the condition pushdown OFF.
If this worked in previous versions, maybe this was broken in the fix for
MDEV-7549?https://mariadb.atlassian.net/browse/MDEV-7549