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
7 changes: 4 additions & 3 deletions internal/stackql/astvisit/from_rewrite.go
Original file line number Diff line number Diff line change
Expand Up @@ -13,6 +13,7 @@ import (
"github.com/stackql/stackql/internal/stackql/tablenamespace"
"github.com/stackql/stackql/internal/stackql/taxonomy"
"github.com/stackql/stackql/pkg/astformat"
"github.com/stackql/stackql/pkg/textutil"

"github.com/stackql/stackql-parser/go/sqltypes"
"github.com/stackql/stackql-parser/go/vt/sqlparser"
Expand Down Expand Up @@ -676,14 +677,14 @@ func (v *standardFromRewriteAstVisitor) Visit(node sqlparser.SQLNode) error {
if !node.As.IsEmpty() {
viewAlias = node.As.GetRawVal()
}
templateString := fmt.Sprintf(` ( %%s ) AS "%s" `, viewAlias)
templateString := fmt.Sprintf(` ( %s ) AS "%s" `, textutil.IndirectQueryPlaceholder, viewAlias)
v.rewrittenQuery = templateString
v.indirectContexts = append(v.indirectContexts, indirect.GetSelectContext())
aliasHandledByIndirect = true
case astindirect.SubqueryType:
// Note: CTEs are converted to SubqueryType at AST level,
// so this path handles both regular subqueries and CTEs.
templateString := ` ( %s ) `
templateString := " ( " + textutil.IndirectQueryPlaceholder + " ) "
v.rewrittenQuery = templateString
v.indirectContexts = append(v.indirectContexts, indirect.GetSelectContext())
case astindirect.MaterializedViewType, astindirect.PhysicalTableType:
Expand Down Expand Up @@ -801,7 +802,7 @@ func (v *standardFromRewriteAstVisitor) Visit(node sqlparser.SQLNode) error {
v.hoistedOnClauseTables = append(v.hoistedOnClauseTables, lhsHoistedIntoOn...)
v.hoistedOnClauseTables = append(v.hoistedOnClauseTables, rhsHoistedIntoOn...)
if len(v.hoistedOnClauseTables) > 0 {
v.rewrittenQuery = fmt.Sprintf("%s %s %s ON ( %%s ) AND %s", lVis.GetRewrittenQuery(), node.Join, rVis.GetRewrittenQuery(), conditionVis.GetRewrittenQuery())
v.rewrittenQuery = fmt.Sprintf("%s %s %s ON ( %s ) AND %s", lVis.GetRewrittenQuery(), node.Join, rVis.GetRewrittenQuery(), textutil.ControlOnClausePlaceholder, conditionVis.GetRewrittenQuery())
} else {
v.rewrittenQuery = fmt.Sprintf("%s %s %s ON %s", lVis.GetRewrittenQuery(), node.Join, rVis.GetRewrittenQuery(), conditionVis.GetRewrittenQuery())
}
Expand Down
5 changes: 3 additions & 2 deletions internal/stackql/astvisit/specialisations.go
Original file line number Diff line number Diff line change
Expand Up @@ -9,6 +9,7 @@ import (
"github.com/stackql/stackql/internal/stackql/parserutil"
"github.com/stackql/stackql/internal/stackql/sql_system"
"github.com/stackql/stackql/internal/stackql/tablenamespace"
"github.com/stackql/stackql/pkg/textutil"
)

//nolint:errcheck // defer analyser uplifts
Expand Down Expand Up @@ -75,9 +76,9 @@ func GenerateUnionTemplateQuery(
v := NewFragmentRewriteAstVisitor(annotatedAST, "", false, sqlSystem, formatter, namespaceCollection)

var sb strings.Builder
sb.WriteString("%s ")
sb.WriteString(textutil.IndirectQueryPlaceholder + " ")
for _, unionSelect := range node.UnionSelects {
sb.WriteString(fmt.Sprintf("%s %%s ", unionSelect.Type))
sb.WriteString(fmt.Sprintf("%s %s ", unionSelect.Type, textutil.IndirectQueryPlaceholder))
}

var orderByStr, limitStr string
Expand Down
7 changes: 4 additions & 3 deletions internal/stackql/drm/prepared_statement_args.go
Original file line number Diff line number Diff line change
@@ -1,8 +1,9 @@
package drm

import (
"fmt"
"sort"

"github.com/stackql/stackql/pkg/textutil"
)

var (
Expand Down Expand Up @@ -82,7 +83,7 @@ func (ca *standardPreparedStatementArgs) Analyze() error {
func (ca *standardPreparedStatementArgs) compose() (childQueryComposition, error) {
var varArgs []interface{}
query := ca.GetQuery()
var childQueryStrings []interface{}
var childQueryStrings []string
var keys []int
for i := range ca.GetChildren() {
keys = append(keys, i)
Expand All @@ -98,7 +99,7 @@ func (ca *standardPreparedStatementArgs) compose() (childQueryComposition, error
varArgs = append(varArgs, childResponse.GetVarArgs()...)
}
if len(childQueryStrings) > 0 {
query = fmt.Sprintf(ca.GetQuery(), childQueryStrings...)
query = textutil.ExpandPlaceholders(query, textutil.IndirectQueryPlaceholder, childQueryStrings)
}
varArgs = append(varArgs, ca.GetArgs()...)
return newChildQueryComposition(query, varArgs), nil
Expand Down
17 changes: 7 additions & 10 deletions internal/stackql/sql_system/postgres.go
Original file line number Diff line number Diff line change
Expand Up @@ -22,6 +22,7 @@ import (
"github.com/stackql/stackql/internal/stackql/internal_data_transfer/relationaldto"
"github.com/stackql/stackql/internal/stackql/typing"
"github.com/stackql/stackql/pkg/serde"
"github.com/stackql/stackql/pkg/textutil"
)

func newPostgresSystem(
Expand Down Expand Up @@ -1093,7 +1094,7 @@ func (eng *postgresSystem) composeSelectQuery(
var wq strings.Builder
var controlWhereComparisons []string
i := parameterOffset
var hoistedControlOnComparisons []any
var hoistedControlOnComparisons []string
if len(hoistedTableAliases) > 0 {
for _, alias := range hoistedTableAliases {
hoistedControlOnComparisons = append(
Expand All @@ -1102,15 +1103,11 @@ func (eng *postgresSystem) composeSelectQuery(
)
i += len(aliasToCountersMap[alias])
}
// BLOCK protect LHS string formats for indirect replacement
remainingStringFormats := strings.Count(fromString, `%s`)
diffCount := remainingStringFormats - len(hoistedControlOnComparisons)
if diffCount > 0 {
fromString = fmt.Sprintf(strings.Replace(fromString, `%s`, `%%s`, diffCount), hoistedControlOnComparisons...)
} else {
fromString = fmt.Sprintf(fromString, hoistedControlOnComparisons...)
}
// END BLOCK
fromString = textutil.ExpandPlaceholders(
fromString,
textutil.ControlOnClausePlaceholder,
hoistedControlOnComparisons,
)
}
for _, alias := range tableAliases {
controlWhereComparisons = append(controlWhereComparisons, eng.render(alias, i, aliasToCountersMap))
Expand Down
17 changes: 7 additions & 10 deletions internal/stackql/sql_system/sqlite.go
Original file line number Diff line number Diff line change
Expand Up @@ -23,6 +23,7 @@ import (
"github.com/stackql/stackql/internal/stackql/typing"

"github.com/stackql/stackql/pkg/serde"
"github.com/stackql/stackql/pkg/textutil"
)

func newSQLiteSystem(
Expand Down Expand Up @@ -1217,7 +1218,7 @@ func (eng *sqLiteSystem) composeSelectQuery(
quotedColNames = append(quotedColNames, col.CanonicalSelectionString())
}
var wq strings.Builder
var hoistedControlOnComparisons []any
var hoistedControlOnComparisons []string
i := 0
if len(hoistedTableAliases) > 0 {
for _, alias := range hoistedTableAliases {
Expand All @@ -1227,15 +1228,11 @@ func (eng *sqLiteSystem) composeSelectQuery(
)
i++
}
// BLOCK protect LHS string formats for indirect replacement
remainingStringFormats := strings.Count(fromString, `%s`)
diffCount := remainingStringFormats - len(hoistedControlOnComparisons)
if diffCount > 0 {
fromString = fmt.Sprintf(strings.Replace(fromString, `%s`, `%%s`, diffCount), hoistedControlOnComparisons...)
} else {
fromString = fmt.Sprintf(fromString, hoistedControlOnComparisons...)
}
// END BLOCK
fromString = textutil.ExpandPlaceholders(
fromString,
textutil.ControlOnClausePlaceholder,
hoistedControlOnComparisons,
)
}
var controlWhereComparisons []string
for _, alias := range tableAliases {
Expand Down
25 changes: 25 additions & 0 deletions pkg/textutil/textutil.go
Original file line number Diff line number Diff line change
Expand Up @@ -2,6 +2,12 @@ package textutil

import (
"regexp"
"strings"
)

const (
IndirectQueryPlaceholder = "__iql__placeholder_indirect_query__"
ControlOnClausePlaceholder = "__iql__placeholder_control_on_clause__"
)

var (
Expand All @@ -11,3 +17,22 @@ var (
func GetTemplateLikeString(templateString string) string {
return namespaceLikeStringRegex.ReplaceAllString(templateString, "%")
}

func ExpandPlaceholders(template string, placeholder string, replacements []string) string {
if placeholder == "" || len(replacements) == 0 {
return template
}
var expanded strings.Builder
remainder := template
for _, replacement := range replacements {
idx := strings.Index(remainder, placeholder)
if idx < 0 {
break
}
expanded.WriteString(remainder[:idx])
expanded.WriteString(replacement)
remainder = remainder[idx+len(placeholder):]
}
expanded.WriteString(remainder)
return expanded.String()
}
55 changes: 55 additions & 0 deletions pkg/textutil/textutil_test.go
Original file line number Diff line number Diff line change
Expand Up @@ -38,3 +38,58 @@ func TestGetTemplateLikeString(t *testing.T) {
})
}
}

func TestExpandPlaceholders(t *testing.T) {
cases := []struct {
name string
template string
placeholder string
replacements []string
want string
}{
{
name: "percent signs in like patterns survive verbatim",
template: `SELECT a FROM ( ` + textutil.IndirectQueryPlaceholder + ` ) AS "v" WHERE a NOT LIKE '%TLS12%'`,
placeholder: textutil.IndirectQueryPlaceholder,
replacements: []string{"SELECT b AS a FROM t"},
want: `SELECT a FROM ( SELECT b AS a FROM t ) AS "v" WHERE a NOT LIKE '%TLS12%'`,
},
{
name: "successive placeholders expand left to right",
template: textutil.IndirectQueryPlaceholder + " union " + textutil.IndirectQueryPlaceholder + " ",
placeholder: textutil.IndirectQueryPlaceholder,
replacements: []string{"SELECT 1", "SELECT 2"},
want: "SELECT 1 union SELECT 2 ",
},
{
name: "replacement text is not rescanned for placeholders",
template: "a " + textutil.IndirectQueryPlaceholder + " b",
placeholder: textutil.IndirectQueryPlaceholder,
replacements: []string{textutil.IndirectQueryPlaceholder, "surplus"},
want: "a " + textutil.IndirectQueryPlaceholder + " b",
},
{
name: "surplus placeholders are left intact",
template: "a " + textutil.IndirectQueryPlaceholder + " b " + textutil.IndirectQueryPlaceholder,
placeholder: textutil.IndirectQueryPlaceholder,
replacements: []string{"x"},
want: "a x b " + textutil.IndirectQueryPlaceholder,
},
{
name: "no replacements returns template unchanged",
template: "a " + textutil.IndirectQueryPlaceholder,
placeholder: textutil.IndirectQueryPlaceholder,
replacements: nil,
want: "a " + textutil.IndirectQueryPlaceholder,
},
}

for _, tc := range cases {
t.Run(tc.name, func(t *testing.T) {
got := textutil.ExpandPlaceholders(tc.template, tc.placeholder, tc.replacements)
if got != tc.want {
t.Errorf("ExpandPlaceholders() = %q, want %q", got, tc.want)
}
})
}
}
61 changes: 59 additions & 2 deletions test/robot/functional/stackql_mocked_from_cmd_line.robot
Original file line number Diff line number Diff line change
Expand Up @@ -3902,11 +3902,68 @@ Mutable View Select With Path Parameters inside IN Scalars inside WHERE Clause R
... ${outputStr}
... stdout=${CURDIR}/tmp/Mutable-View-Select-With-Path-Parameters-inside-IN-Scalars-inside-WHERE-Clause-Returns-Expected-Result.tmp
... stderr=${CURDIR}/tmp/Mutable-View-Select-With-Path-Parameters-inside-IN-Scalars-inside-WHERE-Clause-Returns-Expected-Result-stderr.tmp
... repeat_count=20
... repeat_count=20

Mutable View Select With LIKE And NOT LIKE Wildcards inside WHERE Clause Returns Expected Result
${inputStr} = Catenate
... create or replace view mutable_view_two as
... select
... kind,
... name,
... maximumCardsPerInstance,
... project
... from google.compute.acceleratorTypes
... where
... project = 'rubbish-project'
... and
... zone = 'australia-southeast1-a'
... ;
... select
... kind, name, maximumCardsPerInstance, project
... from mutable_view_two
... where project in ('testing-project', 'another-project')
... and name like '%tesla%'
... and name not like '%rubbish%'
... order by name desc, project desc
... ;
... drop view mutable_view_two
... ;
${outputStr} = Catenate SEPARATOR=\n
... |-------------------------|---------------------|-------------------------|-----------------|
... |${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}kind${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}name${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}maximumCardsPerInstance${SPACE}|${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}project${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|
... |-------------------------|---------------------|-------------------------|-----------------|
... |${SPACE}compute#acceleratorType${SPACE}|${SPACE}nvidia-tesla-t4-vws${SPACE}|${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}4${SPACE}|${SPACE}testing-project${SPACE}|
... |-------------------------|---------------------|-------------------------|-----------------|
... |${SPACE}compute#acceleratorType${SPACE}|${SPACE}nvidia-tesla-t4-vws${SPACE}|${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}4${SPACE}|${SPACE}another-project${SPACE}|
... |-------------------------|---------------------|-------------------------|-----------------|
... |${SPACE}compute#acceleratorType${SPACE}|${SPACE}nvidia-tesla-t4${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}4${SPACE}|${SPACE}testing-project${SPACE}|
... |-------------------------|---------------------|-------------------------|-----------------|
... |${SPACE}compute#acceleratorType${SPACE}|${SPACE}nvidia-tesla-t4${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}4${SPACE}|${SPACE}another-project${SPACE}|
... |-------------------------|---------------------|-------------------------|-----------------|
... |${SPACE}compute#acceleratorType${SPACE}|${SPACE}nvidia-tesla-p4-vws${SPACE}|${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}4${SPACE}|${SPACE}testing-project${SPACE}|
... |-------------------------|---------------------|-------------------------|-----------------|
... |${SPACE}compute#acceleratorType${SPACE}|${SPACE}nvidia-tesla-p4-vws${SPACE}|${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}4${SPACE}|${SPACE}another-project${SPACE}|
... |-------------------------|---------------------|-------------------------|-----------------|
... |${SPACE}compute#acceleratorType${SPACE}|${SPACE}nvidia-tesla-p4${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}4${SPACE}|${SPACE}testing-project${SPACE}|
... |-------------------------|---------------------|-------------------------|-----------------|
... |${SPACE}compute#acceleratorType${SPACE}|${SPACE}nvidia-tesla-p4${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}4${SPACE}|${SPACE}another-project${SPACE}|
... |-------------------------|---------------------|-------------------------|-----------------|
Should StackQL Exec Inline Equal
... ${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}
... stdout=${CURDIR}/tmp/Mutable-View-Select-With-LIKE-And-NOT-LIKE-Wildcards-inside-WHERE-Clause-Returns-Expected-Result.tmp
... stderr=${CURDIR}/tmp/Mutable-View-Select-With-LIKE-And-NOT-LIKE-Wildcards-inside-WHERE-Clause-Returns-Expected-Result-stderr.tmp

Select With Path Parameters inside IN Scalars Mixed With an Equals Parameter all inside WHERE Clause Returns Expected Result
${inputStr} = Catenate
... select
... select
... id,
... name
... from google.compute.acceleratorTypes
Expand Down
Loading