vitess.io/vitess@v0.16.2/go/test/endtoend/vtgate/queries/subquery/subquery_test.go (about)

     1  /*
     2  Copyright 2021 The Vitess Authors.
     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 subquery
    18  
    19  import (
    20  	"testing"
    21  
    22  	"vitess.io/vitess/go/test/endtoend/utils"
    23  
    24  	"github.com/stretchr/testify/assert"
    25  	"github.com/stretchr/testify/require"
    26  
    27  	"vitess.io/vitess/go/test/endtoend/cluster"
    28  )
    29  
    30  func start(t *testing.T) (utils.MySQLCompare, func()) {
    31  	mcmp, err := utils.NewMySQLCompare(t, vtParams, mysqlParams)
    32  	require.NoError(t, err)
    33  
    34  	deleteAll := func() {
    35  		_, _ = utils.ExecAllowError(t, mcmp.VtConn, "set workload = oltp")
    36  
    37  		tables := []string{"t1", "t1_id2_idx", "t2", "t2_id4_idx"}
    38  		for _, table := range tables {
    39  			_, _ = mcmp.ExecAndIgnore("delete from " + table)
    40  		}
    41  	}
    42  
    43  	deleteAll()
    44  
    45  	return mcmp, func() {
    46  		deleteAll()
    47  		mcmp.Close()
    48  		cluster.PanicHandler(t)
    49  	}
    50  }
    51  
    52  func TestSubqueriesHasValues(t *testing.T) {
    53  	mcmp, closer := start(t)
    54  	defer closer()
    55  
    56  	mcmp.Exec("insert into t1(id1, id2) values (0,1),(1,2),(2,3),(3,4),(4,5),(5,6)")
    57  	mcmp.AssertMatches(`SELECT id2 FROM t1 WHERE id1 IN (SELECT id1 FROM t1 WHERE id1 > 10)`, `[]`)
    58  	mcmp.AssertMatches(`SELECT id2 FROM t1 WHERE id1 NOT IN (SELECT id1 FROM t1 WHERE id1 > 10) ORDER BY id2`, `[[INT64(1)] [INT64(2)] [INT64(3)] [INT64(4)] [INT64(5)] [INT64(6)]]`)
    59  }
    60  
    61  // Test only supported in >= v16.0.0
    62  func TestSubqueriesExists(t *testing.T) {
    63  	utils.SkipIfBinaryIsBelowVersion(t, 16, "vtgate")
    64  	mcmp, closer := start(t)
    65  	defer closer()
    66  
    67  	mcmp.Exec("insert into t1(id1, id2) values (0,1),(1,2),(2,3),(3,4),(4,5),(5,6)")
    68  	mcmp.AssertMatches(`SELECT id2 FROM t1 WHERE EXISTS (SELECT id1 FROM t1 WHERE id1 > 0) ORDER BY id2`, `[[INT64(1)] [INT64(2)] [INT64(3)] [INT64(4)] [INT64(5)] [INT64(6)]]`)
    69  	mcmp.AssertMatches(`select * from (select 1) as tmp where exists(select 1 from t1 where id1 = 1)`, `[[INT32(1)]]`)
    70  }
    71  
    72  func TestQueryAndSubQWithLimit(t *testing.T) {
    73  	mcmp, closer := start(t)
    74  	defer closer()
    75  
    76  	mcmp.Exec("insert into t1(id1, id2) values(0,0),(1,1),(2,2),(3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8),(9,9)")
    77  	result := mcmp.Exec(`select id1, id2 from t1 where id1 >= ( select id1 from t1 order by id1 asc limit 1) limit 100`)
    78  	assert.Equal(t, 10, len(result.Rows))
    79  }
    80  
    81  func TestSubQueryOnTopOfSubQuery(t *testing.T) {
    82  	mcmp, closer := start(t)
    83  	defer closer()
    84  
    85  	mcmp.Exec(`insert into t1(id1, id2) values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5)`)
    86  	mcmp.Exec(`insert into t2(id3, id4) values (1, 3), (2, 4)`)
    87  
    88  	mcmp.AssertMatches("select id1 from t1 where id1 not in (select id3 from t2) and id2 in (select id4 from t2) order by id1", `[[INT64(3)] [INT64(4)]]`)
    89  }
    90  
    91  func TestSubqueryInINClause(t *testing.T) {
    92  	mcmp, closer := start(t)
    93  	defer closer()
    94  
    95  	mcmp.Exec("insert into t1(id1, id2) values(0,0),(1,1)")
    96  	mcmp.AssertMatches(`SELECT id2 FROM t1 WHERE id1 IN (SELECT 1 FROM dual)`, `[[INT64(1)]]`)
    97  }
    98  
    99  func TestSubqueryInUpdate(t *testing.T) {
   100  	utils.SkipIfBinaryIsBelowVersion(t, 14, "vtgate")
   101  	mcmp, closer := start(t)
   102  	defer closer()
   103  
   104  	conn := mcmp.VtConn
   105  
   106  	utils.Exec(t, conn, `insert into t1(id1, id2) values (1, 10), (2, 20), (3, 30), (4, 40), (5, 50)`)
   107  	utils.Exec(t, conn, `insert into t2(id3, id4) values (1, 3), (2, 4)`)
   108  	utils.AssertMatches(t, conn, `SELECT id2, keyspace_id FROM t1_id2_idx WHERE id2 IN (2,10)`, `[[INT64(10) VARBINARY("\x16k@\xb4J\xbaK\xd6")]]`)
   109  	utils.Exec(t, conn, `update /*vt+ PLANNER=gen4 */ t1 set id2 = (select count(*) from t2) where id1 = 1`)
   110  	utils.AssertMatches(t, conn, `SELECT id2 FROM t1 WHERE id1 = 1`, `[[INT64(2)]]`)
   111  	utils.AssertMatches(t, conn, `SELECT id2, keyspace_id FROM t1_id2_idx WHERE id2 IN (2,10)`, `[[INT64(2) VARBINARY("\x16k@\xb4J\xbaK\xd6")]]`)
   112  }
   113  
   114  func TestSubqueryInReference(t *testing.T) {
   115  	utils.SkipIfBinaryIsBelowVersion(t, 14, "vtgate")
   116  	mcmp, closer := start(t)
   117  	defer closer()
   118  
   119  	mcmp.Exec(`insert into t1(id1, id2) values (1,10), (2, 20), (3, 30), (4, 40), (5, 50)`)
   120  	mcmp.AssertMatches(`select exists(select * from t1 where id1 = 3)`, `[[INT64(1)]]`)
   121  	mcmp.AssertMatches(`select exists(select * from t1 where id1 = 9)`, `[[INT64(0)]]`)
   122  	mcmp.AssertMatches(`select exists(select * from t1)`, `[[INT64(1)]]`)
   123  	mcmp.AssertMatches(`select exists(select * from t1 where id2 = 30)`, `[[INT64(1)]]`)
   124  	mcmp.AssertMatches(`select exists(select * from t1 where id2 = 9)`, `[[INT64(0)]]`)
   125  	mcmp.AssertMatches(`select count(*) from t1 where id2 = 9`, `[[INT64(0)]]`)
   126  
   127  	mcmp.AssertMatches(`select 1 in (select 1 from t1 where id1 = 3)`, `[[INT64(1)]]`)
   128  	mcmp.AssertMatches(`select 1 in (select 1 from t1 where id1 = 9)`, `[[INT64(0)]]`)
   129  	mcmp.AssertMatches(`select 1 in (select id1 from t1)`, `[[INT64(1)]]`)
   130  	mcmp.AssertMatches(`select 1 in (select 1 from t1 where id2 = 30)`, `[[INT64(1)]]`)
   131  	mcmp.AssertMatches(`select 1 in (select 1 from t1 where id2 = 9)`, `[[INT64(0)]]`)
   132  
   133  	mcmp.AssertMatches(`select 1 not in (select 1 from t1 where id1 = 3)`, `[[INT64(0)]]`)
   134  	mcmp.AssertMatches(`select 1 not in (select 1 from t1 where id1 = 9)`, `[[INT64(1)]]`)
   135  	mcmp.AssertMatches(`select 1 not in (select id1 from t1)`, `[[INT64(0)]]`)
   136  	mcmp.AssertMatches(`select 1 not in (select 1 from t1 where id2 = 30)`, `[[INT64(0)]]`)
   137  	mcmp.AssertMatches(`select 1 not in (select 1 from t1 where id2 = 9)`, `[[INT64(1)]]`)
   138  
   139  	mcmp.AssertMatches(`select (select id2 from t1 where id1 = 3)`, `[[INT64(30)]]`)
   140  	mcmp.AssertMatches(`select (select id2 from t1 where id1 = 9)`, `[[NULL]]`)
   141  	mcmp.AssertMatches(`select (select id1 from t1 where id2 = 30)`, `[[INT64(3)]]`)
   142  	mcmp.AssertMatches(`select (select id1 from t1 where id2 = 9)`, `[[NULL]]`)
   143  }