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
8 changes: 4 additions & 4 deletions internal/stackql/astanalysis/earlyanalysis/ast_expand.go
Original file line number Diff line number Diff line change
Expand Up @@ -993,11 +993,11 @@ func (v *indirectExpandAstVisitor) Visit(node sqlparser.SQLNode) error {
v.containsNativeBackendMaterial = true

case sqlparser.JoinCondition:
err := node.On.Accept(v)
if err != nil {
return err
}
if node.On != nil {
err := node.On.Accept(v)
if err != nil {
return err
}
buf.AstPrintf(node, " on %v", node.On)
}
if node.Using != nil {
Expand Down
23 changes: 18 additions & 5 deletions internal/stackql/astvisit/from_rewrite.go
Original file line number Diff line number Diff line change
Expand Up @@ -23,6 +23,9 @@ var (
_ FromRewriteAstVisitor = &standardFromRewriteAstVisitor{}
)

// The parser folds CROSS JOIN into sqlparser.JoinStr, so it is re-emitted here.
const crossJoinStr = "cross join"

type FromRewriteAstVisitor interface {
sqlparser.SQLAstVisitor
GetIndirectContexts() []drm.PreparedStatementCtx
Expand Down Expand Up @@ -801,11 +804,21 @@ func (v *standardFromRewriteAstVisitor) Visit(node sqlparser.SQLNode) error {
// accumulate hoisted tables
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(), textutil.ControlOnClausePlaceholder, conditionVis.GetRewrittenQuery())
} else {
v.rewrittenQuery = fmt.Sprintf("%s %s %s ON %s", lVis.GetRewrittenQuery(), node.Join, rVis.GetRewrittenQuery(), conditionVis.GetRewrittenQuery())
}
joinStr := node.Join
hasCondition := node.Condition.On != nil || node.Condition.Using != nil
var onClause string
switch {
case len(v.hoistedOnClauseTables) > 0 && hasCondition:
onClause = fmt.Sprintf(" ON ( %s ) AND %s", textutil.ControlOnClausePlaceholder, conditionVis.GetRewrittenQuery())
case len(v.hoistedOnClauseTables) > 0:
onClause = fmt.Sprintf(" ON ( %s )", textutil.ControlOnClausePlaceholder)
case hasCondition:
onClause = fmt.Sprintf(" ON %s", conditionVis.GetRewrittenQuery())
case joinStr == sqlparser.JoinStr:
// JOIN without ON is a cross join; postgres requires it spelled out
joinStr = crossJoinStr
}
v.rewrittenQuery = fmt.Sprintf("%s %s %s%s", lVis.GetRewrittenQuery(), joinStr, rVis.GetRewrittenQuery(), onClause)

case *sqlparser.IndexHints:
buf.AstPrintf(node, " %sindex ", node.Type)
Expand Down
4 changes: 3 additions & 1 deletion internal/stackql/astvisit/param_extract.go
Original file line number Diff line number Diff line change
Expand Up @@ -654,7 +654,9 @@ func (v *standardParserParamAstVisitor) Visit(node sqlparser.SQLNode) error {
case *sqlparser.JoinTableExpr:
node.LeftExpr.Accept(v)
node.RightExpr.Accept(v)
node.Condition.On.Accept(v)
if node.Condition.On != nil {
node.Condition.On.Accept(v)
}
buf.AstPrintf(node, "%v %s %v%v", node.LeftExpr, node.Join, node.RightExpr, node.Condition)

case *sqlparser.IndexHints:
Expand Down
56 changes: 56 additions & 0 deletions test/robot/functional/stackql_mocked_from_cmd_line.robot
Original file line number Diff line number Diff line change
Expand Up @@ -2656,6 +2656,62 @@ Inner Join Users Cross Cloud Fuzzy Match
... ${outputStr}
... stdout=${CURDIR}/tmp/Inner-Join-Users-Cross-Cloud-Fuzzy-Match.tmp

Inner Join Without ON Clause Is Treated As Cross Join
${sqliteInputStr} = Catenate
... select
... aid.UserName as aws_user_name
... ,json_extract(gad.name, '$.fullName') as gcp_user_name
... from
... aws.iam.users aid
... JOIN
... googleadmin.directory.users gad
... WHERE
... aid.region = 'us-east-1'
... AND
... gad.domain = 'grubit.com'
... AND
... lower(substr(aid.UserName, 1, 5)) = lower(substr(json_extract(gad.name, '$.fullName'), 1, 5))
... ORDER BY
... aws_user_name DESC
... ;
${postgresInputStr} = Catenate
... select
... aid.UserName as aws_user_name
... ,json_extract_path_text(gad.name, 'fullName') as gcp_user_name
... from
... aws.iam.users aid
... JOIN
... googleadmin.directory.users gad
... WHERE
... aid.region = 'us-east-1'
... AND
... gad.domain = 'grubit.com'
... AND
... lower(substr(aid.UserName, 1, 5)) = lower(substr(json_extract_path_text(gad.name, 'fullName'), 1, 5))
... ORDER BY
... aws_user_name DESC
... ;
${inputStr} = Set Variable If "${SQL_BACKEND}" == "postgres_tcp" ${postgresInputStr} ${sqliteInputStr}
${outputStr} = Catenate SEPARATOR=\n
... |---------------|----------------|
... |${SPACE}aws_user_name${SPACE}|${SPACE}gcp_user_name${SPACE}${SPACE}|
... |---------------|----------------|
... |${SPACE}Jackie${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}Jackie${SPACE}Citizen${SPACE}|
... |---------------|----------------|
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}
... ${EMPTY}
... stdout=${CURDIR}/tmp/Inner-Join-Without-ON-Clause-Is-Treated-As-Cross-Join.tmp
... stderr=${CURDIR}/tmp/Inner-Join-Without-ON-Clause-Is-Treated-As-Cross-Join-stderr.tmp

Google Admin Directory Small Response Also De Facto Credentials Path Env Var
Set Environment Variable GOOGLE_APPLICATION_CREDENTIALS ${GOOGLE_APPLICATION_CREDENTIALS}
${inputStr} = Catenate
Expand Down
Loading