Skip to content

Commit 032bcf9

Browse files
matrivmatriv
committed
SQL: Implement :: cast operator (#38774)
`<expression>::<dataType>` is a simplified altenative syntax to `CAST(<expression> AS <dataType> which exists in PostgreSQL and provides an improved user experience and possibly more compact SQL queries. Fixes: #38717
1 parent 12eac6a commit 032bcf9

13 files changed

Lines changed: 1078 additions & 933 deletions

File tree

‎docs/reference/sql/functions/operators.asciidoc‎

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -126,9 +126,21 @@ include-tagged::{sql-specs}/arithmetic.sql-spec[multiply]
126126
include-tagged::{sql-specs}/arithmetic.sql-spec[divide]
127127
--------------------------------------------------
128128

129-
* https://en.wikipedia.org/wiki/Modulo_operation[Modulo] or Reminder(`%`)
129+
* https://en.wikipedia.org/wiki/Modulo_operation[Modulo] or Remainder(`%`)
130130

131131
["source","sql",subs="attributes,callouts,macros"]
132132
--------------------------------------------------
133133
include-tagged::{sql-specs}/arithmetic.sql-spec[mod]
134134
--------------------------------------------------
135+
136+
[[sql-operators-cast]]
137+
=== Cast Operators
138+
139+
* Cast (`::`)
140+
141+
`::` provides an alternative syntax to the <<sql-functions-type-conversion-cast>> function.
142+
143+
["source","sql",subs="attributes,callouts,macros"]
144+
--------------------------------------------------
145+
include-tagged::{sql-specs}/docs.csv-spec[conversionStringToLongCastOperator]
146+
--------------------------------------------------

‎x-pack/plugin/sql/qa/src/main/resources/docs.csv-spec‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1541,6 +1541,16 @@ SELECT CONVERT('123', SQL_INTEGER) AS int;
15411541
// end::conversionStringToIntConvertODBCDataType
15421542
;
15431543

1544+
conversionStringToLongCastOperator
1545+
// tag::conversionStringToLongCastOperator
1546+
SELECT '123'::long AS long;
1547+
1548+
long
1549+
---------------
1550+
123
1551+
// end::conversionStringToLongCastOperator
1552+
;
1553+
15441554

15451555
///////////////////////////////
15461556
//

‎x-pack/plugin/sql/src/main/antlr/SqlBase.g4‎

Lines changed: 12 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -210,6 +210,7 @@ valueExpression
210210
| left=valueExpression operator=(ASTERISK | SLASH | PERCENT) right=valueExpression #arithmeticBinary
211211
| left=valueExpression operator=(PLUS | MINUS) right=valueExpression #arithmeticBinary
212212
| left=valueExpression comparisonOperator right=valueExpression #comparison
213+
| valueExpression CAST_OP dataType #castOperatorExpression
213214
;
214215

215216
primaryExpression
@@ -224,22 +225,22 @@ primaryExpression
224225
| '(' expression ')' #parenthesizedExpression
225226
;
226227

228+
builtinDateTimeFunction
229+
: name=CURRENT_DATE ('(' ')')?
230+
| name=CURRENT_TIMESTAMP ('(' precision=INTEGER_VALUE? ')')?
231+
;
232+
227233
castExpression
228-
: castTemplate
229-
| FUNCTION_ESC castTemplate ESC_END
234+
: castTemplate
235+
| FUNCTION_ESC castTemplate ESC_END
230236
| convertTemplate
231237
| FUNCTION_ESC convertTemplate ESC_END
232238
;
233-
239+
234240
castTemplate
235241
: CAST '(' expression AS dataType ')'
236242
;
237243

238-
builtinDateTimeFunction
239-
: name=CURRENT_DATE ('(' ')')?
240-
| name=CURRENT_TIMESTAMP ('(' precision=INTEGER_VALUE? ')')?
241-
;
242-
243244
convertTemplate
244245
: CONVERT '(' expression ',' dataType ')'
245246
;
@@ -457,6 +458,7 @@ GUID_ESC: '{GUID';
457458

458459
ESC_END: '}';
459460

461+
// Operators
460462
EQ : '=';
461463
NULLEQ: '<=>';
462464
NEQ : '<>' | '!=';
@@ -470,6 +472,7 @@ MINUS: '-';
470472
ASTERISK: '*';
471473
SLASH: '/';
472474
PERCENT: '%';
475+
CAST_OP: '::';
473476
CONCAT: '||';
474477
DOT: '.';
475478
PARAM: '?';
@@ -494,7 +497,7 @@ IDENTIFIER
494497
;
495498

496499
DIGIT_IDENTIFIER
497-
: DIGIT (LETTER | DIGIT | '_' | '@' | ':')+
500+
: DIGIT (LETTER | DIGIT | '_' | '@')+
498501
;
499502

500503
TABLE_IDENTIFIER

‎x-pack/plugin/sql/src/main/antlr/SqlBase.tokens‎

Lines changed: 21 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -109,22 +109,23 @@ MINUS=108
109109
ASTERISK=109
110110
SLASH=110
111111
PERCENT=111
112-
CONCAT=112
113-
DOT=113
114-
PARAM=114
115-
STRING=115
116-
INTEGER_VALUE=116
117-
DECIMAL_VALUE=117
118-
IDENTIFIER=118
119-
DIGIT_IDENTIFIER=119
120-
TABLE_IDENTIFIER=120
121-
QUOTED_IDENTIFIER=121
122-
BACKQUOTED_IDENTIFIER=122
123-
SIMPLE_COMMENT=123
124-
BRACKETED_COMMENT=124
125-
WS=125
126-
UNRECOGNIZED=126
127-
DELIMITER=127
112+
CAST_OP=112
113+
CONCAT=113
114+
DOT=114
115+
PARAM=115
116+
STRING=116
117+
INTEGER_VALUE=117
118+
DECIMAL_VALUE=118
119+
IDENTIFIER=119
120+
DIGIT_IDENTIFIER=120
121+
TABLE_IDENTIFIER=121
122+
QUOTED_IDENTIFIER=122
123+
BACKQUOTED_IDENTIFIER=123
124+
SIMPLE_COMMENT=124
125+
BRACKETED_COMMENT=125
126+
WS=126
127+
UNRECOGNIZED=127
128+
DELIMITER=128
128129
'('=1
129130
')'=2
130131
','=3
@@ -235,6 +236,7 @@ DELIMITER=127
235236
'*'=109
236237
'/'=110
237238
'%'=111
238-
'||'=112
239-
'.'=113
240-
'?'=114
239+
'::'=112
240+
'||'=113
241+
'.'=114
242+
'?'=115

‎x-pack/plugin/sql/src/main/antlr/SqlBaseLexer.tokens‎

Lines changed: 20 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -109,21 +109,22 @@ MINUS=108
109109
ASTERISK=109
110110
SLASH=110
111111
PERCENT=111
112-
CONCAT=112
113-
DOT=113
114-
PARAM=114
115-
STRING=115
116-
INTEGER_VALUE=116
117-
DECIMAL_VALUE=117
118-
IDENTIFIER=118
119-
DIGIT_IDENTIFIER=119
120-
TABLE_IDENTIFIER=120
121-
QUOTED_IDENTIFIER=121
122-
BACKQUOTED_IDENTIFIER=122
123-
SIMPLE_COMMENT=123
124-
BRACKETED_COMMENT=124
125-
WS=125
126-
UNRECOGNIZED=126
112+
CAST_OP=112
113+
CONCAT=113
114+
DOT=114
115+
PARAM=115
116+
STRING=116
117+
INTEGER_VALUE=117
118+
DECIMAL_VALUE=118
119+
IDENTIFIER=119
120+
DIGIT_IDENTIFIER=120
121+
TABLE_IDENTIFIER=121
122+
QUOTED_IDENTIFIER=122
123+
BACKQUOTED_IDENTIFIER=123
124+
SIMPLE_COMMENT=124
125+
BRACKETED_COMMENT=125
126+
WS=126
127+
UNRECOGNIZED=127
127128
'('=1
128129
')'=2
129130
','=3
@@ -234,6 +235,7 @@ UNRECOGNIZED=126
234235
'*'=109
235236
'/'=110
236237
'%'=111
237-
'||'=112
238-
'.'=113
239-
'?'=114
238+
'::'=112
239+
'||'=113
240+
'.'=114
241+
'?'=115

‎x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/parser/ExpressionBuilder.java‎

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -423,7 +423,7 @@ public DataType visitPrimitiveDataType(PrimitiveDataTypeContext ctx) {
423423
case "ip":
424424
return DataType.IP;
425425
default:
426-
throw new ParsingException(source(ctx), "Does not recognize type {}", type);
426+
throw new ParsingException(source(ctx), "Does not recognize type [{}]", ctx.getText());
427427
}
428428
}
429429

@@ -443,7 +443,7 @@ public Cast visitCastExpression(CastExpressionContext ctx) {
443443
dataType = DataType.fromOdbcType(convertDataType);
444444
if (dataType == null) {
445445
throw new ParsingException(source(convertTc.dataType()), "Invalid data type [{}] provided", convertDataType);
446-
}
446+
}
447447
} else {
448448
try {
449449
dataType = DataType.valueOf(convertDataType);
@@ -455,6 +455,11 @@ public Cast visitCastExpression(CastExpressionContext ctx) {
455455
}
456456
}
457457

458+
@Override
459+
public Object visitCastOperatorExpression(SqlBaseParser.CastOperatorExpressionContext ctx) {
460+
return new Cast(source(ctx), expression(ctx.valueExpression()), typedParsing(ctx.dataType(), DataType.class));
461+
}
462+
458463
@Override
459464
public Function visitExtractExpression(ExtractExpressionContext ctx) {
460465
ExtractTemplateContext template = ctx.extractTemplate();

‎x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/parser/SqlBaseBaseListener.java‎

Lines changed: 18 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -599,6 +599,18 @@ class SqlBaseBaseListener implements SqlBaseListener {
599599
* <p>The default implementation does nothing.</p>
600600
*/
601601
@Override public void exitComparison(SqlBaseParser.ComparisonContext ctx) { }
602+
/**
603+
* {@inheritDoc}
604+
*
605+
* <p>The default implementation does nothing.</p>
606+
*/
607+
@Override public void enterCastOperatorExpression(SqlBaseParser.CastOperatorExpressionContext ctx) { }
608+
/**
609+
* {@inheritDoc}
610+
*
611+
* <p>The default implementation does nothing.</p>
612+
*/
613+
@Override public void exitCastOperatorExpression(SqlBaseParser.CastOperatorExpressionContext ctx) { }
602614
/**
603615
* {@inheritDoc}
604616
*
@@ -736,37 +748,37 @@ class SqlBaseBaseListener implements SqlBaseListener {
736748
*
737749
* <p>The default implementation does nothing.</p>
738750
*/
739-
@Override public void enterCastExpression(SqlBaseParser.CastExpressionContext ctx) { }
751+
@Override public void enterBuiltinDateTimeFunction(SqlBaseParser.BuiltinDateTimeFunctionContext ctx) { }
740752
/**
741753
* {@inheritDoc}
742754
*
743755
* <p>The default implementation does nothing.</p>
744756
*/
745-
@Override public void exitCastExpression(SqlBaseParser.CastExpressionContext ctx) { }
757+
@Override public void exitBuiltinDateTimeFunction(SqlBaseParser.BuiltinDateTimeFunctionContext ctx) { }
746758
/**
747759
* {@inheritDoc}
748760
*
749761
* <p>The default implementation does nothing.</p>
750762
*/
751-
@Override public void enterCastTemplate(SqlBaseParser.CastTemplateContext ctx) { }
763+
@Override public void enterCastExpression(SqlBaseParser.CastExpressionContext ctx) { }
752764
/**
753765
* {@inheritDoc}
754766
*
755767
* <p>The default implementation does nothing.</p>
756768
*/
757-
@Override public void exitCastTemplate(SqlBaseParser.CastTemplateContext ctx) { }
769+
@Override public void exitCastExpression(SqlBaseParser.CastExpressionContext ctx) { }
758770
/**
759771
* {@inheritDoc}
760772
*
761773
* <p>The default implementation does nothing.</p>
762774
*/
763-
@Override public void enterBuiltinDateTimeFunction(SqlBaseParser.BuiltinDateTimeFunctionContext ctx) { }
775+
@Override public void enterCastTemplate(SqlBaseParser.CastTemplateContext ctx) { }
764776
/**
765777
* {@inheritDoc}
766778
*
767779
* <p>The default implementation does nothing.</p>
768780
*/
769-
@Override public void exitBuiltinDateTimeFunction(SqlBaseParser.BuiltinDateTimeFunctionContext ctx) { }
781+
@Override public void exitCastTemplate(SqlBaseParser.CastTemplateContext ctx) { }
770782
/**
771783
* {@inheritDoc}
772784
*

‎x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/parser/SqlBaseBaseVisitor.java‎

Lines changed: 10 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -354,6 +354,13 @@ class SqlBaseBaseVisitor<T> extends AbstractParseTreeVisitor<T> implements SqlBa
354354
* {@link #visitChildren} on {@code ctx}.</p>
355355
*/
356356
@Override public T visitComparison(SqlBaseParser.ComparisonContext ctx) { return visitChildren(ctx); }
357+
/**
358+
* {@inheritDoc}
359+
*
360+
* <p>The default implementation returns the result of calling
361+
* {@link #visitChildren} on {@code ctx}.</p>
362+
*/
363+
@Override public T visitCastOperatorExpression(SqlBaseParser.CastOperatorExpressionContext ctx) { return visitChildren(ctx); }
357364
/**
358365
* {@inheritDoc}
359366
*
@@ -437,21 +444,21 @@ class SqlBaseBaseVisitor<T> extends AbstractParseTreeVisitor<T> implements SqlBa
437444
* <p>The default implementation returns the result of calling
438445
* {@link #visitChildren} on {@code ctx}.</p>
439446
*/
440-
@Override public T visitCastExpression(SqlBaseParser.CastExpressionContext ctx) { return visitChildren(ctx); }
447+
@Override public T visitBuiltinDateTimeFunction(SqlBaseParser.BuiltinDateTimeFunctionContext ctx) { return visitChildren(ctx); }
441448
/**
442449
* {@inheritDoc}
443450
*
444451
* <p>The default implementation returns the result of calling
445452
* {@link #visitChildren} on {@code ctx}.</p>
446453
*/
447-
@Override public T visitCastTemplate(SqlBaseParser.CastTemplateContext ctx) { return visitChildren(ctx); }
454+
@Override public T visitCastExpression(SqlBaseParser.CastExpressionContext ctx) { return visitChildren(ctx); }
448455
/**
449456
* {@inheritDoc}
450457
*
451458
* <p>The default implementation returns the result of calling
452459
* {@link #visitChildren} on {@code ctx}.</p>
453460
*/
454-
@Override public T visitBuiltinDateTimeFunction(SqlBaseParser.BuiltinDateTimeFunctionContext ctx) { return visitChildren(ctx); }
461+
@Override public T visitCastTemplate(SqlBaseParser.CastTemplateContext ctx) { return visitChildren(ctx); }
455462
/**
456463
* {@inheritDoc}
457464
*

0 commit comments

Comments
 (0)