varchar2 support

[yitsushi]

What do you want to change?

Oracle has a varchar2 datatype, and it's the same as varchar (at least for now), but it can change in the future. Because of some scanning tools, like SonarCube yells at us to use varchar2 with the following reason:

Currently, VARCHAR and VARCHAR2 are identical data types. But to prevent future changes in behavior, Oracle recommends the use of VARCHAR2.

• varchar is ANSI, varchar2 is Oracle.
• With MariaDB: "In Oracle mode from MariaDB 10.3, VARCHAR2 is a synonym."
• I think the generated code shouldn't change much, I think it can be handled the same way as varchar to generate code. People can still use varchar.

Right now if I try to use varchar2 it fails with syntax error):

❯ sqlc generate
# package database
db/migrations/20240510112356_init.sql:3:16: syntax error near "varchar2(36) not null primary key,"

References:

• https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-0BC16006-32F1-42B1-B45E-F27A494963FF
• https://mariadb.com/kb/en/varchar/#oracle-mode

What database engines need to be changed?

MySQL

What programming language backends need to be changed?

Go

[yitsushi]

I check what I can do, but it seems to me it's a pingcap/tidb issue as this hits error in internal/engine/dolphin/parse.go:

    stmtNodes, _, err := p.pingcap.Parse(string(blob), "", "")
    if err != nil {
        return nil, normalizeErr(err)
    }

If I understand that parser correctly, it doesn't know anything about varchar2 based on this file: https://github.com/pingcap/tidb/blob/master/pkg/parser/parser.y