Merge pull request taozhi8833998#783 from taozhi8833998/bug-func-mysql

taozhi8833998 · web-flow · commit 7b0935630ca8 · 2021-10-23T10:27:03.000+08:00
bug: support left function name and convert function expr in mysql
diff --git a/pegjs/mysql.pegjs b/pegjs/mysql.pegjs
@@ -2087,8 +2087,30 @@ count_arg
 star_expr
   = "*" { return { type: 'star', value: '*' }; }
 
+convert_args
+  = c:column_ref __ COMMA __ d:data_type {
+    return {
+      type: 'expr_list',
+      value: [c, { value: d.dataType.toUpperCase() }]
+    }
+  }
+  / c:column_ref __ KW_USING __ d:ident_name {
+    c.suffix = `USING ${d}`
+    return {
+      type: 'expr_list',
+      value: [c]
+    }
+  }
+
 func_call
-  = name:proc_func_name __ LPAREN __ l:expr_list? __ RPAREN __ bc:over_partition? {
+  = 'convert'i __ LPAREN __ l:convert_args __ RPAREN {
+    return {
+        type: 'function',
+        name: 'CONVERT',
+        args: l
+    };
+  }
+  / name:proc_func_name __ LPAREN __ l:expr_list? __ RPAREN __ bc:over_partition? {
       return {
         type: 'function',
         name: name,
@@ -2637,6 +2659,7 @@ proc_func_name
       }
       return name;
     }
+    / KW_LEFT { return 'LEFT' }
 
 proc_func_call
   = name:proc_func_name __ LPAREN __ l:proc_primary_list? __ RPAREN {
diff --git a/src/column.js b/src/column.js
@@ -22,6 +22,7 @@ function columnOffsetToSQL(column, isDual) {
 function columnRefToSQL(expr) {
   const {
     arrow, as, collate, column, isDual, table, parentheses, property,
+    suffix,
   } = expr
   let str = column === '*' ? '*' : columnOffsetToSQL(column, isDual)
   if (table) str = `${identifierToSql(table)}.${str}`
@@ -31,6 +32,7 @@ function columnRefToSQL(expr) {
     commonOptionConnector(arrow, literalToSQL, property),
   ]
   if (collate) result.push(commonTypeValue(collate).join(' '))
+  result.push(toUpper(suffix))
   const sql = result.filter(hasVal).join(' ')
   return parentheses ? `(${sql})` : sql
 }
diff --git a/src/expr.js b/src/expr.js
@@ -19,11 +19,11 @@ const exprToSQLConvertFn = {
   window_func : windowFuncToSQL,
   'array'     : arrayStructExprToSQL,
   assign      : assignToSQL,
-  extract     : extractFunToSQL,
   binary_expr : binaryToSQL,
   case        : caseToSQL,
   cast        : castToSQL,
   column_ref  : columnRefToSQL,
+  extract     : extractFunToSQL,
   function    : funcToSQL,
   interval    : intervalToSQL,
   show        : showToSQL,
diff --git a/test/select.spec.js b/test/select.spec.js
@@ -103,7 +103,6 @@ describe('select', () => {
 
   it('should have appropriate types', () => {
     const ast = parser.astify('SELECT SQL_NO_CACHE DISTINCT a FROM b WHERE c = 0 GROUP BY d ORDER BY e limit 3');
-
     expect(ast.options).to.be.an('array');
     expect(ast.distinct).to.equal('DISTINCT');
     expect(ast.from).to.be.an('array');
@@ -669,6 +668,14 @@ describe('select', () => {
       expect(ast.orderby).to.be.null;
       expect(ast.limit).to.be.null;
     })
+    it('should support left and covert fun', () => {
+      expect(getParsedSql(`select * from test where LEFT(column,2)="ts";`))
+        .to.equal("SELECT * FROM `test` WHERE LEFT(`column`, 2) = 'ts'")
+      expect(getParsedSql(`select * from test where CONVERT(column, DATE)="test";`))
+        .to.equal("SELECT * FROM `test` WHERE CONVERT(`column`, DATE) = 'test'")
+      expect(getParsedSql(`select * from test where CONVERT(column using utf8)="test";`))
+        .to.equal("SELECT * FROM `test` WHERE CONVERT(`column` USING UTF8) = 'test'")
+    })
   })
 
   describe('limit clause', () => {
