Incorrect parsing of timestamp's milliseconds with a leading zero

[akarmes]

Describe the bug

Given a column of type timestamp, leading zero in milliseconds is ignored. Example: inserted value "2021-03-03T08:31:15.077Z" will be read as "2021-03-03 08:31:15.770000"

To Reproduce

Given following table:

SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'solutions';

# id | character varying
# created_at | timestamp without time zone

And following test script

const AWS = require('aws-sdk');

const resourceArn = '';
const secretArn = '';
const database = '';
const endpoint = '';

const rdsDataService = new AWS.RDSDataService({ endpoint });

const executeSql = (sql) => rdsDataService.executeStatement({ resourceArn, secretArn, database, sql }).promise();

const test = async (sql) => {
  await executeSql(`INSERT INTO solutions (id, created_at) VALUES ('1', '2021-03-03T08:31:15.077Z')`);
  const { records } = await executeSql(`SELECT * FROM solutions WHERE id = '1'`);
  console.log(records);
}

The output would be following:

[
  [
    { stringValue: '1' },
    { stringValue: '2021-03-03 08:31:15.770000' },
  ]
]

Expected behavior

Is should behave equally to AWS Aurora Data API and return:

[
  [
    { stringValue: '1' },
    { stringValue: '2021-03-03 08:31:15.077' },
  ]
]

[koxudaxi]

@akarmes
Thank you for creating this issue.
OK, I will fix it!!

[koxudaxi]

@akarmes
I have released the fixed version as 0.5.6 🎉

[akarmes]

@koxudaxi Thank you for your fix! I've tried 0.5.6 and now output format is correct in the sense that it returns 3 digits for milliseconds.

But the other issue still persists: it has a bug with leading zero. The value 2021-03-03 00:00:00.055 will be parsed as 2021-03-03 00:00:00.550.

[koxudaxi]

@akarmes
hmm, I can't reproduce the promble.

$ aws rds-data execute-statement --database 'test' \
    --resource-arn $RDS_DATA_API_CLIENT_RESOURCE_ARN \
    --secret-arn $RDS_DATA_API_CLIENT_SECRETARN \
    --include-result-metadata \
    --sql $'insert into example(t) values (\'2020-03-11 11:04:21.055\')'

 "stringValue": "2020-03-11 11:04:21.055"

Also, I used this logic to get the first three numbers.
I don't understand why the first zero is dropped. 🤔

local-data-api/local_data_api/resources/resource.py

Lines 335 to 337 in dde8601

	@classmethod
	def _format_datetime(cls, value: Any) -> str:
	return re.match(r'^[^.]+(\.\d{3}|$)', str(value)).group() # type: ignore

[koxudaxi]

I'm sorry, you are correct.
I ran the command to real Data-API 😢

I found the bug in the driver.
https://github.com/baztian/jaydebeapi/blob/cd2fd4c6c047803916fc92a534f02f1213e252d6/jaydebeapi/__init__.py#L619-L625

d = d.replace(microsecond=int(str(java_val.getNanos())[:6]))

baztian/jaydebeapi#177
This PR has not merged yet. 😓

[koxudaxi]

@akarmes
I have released a new version as 0.5.7
It passed the integration test.

local-data-api/scripts/integration-test.sh

Lines 103 to 104 in 18a8d3f

	--sql $'SELECT CAST(\'2021-03-10 22:41:04.068123+02\' AS TIMESTAMPTZ) AS value' \
	| jq -e '.records[0][0].stringValue == "2021-03-10 20:41:04.068"'

[akarmes]

Thank you @koxudaxi! I can confirm the issue is resolved.

[akarmes]

@koxudaxi I've tested version 0.5.7 more extensively and noticed, that given a row with null value for column of a timestamp type, local-data-api is returning string "None". I'll post an example later

[koxudaxi]

@akarmes
I just fixed it and released 0.5.8 🚀
Thank you very much.

The wrapper of the JDBC driver for Python is buggy.
I want to replace this implementation with Kotlin when I get the time 😅
#70

[akarmes]

I was just writing next bug report 😅 Thank you again!