GitHub user shuangshuangwang opened a pull request:
https://github.com/apache/spark/pull/18445
[Spark-19726][SQL] Faild to insert null timestamp value to mysql using
spark jdbc
## What changes were proposed in this pull request?
when creating table like following:
> create table timestamp_test(id int(11), time_stamp timestamp not null
default current_timestamp);
The result of Excuting "insert into timestamp_test values (111, null)" is
different between Spark and JDBC.
```
mysql> select * from timestamp_test;
+------+---------------------+
| id | time_stamp |
+------+---------------------+
| 111 | 1970-01-01 00:00:00 | -> spark
| 111 | 2017-06-27 19:32:38 | -> mysql
+------+---------------------+
2 rows in set (0.00 sec)
```
Because in such case ```StructField.nullable``` is false, so the
generated codes of ```InvokeLike``` and ```BoundReference``` don't check
whether the field is null or not. Instead, they directly use
```CodegenContext.INPUT_ROW.getLong(1)```, however,
```UnsafeRow.setNullAt(1)``` will put 0 in the underlying memory.
The PR will ```always``` set ```StructField.nullable``` true after
obtaining metadata from jdbc connection, Since we can insert null to not null
timestamp column in MySQL. In this way, spark will propagate null to underlying
DB engine, and let DB to choose how to process NULL.
## How was this patch tested?
Added tests.
Please review http://spark.apache.org/contributing.html before opening a
pull request.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/shuangshuangwang/spark SPARK-19726
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/spark/pull/18445.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #18445
----
commit 2e05a85579570705026a7628135f93d303f34537
Author: YIHAODIAN\wangshuangshuang <[email protected]>
Date: 2017-06-27T09:58:57Z
Spark-19726: Insert into null timestamp value into mysql use null instead
of spark_default_timestamp_value
commit 588ccad369f3f8eb8c38c2f01748e5262ee5a676
Author: YIHAODIAN\wangshuangshuang <[email protected]>
Date: 2017-06-28T02:30:03Z
Spark-19726: modify unit tests to expect catch exception
commit 3b1785cae43c1d027687f5518c33bd7d743d002b
Author: YIHAODIAN\wangshuangshuang <[email protected]>
Date: 2017-06-28T03:06:24Z
Spark-19726: modify unit tests tablename same with jdbc
----
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at [email protected] or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
