github.com/siglens/siglens@v0.0.0-20240328180423-f7ce9ae441ed/pkg/ast/sql/sqlparser_test.go (about)

     1  /*
     2  Copyright 2023.
     3  
     4  Licensed under the Apache License, Version 2.0 (the "License");
     5  you may not use this file except in compliance with the License.
     6  You may obtain a copy of the License at
     7  
     8      http://www.apache.org/licenses/LICENSE-2.0
     9  
    10  Unless required by applicable law or agreed to in writing, software
    11  distributed under the License is distributed on an "AS IS" BASIS,
    12  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    13  See the License for the specific language governing permissions and
    14  limitations under the License.
    15  */
    16  
    17  package sql
    18  
    19  import (
    20  	"testing"
    21  
    22  	query "github.com/siglens/siglens/pkg/es/query"
    23  	"github.com/siglens/siglens/pkg/segment/structs"
    24  	"github.com/siglens/siglens/pkg/segment/utils"
    25  	"github.com/stretchr/testify/assert"
    26  )
    27  
    28  func Test_ParseSelect(t *testing.T) {
    29  	query_string := "SELECT COUNT(latency) FROM `*` GROUP BY country"
    30  	_, aggs, _, err := ConvertToASTNodeSQL(query_string, 0)
    31  	assert.Nil(t, err)
    32  
    33  	testMeasureOp := append(make([]*structs.MeasureAggregator, 0), &structs.MeasureAggregator{
    34  		MeasureCol: "latency", MeasureFunc: utils.Count,
    35  	})
    36  	testGroupBy := &structs.GroupByRequest{GroupByColumns: make([]string, 0), MeasureOperations: make([]*structs.MeasureAggregator, 0), BucketCount: 100}
    37  	testGroupBy.GroupByColumns = append(testGroupBy.GroupByColumns, "country")
    38  	testGroupBy.MeasureOperations = append(testGroupBy.MeasureOperations, &structs.MeasureAggregator{
    39  		MeasureCol: "latency", MeasureFunc: utils.Count,
    40  	})
    41  	_, err = query.GetMatchAllASTNode(0)
    42  
    43  	assert.Nil(t, err)
    44  	assert.Equal(t, aggs.GroupByRequest, testGroupBy)
    45  	assert.Equal(t, aggs.MeasureOperations, testMeasureOp)
    46  }
    47  
    48  func Test_ParseSelectAliased(t *testing.T) {
    49  	testRenameColumns := make(map[string]string, 0)
    50  	query_string := "select batch as bt from ind-0"
    51  	_, aggs, _, err := ConvertToASTNodeSQL(query_string, 0)
    52  	assert.Nil(t, err)
    53  	testRenameColumns["batch"] = "bt"
    54  	assert.Equal(t, aggs.OutputTransforms.OutputColumns.RenameColumns, testRenameColumns)
    55  
    56  	query_string = "select city, batch as bt from ind-0"
    57  	_, aggs, _, err = ConvertToASTNodeSQL(query_string, 0)
    58  	assert.Nil(t, err)
    59  	assert.Equal(t, aggs.OutputTransforms.OutputColumns.RenameColumns, testRenameColumns)
    60  	assert.Equal(t, aggs.OutputTransforms.OutputColumns.IncludeColumns[0], "city")
    61  
    62  	query_string = "select batch as bt from `ind-0`"
    63  	_, aggs, _, err = ConvertToASTNodeSQL(query_string, 0)
    64  	assert.Nil(t, err)
    65  	assert.Equal(t, aggs.OutputTransforms.OutputColumns.RenameColumns, testRenameColumns)
    66  
    67  	query_string = "select batch as bt, city as ct from `*`"
    68  	_, aggs, _, err = ConvertToASTNodeSQL(query_string, 0)
    69  	assert.Nil(t, err)
    70  	testRenameColumns["city"] = "ct"
    71  	assert.Equal(t, aggs.OutputTransforms.OutputColumns.RenameColumns, testRenameColumns)
    72  
    73  	query_string = "select batch as bt, city as `ct` from ind-0"
    74  	_, aggs, _, err = ConvertToASTNodeSQL(query_string, 0)
    75  	assert.Nil(t, err)
    76  	assert.Equal(t, aggs.OutputTransforms.OutputColumns.RenameColumns, testRenameColumns)
    77  
    78  	query_string = "select batch as bt, city as `ct`, country as cy, host from ind-0"
    79  	_, aggs, _, err = ConvertToASTNodeSQL(query_string, 0)
    80  	testRenameColumns["country"] = "cy"
    81  	assert.Nil(t, err)
    82  	assert.Equal(t, aggs.OutputTransforms.OutputColumns.RenameColumns, testRenameColumns)
    83  	assert.Equal(t, aggs.OutputTransforms.OutputColumns.IncludeColumns[3], "host")
    84  
    85  }
    86  
    87  func Test_ParseOrderBy(t *testing.T) {
    88  	query_string := "select batch as bt, city as `ct`, country as cy, host from ind-0 order by batch"
    89  	_, aggs, _, err := ConvertToASTNodeSQL(query_string, 0)
    90  	assert.Nil(t, err)
    91  	assert.Equal(t, aggs.Sort.Ascending, true)
    92  	assert.Equal(t, aggs.Sort.ColName, "batch")
    93  
    94  	query_string = "select batch as bt, city as `ct`, country as cy, host from ind-0 order by batch desc"
    95  	_, aggs, _, err = ConvertToASTNodeSQL(query_string, 0)
    96  	assert.Nil(t, err)
    97  	assert.Equal(t, aggs.Sort.Ascending, false)
    98  	assert.Equal(t, aggs.Sort.ColName, "batch")
    99  
   100  	query_string = "select batch as bt, city as `ct`, country as cy, host from ind-0 order by batch asc"
   101  	_, aggs, _, err = ConvertToASTNodeSQL(query_string, 0)
   102  	assert.Nil(t, err)
   103  	assert.Equal(t, aggs.Sort.Ascending, true)
   104  	assert.Equal(t, aggs.Sort.ColName, "batch")
   105  
   106  }
   107  
   108  func Test_ParseShow(t *testing.T) {
   109  	query_show := "show columns from `ind-0`"
   110  	_, aggs, _, err := ConvertToASTNodeSQL(query_show, 0)
   111  	assert.Nil(t, err)
   112  	testShowRequest := &structs.ShowRequest{ColumnsRequest: &structs.ShowColumns{InTable: "ind-0"}}
   113  	assert.Equal(t, aggs.ShowRequest, testShowRequest)
   114  
   115  	query_describe := "describe ind-0"
   116  	_, aggs, _, err = ConvertToASTNodeSQL(query_describe, 0)
   117  	assert.Nil(t, err)
   118  	assert.Equal(t, aggs.ShowRequest, testShowRequest)
   119  }
   120  
   121  func Test_ParseGroupByRound(t *testing.T) {
   122  	query_string := "SELECT ROUND(sum(latitude), 2) FROM `*` GROUP BY city"
   123  
   124  	_, aggs, _, err := ConvertToASTNodeSQL(query_string, 0)
   125  
   126  	assert.Nil(t, err)
   127  
   128  	assert.NotNil(t, aggs.OutputTransforms.LetColumns)
   129  	assert.NotNil(t, aggs.OutputTransforms.LetColumns.ValueColRequest)
   130  	assert.NotNil(t, aggs.OutputTransforms.LetColumns.ValueColRequest.NumericExpr)
   131  
   132  	leftExpr := aggs.OutputTransforms.LetColumns.ValueColRequest.NumericExpr.Left
   133  	rightExpr := aggs.OutputTransforms.LetColumns.ValueColRequest.NumericExpr.Right
   134  	Op := aggs.OutputTransforms.LetColumns.ValueColRequest.NumericExpr.Op
   135  
   136  	assert.Equal(t, Op, "round")
   137  	assert.Equal(t, leftExpr.Value, "sum(latitude)")
   138  	assert.Equal(t, rightExpr.Value, "2")
   139  	assert.Equal(t, leftExpr.ValueIsField, true)
   140  	assert.Equal(t, rightExpr.ValueIsField, false)
   141  	assert.Nil(t, leftExpr.Val)
   142  	assert.Equal(t, aggs.OutputTransforms.LetColumns.NewColName, "round(sum(latitude))")
   143  
   144  	// round with alias
   145  	query_string = "SELECT ROUND(sum(latitude), 2) as lat_sum FROM `*` GROUP BY city"
   146  	_, aggs, _, err = ConvertToASTNodeSQL(query_string, 0)
   147  
   148  	assert.Nil(t, err)
   149  
   150  	assert.NotNil(t, aggs.OutputTransforms.LetColumns)
   151  
   152  	assert.NotNil(t, aggs.OutputTransforms.LetColumns.ValueColRequest)
   153  
   154  	assert.NotNil(t, aggs.OutputTransforms.LetColumns.ValueColRequest.NumericExpr)
   155  
   156  	leftExpr = aggs.OutputTransforms.LetColumns.ValueColRequest.NumericExpr.Left
   157  	rightExpr = aggs.OutputTransforms.LetColumns.ValueColRequest.NumericExpr.Right
   158  	Op = aggs.OutputTransforms.LetColumns.ValueColRequest.NumericExpr.Op
   159  
   160  	assert.Equal(t, Op, "round")
   161  	assert.Equal(t, leftExpr.Value, "sum(latitude)")
   162  	assert.Equal(t, rightExpr.Value, "2")
   163  	assert.Equal(t, leftExpr.ValueIsField, true)
   164  	assert.Equal(t, rightExpr.ValueIsField, false)
   165  	assert.Nil(t, leftExpr.Val)
   166  	assert.Equal(t, aggs.OutputTransforms.LetColumns.NewColName, "lat_sum")
   167  }
   168  
   169  func Test_ParseMathFunctions(t *testing.T) {
   170  	query_string := "SELECT city, ROUND(latitude, 2)"
   171  
   172  	_, aggs, _, err := ConvertToASTNodeSQL(query_string, 0)
   173  
   174  	assert.Nil(t, err)
   175  	assert.NotNil(t, aggs.MathOperations)
   176  	assert.Equal(t, aggs.MathOperations[0].MathCol, "latitude")
   177  	assert.Equal(t, aggs.MathOperations[0].MathFunc, utils.Round)
   178  	assert.Equal(t, aggs.MathOperations[0].ValueColRequest.NumericExpr.Op, "round")
   179  	assert.Equal(t, aggs.MathOperations[0].ValueColRequest.NumericExpr.Left.Value, "latitude")
   180  	assert.Equal(t, aggs.MathOperations[0].ValueColRequest.NumericExpr.Right.Value, "2")
   181  
   182  	// math function with alias
   183  	query_string = "SELECT city, ROUND(latitude, 2) as lat_round"
   184  
   185  	_, aggs, _, err = ConvertToASTNodeSQL(query_string, 0)
   186  
   187  	assert.Nil(t, err)
   188  	assert.NotNil(t, aggs.MathOperations)
   189  	assert.Equal(t, aggs.MathOperations[0].MathCol, "latitude")
   190  	assert.Equal(t, aggs.MathOperations[0].MathFunc, utils.Round)
   191  	assert.Equal(t, aggs.MathOperations[0].ValueColRequest.NumericExpr.Op, "round")
   192  	assert.Equal(t, aggs.MathOperations[0].ValueColRequest.NumericExpr.Left.Value, "latitude")
   193  	assert.Equal(t, aggs.MathOperations[0].ValueColRequest.NumericExpr.Right.Value, "2")
   194  	assert.Equal(t, aggs.OutputTransforms.OutputColumns.RenameColumns["latitude"], "lat_round")
   195  
   196  	// another math function: abs
   197  	query_string = "SELECT city, ABS(latitude) as lat_abs"
   198  
   199  	_, aggs, _, err = ConvertToASTNodeSQL(query_string, 0)
   200  
   201  	assert.Nil(t, err)
   202  	assert.NotNil(t, aggs.MathOperations)
   203  	assert.Equal(t, aggs.MathOperations[0].MathCol, "latitude")
   204  	assert.Equal(t, aggs.MathOperations[0].MathFunc, utils.Abs)
   205  	assert.Equal(t, aggs.MathOperations[0].ValueColRequest.NumericExpr.Op, "abs")
   206  	assert.Equal(t, aggs.MathOperations[0].ValueColRequest.NumericExpr.Left.Value, "latitude")
   207  	assert.Equal(t, aggs.OutputTransforms.OutputColumns.RenameColumns["latitude"], "lat_abs")
   208  }