[MDEV-7843] Second an subsequent SQL Server datetime columns of a CONNECT table have invalid default value of '0000-00-00 00:00:00' - JIRA

Details

Type: Bug
Status: Open
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.0.17
Fix Version/s: 10.0
Component/s: Storage Engine - Connect
Labels:
None
Environment:
Windows 7/ Windows Server 2008R2, MS SQL Server 2012

Description

[NOTE] This might be related to issue MDEV-7842

When creating CONNECT table using auto column discovery. Second and subsequent SQL Server datetime columns will be created with an invalid default value of '0000-00-00 00:00:00'.

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
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]> create table ts_test
    -> ENGINE=CONNECT CONNECTION='DSN=CCRDB01;uid=username;pwd=password' `TABLE_TYPE`='ODBC' `BLOCK_SIZE`=10
    -> ;
Query OK, 0 rows affected (0.10 sec)

MariaDB [test]> show create table ts_test\G
*************************** 1. row ***************************
       Table: ts_test
Create Table: CREATE TABLE `ts_test` (
  `col1` int(10) DEFAULT NULL,
  `col2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAM
P,
  `col3` varchar(255) DEFAULT NULL,
  `col4` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `col5` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=CCRDB01;uid=username;pwd=password' `TABLE_TYPE`='ODBC' `BLOCK_SIZE`=10
1 row in set (0.00 sec)

MariaDB [test]>

The source table on SQL Server is created as follows

create table ts_test
( col1 int,
col2 datetime,
col3 varchar(255),
col4 datetime,
col5 datetime
)

This did not affect 10.0.15 as that version used the type of datetime instead of timestamp for these columns

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 114
Server version: 10.0.15-MariaDB-log mariadb.org binary distribution

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost-master [(none)]> use test
Database changed
root@localhost-master [test]> create table ts_test
    -> ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=CCRDB01;uid=username;pwd=password' `TABLE_TYPE`='ODBC' `BLOCK_SIZE`=10;
Query OK, 0 rows affected (0.13 sec)

root@localhost-master [test]> show create table ts_test\G
*************************** 1. row ***************************
       Table: ts_test
Create Table: CREATE TABLE `ts_test` (
  `col1` int(10) DEFAULT NULL,
  `col2` datetime DEFAULT NULL,
  `col3` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `col4` datetime DEFAULT NULL,
  `col5` datetime DEFAULT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=CCRDB01;uid=username;pwd=password' `TABLE_TYPE`='ODBC' `BLOCK_SIZE`=10
1 row in set (0.00 sec)

root@localhost-master [test]>

As CONNECT tables are just an interface to an actual remote table. It might be better to not automatically specify any columns NOT NULLable, and instead allow the remote table to enforce the check.

Gliffy Diagrams

Attachments

Activity

Hide

Permalink

Olivier Bertrand added a comment - 2015-04-01 16:37

Hummm... they are apparently several issues concerning this.
1)
Data sources often return incoherent information when queried by the ODBC SQLColumns function. For instance, when Oracle is queried for the JOB_HISTORY table it returns:

table_name	column_name	data_type	type_name
JOB_HISTORY	EMPLOYEE_ID	3	DECIMAL
JOB_HISTORY	START_DATE	11	DATE
JOB_HISTORY	END_DATE	11	DATE
JOB_HISTORY	JOB_ID	12	VARCHAR2
JOB_HISTORY	DEPARTMENT_ID	3	DECIMAL

Here for the date columns the return type is 11 that is defined in sqlext.h as SQL_TIMESTAMP. Other data sources return 11 for DATETIME and some others correctly return 9.
2)
CONNECT currently set the data type from the returned data_type information. This is why it sets some columns as TIMESTAMP where is was set to DATETIME in previous versions. Perhaps this should be changed and the type_name also be tested to decide exactly to what type a column should be set.
3)
The way CONNECT works when columns are set via discovery is to retrieves these information and to make a fake CREATE TABLE statement that is sent to the MariaDB init_from_sql_statement_string function.

In the case above, after executing:

create table orajob engine=connect table_type=ODBC connection='DSN=ORACLE_TEST;PWD=manager;' DBNAME='HR' TABNAME='JOB_HISTORY';

this statement is:

CREATE TABLE whatever (`EMPLOYEE_ID` DECIMAL(8) NOT NULL,`START_DATE` TIMESTAMP NOT NULL,`END_DATE` TIMESTAMP NOT NULL,`JOB_ID` VARCHAR(10) NOT NULL,`DEPARTMENT_ID` DECIMAL(6)) TABLE_TYPE='ODBC' TABNAME='JOB_HISTORY' DBNAME='HR' CONNECTION='DSN=ORACLE_TEST;PWD=manager;'

As explained above, the date columns are defined as TIMESTAMP but this statement does not say anything about their default values. What happens after that occurs inside the init_from_sql_statement_string function and is no more CONNECT business. Indeed:

show create table orajob;

returns:

CREATE TABLE `orajob` (
  `EMPLOYEE_ID` decimal(8,0) NOT NULL,
  `START_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `END_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `JOB_ID` varchar(10) NOT NULL,
  `DEPARTMENT_ID` decimal(6,0) DEFAULT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ORACLE_TEST;PWD=manager;' `TABLE_TYPE`='ODBC' `TABNAME`='JOB_HISTORY' `DBNAME`='HR';

The additional question about specifying all columns as not null seems reasonable but will not solve the problem of MariaDB adding default values in init_from_sql_statement_string.

Show

Olivier Bertrand added a comment - 2015-04-01 16:37 Hummm... they are apparently several issues concerning this. 1) Data sources often return incoherent information when queried by the ODBC SQLColumns function. For instance, when Oracle is queried for the JOB_HISTORY table it returns: table_name column_name data_type type_name JOB_HISTORY EMPLOYEE_ID 3 DECIMAL JOB_HISTORY START_DATE 11 DATE JOB_HISTORY END_DATE 11 DATE JOB_HISTORY JOB_ID 12 VARCHAR2 JOB_HISTORY DEPARTMENT_ID 3 DECIMAL Here for the date columns the return type is 11 that is defined in sqlext.h as SQL_TIMESTAMP. Other data sources return 11 for DATETIME and some others correctly return 9. 2) CONNECT currently set the data type from the returned data_type information. This is why it sets some columns as TIMESTAMP where is was set to DATETIME in previous versions. Perhaps this should be changed and the type_name also be tested to decide exactly to what type a column should be set. 3) The way CONNECT works when columns are set via discovery is to retrieves these information and to make a fake CREATE TABLE statement that is sent to the MariaDB init_from_sql_statement_string function. In the case above, after executing: create table orajob engine=connect table_type=ODBC connection='DSN=ORACLE_TEST;PWD=manager;' DBNAME='HR' TABNAME='JOB_HISTORY'; this statement is: CREATE TABLE whatever (`EMPLOYEE_ID` DECIMAL(8) NOT NULL,`START_DATE` TIMESTAMP NOT NULL,`END_DATE` TIMESTAMP NOT NULL,`JOB_ID` VARCHAR(10) NOT NULL,`DEPARTMENT_ID` DECIMAL(6)) TABLE_TYPE='ODBC' TABNAME='JOB_HISTORY' DBNAME='HR' CONNECTION='DSN=ORACLE_TEST;PWD=manager;' As explained above, the date columns are defined as TIMESTAMP but this statement does not say anything about their default values. What happens after that occurs inside the init_from_sql_statement_string function and is no more CONNECT business. Indeed: show create table orajob; returns: CREATE TABLE `orajob` ( `EMPLOYEE_ID` decimal(8,0) NOT NULL, `START_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `END_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `JOB_ID` varchar(10) NOT NULL, `DEPARTMENT_ID` decimal(6,0) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ORACLE_TEST;PWD=manager;' `TABLE_TYPE`='ODBC' `TABNAME`='JOB_HISTORY' `DBNAME`='HR'; The additional question about specifying all columns as not null seems reasonable but will not solve the problem of MariaDB adding default values in init_from_sql_statement_string .

Second an subsequent SQL Server datetime columns of a CONNECT table have invalid default value of '0000-00-00 00:00:00'

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile