github.com/yongjacky/phoenix-go-orm-builder@v0.3.5/builder_set_operations_test.go (about)

     1  // Copyright 2019 The Xorm Authors. All rights reserved.
     2  // Use of this source code is governed by a BSD-style
     3  // license that can be found in the LICENSE file.
     4  
     5  package builder
     6  
     7  import (
     8  	"fmt"
     9  	"testing"
    10  
    11  	"github.com/stretchr/testify/assert"
    12  )
    13  
    14  func TestBuilder_Union(t *testing.T) {
    15  	sql, args, err := Select("*").From("t1").Where(Eq{"status": "1"}).
    16  		Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).
    17  		Union("distinct", Select("*").From("t2").Where(Eq{"status": "3"})).
    18  		Union("", Select("*").From("t2").Where(Eq{"status": "3"})).
    19  		ToSQL()
    20  	assert.NoError(t, err)
    21  	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)
    22  	assert.EqualValues(t, []interface{}{"1", "2", "3", "3"}, args)
    23  
    24  	// sub-query will inherit dialect from the main one
    25  	sql, args, err = MySQL().Select("*").From("t1").Where(Eq{"status": "1"}).
    26  		Union("all", Select("*").From("t2").Where(Eq{"status": "2"}).Limit(10)).
    27  		Union("", Select("*").From("t2").Where(Eq{"status": "3"})).
    28  		ToSQL()
    29  	assert.NoError(t, err)
    30  	assert.EqualValues(t, "(SELECT * FROM t1 WHERE status=?) UNION ALL (SELECT * FROM t2 WHERE status=? LIMIT 10) UNION (SELECT * FROM t2 WHERE status=?)", sql)
    31  	assert.EqualValues(t, []interface{}{"1", "2", "3"}, args)
    32  
    33  	// will raise error
    34  	_, _, err = MySQL().Select("*").From("t1").Where(Eq{"status": "1"}).
    35  		Union("all", Oracle().Select("*").From("t2").Where(Eq{"status": "2"}).Limit(10)).
    36  		ToSQL()
    37  	assert.Error(t, err)
    38  	assert.EqualValues(t, ErrInconsistentDialect, err)
    39  
    40  	// will raise error
    41  	_, _, err = Select("*").From("table1").Where(Eq{"a": "1"}).
    42  		Union("all", Select("*").From("table2").Where(Eq{"a": "2"})).
    43  		Where(Eq{"a": 2}).Limit(5, 10).
    44  		ToSQL()
    45  	assert.Error(t, err)
    46  	assert.EqualValues(t, ErrNotUnexpectedUnionConditions, err)
    47  
    48  	// will raise error
    49  	_, _, err = Delete(Eq{"a": 1}).From("t1").
    50  		Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).ToSQL()
    51  	assert.Error(t, err)
    52  	assert.EqualValues(t, ErrUnsupportedUnionMembers, err)
    53  
    54  	// will be overwrote by SELECT op
    55  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
    56  		Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).
    57  		Select("*").From("t2").ToSQL()
    58  	assert.NoError(t, err)
    59  	fmt.Println(sql, args)
    60  
    61  	// will be overwrote by DELETE op
    62  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
    63  		Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).
    64  		Delete(Eq{"status": "1"}).From("t2").ToSQL()
    65  	assert.NoError(t, err)
    66  	fmt.Println(sql, args)
    67  
    68  	// will be overwrote by INSERT op
    69  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
    70  		Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).
    71  		Insert(Eq{"status": "1"}).Into("t2").ToSQL()
    72  	assert.NoError(t, err)
    73  	fmt.Println(sql, args)
    74  }
    75  
    76  func TestBuilder_Intersect(t *testing.T) {
    77  	sql, args, err := Select("*").From("t1").Where(Eq{"status": "1"}).
    78  		Intersect("all", Select("*").From("t2").Where(Eq{"status": "2"})).
    79  		Intersect("distinct", Select("*").From("t2").Where(Eq{"status": "3"})).
    80  		Intersect("", Select("*").From("t2").Where(Eq{"status": "3"})).
    81  		ToSQL()
    82  	assert.NoError(t, err)
    83  	assert.EqualValues(t, "(SELECT * FROM t1 WHERE status=?) INTERSECT ALL (SELECT * FROM t2 WHERE status=?) INTERSECT DISTINCT (SELECT * FROM t2 WHERE status=?) INTERSECT (SELECT * FROM t2 WHERE status=?)", sql)
    84  	assert.EqualValues(t, []interface{}{"1", "2", "3", "3"}, args)
    85  
    86  	// sub-query will inherit dialect from the main one
    87  	sql, args, err = MySQL().Select("*").From("t1").Where(Eq{"status": "1"}).
    88  		Intersect("all", Select("*").From("t2").Where(Eq{"status": "2"}).Limit(10)).
    89  		Intersect("", Select("*").From("t2").Where(Eq{"status": "3"})).
    90  		ToSQL()
    91  	assert.NoError(t, err)
    92  	assert.EqualValues(t, "(SELECT * FROM t1 WHERE status=?) INTERSECT ALL (SELECT * FROM t2 WHERE status=? LIMIT 10) INTERSECT (SELECT * FROM t2 WHERE status=?)", sql)
    93  	assert.EqualValues(t, []interface{}{"1", "2", "3"}, args)
    94  
    95  	// will raise error
    96  	_, _, err = MySQL().Select("*").From("t1").Where(Eq{"status": "1"}).
    97  		Intersect("all", Oracle().Select("*").From("t2").Where(Eq{"status": "2"}).Limit(10)).
    98  		ToSQL()
    99  	assert.Error(t, err)
   100  	assert.EqualValues(t, ErrInconsistentDialect, err)
   101  
   102  	// will raise error
   103  	_, _, err = Select("*").From("table1").Where(Eq{"a": "1"}).
   104  		Intersect("all", Select("*").From("table2").Where(Eq{"a": "2"})).
   105  		Where(Eq{"a": 2}).Limit(5, 10).
   106  		ToSQL()
   107  	assert.Error(t, err)
   108  	assert.EqualValues(t, ErrNotUnexpectedUnionConditions, err)
   109  
   110  	// will raise error
   111  	_, _, err = Delete(Eq{"a": 1}).From("t1").
   112  		Intersect("all", Select("*").From("t2").Where(Eq{"status": "2"})).ToSQL()
   113  	assert.Error(t, err)
   114  	assert.EqualValues(t, ErrUnsupportedUnionMembers, err)
   115  
   116  	// will be overwrote by SELECT op
   117  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
   118  		Intersect("all", Select("*").From("t2").Where(Eq{"status": "2"})).
   119  		Select("*").From("t2").ToSQL()
   120  	assert.NoError(t, err)
   121  	fmt.Println(sql, args)
   122  
   123  	// will be overwrote by DELETE op
   124  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
   125  		Intersect("all", Select("*").From("t2").Where(Eq{"status": "2"})).
   126  		Delete(Eq{"status": "1"}).From("t2").ToSQL()
   127  	assert.NoError(t, err)
   128  	fmt.Println(sql, args)
   129  
   130  	// will be overwrote by INSERT op
   131  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
   132  		Intersect("all", Select("*").From("t2").Where(Eq{"status": "2"})).
   133  		Insert(Eq{"status": "1"}).Into("t2").ToSQL()
   134  	assert.NoError(t, err)
   135  	fmt.Println(sql, args)
   136  }
   137  
   138  func TestBuilder_Except(t *testing.T) {
   139  	sql, args, err := Select("*").From("t1").Where(Eq{"status": "1"}).
   140  		Except("all", Select("*").From("t2").Where(Eq{"status": "2"})).
   141  		Except("distinct", Select("*").From("t2").Where(Eq{"status": "3"})).
   142  		Except("", Select("*").From("t2").Where(Eq{"status": "3"})).
   143  		ToSQL()
   144  	assert.NoError(t, err)
   145  	assert.EqualValues(t, "(SELECT * FROM t1 WHERE status=?) EXCEPT ALL (SELECT * FROM t2 WHERE status=?) EXCEPT DISTINCT (SELECT * FROM t2 WHERE status=?) EXCEPT (SELECT * FROM t2 WHERE status=?)", sql)
   146  	assert.EqualValues(t, []interface{}{"1", "2", "3", "3"}, args)
   147  
   148  	// sub-query will inherit dialect from the main one
   149  	sql, args, err = MySQL().Select("*").From("t1").Where(Eq{"status": "1"}).
   150  		Except("all", Select("*").From("t2").Where(Eq{"status": "2"}).Limit(10)).
   151  		Except("", Select("*").From("t2").Where(Eq{"status": "3"})).
   152  		ToSQL()
   153  	assert.NoError(t, err)
   154  	assert.EqualValues(t, "(SELECT * FROM t1 WHERE status=?) EXCEPT ALL (SELECT * FROM t2 WHERE status=? LIMIT 10) EXCEPT (SELECT * FROM t2 WHERE status=?)", sql)
   155  	assert.EqualValues(t, []interface{}{"1", "2", "3"}, args)
   156  
   157  	// will raise error
   158  	_, _, err = MySQL().Select("*").From("t1").Where(Eq{"status": "1"}).
   159  		Except("all", Oracle().Select("*").From("t2").Where(Eq{"status": "2"}).Limit(10)).
   160  		ToSQL()
   161  	assert.Error(t, err)
   162  	assert.EqualValues(t, ErrInconsistentDialect, err)
   163  
   164  	// will raise error
   165  	_, _, err = Select("*").From("table1").Where(Eq{"a": "1"}).
   166  		Except("all", Select("*").From("table2").Where(Eq{"a": "2"})).
   167  		Where(Eq{"a": 2}).Limit(5, 10).
   168  		ToSQL()
   169  	assert.Error(t, err)
   170  	assert.EqualValues(t, ErrNotUnexpectedUnionConditions, err)
   171  
   172  	// will raise error
   173  	_, _, err = Delete(Eq{"a": 1}).From("t1").
   174  		Except("all", Select("*").From("t2").Where(Eq{"status": "2"})).ToSQL()
   175  	assert.Error(t, err)
   176  	assert.EqualValues(t, ErrUnsupportedUnionMembers, err)
   177  
   178  	// will be overwrote by SELECT op
   179  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
   180  		Except("all", Select("*").From("t2").Where(Eq{"status": "2"})).
   181  		Select("*").From("t2").ToSQL()
   182  	assert.NoError(t, err)
   183  	fmt.Println(sql, args)
   184  
   185  	// will be overwrote by DELETE op
   186  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
   187  		Except("all", Select("*").From("t2").Where(Eq{"status": "2"})).
   188  		Delete(Eq{"status": "1"}).From("t2").ToSQL()
   189  	assert.NoError(t, err)
   190  	fmt.Println(sql, args)
   191  
   192  	// will be overwrote by INSERT op
   193  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
   194  		Except("all", Select("*").From("t2").Where(Eq{"status": "2"})).
   195  		Insert(Eq{"status": "1"}).Into("t2").ToSQL()
   196  	assert.NoError(t, err)
   197  	fmt.Println(sql, args)
   198  }
   199  
   200  func TestBuilder_SetOperations(t *testing.T) {
   201  	sql, args, err := Select("*").From("t1").Where(Eq{"status": "1"}).
   202  		Union("all", Select("*").From("t2").Where(Eq{"status": "2"})).
   203  		Intersect("distinct", Select("*").From("t2").Where(Eq{"status": "3"})).
   204  		Except("", Select("*").From("t2").Where(Eq{"status": "3"})).
   205  		ToSQL()
   206  	assert.NoError(t, err)
   207  	assert.EqualValues(t, "(SELECT * FROM t1 WHERE status=?) UNION ALL (SELECT * FROM t2 WHERE status=?) INTERSECT DISTINCT (SELECT * FROM t2 WHERE status=?) EXCEPT (SELECT * FROM t2 WHERE status=?)", sql)
   208  	assert.EqualValues(t, []interface{}{"1", "2", "3", "3"}, args)
   209  
   210  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
   211  		Intersect("all", Select("*").From("t2").Where(Eq{"status": "2"})).
   212  		Union("distinct", Select("*").From("t2").Where(Eq{"status": "3"})).
   213  		Except("", Select("*").From("t2").Where(Eq{"status": "3"})).
   214  		ToSQL()
   215  	assert.NoError(t, err)
   216  	assert.EqualValues(t, "(SELECT * FROM t1 WHERE status=?) INTERSECT ALL (SELECT * FROM t2 WHERE status=?) UNION DISTINCT (SELECT * FROM t2 WHERE status=?) EXCEPT (SELECT * FROM t2 WHERE status=?)", sql)
   217  	assert.EqualValues(t, []interface{}{"1", "2", "3", "3"}, args)
   218  
   219  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
   220  		Except("all", Select("*").From("t2").Where(Eq{"status": "2"})).
   221  		Intersect("distinct", Select("*").From("t2").Where(Eq{"status": "3"})).
   222  		Union("", Select("*").From("t2").Where(Eq{"status": "3"})).
   223  		ToSQL()
   224  	assert.NoError(t, err)
   225  	assert.EqualValues(t, "(SELECT * FROM t1 WHERE status=?) EXCEPT ALL (SELECT * FROM t2 WHERE status=?) INTERSECT DISTINCT (SELECT * FROM t2 WHERE status=?) UNION (SELECT * FROM t2 WHERE status=?)", sql)
   226  	assert.EqualValues(t, []interface{}{"1", "2", "3", "3"}, args)
   227  
   228  	// sub-query will inherit dialect from the main one
   229  	sql, args, err = MySQL().Select("*").From("t1").Where(Eq{"status": "1"}).
   230  		Intersect("all", Select("*").From("t2").Where(Eq{"status": "2"}).Limit(10)).
   231  		Intersect("", Select("*").From("t2").Where(Eq{"status": "3"})).
   232  		ToSQL()
   233  	assert.NoError(t, err)
   234  	assert.EqualValues(t, "(SELECT * FROM t1 WHERE status=?) INTERSECT ALL (SELECT * FROM t2 WHERE status=? LIMIT 10) INTERSECT (SELECT * FROM t2 WHERE status=?)", sql)
   235  	assert.EqualValues(t, []interface{}{"1", "2", "3"}, args)
   236  
   237  	// will raise error
   238  	_, _, err = MySQL().Select("*").From("t1").Where(Eq{"status": "1"}).
   239  		Intersect("all", Oracle().Select("*").From("t2").Where(Eq{"status": "2"}).Limit(10)).
   240  		ToSQL()
   241  	assert.Error(t, err)
   242  	assert.EqualValues(t, ErrInconsistentDialect, err)
   243  
   244  	// will raise error
   245  	_, _, err = Select("*").From("table1").Where(Eq{"a": "1"}).
   246  		Intersect("all", Select("*").From("table2").Where(Eq{"a": "2"})).
   247  		Where(Eq{"a": 2}).Limit(5, 10).
   248  		ToSQL()
   249  	assert.Error(t, err)
   250  	assert.EqualValues(t, ErrNotUnexpectedUnionConditions, err)
   251  
   252  	// will raise error
   253  	_, _, err = Delete(Eq{"a": 1}).From("t1").
   254  		Intersect("all", Select("*").From("t2").Where(Eq{"status": "2"})).ToSQL()
   255  	assert.Error(t, err)
   256  	assert.EqualValues(t, ErrUnsupportedUnionMembers, err)
   257  
   258  	// will be overwrote by SELECT op
   259  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
   260  		Intersect("all", Select("*").From("t2").Where(Eq{"status": "2"})).
   261  		Select("*").From("t2").ToSQL()
   262  	assert.NoError(t, err)
   263  	fmt.Println(sql, args)
   264  
   265  	// will be overwrote by DELETE op
   266  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
   267  		Intersect("all", Select("*").From("t2").Where(Eq{"status": "2"})).
   268  		Delete(Eq{"status": "1"}).From("t2").ToSQL()
   269  	assert.NoError(t, err)
   270  	fmt.Println(sql, args)
   271  
   272  	// will be overwrote by INSERT op
   273  	sql, args, err = Select("*").From("t1").Where(Eq{"status": "1"}).
   274  		Intersect("all", Select("*").From("t2").Where(Eq{"status": "2"})).
   275  		Insert(Eq{"status": "1"}).Into("t2").ToSQL()
   276  	assert.NoError(t, err)
   277  	fmt.Println(sql, args)
   278  }