github.com/ydb-platform/ydb-go-sdk/v3@v3.57.0/internal/bind/numeric_args_test.go (about)

     1  package bind
     2  
     3  import (
     4  	"testing"
     5  	"time"
     6  
     7  	"github.com/stretchr/testify/require"
     8  
     9  	"github.com/ydb-platform/ydb-go-sdk/v3/table"
    10  	"github.com/ydb-platform/ydb-go-sdk/v3/table/types"
    11  )
    12  
    13  func TestNumericArgsBindRewriteQuery(t *testing.T) {
    14  	var (
    15  		now = time.Now()
    16  		b   = NumericArgs{}
    17  	)
    18  	for _, tt := range []struct {
    19  		sql    string
    20  		args   []interface{}
    21  		yql    string
    22  		params []interface{}
    23  		err    error
    24  	}{
    25  		{
    26  			sql: `SELECT $123abc, $2`,
    27  			args: []interface{}{
    28  				100,
    29  			},
    30  			err: ErrInconsistentArgs,
    31  		},
    32  		{
    33  			sql: `SELECT $123abc, $1`,
    34  			args: []interface{}{
    35  				200,
    36  			},
    37  			yql: `-- origin query with numeric args replacement
    38  SELECT $123abc, $p0`,
    39  			params: []interface{}{
    40  				table.ValueParam("$p0", types.Int32Value(200)),
    41  			},
    42  		},
    43  		{
    44  			sql: `SELECT $1, $2`,
    45  			args: []interface{}{
    46  				table.ValueParam("$name1", types.Int32Value(100)),
    47  				table.ValueParam("$name2", types.Int32Value(200)),
    48  			},
    49  			yql: `-- origin query with numeric args replacement
    50  SELECT $name1, $name2`,
    51  			params: []interface{}{
    52  				table.ValueParam("$name1", types.Int32Value(100)),
    53  				table.ValueParam("$name2", types.Int32Value(200)),
    54  			},
    55  		},
    56  		{
    57  			sql: `SELECT $1, $2`,
    58  			args: []interface{}{
    59  				table.ValueParam("$namedArg", types.Int32Value(100)),
    60  				200,
    61  			},
    62  			yql: `-- origin query with numeric args replacement
    63  SELECT $namedArg, $p1`,
    64  			params: []interface{}{
    65  				table.ValueParam("$namedArg", types.Int32Value(100)),
    66  				table.ValueParam("$p1", types.Int32Value(200)),
    67  			},
    68  		},
    69  		{
    70  			sql: `SELECT $0, $1`,
    71  			args: []interface{}{
    72  				100,
    73  				200,
    74  			},
    75  			err: ErrUnexpectedNumericArgZero,
    76  		},
    77  		{
    78  			sql: `SELECT $1, $2`,
    79  			args: []interface{}{
    80  				100,
    81  				200,
    82  			},
    83  			yql: `-- origin query with numeric args replacement
    84  SELECT $p0, $p1`,
    85  			params: []interface{}{
    86  				table.ValueParam("$p0", types.Int32Value(100)),
    87  				table.ValueParam("$p1", types.Int32Value(200)),
    88  			},
    89  		},
    90  		{
    91  			sql: `SELECT $1, $2`,
    92  			args: []interface{}{
    93  				100,
    94  			},
    95  			err: ErrInconsistentArgs,
    96  		},
    97  		{
    98  			sql: `SELECT $1, "$2"`,
    99  			args: []interface{}{
   100  				100,
   101  			},
   102  			yql: `-- origin query with numeric args replacement
   103  SELECT $p0, "$2"`,
   104  			params: []interface{}{
   105  				table.ValueParam("$p0", types.Int32Value(100)),
   106  			},
   107  		},
   108  		{
   109  			sql: `SELECT $1, '$2'`,
   110  			args: []interface{}{
   111  				100,
   112  			},
   113  			yql: `-- origin query with numeric args replacement
   114  SELECT $p0, '$2'`,
   115  			params: []interface{}{
   116  				table.ValueParam("$p0", types.Int32Value(100)),
   117  			},
   118  		},
   119  		{
   120  			sql: "SELECT $1, `$2`",
   121  			args: []interface{}{
   122  				100,
   123  			},
   124  			yql: "-- origin query with numeric args replacement\nSELECT $p0, `$2`",
   125  			params: []interface{}{
   126  				table.ValueParam("$p0", types.Int32Value(100)),
   127  			},
   128  		},
   129  		{
   130  			sql:    "SELECT ?, $1, $p0",
   131  			params: []interface{}{},
   132  			err:    ErrInconsistentArgs,
   133  		},
   134  		{
   135  			sql: "SELECT $1, $2, $3",
   136  			args: []interface{}{
   137  				1,
   138  				"test",
   139  				[]string{
   140  					"test1",
   141  					"test2",
   142  					"test3",
   143  				},
   144  			},
   145  			yql: `-- origin query with numeric args replacement
   146  SELECT $p0, $p1, $p2`,
   147  			params: []interface{}{
   148  				table.ValueParam("$p0", types.Int32Value(1)),
   149  				table.ValueParam("$p1", types.TextValue("test")),
   150  				table.ValueParam("$p2", types.ListValue(
   151  					types.TextValue("test1"),
   152  					types.TextValue("test2"),
   153  					types.TextValue("test3"),
   154  				)),
   155  			},
   156  		},
   157  		{
   158  			sql: "SELECT $1, $2, $3, $1, $2",
   159  			args: []interface{}{
   160  				1,
   161  				"test",
   162  				[]string{
   163  					"test1",
   164  					"test2",
   165  					"test3",
   166  				},
   167  			},
   168  			yql: `-- origin query with numeric args replacement
   169  SELECT $p0, $p1, $p2, $p0, $p1`,
   170  			params: []interface{}{
   171  				table.ValueParam("$p0", types.Int32Value(1)),
   172  				table.ValueParam("$p1", types.TextValue("test")),
   173  				table.ValueParam("$p2", types.ListValue(
   174  					types.TextValue("test1"),
   175  					types.TextValue("test2"),
   176  					types.TextValue("test3"),
   177  				)),
   178  			},
   179  		},
   180  		{
   181  			sql: "SELECT $1, $2, $3",
   182  			args: []interface{}{
   183  				types.Int32Value(1),
   184  				types.TextValue("test"),
   185  				types.ListValue(
   186  					types.TextValue("test1"),
   187  					types.TextValue("test2"),
   188  					types.TextValue("test3"),
   189  				),
   190  			},
   191  			yql: `-- origin query with numeric args replacement
   192  SELECT $p0, $p1, $p2`,
   193  			params: []interface{}{
   194  				table.ValueParam("$p0", types.Int32Value(1)),
   195  				table.ValueParam("$p1", types.TextValue("test")),
   196  				table.ValueParam("$p2", types.ListValue(
   197  					types.TextValue("test1"),
   198  					types.TextValue("test2"),
   199  					types.TextValue("test3"),
   200  				)),
   201  			},
   202  		},
   203  		{
   204  			sql: "SELECT $1, a, b, c WHERE id = $1 AND date < $2 AND value IN ($3)",
   205  			args: []interface{}{
   206  				1, now, []string{"3"},
   207  			},
   208  			yql: `-- origin query with numeric args replacement
   209  SELECT $p0, a, b, c WHERE id = $p0 AND date < $p1 AND value IN ($p2)`,
   210  			params: []interface{}{
   211  				table.ValueParam("$p0", types.Int32Value(1)),
   212  				table.ValueParam("$p1", types.TimestampValueFromTime(now)),
   213  				table.ValueParam("$p2", types.ListValue(types.TextValue("3"))),
   214  			},
   215  		},
   216  		{
   217  			sql: "SELECT 1",
   218  			yql: "SELECT 1",
   219  		},
   220  		{
   221  			sql: `
   222  SELECT 1`,
   223  			yql: `
   224  SELECT 1`,
   225  		},
   226  		{
   227  			sql: "SELECT $1, $2",
   228  			args: []interface{}{
   229  				1,
   230  			},
   231  			err: ErrInconsistentArgs,
   232  		},
   233  		{
   234  			sql: "SELECT $1, $2 -- some comment with $3",
   235  			args: []interface{}{
   236  				100,
   237  				200,
   238  			},
   239  			yql: `-- origin query with numeric args replacement
   240  SELECT $p0, $p1 -- some comment with $3`,
   241  			params: []interface{}{
   242  				table.ValueParam("$p0", types.Int32Value(100)),
   243  				table.ValueParam("$p1", types.Int32Value(200)),
   244  			},
   245  		},
   246  		{
   247  			sql: "SELECT $1 /* some comment with $3 */, $2",
   248  			args: []interface{}{
   249  				100,
   250  				200,
   251  			},
   252  			yql: `-- origin query with numeric args replacement
   253  SELECT $p0 /* some comment with $3 */, $p1`,
   254  			params: []interface{}{
   255  				table.ValueParam("$p0", types.Int32Value(100)),
   256  				table.ValueParam("$p1", types.Int32Value(200)),
   257  			},
   258  		},
   259  		{
   260  			sql: "SELECT $1, $2 -- some comment with $3",
   261  			args: []interface{}{
   262  				100,
   263  			},
   264  			err: ErrInconsistentArgs,
   265  		},
   266  		{
   267  			sql: "SELECT $1, $2, $3",
   268  			args: []interface{}{
   269  				100,
   270  				200,
   271  			},
   272  			err: ErrInconsistentArgs,
   273  		},
   274  		{
   275  			sql: `
   276  SELECT $1 /* some comment with $3 */, $2`,
   277  			args: []interface{}{
   278  				100,
   279  				200,
   280  			},
   281  			yql: `-- origin query with numeric args replacement
   282  
   283  SELECT $p0 /* some comment with $3 */, $p1`,
   284  			params: []interface{}{
   285  				table.ValueParam("$p0", types.Int32Value(100)),
   286  				table.ValueParam("$p1", types.Int32Value(200)),
   287  			},
   288  		},
   289  		{
   290  			sql: "SELECT $1, $2",
   291  			args: []interface{}{
   292  				100,
   293  				200,
   294  			},
   295  			yql: `-- origin query with numeric args replacement
   296  SELECT $p0, $p1`,
   297  			params: []interface{}{
   298  				table.ValueParam("$p0", types.Int32Value(100)),
   299  				table.ValueParam("$p1", types.Int32Value(200)),
   300  			},
   301  		},
   302  		{
   303  			sql: "SELECT $1, $2",
   304  			args: []interface{}{
   305  				100,
   306  				200,
   307  			},
   308  			yql: `-- origin query with numeric args replacement
   309  SELECT $p0, $p1`,
   310  			params: []interface{}{
   311  				table.ValueParam("$p0", types.Int32Value(100)),
   312  				table.ValueParam("$p1", types.Int32Value(200)),
   313  			},
   314  		},
   315  	} {
   316  		t.Run("", func(t *testing.T) {
   317  			yql, params, err := b.RewriteQuery(tt.sql, tt.args...)
   318  			if tt.err != nil {
   319  				require.Error(t, err)
   320  				require.ErrorIs(t, err, tt.err)
   321  			} else {
   322  				require.NoError(t, err)
   323  				require.Equal(t, tt.yql, yql)
   324  				require.Equal(t, tt.params, params)
   325  			}
   326  		})
   327  	}
   328  }