[MDEV-7843] Second an subsequent SQL Server datetime columns of a CONNECT table have invalid default value of '0000-00-00 00:00:00' Created: 2015-03-26  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.0.17, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Tuco Assignee: Andrew Hutchings
Resolution: Unresolved Votes: 0
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.



 Comments   
Comment by Olivier Bertrand [ 2015-04-01 ]

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.

Comment by Elena Stepanova [ 2023-01-22 ]

Updating versions under assumption that it hasn't been fixed.

Generated at Thu Feb 08 07:22:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.