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
|
// Copyright 2018 The Xorm Authors. All rights reserved.
// Use of this source code is governed by a BSD-style
// license that can be found in the LICENSE file.
package builder
import (
"fmt"
"testing"
"github.com/stretchr/testify/assert"
)
func TestBuilder_Union(t *testing.T) {
sql, args, err := Select("*").From("t1").Where(Eq{"status": "1"}).
Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).
Union("distinct", Select("*").From("t2").Where(Eq{"status": "3"})).
Union("", Select("*").From("t2").Where(Eq{"status": "3"})).
ToSQL()
assert.NoError(t, err)
assert.EqualValues(t, "(SELECT * FROM t1 WHERE status=?) UNION ALL (SELECT * FROM t2 WHERE status=?) UNION DISTINCT (SELECT * FROM t2 WHERE status=?) UNION (SELECT * FROM t2 WHERE status=?)", sql)
assert.EqualValues(t, []interface{}{"1", "2", "3", "3"}, args)
// sub-query will inherit dialect from the main one
sql, args, err = MySQL().Select("*").From("t1").Where(Eq{"status": "1"}).
Union("all", Select("*").From("t2").Where(Eq{"status": "2"}).Limit(10)).
Union("", Select("*").From("t2").Where(Eq{"status": "3"})).
ToSQL()
assert.NoError(t, err)
assert.EqualValues(t, "(SELECT * FROM t1 WHERE status=?) UNION ALL (SELECT * FROM t2 WHERE status=? LIMIT 10) UNION (SELECT * FROM t2 WHERE status=?)", sql)
assert.EqualValues(t, []interface{}{"1", "2", "3"}, args)
// will raise error
sql, args, err = MySQL().Select("*").From("t1").Where(Eq{"status": "1"}).
Union("all", Oracle().Select("*").From("t2").Where(Eq{"status": "2"}).Limit(10)).
ToSQL()
assert.Error(t, err)
assert.EqualValues(t, ErrInconsistentDialect, err)
// will raise error
sql, args, err = Select("*").From("table1").Where(Eq{"a": "1"}).
Union("all", Select("*").From("table2").Where(Eq{"a": "2"})).
Where(Eq{"a": 2}).Limit(5, 10).
ToSQL()
assert.Error(t, err)
assert.EqualValues(t, ErrNotUnexpectedUnionConditions, err)
// will raise error
sql, args, err = Delete(Eq{"a": 1}).From("t1").
Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).ToSQL()
assert.Error(t, err)
assert.EqualValues(t, ErrUnsupportedUnionMembers, err)
// will be overwrote by SELECT op
sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).
Select("*").From("t2").ToSQL()
assert.NoError(t, err)
fmt.Println(sql, args)
// will be overwrote by DELETE op
sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).
Delete(Eq{"status": "1"}).From("t2").ToSQL()
assert.NoError(t, err)
fmt.Println(sql, args)
// will be overwrote by INSERT op
sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).
Insert(Eq{"status": "1"}).Into("t2").ToSQL()
assert.NoError(t, err)
fmt.Println(sql, args)
}
|
