bigquery/statement_test.go at main · viant/bigquery · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
package bigquery

import (
	"github.com/stretchr/testify/assert"
	"testing"
)

func Test_CheckQueryParameters(t *testing.T) {
	var testCases = []struct {
		description string
		SQL         string
		exepcted    int
	}{
		{
			description: "Merge with inline params",
			SQL: `MERGE INTO
  mock.customers_robin c
USING
  (
  SELECT
    1001 AS id,
    'Sally' AS first_name,
    'Thomas' AS last_name,
    'sally.thomas@acme.com' AS email,
    FALSE AS __artie_delete
  UNION ALL
  SELECT
    1002,
    'George',
    'Bailey',
    'gbailey@foobar.com',
    FALSE
  UNION ALL
  SELECT
    1003,
    'Edward',
    'Walker',
    'ed@walker.com',
    FALSE
  UNION ALL
  SELECT
    1004,
    'Anne',
    'Kretchmar',
    'annek@noanswer.org',
    FALSE) AS cc
ON
  c.id = cc.id
  WHEN MATCHED AND cc.__artie_delete THEN DELETE
  WHEN MATCHED
  AND IFNULL(cc.__artie_delete, FALSE) = FALSE THEN
UPDATE
SET
  id = cc.id,
  first_name = cc.first_name,
  last_name = cc.last_name,
  email = cc.email
  WHEN NOT MATCHED
  AND IFNULL(cc.__artie_delete, FALSE) = FALSE THEN
INSERT
  ( id,
    first_name,
    last_name,
    email )
VALUES
  ( cc.id,cc.first_name,cc.last_name,cc.email );`,
		},

		{
			description: "Merge with binding params",
			SQL: `MERGE INTO
  mock.customers_robin c
USING
  (
  SELECT
    1001 AS id,
    'Sally' AS first_name,
    'Thomas' AS last_name,
    'sally.thomas@acme.com' AS email,
    FALSE AS __artie_delete
  UNION ALL
  SELECT
    1002,
    'George',
    'Bailey',
    'gbailey@foobar.com',
    FALSE
  UNION ALL
  SELECT
    1003,
    'Edward',
    'Walker',
    @email,
    FALSE
  UNION ALL
  SELECT
    1004,
    'Anne',
    'Kretchmar',
    'annek@noanswer.org',
    FALSE) AS cc
ON
  c.id = cc.id
  WHEN MATCHED AND cc.__artie_delete THEN DELETE
  WHEN MATCHED
  AND IFNULL(cc.__artie_delete, FALSE) = FALSE THEN
UPDATE
SET
  id = cc.id,
  first_name = cc.first_name,
  last_name = cc.last_name,
  email = cc.email
  WHEN NOT MATCHED
  AND IFNULL(cc.__artie_delete, FALSE) = FALSE THEN
INSERT
  ( id,
    first_name,
    last_name,
    email )
VALUES
  ( cc.id,cc.first_name,cc.last_name,cc.email );`,
			exepcted: 1,
		},
		{
			description: "legacy decorator",
			SQL:         `SELECT * FROM [project:dataset.table@1700000000000-1700003600000]`,
			exepcted:    0,
		},
	}

	for _, testCase := range testCases {
		actual := checkQueryParameters(testCase.SQL)
		assert.Equal(t, testCase.exepcted, actual, testCase.description)
	}
}