github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/table_ref_test.go (about)

     1  // Copyright 2017 The Cockroach Authors.
     2  //
     3  // Use of this software is governed by the Business Source License
     4  // included in the file licenses/BSL.txt.
     5  //
     6  // As of the Change Date specified in that file, in accordance with
     7  // the Business Source License, use of this software will be governed
     8  // by the Apache License, Version 2.0, included in the file
     9  // licenses/APL.txt.
    10  
    11  package sql_test
    12  
    13  import (
    14  	"context"
    15  	"fmt"
    16  	"testing"
    17  
    18  	"github.com/cockroachdb/cockroach/pkg/keys"
    19  	"github.com/cockroachdb/cockroach/pkg/sql/sqlbase"
    20  	"github.com/cockroachdb/cockroach/pkg/sql/tests"
    21  	"github.com/cockroachdb/cockroach/pkg/testutils/serverutils"
    22  	"github.com/cockroachdb/cockroach/pkg/util/leaktest"
    23  )
    24  
    25  func TestTableRefs(t *testing.T) {
    26  	defer leaktest.AfterTest(t)()
    27  
    28  	params, _ := tests.CreateTestServerParams()
    29  	s, db, kvDB := serverutils.StartServer(t, params)
    30  	defer s.Stopper().Stop(context.Background())
    31  
    32  	// Populate the test database.
    33  	stmt := `
    34  CREATE DATABASE test;
    35  CREATE TABLE test.t(a INT PRIMARY KEY, xx INT, b INT, c INT);
    36  CREATE TABLE test.hidden(a INT, b INT);
    37  CREATE INDEX bc ON test.t(b, c);
    38  `
    39  	_, err := db.Exec(stmt)
    40  	if err != nil {
    41  		t.Fatal(err)
    42  	}
    43  
    44  	// Retrieve the numeric descriptors.
    45  	tableDesc := sqlbase.GetTableDescriptor(kvDB, keys.SystemSQLCodec, "test", "t")
    46  	tID := tableDesc.ID
    47  	var aID, bID, cID sqlbase.ColumnID
    48  	for i := range tableDesc.Columns {
    49  		c := &tableDesc.Columns[i]
    50  		switch c.Name {
    51  		case "a":
    52  			aID = c.ID
    53  		case "b":
    54  			bID = c.ID
    55  		case "c":
    56  			cID = c.ID
    57  		}
    58  	}
    59  	pkID := tableDesc.PrimaryIndex.ID
    60  	secID := tableDesc.Indexes[0].ID
    61  
    62  	// Retrieve the numeric descriptors.
    63  	tableDesc = sqlbase.GetTableDescriptor(kvDB, keys.SystemSQLCodec, "test", "hidden")
    64  	tIDHidden := tableDesc.ID
    65  	var rowIDHidden sqlbase.ColumnID
    66  	for i := range tableDesc.Columns {
    67  		c := &tableDesc.Columns[i]
    68  		switch c.Name {
    69  		case "rowid":
    70  			rowIDHidden = c.ID
    71  		}
    72  	}
    73  
    74  	// Make some schema changes meant to shuffle the ID/name mapping.
    75  	stmt = `
    76  ALTER TABLE test.t RENAME COLUMN b TO d;
    77  ALTER TABLE test.t RENAME COLUMN a TO p;
    78  ALTER TABLE test.t DROP COLUMN xx;
    79  `
    80  	_, err = db.Exec(stmt)
    81  	if err != nil {
    82  		t.Fatal(err)
    83  	}
    84  
    85  	// Check the table references.
    86  	testData := []struct {
    87  		tableExpr       string
    88  		expectedColumns string
    89  		expectedError   string
    90  	}{
    91  		{fmt.Sprintf("[%d as t]", tID), `(p, d, c)`, ``},
    92  		{fmt.Sprintf("[%d(%d) as t]", tID, aID), `(p)`, ``},
    93  		{fmt.Sprintf("[%d(%d) as t]", tID, bID), `(d)`, ``},
    94  		{fmt.Sprintf("[%d(%d) as t]", tID, cID), `(c)`, ``},
    95  		{fmt.Sprintf("[%d as t]@bc", tID), `(p, d, c)`, ``},
    96  		{fmt.Sprintf("[%d(%d) as t]@bc", tID, aID), `(p)`, ``},
    97  		{fmt.Sprintf("[%d(%d) as t]@bc", tID, bID), `(d)`, ``},
    98  		{fmt.Sprintf("[%d(%d) as t]@bc", tID, cID), `(c)`, ``},
    99  		{fmt.Sprintf("[%d(%d, %d, %d) as t]", tID, cID, bID, aID), `(c, d, p)`, ``},
   100  		{fmt.Sprintf("[%d(%d, %d, %d) as t(c, b, a)]", tID, cID, bID, aID), `(c, b, a)`, ``},
   101  		{fmt.Sprintf("[%d() as t]", tID), ``, `pq: an explicit list of column IDs must include at least one column`},
   102  		{`[666() as t]`, ``, `pq: [666() AS t]: relation "[666]" does not exist`},
   103  		{fmt.Sprintf("[%d(666) as t]", tID), ``, `pq: column [666] does not exist`},
   104  		{fmt.Sprintf("test.t@[%d]", pkID), `(p, d, c)`, ``},
   105  		{fmt.Sprintf("test.t@[%d]", secID), `(p, d, c)`, ``},
   106  		{`test.t@[666]`, ``, `pq: index [666] not found`},
   107  		{fmt.Sprintf("[%d as t]@[%d]", tID, pkID), `(p, d, c)`, ``},
   108  		{fmt.Sprintf("[%d(%d) as t]@[%d]", tID, aID, pkID), `(p)`, ``},
   109  		{fmt.Sprintf("[%d(%d) as t]@[%d]", tID, bID, pkID), `(d)`, ``},
   110  		{fmt.Sprintf("[%d(%d) as t]@[%d]", tID, cID, pkID), `(c)`, ``},
   111  		{fmt.Sprintf("[%d(%d) as t]@[%d]", tID, aID, secID), `(p)`, ``},
   112  		{fmt.Sprintf("[%d(%d) as t]@[%d]", tID, bID, secID), `(d)`, ``},
   113  		{fmt.Sprintf("[%d(%d) as t]@[%d]", tID, cID, secID), `(c)`, ``},
   114  		{fmt.Sprintf("[%d(%d) as t]", tIDHidden, rowIDHidden), `()`, ``},
   115  	}
   116  
   117  	for i, d := range testData {
   118  		t.Run(d.tableExpr, func(t *testing.T) {
   119  			sql := `SELECT columns FROM [EXPLAIN(VERBOSE) SELECT * FROM ` + d.tableExpr + "] WHERE columns != ''"
   120  			var columns string
   121  			if err := db.QueryRow(sql).Scan(&columns); err != nil {
   122  				if d.expectedError != "" {
   123  					if err.Error() != d.expectedError {
   124  						t.Fatalf("%d: %s: expected error: %s, got: %v", i, d.tableExpr, d.expectedError, err)
   125  					}
   126  				} else {
   127  					t.Fatalf("%d: %s: query failed: %v", i, d.tableExpr, err)
   128  				}
   129  			}
   130  
   131  			if columns != d.expectedColumns {
   132  				t.Fatalf("%d: %s: expected: %s, got: %s", i, d.tableExpr, d.expectedColumns, columns)
   133  			}
   134  		})
   135  	}
   136  }