Incorrect behavior in `users` tables with dates

corymartin commented 1 year ago

These issues are specific to tables named users, case-insensitive. I have not observed the issues in tables by other names, and I have tried this in multiple databases.

Also, this follows a conversation with @kostasb from Discord https://discord.com/channels/996791218879086662/1002130113565053008/1051530218394890271

Observed issues:

Dates do not appear in UI

However the data is there when queried via client
Filtering by date does not work. The below query should return null, as it does in tables NOT named users.
Data from users is not available in "Search"

To Reproduce

Create a table users or Users in the Xata UI

Create schema via API

curl -v -X PUT https://<workspace>.<region>.xata.sh/db/<database>:main/tables/users/schema  --header 'Content-Type: application/json' --header "Authorization: Bearer <api_key>" -d '
{
  "columns": [
    {
      "name": "handle",
      "type": "string",
      "unique": true
    },
    {
      "name": "_handle",
      "type": "string",
      "unique": true
    },
    {
      "name": "profile",
      "type": "object",
      "columns": [
        {
          "name": "name",
          "type": "string"
        },
        {
          "name": "bio",
          "type": "text"
        },
        {
          "name": "location",
          "type": "string"
        },
        {
          "name": "website",
          "type": "string"
        }
      ]
    },
    {
      "name": "settings",
      "type": "object",
      "columns": [
        {
          "name": "app_theme",
          "type": "string",
          "notNull": true,
          "defaultValue": "system"
        }
      ]
    },
    {
      "name": "created_at",
      "type": "datetime",
      "notNull": true,
      "defaultValue": "now"
    },
    {
      "name": "updated_at",
      "type": "datetime",
      "notNull": true,
      "defaultValue": "now"
    }
  ]
}'

Add a user via client. I used the generated client (npx xata codegen).

await xata.db.users.create('V7XH49jk5oWknYtn73wFwzsH74s1', {
handle: 'test',
});

Expected behavior

For users data to work like data from other tables.

Software version

From package.json

"dependencies" {
  "@xata.io/client": "^0.21.3"
},
"devDependencies" {
  "@xata.io/cli": "^0.11.4"
}

Additional context

I just started playing with Xata a few days ago, FYI. I'm already a fan and hope to see you guys go far!

tsg commented 1 year ago

hi @corymartin thanks a lot of for the excellent report! I can confirm there is a bug, it seems related to the now function generating the date with a slightly different format (nanoseconds vs milliseconds).

In my tests, equality date comparison don't work because of this, but $gt and $lt work as expected, for example:

const res = await xata.db.test
  .filter({
    $all: [
      {
        created_at: {
          $ge: "2022-12-14T19:56:00.895Z",
        },
      },
      {
        created_at: {
          $lt: "2022-12-14T19:56:00.896Z",
        },
      },
    ],
  })
  .getMany();

We'll work on fixing this ASAP. If you can avoid dates with default value now for the moment, that would avoid these issues.

tsg commented 1 year ago

@corymartin I forgot to write back, but the fix for these issues should be in since yesterday. Can you try again, please, and let me know if you notice any issues?

corymartin commented 1 year ago

Thanks @tsg

It worked with the TS client await xata.db.Table.create()

However with transactions - which I realize is still in beta - I get an error:

FetcherError: Bulk operation failed

{
  status: 400,
  errors: [
    {
      index: 0,
      message: 'table [UserPrograms]: select record: invalid record: column [createdAt]: cannot be null'
    }
  ],
  requestId: '6f657ab5-e132-9ac3-b5c5-acbce46232fd',
  cause: undefined
}

corymartin commented 1 year ago

Also, it seems that FetcherError is not exported to the @xata.io/client package? https://github.com/xataio/client-ts/blob/6500a88b92fa4239f8e0fd104a3ad5aa6c9542a6/packages/client/src/api/errors.ts#L14

I was looking to do an instanceof test on the error. XataError is exported but FetcherError is not of that type. Is this by design?

SferaDev commented 1 year ago

Also, it seems that FetcherError is not exported to the @xata.io/client package? https://github.com/xataio/client-ts/blob/6500a88b92fa4239f8e0fd104a3ad5aa6c9542a6/packages/client/src/api/errors.ts#L14

I was looking to do an instanceof test on the error. XataError is exported but FetcherError is not of that type. Is this by design?

That's a bug, will export it in next release

SferaDev commented 1 year ago

@corymartin https://github.com/xataio/client-ts/releases/tag/%40xata.io/client%400.21.5

kostasb commented 1 year ago

Thanks @tsg

It worked with the TS client await xata.db.Table.create()

However with transactions - which I realize is still in beta - I get an error:
FetcherError: Bulk operation failed

{
  status: 400,
  errors: [
    {
      index: 0,
      message: 'table [UserPrograms]: select record: invalid record: column [createdAt]: cannot be null'
    }
  ],
  requestId: '6f657ab5-e132-9ac3-b5c5-acbce46232fd',
  cause: undefined
}

Hi @corymartin, thanks for the report. After checking our logs, it appears in this request a value of null was specified for the createdAt field in the transaction. For security purposes we do not log the content of your request, but the trace implies that a null value was specified.

In order for the column value to receive the default timestamp ofnow, the createdAt field shouldn't be present in the insert or update operation on the record. In case it is present but with a null value, the error you encountered is returned.

Let us know if you are still encountering this issue and, if that's the case, how the transaction request body is created?

corymartin commented 1 year ago

@kostasb

The query I ran is:

    const programId = uniqueId();
    await xata.transactions.run([
      {
        insert: {
          table: 'UserPrograms',
          record: { id: programId, user: uid, title: 'now test' },
        },
      },
      {
        insert: {
          table: 'UserOwnedPrograms',
          record: { user: uid, userProgram: programId },
        },
      },
    ]);

I just ran it a few more times, each getting the same error as above requestId: 'a9907441-d035-9a08-8a31-f49282469d53' requestId: '5fbe42d3-337e-9a0f-ab8b-970e2d7e56d4' requestId: '9d539135-3b2f-9992-b792-724bcce7ad8e'

There are 2 date columns (createdAt and updatedAt) where the schema is

        {
          "name": "createdAt",
          "type": "datetime",
          "notNull": true,
          "defaultValue": "now"
        }

corymartin commented 1 year ago

Schema dump

{
  "tables": [
    {
      "name": "Users",
      "columns": [
        {
          "name": "handle",
          "type": "string",
          "unique": true
        },
        {
          "name": "_handle",
          "type": "string",
          "unique": true
        },
        {
          "name": "profile",
          "type": "object",
          "columns": [
            {
              "name": "name",
              "type": "string"
            },
            {
              "name": "bio",
              "type": "text"
            },
            {
              "name": "location",
              "type": "string"
            },
            {
              "name": "website",
              "type": "string"
            }
          ]
        },
        {
          "name": "settings",
          "type": "object",
          "columns": [
            {
              "name": "appTheme",
              "type": "string"
            }
          ]
        },
        {
          "name": "createdAt",
          "type": "datetime",
          "notNull": true,
          "defaultValue": "now"
        },
        {
          "name": "updatedAt",
          "type": "datetime",
          "notNull": true,
          "defaultValue": "now"
        }
      ]
    },
    {
      "name": "UserPrograms",
      "columns": [
        {
          "name": "user",
          "type": "link",
          "link": {
            "table": "Users"
          }
        },
        {
          "name": "title",
          "type": "string",
          "notNull": true,
          "defaultValue": ""
        },
        {
          "name": "createdAt",
          "type": "datetime",
          "notNull": true,
          "defaultValue": "now"
        },
        {
          "name": "updatedAt",
          "type": "datetime",
          "notNull": true,
          "defaultValue": "now"
        }
      ]
    },
    {
      "name": "UserOwnedPrograms",
      "columns": [
        {
          "name": "user",
          "type": "link",
          "link": {
            "table": "Users"
          }
        },
        {
          "name": "userProgram",
          "type": "link",
          "link": {
            "table": "UserPrograms"
          }
        },
        {
          "name": "createdAt",
          "type": "datetime",
          "notNull": true,
          "defaultValue": "now"
        }
      ]
    }
  ]
}

kostasb commented 1 year ago

Thank you for the additional details @corymartin , we will investigate and get back to you with our findings or a fix, if applicable.

kostasb commented 1 year ago

@corymartin we confirmed the problem with transactions failing to use the default value is due to a bug and we are working on it. I will let you know as soon as the issue is resolved.

kostasb commented 1 year ago

Hi @corymartin the issue with transactions + default values has been resolved. The transaction requests in your earlier comment should work now!

Let us know in case you encounter any issues, here or at support@xata.io. Otherwise I believe we can close this current issue if all items are addressed?

corymartin commented 1 year ago

@kostasb LGTM! It is working as expected now. Thanks for addressing this.

xataio / client-ts

Incorrect behavior in `users` tables with dates #819

Observed issues: