spannertest: support for aliases in SELECT statements

[jrmeinride]

Is your feature request related to a problem? Please describe.
To make our queries shorter and easier to read we use aliases in our queries. We want to unit test code that has such queries.

Example:

SELECT t1.* 
FROM table1 AS t1
	LEFT OUTER HASH JOIN table2 AS t2 
		ON t1.id = t2.table1_id

Granted, at this point there is no support for JOIN in SELECT statements, but we are hoping that this will be added, so that we can properly unit test our code.

Describe the solution you'd like
We would like to se aliases in SELECT statements.

Describe alternatives you've considered
The only viable option would be to spin up a new table in one of our projects, but that is out of scope for unit tests.

Additional context
Ẃe have another feature request for JOIN in SELECT statements, that is by far a more important feature. This would be a further quality of life improvement, we can write queries without aliases, though it would be nice to have them.

[dsymonds]

There's already support for aliases in SELECT expression lists and FROM clauses, I believe. Do you have an example query not using JOIN that has an alias that doesn't work?

[jrmeinride]

There's already support for aliases in SELECT expression lists and FROM clauses, I believe. Do you have an example query not using JOIN that has an alias that doesn't work?

Using the 1.6.0 version of spanner we get this error message when I run a query:

SQL for table:

CREATE TABLE something (
    id STRING(100) NOT NULL,
    created_time TIMESTAMP OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY(id)

Test code:

func Test_Spanner(t *testing.T) {
	fx := newFixture(t)
	defer fx.Fixture.Teardown()
	stmt := spanner.Statement{
		SQL: `SELECT s.* 
		FROM something as s
		WHERE s.id = @some_id 
		ORDER BY s.created_time ASC
		LIMIT 1`,
		Params: map[string]interface{}{
			"some_id": "1",
		},
	}
	rowIterator := fx.SpannerClient.Single().Query(context.Background(), stmt)
	defer rowIterator.Stop()
	_, err := rowIterator.Next()
	if err != nil {
		if err == iterator.Done {
			t.Log("done")
		} else {
			t.Log(err)
			t.FailNow()
		}
	}
}

service_test.go:nn: spanner: code = "InvalidArgument", desc = "bad query: unexpected trailing query contents "s.* \n\t\tFROM something as s\n\t\tWHERE s.id = @some_id \n\t\tORDER BY s.created_time ASC\n\t\tLIMIT 1""

[dsymonds]

The missing feature there is the s.*, s.id and s.created_time parts of the query rather than the alias itself, but yes, this is a known TODO. (The distinction here is that SELECT * FROM something AS s WHERE id = @some_id ORDER BY created_time ASC LIMIT 1 should work fine, though that s alias is not usable).

[jrmeinride]

I ran a test for that case, and got an error. I tested with and without the AS keyword, neither worked for me :-(

func Test_Spanner(t *testing.T) {
	fx := newFixture(t)
	defer fx.Fixture.Teardown()
	stmt := spanner.Statement{
		SQL: `SELECT * 
		FROM something AS s
		WHERE s.id = @some_id 
		ORDER BY s.created_time ASC
		LIMIT 1`,
		Params: map[string]interface{}{
			"some_id": "1",
		},
	}
	rowIterator := fx.SpannerClient.Single().Query(context.Background(), stmt)
	defer rowIterator.Stop()
	_, err := rowIterator.Next()
	if err != nil {
		if err == iterator.Done {
			t.Log("done")
		} else {
			t.Log(err)
			t.FailNow()
		}
	}
}

Output:

service_test.go:nn: spanner: code = "InvalidArgument", desc = "bad query: unexpected trailing query contents \"s.id = @some_id \\n\\t\\tORDER BY s.created_time ASC\\n\\t\\tLIMIT 1\""