Bug Report: Unknown column in field list error for COUNT from subquery which includes ORDER BY, LIMIT, and OFFSET

[brendar]

Overview of the Issue

On v18+ executing this query:

SELECT COUNT(*) FROM (SELECT 1 AS one FROM `stuff` WHERE `stuff`.`is_not_deleted` = true ORDER BY id DESC LIMIT 25 OFFSET 0) subquery_for_count

Results in this error:

ERROR 1054 (42S22): target: foo.-80.primary: vttablet: rpc error: code = NotFound desc = Unknown column 'id' in 'field list' (errno 1054) (sqlstate 42S22) (CallerID: userData1): Sql: "select 1, id, weight_string(id) from (select 1 as one from stuff where stuff.is_not_deleted = true) as subquery_for_count order by id desc limit :__upper_limit", BindVars: {#maxLimit: "type:INT64 value:\"10001\""__upper_limit: "type:INT64 value:\"25\""vtg1: "type:INT64 value:\"0\""vtg2: "type:INT64 value:\"25\""}

It looks like the subquery rewritten by vtgate is incorrect as it doesn't return an id column

select 1, id, weight_string(id) from (select 1 as one from stuff where stuff.is_not_deleted = true) as subquery_for_count order by id desc limit :__upper_limit

The issue appears to have been introduced somewhere between v17.0.7 and v18.0.5 and is still present on main

Reproduction Steps

1. Create a test keyspace foo with two shards

   bin/vttestserver --keyspaces "foo" --num_shards 2 --port 6000
   

2. Apply a sharded vschema

   bin/vtctldclient --server 127.0.0.1:6001 ApplyVSchema --vschema-file test_vschema.json foo
   

   where test_vschema.json contains:

   {
     "sharded": true,
     "vindexes": {
       "hash": {
         "type": "hash"
       }
     },
     "tables": {
       "stuff": {
         "column_vindexes": [
           {
             "column": "id",
             "name": "hash"
           }
         ]
       }
     }
   }

3. Connect to vtgate (vtcombo)

   mysql -h 127.0.0.1 --port 6003 --user root
   

4. Execute

   mysql> use foo;
   Database changed
   
   mysql> create table stuff (id bigint primary key, is_not_deleted bool not null);
   Query OK, 0 rows affected (0.03 sec)
   
   mysql> SELECT COUNT(*) FROM (SELECT 1 AS one FROM `stuff` WHERE `stuff`.`is_not_deleted` = true ORDER BY id DESC LIMIT 25 OFFSET 0) subquery_for_count;
   ERROR 1054 (42S22): target: foo.-80.primary: vttablet: rpc error: code = NotFound desc = Unknown column 'id' in 'field list' (errno 1054) (sqlstate 42S22) (CallerID: userData1): Sql: "select 1, id, weight_string(id) from (select 1 as one from stuff where stuff.is_not_deleted = true) as subquery_for_count order by id desc limit :__upper_limit", BindVars: {#maxLimit: "type:INT64 value:\"10001\""__upper_limit: "type:INT64 value:\"25\""vtg1: "type:INT64 value:\"0\""vtg2: "type:INT64 value:\"25\""}
   

Binary Version

$ bin/vtgate --version
vtgate version Version: 18.0.5 (Git revision 4bd2e1c2f88cbff68f8b969a9ee6dad236713490 branch 'HEAD') built on Tue Jun 18 17:25:30 EDT 2024 by brendan@slab using go1.22.4 darwin/arm64

Operating System and Environment details

n/a

Log Fragments

No response