SQL: SYS COLUMNS returns incorrect data for a field with the same name in multiple indices

[astefan]

Consider index test1:

{
  "mappings": {
    "properties": {
      "cycle_type": {
        "type": "keyword"
      },
      "name" : {
        "type" : "text",
        "fields": {
        	"raw": {
        		"type": "keyword"
        	}
        }
      }
    }
  }
}

and a similar index called test2 where the field name is keyword (vs. text in test1):

{
  "mappings": {
    "properties": {
      "cycle_type": {
        "type": "keyword"
      },
      "name" : {
        "type" : "keyword",
        "fields": {
        	"raw": {
        		"type": "keyword"
        	}
        }
      }
    }
  }
}

SYS COLUMNS will generate the following output:

   TABLE_CAT   |  TABLE_SCHEM  |  TABLE_NAME   |  COLUMN_NAME  |   DATA_TYPE   |   TYPE_NAME   |  COLUMN_SIZE  | BUFFER_LENGTH |DECIMAL_DIGITS |NUM_PREC_RADIX |   NULLABLE    |    REMARKS    |  COLUMN_DEF   | SQL_DATA_TYPE |SQL_DATETIME_SUB|CHAR_OCTET_LENGTH|ORDINAL_POSITION|  IS_NULLABLE  | SCOPE_CATALOG | SCOPE_SCHEMA  |  SCOPE_TABLE  |SOURCE_DATA_TYPE|IS_AUTOINCREMENT|IS_GENERATEDCOLUMN
---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+----------------+-----------------+----------------+---------------+---------------+---------------+---------------+----------------+----------------+------------------
runTask        |null           |test1          |cycle_type     |12             |KEYWORD        |32766          |2147483647     |null           |null           |1              |null           |null           |12             |0               |2147483647       |1               |YES            |null           |null           |null           |null            |NO              |NO                
runTask        |null           |test1          |name           |12             |TEXT           |2147483647     |2147483647     |null           |null           |1              |null           |null           |12             |0               |2147483647       |2               |YES            |null           |null           |null           |null            |NO              |NO                
runTask        |null           |test1          |name.raw       |12             |KEYWORD        |32766          |2147483647     |null           |null           |1              |null           |null           |12             |0               |2147483647       |3               |YES            |null           |null           |null           |null            |NO              |NO                
runTask        |null           |test2          |cycle_type     |12             |KEYWORD        |32766          |2147483647     |null           |null           |1              |null           |null           |12             |0               |2147483647       |1               |YES            |null           |null           |null           |null            |NO              |NO                
runTask        |null           |test2          |name           |12             |TEXT           |2147483647     |2147483647     |null           |null           |1              |null           |null           |12             |0               |2147483647       |2               |YES            |null           |null           |null           |null            |NO              |NO                
runTask        |null           |test2          |name.raw       |12             |KEYWORD        |32766          |2147483647     |null           |null           |1              |null           |null           |12             |0               |2147483647       |3               |YES            |null           |null           |null           |null            |NO              |NO                

The issue with the output above is that name is of type TEXT for both test1 and test2 whereas it should be TEXT for test1 and KEYWORD for test2.
This is like this because the data structure that holds the generated fields for the SYS COLUMNS output identifies them by name, without considering also the index it belongs to.

[elasticmachine]

Pinging @elastic/es-search (:Search/SQL)

[matriv]

@elastic/es-ql

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)