Skip to content

Commit cefd0b7

Browse files
jim-lakejim-lake
committed
handle collate everywhere its legal
1 parent 9f37d18 commit cefd0b7

5 files changed

Lines changed: 77 additions & 11 deletions

File tree

‎pegjs/mysql.pegjs‎

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3320,10 +3320,12 @@ unary_operator
33203320
= '!' / '-' / '+' / '~'
33213321

33223322
jsonb_expr
3323-
= head:primary __ tail: (__ ('?|' / '?&' / '?' / '#-' / '#>>' / '#>' / DOUBLE_ARROW / SINGLE_ARROW / '@>' / '<@') __ primary)* {
3323+
= head:primary __ tail: (__ ('?|' / '?&' / '?' / '#-' / '#>>' / '#>' / DOUBLE_ARROW / SINGLE_ARROW / '@>' / '<@') __ primary)* __ ce:(collate_expr)? {
33243324
// => primary | binary_expr
3325-
if (!tail || tail.length === 0) return head
3326-
return createBinaryExprChain(head, tail)
3325+
let result = head
3326+
if (tail && tail.length > 0) result = createBinaryExprChain(head, tail)
3327+
if (ce) result.collate = ce
3328+
return result
33273329
}
33283330

33293331
primary

‎src/binary.js‎

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,5 @@
11
import { exprToSQL } from './expr'
2+
import { collateToSQL } from './collate'
23
import { hasVal, toUpper } from './util'
34

45
function binaryToSQL(expr) {
@@ -25,9 +26,11 @@ function binaryToSQL(expr) {
2526
}
2627
const escape = expr.right.escape || {}
2728
const leftPart = Array.isArray(expr.left) ? expr.left.map(exprToSQL).join(', ') : exprToSQL(expr.left)
29+
const collateStr = collateToSQL(expr.collate)
2830
const str = [leftPart, operator, rstr, toUpper(escape.type), exprToSQL(escape.value)].filter(hasVal).join(operator === '.' ? '' : ' ')
29-
const result = [expr.parentheses ? `(${str})` : str]
30-
return result.join(' ')
31+
let result = expr.parentheses ? `(${str})` : str
32+
if (collateStr) result = `${result} ${collateStr}`
33+
return result
3134
}
3235

3336
export {

‎src/func.js‎

Lines changed: 8 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,5 @@
11
import { arrayIndexToSQL, columnOffsetToSQL } from './column'
2+
import { collateToSQL } from './collate'
23
import { exprToSQL, orderOrPartitionByToSQL } from './expr'
34
import { hasVal, identifierToSql, literalToSQL, toUpper } from './util'
45
import { overToSQL } from './over'
@@ -28,7 +29,7 @@ function arrayDimensionToSymbol(target) {
2829
}
2930

3031
function castToSQL(expr) {
31-
const { target: targets, expr: expression, keyword, symbol, as: alias, offset, parentheses: outParentheses } = expr
32+
const { target: targets, expr: expression, keyword, symbol, as: alias, offset, parentheses: outParentheses, collate } = expr
3233
let prefix = columnOffsetToSQL({ expr: expression, offset })
3334
const result = []
3435
for (let i = 0, len = targets.length; i < len; ++i) {
@@ -52,8 +53,9 @@ function castToSQL(expr) {
5253
targetResult.push(symbolChar, quoted, dataType, quoted, arrayDimension, str, suffix)
5354
result.push(targetResult.filter(hasVal).join(''))
5455
}
56+
const collateStr = collateToSQL(collate)
5557
if (alias) result.push(` AS ${identifierToSql(alias)}`)
56-
const sql = result.filter(hasVal).join('')
58+
const sql = [result.filter(hasVal).join(''), collateStr].filter(hasVal).join(' ')
5759
return outParentheses ? `(${sql})` : sql
5860
}
5961

@@ -98,12 +100,13 @@ function withinGroupToSQL(stmt) {
98100
}
99101

100102
function funcToSQL(expr) {
101-
const { args, array_index, name, args_parentheses, parentheses, within_group: withinGroup, over, suffix } = expr
103+
const { args, array_index, collate, name, args_parentheses, parentheses, within_group: withinGroup, over, suffix } = expr
102104
const overStr = overToSQL(over)
103105
const withinGroupStr = withinGroupToSQL(withinGroup)
104106
const suffixStr = exprToSQL(suffix)
107+
const collateStr = collateToSQL(collate)
105108
const funcName = [literalToSQL(name.schema), name.name.map(literalToSQL).join('.')].filter(hasVal).join('.')
106-
if (!args) return [funcName, withinGroupStr, overStr].filter(hasVal).join(' ')
109+
if (!args) return [funcName, collateStr, withinGroupStr, overStr].filter(hasVal).join(' ')
107110
const separator = expr.separator || ', '
108111
let fromPosition = 0
109112
if (toUpper(funcName) === 'TRIM') {
@@ -131,7 +134,7 @@ function funcToSQL(expr) {
131134
}
132135
if (args_parentheses !== false) str.push(')')
133136
str.push(arrayIndexToSQL(array_index))
134-
str = [str.join(''), suffixStr].filter(hasVal).join(' ')
137+
str = [str.join(''), suffixStr, collateStr].filter(hasVal).join(' ')
135138
return [parentheses ? `(${str})` : str, withinGroupStr, overStr].filter(hasVal).join(' ')
136139
}
137140

‎test/mysql-mariadb.spec.js‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1367,7 +1367,7 @@ describe('mysql', () => {
13671367

13681368
it('should throw error when args is not right', () => {
13691369
let sql = `select convert(json_unquote(json_extract('{"thing": "252"}', "$.thing")));`
1370-
expect(parser.astify.bind(parser, sql)).to.throw('Expected "!=", "#", "#-", "#>", "#>>", "%", "&", "&&", "*", "+", ",", "-", "--", "->", "->>", "/", "/*", "<", "<<", "<=", "<>", "<@", "=", ">", ">=", ">>", "?", "?&", "?|", "@>", "AND", "BETWEEN", "IN", "IS", "LIKE", "NOT", "ON", "OR", "OVER", "REGEXP", "RLIKE", "USING", "XOR", "^", "div", "mod", "|", "||", or [ \\t\\n\\r] but ")" found.')
1370+
expect(parser.astify.bind(parser, sql)).to.throw('Expected "!=", "#", "#-", "#>", "#>>", "%", "&", "&&", "*", "+", ",", "-", "--", "->", "->>", "/", "/*", "<", "<<", "<=", "<>", "<@", "=", ">", ">=", ">>", "?", "?&", "?|", "@>", "AND", "BETWEEN", "COLLATE", "IN", "IS", "LIKE", "NOT", "ON", "OR", "OVER", "REGEXP", "RLIKE", "USING", "XOR", "^", "div", "mod", "|", "||", or [ \\t\\n\\r] but ")" found.')
13711371
expect(parser.astify.bind(parser, 'select convert("");')).to.throw('Expected "!=", "#", "#-", "#>", "#>>", "%", "&", "&&", "*", "+", ",", "-", "--", "->", "->>", "/", "/*", "<", "<<", "<=", "<>", "<@", "=", ">", ">=", ">>", "?", "?&", "?|", "@>", "AND", "BETWEEN", "COLLATE", "IN", "IS", "LIKE", "NOT", "OR", "REGEXP", "RLIKE", "USING", "XOR", "^", "div", "mod", "|", "||", or [ \\t\\n\\r] but ")" found.')
13721372
sql = 'SELECT AVG(Quantity,age) FROM table1;'
13731373
expect(parser.astify.bind(parser, sql)).to.throw('Expected "!=", "#", "#-", "#>", "#>>", "%", "&", "&&", "(", ")", "*", "+", "-", "--", "->", "->>", ".", "/", "/*", "<", "<<", "<=", "<>", "<@", "=", ">", ">=", ">>", "?", "?&", "?|", "@>", "AND", "BETWEEN", "COLLATE", "IN", "IS", "LIKE", "NOT", "OR", "REGEXP", "RLIKE", "XOR", "^", "div", "mod", "|", "||", [ \\t\\n\\r], [A-Za-z0-9_$\\x80-￿], or [A-Za-z0-9_:一-龥À-ſ] but "," found')

‎test/select.spec.js‎

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -760,6 +760,64 @@ describe('select', () => {
760760
.to.equal("SELECT CONVERT('test', CHAR(10) CHARACTER SET utf8mb4)")
761761
expect(getParsedSql(`SELECT CONVERT('test' USING utf8mb4) COLLATE utf8mb4_bin;`))
762762
.to.equal("SELECT CONVERT('test' USING UTF8MB4) COLLATE utf8mb4_bin")
763+
expect(getParsedSql(`SELECT CONCAT("a", "b") COLLATE UTF8MB4_BIN = "AB" AS result`))
764+
.to.equal('SELECT CONCAT("a", "b") COLLATE UTF8MB4_BIN = "AB" AS `result`')
765+
})
766+
767+
it('should parse COLLATE on function expressions', () => {
768+
const ast = parser.astify('SELECT CONCAT("a", "b") COLLATE UTF8MB4_BIN = "AB" AS result');
769+
expect(ast.columns[0]).to.have.property('as', 'result');
770+
expect(ast.columns[0].expr).to.have.property('type', 'binary_expr');
771+
expect(ast.columns[0].expr).to.have.property('operator', '=');
772+
expect(ast.columns[0].expr.left).to.have.property('type', 'function');
773+
expect(ast.columns[0].expr.left).to.have.property('collate');
774+
expect(ast.columns[0].expr.left.collate).to.have.property('type', 'collate');
775+
expect(ast.columns[0].expr.left.collate.collate).to.have.property('name', 'UTF8MB4_BIN');
776+
})
777+
778+
it('should parse COLLATE on CONVERT expressions', () => {
779+
const ast = parser.astify("SELECT CONVERT('test' USING utf8mb4) COLLATE utf8mb4_bin");
780+
781+
expect(ast.columns[0].expr).to.have.property('type', 'function');
782+
expect(ast.columns[0].expr).to.have.property('collate');
783+
expect(ast.columns[0].expr.collate).to.have.property('type', 'collate');
784+
expect(ast.columns[0].expr.collate.collate).to.have.property('name', 'utf8mb4_bin');
785+
})
786+
787+
it('should parse COLLATE on parenthesized expressions', () => {
788+
const ast = parser.astify("SELECT * FROM product WHERE (id = '1' OR id = '2') COLLATE utf8mb4_general_ci");
789+
790+
expect(ast.where).to.have.property('type', 'binary_expr');
791+
expect(ast.where).to.have.property('operator', 'OR');
792+
expect(ast.where).to.have.property('parentheses', true);
793+
expect(ast.where).to.have.property('collate');
794+
expect(ast.where.collate).to.have.property('type', 'collate');
795+
expect(ast.where.collate.collate).to.have.property('name', 'utf8mb4_general_ci');
796+
})
797+
798+
it('should parse COLLATE on CAST expressions', () => {
799+
const ast = parser.astify('SELECT CAST(test AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin');
800+
801+
expect(ast.columns[0].expr).to.have.property('type', 'cast');
802+
expect(ast.columns[0].expr).to.have.property('collate');
803+
expect(ast.columns[0].expr.collate).to.have.property('type', 'collate');
804+
expect(ast.columns[0].expr.collate.collate).to.have.property('name', 'utf8mb4_bin');
805+
})
806+
807+
it('should parse COLLATE on arithmetic expressions', () => {
808+
const ast = parser.astify('SELECT (1 + 2) COLLATE UTF8_BIN = "12" AS result');
809+
810+
expect(ast.columns[0]).to.have.property('as', 'result');
811+
expect(ast.columns[0].expr).to.have.property('type', 'binary_expr');
812+
expect(ast.columns[0].expr).to.have.property('operator', '=');
813+
expect(ast.columns[0].expr.left).to.have.property('type', 'binary_expr');
814+
expect(ast.columns[0].expr.left).to.have.property('parentheses', true);
815+
expect(ast.columns[0].expr.left).to.have.property('collate');
816+
expect(ast.columns[0].expr.left.collate).to.have.property('type', 'collate');
817+
expect(ast.columns[0].expr.left.collate.collate).to.have.property('name', 'UTF8_BIN');
818+
})
819+
820+
it('should support convert additive expr', () => {
763821
expect(getParsedSql(`select TYPE,taxpayer_Type,CONVERT(tax_Amount, DECIMAL(12,2)) AS tax_amount,CAST(tax_currency AS DECIMAL(12,2)) tax_currency from rs_order_tax where billno="{{billno}}" and Business_Type="order";`))
764822
.to.equal('SELECT `TYPE`, `taxpayer_Type`, CONVERT(`tax_Amount`, DECIMAL(12, 2)) AS `tax_amount`, CAST(`tax_currency` AS DECIMAL(12, 2)) AS `tax_currency` FROM `rs_order_tax` WHERE `billno` = "{{billno}}" AND `Business_Type` = "order"')
765823
expect(getParsedSql(`SELECT CONVERT('test', INT(11) unsigned);`))

0 commit comments

Comments
 (0)