[BUG] Unable to run queries in documentation

[ksco92]

What is the bug?
A clear and concise description of the bug.

I have AWS OS cluster running version 2.3. I am unable to run the following queries when I make an HTTP request:

POST /_plugins/_sql
{
  "query": "select current_date();"
}

POST /_plugins/_sql
{
  "query": "select curdate();"
}

POST /_plugins/_sql
{
  "query": "select sysdate();"
}

All of them return the same:

{
  "error": {
    "reason": "Invalid SQL query",
    "details": "",
    "type": "NullPointerException"
  },
  "status": 400
}

Allo those queries are actually copied from the docs: https://github.com/opensearch-project/sql/blob/main/docs/user/dql/functions.rst

This also happens in the query workbench in the UI, running:

select sysdate();

Returns:

Select sysdate(): Bad Request, this query is not runnable.

How can one reproduce the bug?
Steps to reproduce the behavior:

It can be reproduced by spinning up a OS cluster on 2.3 and sending a request with any of the queries above.

What is the expected behavior?
The queries should return the expected results listed in the documentation.

What is your host/environment?

• OS: AWS
• Version 2.3
• Plugins: sql

Do you have any screenshots?

If applicable, add screenshots to help explain your problem.

Do you have any additional context?
The requests are SigV4 signed and sent via the elasticsearch plugin from DataGrip. Other queries run properly:

POST /_plugins/_sql
{
  "query": "select count(*) from my_alias limit 1;"
}

Returns:

{
  "schema": [
    {
      "name": "count(*)",
      "type": "integer"
    }
  ],
  "datarows": [
    [
      12315361
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}

[Yury-Fridlyand]

@ksco92,
Thank you for reporting that. It is weird, sysdate, current_date and curdate were released prior to 2.3.
Did you upgrade your cluster from older version to 2.3?

[ksco92]

@Yury-Fridlyand no, it was recently created with CDK directly on 2.3. I actually have this issue in 6 different clusters, all of different sizes, across 5 different AWS accounts and 2 different regions.

[ksco92]

IDK if this is relevant here, but I filed this too:

opensearch-project/sql-jdbc#67

[Yury-Fridlyand]

Can you share please your CDK script if possible? I'd like to re-run it and reproduce the issue.
Please, delete all sensitive data prior to sharing.

[ksco92]

@Yury-Fridlyand Decoupling the infrastructure sensitive details is a little complicated, but here is the actual Construct that creates the domain. After this, there are no settings changed at all:

this.domain = new Domain(this, `OpenSearchDomain${props.stage}${Stack.of(this).region}`, {
            version: EngineVersion.OPENSEARCH_2_3,
            domainName: `mydomain${props.stage}${Stack.of(this).region}`,
            ebs: {
                volumeSize,
                volumeType: EbsDeviceVolumeType.GP3,
            },
            enableVersionUpgrade: true,
            encryptionAtRest: {
                enabled: true,
                kmsKey: openSearchKmsKey,
            },
            enforceHttps: true,
            securityGroups: [
                this.openSearchSecurityGroup,
            ],
            vpc: props.vpc,
            vpcSubnets: [
                {
                    subnetType: SubnetType.PRIVATE_WITH_EGRESS,
                },
            ],
            zoneAwareness: {
                availabilityZoneCount: 2,
            },
            capacity: {
                masterNodes: 3,
                masterNodeInstanceType: instanceType,
                dataNodes: this.dataNodeCount,
                dataNodeInstanceType: instanceType,
            },
            logging: {
                slowSearchLogEnabled: true,
                appLogEnabled: true,
                slowIndexLogEnabled: true,
                auditLogEnabled: true,
            },
            fineGrainedAccessControl: {
                masterUserArn: cognitoObjects.authenticatedRole.roleArn,
            },
            nodeToNodeEncryption: true,
            tlsSecurityPolicy: TLSSecurityPolicy.TLS_1_2,
            removalPolicy: RemovalPolicy.DESTROY,
            accessPolicies: [
                new PolicyStatement({
                    actions: [
                        'es:ESHttp*',
                    ],
                    effect: Effect.ALLOW,
                    principals: [
                        adminRole,
                        cognitoObjects.authenticatedRole,
                        openSearchCustomerRole,
                    ],
                }),
            ],
            customEndpoint: {
                certificate: props.certificate,
                hostedZone: props.privateHostedZone,
                domainName: this.dnsName,
            },
            cognitoDashboardsAuth: {
                role: openSearchAdminRole,
                identityPoolId: cognitoObjects.identityPool.ref,
                userPoolId: cognitoObjects.userPool.userPoolId,
            },
        });

In summary, it's in a private VPC, uses cognito for dashboards auth and has custom endpoint. This is in CDK V2:

https://docs.aws.amazon.com/cdk/api/v2/docs/aws-cdk-lib.aws_opensearchservice.Domain.html

[Yury-Fridlyand]

Awesome!
I'll try this sometime later. Sorry, I can't make a promise.

[Yury-Fridlyand]

@ksco92,
I just re-checked all things. Implementation of sysdate, curdate and other now-like functions was done in #754 on 2022-09-22, but OpenSearch 2.3 was released on 2022-09-14.
Unfortunately, there is a bit of mess in our release notes and dates when release posted on the repository don't match actual release dates.
Please, take into account that documentation on repository versioned by git. You posted a link to SQL functions on main branch, it contains all things exist in the SQL plugin, even something not released in upcoming 2.7 version. To see what is supported by 2.3, follow this link.

[Yury-Fridlyand]

I'm closing this issue for now. Please, try updating your OpenSearch cluster if these functions are essential for you.
Feel free to comment, re-open or open a new issue if you need to.

[ksco92]

@Yury-Fridlyand this was helpful thanks!

One last question, did I miss this detail just because the OS docs are not versioned?

[Yury-Fridlyand]

They are versioned by git. Select a version in the branch selector on the left:
https://github.com/opensearch-project/sql/blob/main/docs/user/dql/functions.rst#current-date