Skip to content
Merged
Show file tree
Hide file tree
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
1 change: 1 addition & 0 deletions internal/stackql/astindirect/materialized_view_indirect.go
Original file line number Diff line number Diff line change
@@ -1,3 +1,4 @@
//nolint:dupl // mirrors physical_table_indirect.go; consolidation deferred
package astindirect

import (
Expand Down
3 changes: 2 additions & 1 deletion internal/stackql/astindirect/physical_table_indirect.go
Original file line number Diff line number Diff line change
@@ -1,3 +1,4 @@
//nolint:dupl // mirrors materialized_view_indirect.go; consolidation deferred
package astindirect

import (
Expand Down Expand Up @@ -78,7 +79,7 @@ func (v *physicalTable) GetRelationalColumnByIdentifier(name string) (typing.Rel
if col.GetName() == name {
return col, true
}
if col.GetAlias() == name {
if col.GetAlias() != "" && col.GetAlias() == name {
return col, true
}
}
Expand Down
27 changes: 26 additions & 1 deletion internal/stackql/astvisit/query_rewriting.go
Original file line number Diff line number Diff line change
Expand Up @@ -114,6 +114,17 @@ func (v *standardQueryRewriteAstVisitor) isExpressionProjection(node *sqlparser.
return !isColumnReference
}

// cloneRelationalColumn returns a fresh column carrying the catalogued identity
// (name, type, width, oid) of col, so that projection-level decoration such as
// an alias or qualifier never mutates the relation's shared catalogue entry.
func cloneRelationalColumn(col typing.RelationalColumn) typing.RelationalColumn {
rv := typing.NewRelationalColumn(col.GetName(), col.GetType()).WithWidth(col.GetWidth())
if oID, hasOID := col.GetOID(); hasOID {
rv = rv.WithOID(oID)
}
return rv
}

// TODO: introduce dependency on RDBMS
func (v *standardQueryRewriteAstVisitor) getTypeFromParserType(t sqlparser.ValType) string {
//nolint:exhaustive // acceptable
Expand Down Expand Up @@ -685,7 +696,21 @@ func (v *standardQueryRewriteAstVisitor) Visit(node sqlparser.SQLNode) error {
return nil
}

relationalCol, ok := indirect.GetRelationalColumnByIdentifier(col.Name)
// Only a bare column reference binds to the relation's catalogued
// column. An expression over a column (CASE, function call,
// concatenation, ...) is not that column: binding it collapsed the
// projection to the referenced column (issue #798). The catalogued
// column is shared across the whole statement, so it is copied
// before per-projection decoration is applied.
var relationalCol typing.RelationalColumn
var ok bool
if !v.isExpressionProjection(node) {
var catalogueCol typing.RelationalColumn
catalogueCol, ok = indirect.GetRelationalColumnByIdentifier(col.Name)
if ok {
relationalCol = cloneRelationalColumn(catalogueCol)
}
}
if !ok {
if col.Val != nil {
relationalCol = typing.NewRelationalColumn(col.Name, v.getTypeFromParserType(col.Type)).WithDecorated(col.DecoratedColumn)
Expand Down
36 changes: 36 additions & 0 deletions internal/stackql/sql_system/postgres.go
Original file line number Diff line number Diff line change
Expand Up @@ -1306,6 +1306,10 @@ func (eng *postgresSystem) GCCollectObsoleted(minTransactionID int) error {
return eng.gCCollectObsoleted(minTransactionID)
}

// Garbage collection and purge act on provider cache tables only. The physical
// tables behind user space tables and materialized views are user data and are
// excluded via the "__iql__.tables" and "__iql__.materialized_views" catalogues
// (issue #799).
func (eng *postgresSystem) gCCollectObsoleted(minTransactionID int) error {
maxTxnColName := eng.controlAttributes.GetControlMaxTxnColumnName()
obtainQuery := fmt.Sprintf(
Expand All @@ -1322,6 +1326,14 @@ func (eng *postgresSystem) gCCollectObsoleted(minTransactionID int) error {
table_schema = $2
and
table_name not like '__iql__%%'
and
table_name NOT IN (SELECT view_name FROM "__iql__.materialized_views")
and
table_name NOT IN (SELECT table_name FROM "__iql__.tables")
and
(table_schema || '.' || table_name) NOT IN (SELECT view_name FROM "__iql__.materialized_views")
and
(table_schema || '.' || table_name) NOT IN (SELECT table_name FROM "__iql__.tables")
`,
eng.tableSchema,
maxTxnColName,
Expand Down Expand Up @@ -1360,6 +1372,14 @@ func (eng *postgresSystem) gCCollectAll() error {
table_schema = $2
and
table_name not like '__iql__%%'
and
table_name NOT IN (SELECT view_name FROM "__iql__.materialized_views")
and
table_name NOT IN (SELECT table_name FROM "__iql__.tables")
and
(table_schema || '.' || table_name) NOT IN (SELECT view_name FROM "__iql__.materialized_views")
and
(table_schema || '.' || table_name) NOT IN (SELECT table_name FROM "__iql__.tables")
`,
eng.tableSchema,
)
Expand Down Expand Up @@ -1522,6 +1542,14 @@ func (eng *postgresSystem) gcPurgeEphemeral() error {
table_name NOT like $3
and
table_name not like '__iql__%'
and
table_name NOT IN (SELECT view_name FROM "__iql__.materialized_views")
and
table_name NOT IN (SELECT table_name FROM "__iql__.tables")
and
(table_schema || '.' || table_name) NOT IN (SELECT view_name FROM "__iql__.materialized_views")
and
(table_schema || '.' || table_name) NOT IN (SELECT table_name FROM "__iql__.tables")
`
rows, err := eng.sqlEngine.Query(query, eng.tableCatalog, eng.tableSchema, eng.analyticsNamespaceLikeString)
if err != nil {
Expand Down Expand Up @@ -1552,6 +1580,14 @@ func (eng *postgresSystem) purgeAll() error {
table_schema = $2
AND
table_name NOT LIKE '__iql__%'
and
table_name NOT IN (SELECT view_name FROM "__iql__.materialized_views")
and
table_name NOT IN (SELECT table_name FROM "__iql__.tables")
and
(table_schema || '.' || table_name) NOT IN (SELECT view_name FROM "__iql__.materialized_views")
and
(table_schema || '.' || table_name) NOT IN (SELECT table_name FROM "__iql__.tables")
`
deleteQueryResultSet, err := eng.sqlEngine.Query(obtainQuery, eng.tableCatalog, eng.tableSchema)
if err != nil {
Expand Down
20 changes: 20 additions & 0 deletions internal/stackql/sql_system/sqlite.go
Original file line number Diff line number Diff line change
Expand Up @@ -413,6 +413,10 @@ func (eng *sqLiteSystem) obtainRelationalColumnFromExternalSQLtable(
return relationalColumn, nil
}

// Garbage collection and purge act on provider cache tables only. The physical
// tables behind user space tables and materialized views are user data and are
// excluded via the "__iql__.tables" and "__iql__.materialized_views" catalogues
// (issue #799).
func (eng *sqLiteSystem) gCCollectObsoleted(minTransactionID int) error {
maxTxnColName := eng.controlAttributes.GetControlMaxTxnColumnName()
obtainQuery := fmt.Sprintf(
Expand All @@ -427,6 +431,10 @@ func (eng *sqLiteSystem) gCCollectObsoleted(minTransactionID int) error {
name not like '__iql__%%'
and
name NOT LIKE 'sqlite_%%'
and
name NOT IN (SELECT view_name FROM "__iql__.materialized_views")
and
name NOT IN (SELECT table_name FROM "__iql__.tables")
`,
maxTxnColName,
minTransactionID,
Expand Down Expand Up @@ -458,6 +466,10 @@ func (eng *sqLiteSystem) gCCollectAll() error {
name not like '__iql__%%'
and
name NOT LIKE 'sqlite_%%'
and
name NOT IN (SELECT view_name FROM "__iql__.materialized_views")
and
name NOT IN (SELECT table_name FROM "__iql__.tables")
`
deleteQueryResultSet, err := eng.sqlEngine.Query(obtainQuery)
if err != nil {
Expand Down Expand Up @@ -1449,6 +1461,10 @@ func (eng *sqLiteSystem) gcPurgeEphemeral() error {
name not like '__iql__%'
and
name NOT LIKE 'sqlite_%'
and
name NOT IN (SELECT view_name FROM "__iql__.materialized_views")
and
name NOT IN (SELECT table_name FROM "__iql__.tables")
`
rows, err := eng.sqlEngine.Query(query, eng.analyticsNamespaceLikeString)
if err != nil {
Expand Down Expand Up @@ -1481,6 +1497,10 @@ func (eng *sqLiteSystem) purgeAll() error {
name NOT LIKE '__iql__%'
and
name NOT LIKE 'sqlite_%'
and
name NOT IN (SELECT view_name FROM "__iql__.materialized_views")
and
name NOT IN (SELECT table_name FROM "__iql__.tables")
`
deleteQueryResultSet, err := eng.sqlEngine.Query(obtainQuery)
if err != nil {
Expand Down
95 changes: 95 additions & 0 deletions test/robot/functional/stackql_mocked_from_cmd_line.robot
Original file line number Diff line number Diff line change
Expand Up @@ -1251,6 +1251,101 @@ Create Table Scenario Working
... ${outputStr}
... stderr=${CURDIR}/tmp/Create-Table-Scenario-Working.tmp

Select Expressions Over User Space Table and Materialized View Survive Purge
[Documentation] Expressions in a SELECT over user space tables and materialized
... views are evaluated rather than collapsed to the referenced column
... (issue #798), and PURGE leaves user space tables and materialized
... views intact (issue #799).
${inputStr} = Catenate
... create table repos_798 (repo text, enabled text);
... insert into repos_798 (repo, enabled) values ('a', 'true'), ('b', 'false');
... select repo, case when enabled = 'true' then 'pass' else 'fail' end as status from repos_798 order by repo;
... select repo, upper(enabled) as u, enabled || '!' as e from repos_798 order by repo;
... create materialized view mv_798 as select repo, enabled from repos_798;
... select repo, case when enabled = 'true' then 'pass' else 'fail' end as status, enabled || '!' as e from mv_798 order by repo;
... select count(*) as n from mv_798;
... purge conservative;
... select count(*) as n from mv_798;
... purge;
... select count(*) as n from mv_798;
... select count(*) as n from repos_798;
... drop materialized view mv_798;
... drop table repos_798;
${outputStr} = Catenate SEPARATOR=\n
... |------|--------|
... |${SPACE}repo${SPACE}|${SPACE}status${SPACE}|
... |------|--------|
... |${SPACE}a${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}pass${SPACE}${SPACE}${SPACE}|
... |------|--------|
... |${SPACE}b${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}fail${SPACE}${SPACE}${SPACE}|
... |------|--------|
... |------|-------|--------|
... |${SPACE}repo${SPACE}|${SPACE}${SPACE}${SPACE}u${SPACE}${SPACE}${SPACE}|${SPACE}${SPACE}${SPACE}e${SPACE}${SPACE}${SPACE}${SPACE}|
... |------|-------|--------|
... |${SPACE}a${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}TRUE${SPACE}${SPACE}|${SPACE}true!${SPACE}${SPACE}|
... |------|-------|--------|
... |${SPACE}b${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}FALSE${SPACE}|${SPACE}false!${SPACE}|
... |------|-------|--------|
... |------|--------|--------|
... |${SPACE}repo${SPACE}|${SPACE}status${SPACE}|${SPACE}${SPACE}${SPACE}e${SPACE}${SPACE}${SPACE}${SPACE}|
... |------|--------|--------|
... |${SPACE}a${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}pass${SPACE}${SPACE}${SPACE}|${SPACE}true!${SPACE}${SPACE}|
... |------|--------|--------|
... |${SPACE}b${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}fail${SPACE}${SPACE}${SPACE}|${SPACE}false!${SPACE}|
... |------|--------|--------|
... |---|
... |${SPACE}n${SPACE}|
... |---|
... |${SPACE}2${SPACE}|
... |---|
... |--------------------------------|
... |${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}message${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|
... |--------------------------------|
... |${SPACE}PURGE${SPACE}of${SPACE}type${SPACE}'conservative'${SPACE}${SPACE}${SPACE}|
... |${SPACE}successfully${SPACE}completed${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|
... |--------------------------------|
... |---|
... |${SPACE}n${SPACE}|
... |---|
... |${SPACE}2${SPACE}|
... |---|
... |--------------------------|
... |${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}message${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|
... |--------------------------|
... |${SPACE}purge${SPACE}'GLOBAL'${SPACE}completed${SPACE}|
... |--------------------------|
... |---|
... |${SPACE}n${SPACE}|
... |---|
... |${SPACE}2${SPACE}|
... |---|
... |---|
... |${SPACE}n${SPACE}|
... |---|
... |${SPACE}2${SPACE}|
... |---|
${stdErrStr} = Catenate SEPARATOR=\n
... DDL Execution Completed
... insert into table completed
... DDL Execution Completed
... Global PURGE successfully completed
... DDL Execution Completed
... DDL Execution Completed
Should Stackql Exec Inline Equal Both Streams
... ${STACKQL_EXE}
... ${OKTA_SECRET_STR}
... ${GITHUB_SECRET_STR}
... ${K8S_SECRET_STR}
... ${REGISTRY_NO_VERIFY_CFG_STR}
... ${AUTH_CFG_STR}
... ${SQL_BACKEND_CFG_STR_CANONICAL}
... ${inputStr}
... ${outputStr}
... ${stdErrStr}
... stdout=${CURDIR}/tmp/Select-Expressions-Over-User-Space-Table-and-Materialized-View-Survive-Purge.tmp
... stderr=${CURDIR}/tmp/Select-Expressions-Over-User-Space-Table-and-Materialized-View-Survive-Purge-stderr.tmp


Create Static Materialized View Scenario Working
${inputStr} = Catenate
... create materialized view mv_one as select 1 as one;
Expand Down