Skip to content

Commit 526b4e5

Browse files
andy-kimballandy-kimball
committed
opt: Add support for views
Allow views to be used as a data source in addition to tables. Generalize Catalog interface to resolve data sources rather than just tables. Separate out privilege checking so that it can be better controlled by optbuilder. If selecting from a view, we want to check SELECT privilege on the view, and not on the underlying table(s). Release note: None
1 parent 4b54f2b commit 526b4e5

28 files changed

Lines changed: 891 additions & 535 deletions

‎pkg/sql/logictest/testdata/logic_test/numeric_references‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -83,3 +83,9 @@ query I
8383
SELECT count(rowid) FROM [54(3) AS num_ref_hidden]
8484
----
8585
3
86+
87+
# Ensure that privileges are checked when using numeric references.
88+
user testuser
89+
90+
statement error pq: user testuser does not have SELECT privilege on relation num_ref
91+
SELECT * FROM [53 AS t]

‎pkg/sql/logictest/testdata/logic_test/optimizer‎

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -91,6 +91,9 @@ query I rowsort
9191
SELECT job_id FROM crdb_internal.jobs
9292
----
9393

94+
statement ok
95+
CREATE SEQUENCE seq
96+
9497
statement ok
9598
SET EXPERIMENTAL_OPT = ALWAYS
9699

@@ -101,8 +104,8 @@ INSERT INTO test (k, v) VALUES (5, 50)
101104
query error pq: aggregates with FILTER are not supported yet
102105
SELECT count(*) FILTER (WHERE v>10) FROM t
103106

104-
query error pq: views and sequences are not supported
105-
SELECT * FROM tview
107+
query error pq: sequences are not supported
108+
SELECT * FROM seq
106109

107110
statement ok
108111
SET EXPERIMENTAL_OPT = LOCAL

‎pkg/sql/logictest/testdata/logic_test/views‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,8 @@
11
# LogicTest: local local-opt local-parallel-stmts fakedist fakedist-opt fakedist-metadata
22

3+
# NOTE: Keep this table at the beginning of the file to ensure that its numeric
4+
# reference is 53 (the numeric reference of the first table). If the
5+
# numbering scheme in cockroach changes, this test will break.
36
statement ok
47
CREATE TABLE t (a INT PRIMARY KEY, b INT)
58

@@ -274,6 +277,27 @@ SELECT * FROM v6
274277

275278
user root
276279

280+
# Ensure that views work over tables identified by numeric reference.
281+
statement ok
282+
CREATE VIEW num_ref_view AS SELECT a, b FROM [53 AS t]
283+
284+
statement ok
285+
GRANT SELECT ON num_ref_view TO testuser
286+
287+
user testuser
288+
289+
query II rowsort
290+
SELECT * FROM num_ref_view
291+
----
292+
1 99
293+
2 98
294+
3 97
295+
296+
user root
297+
298+
statement ok
299+
DROP VIEW num_ref_view
300+
277301
statement error pgcode 42809 "v1" is not a table
278302
DROP TABLE v1
279303

‎pkg/sql/opt/catalog.go‎

Lines changed: 109 additions & 56 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@ import (
2020
"fmt"
2121
"time"
2222

23+
"github.com/cockroachdb/cockroach/pkg/sql/privilege"
2324
"github.com/cockroachdb/cockroach/pkg/sql/sem/tree"
2425
"github.com/cockroachdb/cockroach/pkg/sql/sem/types"
2526
"github.com/cockroachdb/cockroach/pkg/util/treeprinter"
@@ -36,6 +37,93 @@ type ColumnName string
3637
// index, even if it meant adding a hidden unique rowid column.
3738
const PrimaryIndex = 0
3839

40+
// Catalog is an interface to a database catalog, exposing only the information
41+
// needed by the query optimizer.
42+
type Catalog interface {
43+
// ResolveDataSource locates a data source with the given name and returns it.
44+
// If no such data source exists, then ResolveDataSource returns an error. As
45+
// a side effect, the name parameter is updated to be fully qualified if it
46+
// was not before (i.e. to include catalog and schema names).
47+
ResolveDataSource(ctx context.Context, name *tree.TableName) (DataSource, error)
48+
49+
// ResolveDataSourceByID is similar to ResolveDataSource, except that it
50+
// locates a data source by its unique identifier in the database. This id
51+
// is stable as long as the data source exists.
52+
ResolveDataSourceByID(ctx context.Context, dataSourceID int64) (DataSource, error)
53+
}
54+
55+
// DataSource is an interface to a database object that provides rows, like a
56+
// table, a view, or a sequence.
57+
type DataSource interface {
58+
// Name returns the fully normalized, fully qualified, and fully resolved
59+
// name of the data source. The ExplicitCatalog and ExplicitSchema fields
60+
// will always be true, since all parts of the name are always specified.
61+
Name() *tree.TableName
62+
63+
// CheckPrivilege verifies that the current user has the given privilege on
64+
// this data source. If not, then CheckPrivilege returns an error.
65+
CheckPrivilege(ctx context.Context, priv privilege.Kind) error
66+
}
67+
68+
// Table is an interface to a database table, exposing only the information
69+
// needed by the query optimizer.
70+
type Table interface {
71+
DataSource
72+
73+
// IsVirtualTable returns true if this table is a special system table that
74+
// constructs its rows "on the fly" when it's queried. An example is the
75+
// information_schema tables.
76+
IsVirtualTable() bool
77+
78+
// ColumnCount returns the number of columns in the table.
79+
ColumnCount() int
80+
81+
// Column returns a Column interface to the column at the ith ordinal
82+
// position within the table, where i < ColumnCount.
83+
Column(i int) Column
84+
85+
// LookupColumnOrdinal returns the ordinal of the column with the given ID.
86+
// Note that this takes the internal column ID, and has no relation to
87+
// ColumnIDs in the optimizer.
88+
LookupColumnOrdinal(colID uint32) (int, error)
89+
90+
// IndexCount returns the number of indexes defined on this table. This
91+
// includes the primary index, so the count is always >= 1.
92+
IndexCount() int
93+
94+
// Index returns the ith index, where i < IndexCount. The table's primary
95+
// index is always the 0th index, and is always present (use the
96+
// opt.PrimaryIndex to select it). The primary index corresponds to the
97+
// table's primary key. If a primary key was not explicitly specified, then
98+
// the system implicitly creates one based on a hidden rowid column.
99+
Index(i int) Index
100+
101+
// StatisticCount returns the number of statistics available for the table.
102+
StatisticCount() int
103+
104+
// Statistic returns the ith statistic, where i < StatisticCount.
105+
Statistic(i int) TableStatistic
106+
}
107+
108+
// View is an interface to a database view, exposing only the information needed
109+
// by the query optimizer.
110+
type View interface {
111+
DataSource
112+
113+
// Query returns the SQL text that specifies the SELECT query that constitutes
114+
// this view.
115+
Query() string
116+
117+
// ColumnNameCount returns the number of column names specified in the view.
118+
// If zero, then the columns are not aliased. Otherwise, it will match the
119+
// number of columns in the view.
120+
ColumnNameCount() int
121+
122+
// ColumnNames returns the name of the column at the ith ordinal position
123+
// within the view, where i < ColumnNameCount.
124+
ColumnName(i int) tree.Name
125+
}
126+
39127
// Column is an interface to a table column, exposing only the information
40128
// needed by the query optimizer.
41129
type Column interface {
@@ -175,65 +263,10 @@ type TableStatistic interface {
175263
// TODO(radu): add Histogram().
176264
}
177265

178-
// Table is an interface to a database table, exposing only the information
179-
// needed by the query optimizer.
180-
type Table interface {
181-
// TabName returns the fully normalized, fully qualified, and fully resolved
182-
// name of the table. The ExplicitCatalog and ExplicitSchema fields will
183-
// always be true, since both names are always specified.
184-
TabName() *tree.TableName
185-
186-
// IsVirtualTable returns true if this table is a special system table that
187-
// constructs its rows "on the fly" when it's queried. An example is the
188-
// information_schema tables.
189-
IsVirtualTable() bool
190-
191-
// ColumnCount returns the number of columns in the table.
192-
ColumnCount() int
193-
194-
// Column returns a Column interface to the column at the ith ordinal
195-
// position within the table, where i < ColumnCount.
196-
Column(i int) Column
197-
198-
// LookupColumnOrdinal returns the ordinal of the column with the given ID.
199-
// Note that this takes the internal column ID, and has no relation to
200-
// ColumnIDs in the optimizer.
201-
LookupColumnOrdinal(colID uint32) (int, error)
202-
203-
// IndexCount returns the number of indexes defined on this table. This
204-
// includes the primary index, so the count is always >= 1.
205-
IndexCount() int
206-
207-
// Index returns the ith index, where i < IndexCount. The table's primary
208-
// index is always the 0th index, and is always present (use the
209-
// opt.PrimaryIndex to select it). The primary index corresponds to the
210-
// table's primary key. If a primary key was not explicitly specified, then
211-
// the system implicitly creates one based on a hidden rowid column.
212-
Index(i int) Index
213-
214-
// StatisticCount returns the number of statistics available for the table.
215-
StatisticCount() int
216-
217-
// Statistic returns the ith statistic, where i < StatisticCount.
218-
Statistic(i int) TableStatistic
219-
}
220-
221-
// Catalog is an interface to a database catalog, exposing only the information
222-
// needed by the query optimizer.
223-
type Catalog interface {
224-
// FindTable returns a Table interface for the database table matching the
225-
// given table name. Returns an error if the table does not exist.
226-
FindTable(ctx context.Context, name *tree.TableName) (Table, error)
227-
228-
// FindTableByID returns a Table interface for the database table
229-
// matching the given table ID. Returns an error if the table does not exist.
230-
FindTableByID(ctx context.Context, tableID int64) (Table, error)
231-
}
232-
233266
// FormatCatalogTable nicely formats a catalog table using a treeprinter for
234267
// debugging and testing.
235268
func FormatCatalogTable(tab Table, tp treeprinter.Node) {
236-
child := tp.Childf("TABLE %s", tab.TabName().TableName)
269+
child := tp.Childf("TABLE %s", tab.Name().TableName)
237270

238271
var buf bytes.Buffer
239272
for i := 0; i < tab.ColumnCount(); i++ {
@@ -289,3 +322,23 @@ func formatColumn(col Column, buf *bytes.Buffer) {
289322
fmt.Fprintf(buf, " (hidden)")
290323
}
291324
}
325+
326+
// FormatCatalogView nicely formats a catalog view using a treeprinter for
327+
// debugging and testing.
328+
func FormatCatalogView(view View, tp treeprinter.Node) {
329+
var buf bytes.Buffer
330+
if view.ColumnNameCount() > 0 {
331+
buf.WriteString(" (")
332+
for i := 0; i < view.ColumnNameCount(); i++ {
333+
if i != 0 {
334+
buf.WriteString(", ")
335+
}
336+
buf.WriteString(string(view.ColumnName(i)))
337+
}
338+
buf.WriteString(")")
339+
}
340+
341+
child := tp.Childf("VIEW %s%s", view.Name().TableName, buf.String())
342+
343+
child.Child(view.Query())
344+
}

‎pkg/sql/opt/memo/memo_format.go‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -273,9 +273,9 @@ func (f exprFormatter) formatPrivate(private interface{}, mode formatMode) {
273273
// Don't output name of index if it's the primary index.
274274
tab := f.mem.metadata.Table(t.Table)
275275
if t.Index == opt.PrimaryIndex {
276-
fmt.Fprintf(f.buf, " %s", tab.TabName().TableName)
276+
fmt.Fprintf(f.buf, " %s", tab.Name().TableName)
277277
} else {
278-
fmt.Fprintf(f.buf, " %s@%s", tab.TabName().TableName, tab.Index(t.Index).IdxName())
278+
fmt.Fprintf(f.buf, " %s@%s", tab.Name().TableName, tab.Index(t.Index).IdxName())
279279
}
280280
if t.Reverse {
281281
fmt.Fprintf(f.buf, ",rev")
@@ -294,7 +294,7 @@ func (f exprFormatter) formatPrivate(private interface{}, mode formatMode) {
294294

295295
case *VirtualScanOpDef:
296296
tab := f.mem.metadata.Table(t.Table)
297-
fmt.Fprintf(f.buf, " %s", tab.TabName())
297+
fmt.Fprintf(f.buf, " %s", tab.Name())
298298

299299
case *RowNumberDef:
300300
if !t.Ordering.Any() {
@@ -312,17 +312,17 @@ func (f exprFormatter) formatPrivate(private interface{}, mode formatMode) {
312312

313313
case *IndexJoinDef:
314314
tab := f.mem.metadata.Table(t.Table)
315-
fmt.Fprintf(f.buf, " %s", tab.TabName().TableName)
315+
fmt.Fprintf(f.buf, " %s", tab.Name().TableName)
316316
if mode == formatMemo {
317317
fmt.Fprintf(f.buf, ",cols=%s", t.Cols)
318318
}
319319

320320
case *LookupJoinDef:
321321
tab := f.mem.metadata.Table(t.Table)
322322
if t.Index == opt.PrimaryIndex {
323-
fmt.Fprintf(f.buf, " %s", tab.TabName().TableName)
323+
fmt.Fprintf(f.buf, " %s", tab.Name().TableName)
324324
} else {
325-
fmt.Fprintf(f.buf, " %s@%s", tab.TabName().TableName, tab.Index(t.Index).IdxName())
325+
fmt.Fprintf(f.buf, " %s@%s", tab.Name().TableName, tab.Index(t.Index).IdxName())
326326
}
327327
if mode == formatMemo {
328328
fmt.Fprintf(f.buf, ",keyCols=%v,lookupCols=%s", t.KeyCols, t.LookupCols)

‎pkg/sql/opt/memo/private_defs_test.go‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,7 @@ import (
2121
"github.com/cockroachdb/cockroach/pkg/sql/opt/memo"
2222
"github.com/cockroachdb/cockroach/pkg/sql/opt/props"
2323
"github.com/cockroachdb/cockroach/pkg/sql/opt/testutils/testcat"
24+
"github.com/cockroachdb/cockroach/pkg/sql/sem/tree"
2425
)
2526

2627
func TestScanCanProvideOrdering(t *testing.T) {
@@ -39,7 +40,7 @@ func TestScanCanProvideOrdering(t *testing.T) {
3940
}
4041

4142
md := opt.NewMetadata()
42-
a := md.AddTable(cat.Table("a"))
43+
a := md.AddTable(cat.Table(tree.NewUnqualifiedTableName("a")))
4344

4445
// PRIMARY KEY (k)
4546
primary := 0

‎pkg/sql/opt/memo/statistics_builder_test.go‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -92,8 +92,8 @@ func TestGetStatsFromConstraint(t *testing.T) {
9292
}
9393

9494
mem := New()
95-
tab := catalog.Table("sel")
96-
tabID := mem.metadata.AddTableWithName(tab, tab.TabName().String())
95+
tab := catalog.Table(tree.NewUnqualifiedTableName("sel"))
96+
tabID := mem.metadata.AddTableWithName(tab, tab.Name().String())
9797

9898
// Test that applyConstraintSet correctly updates the statistics from
9999
// constraint set cs, and selectivity is calculated correctly.

‎pkg/sql/opt/metadata.go‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -222,7 +222,7 @@ func (md *Metadata) ColumnOrdinal(id ColumnID) int {
222222
// references to the same table are assigned different table ids (e.g. in a
223223
// self-join query).
224224
func (md *Metadata) AddTable(tab Table) TableID {
225-
return md.AddTableWithName(tab, string(tab.TabName().TableName))
225+
return md.AddTableWithName(tab, string(tab.Name().TableName))
226226
}
227227

228228
// AddTableWithName indexes a new reference to a table within the query.

‎pkg/sql/opt/metadata_test.go‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -73,7 +73,7 @@ func TestMetadataTables(t *testing.T) {
7373

7474
// Add a table reference to the metadata.
7575
a := &testcat.Table{}
76-
a.Name = tree.MakeUnqualifiedTableName(tree.Name("a"))
76+
a.TabName = tree.MakeUnqualifiedTableName(tree.Name("a"))
7777
x := &testcat.Column{Name: "x"}
7878
y := &testcat.Column{Name: "y"}
7979
a.Columns = append(a.Columns, x, y)
@@ -100,7 +100,7 @@ func TestMetadataTables(t *testing.T) {
100100

101101
// Add a table reference without a name to the metadata.
102102
b := &testcat.Table{}
103-
b.Name = tree.MakeUnqualifiedTableName(tree.Name("b"))
103+
b.TabName = tree.MakeUnqualifiedTableName(tree.Name("b"))
104104
b.Columns = append(b.Columns, &testcat.Column{Name: "x"})
105105

106106
otherTabID := md.AddTable(b)
@@ -130,7 +130,7 @@ func TestIndexColumns(t *testing.T) {
130130
}
131131

132132
md := opt.NewMetadata()
133-
a := md.AddTable(cat.Table("a"))
133+
a := md.AddTable(cat.Table(tree.NewUnqualifiedTableName("a")))
134134

135135
k := int(a.ColumnID(0))
136136
i := int(a.ColumnID(1))

‎pkg/sql/opt/norm/factory_test.go‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -35,12 +35,12 @@ func TestFactoryProjectColsFromBoth(t *testing.T) {
3535
pb := projectionsBuilder{f: f}
3636

3737
cat := createFiltersCatalog(t)
38-
a := f.Metadata().AddTable(cat.Table("a"))
38+
a := f.Metadata().AddTable(cat.Table(tree.NewTableName("t", "a")))
3939
ax := a.ColumnID(0)
4040
ay := a.ColumnID(1)
4141
aCols := util.MakeFastIntSet(int(ax), int(ay))
4242

43-
a2 := f.Metadata().AddTable(cat.Table("a"))
43+
a2 := f.Metadata().AddTable(cat.Table(tree.NewTableName("t", "a")))
4444
a2x := a2.ColumnID(0)
4545
a2y := a2.ColumnID(1)
4646
a2Cols := util.MakeFastIntSet(int(a2x), int(a2y))
@@ -121,7 +121,7 @@ func TestSimplifyFilters(t *testing.T) {
121121
f := NewFactory(&evalCtx)
122122

123123
cat := createFiltersCatalog(t)
124-
a := f.Metadata().AddTable(cat.Table("a"))
124+
a := f.Metadata().AddTable(cat.Table(tree.NewTableName("t", "a")))
125125
ax := a.ColumnID(0)
126126

127127
variable := f.ConstructVariable(f.InternColumnID(ax))

0 commit comments

Comments
 (0)