Added badly documented data type alias

[xsist10]

Problem

355

When parsing the following query:

  CREATE TABLE `a` (
      `b` CHARACTER(20)
  );

I received the following warning and the parsed result did not contain column b:

  PHP Notice:  Undefined variable: prevCategory in /vendor/greenlion/php-sql-parser/src/PHPSQLParser/processors/ColumnDefinitionProcessor.php on line 370

I dug into it a bit more and I realised the code didn't support some of the less well documented data types (or rather data type aliases).

Understanding aliases

Creating a table using these aliases is acceptable and will automatically map them to the more familiar types.

  CREATE TABLE `test_alias` (
      `a` INTEGER,
      `b` CHARACTER(10),
      `c` VARCHARACTER(10),
      `d` INT2,
      `e` INT3,
      `f` INT4,
      `g` INT8,
      `h` FLOAT4,
      `i` FLOAT8,
      `j` MIDDLEINT
  );

  SHOW CREATE TABLE test_alias \G
  *************************** 1. row ***************************
         Table: test
  Create Table: CREATE TABLE `test_alias` (
    `a` int DEFAULT NULL,
    `b` char(10) DEFAULT NULL,
    `c` varchar(10) DEFAULT NULL,
    `d` smallint DEFAULT NULL,
    `e` mediumint DEFAULT NULL,
    `f` int DEFAULT NULL,
    `g` bigint DEFAULT NULL,
    `h` float DEFAULT NULL,
    `i` double DEFAULT NULL,
    `j` mediumint DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Real world impact

While these types are not commonly used, there are cases where you will end up parsing a query using them. One example is the performance_schema which seems to use some of the aliases in the DIGEST_TEXT fields.

So if I create this table:

  CREATE TABLE `test_digest` (
      `a` INT,
      `b` CHAR(10),
      `c` VARCHAR(10),
      `d` BIGINT,
      `e` FLOAT,
      `f` DOUBLE,
      `g` MEDIUMINT
  );

and then I ask the performance_schema about this query, it will return this:

  SELECT DIGEST_TEXT
  FROM performance_schema.events_statements_summary_by_digest
  WHERE DIGEST_TEXT LIKE "CREATE TABLE `test_digest` %" \G
  *************************** 1. row ***************************
  DIGEST_TEXT: CREATE TABLE `test_digest` (
      `a` INTEGER ,
      `b` CHARACTER (?) ,
      `c` VARCHARACTER (?) ,
      `d` INT8 ,
      `e` FLOAT4 ,
      `f` FLOAT8 ,
      `g` MIDDLEINT
  )

Solution

Add the missing aliases (or at least the ones I can find based on examples and trawling through the MySQL Keywords List)

Added Aliases

CHARACTER - CHAR
VARCHARACTER - VARCHAR
INT2 - SMALLINT
INT3 - MEDIUMINT
MIDDLEINT - MEDIUMINT
INT4 - INT
INT8 - BIGINT
FLOAT4 - FLOAT
FLOAT8 - DOUBLE

[greenlion]

Thank you for your contribution.