1/* Part of SWI-Prolog 2 3 Author: Matt Lilley 4 E-mail: matt.s.lilley@gmail.com 5 WWW: http://www.swi-prolog.org 6 Copyright (c) 2014, Mike Elston, Matt Lilley 7 All rights reserved. 8 9 Redistribution and use in source and binary forms, with or without 10 modification, are permitted provided that the following conditions 11 are met: 12 13 1. Redistributions of source code must retain the above copyright 14 notice, this list of conditions and the following disclaimer. 15 16 2. Redistributions in binary form must reproduce the above copyright 17 notice, this list of conditions and the following disclaimer in 18 the documentation and/or other materials provided with the 19 distribution. 20 21 THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 22 "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT 23 LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS 24 FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE 25 COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, 26 INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, 27 BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 28 LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER 29 CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT 30 LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN 31 ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 32 POSSIBILITY OF SUCH DAMAGE. 33*/ 34 35/* PostgreSQL is a trademark of the PostgreSQL Global Development Group. 36 Microsoft, SQL Server, and Windows are either registered trademarks or 37 trademarks of Microsoft Corporation in the United States and/or other 38 countries. SQLite is a registered trademark of Hipp, Wyrick & Company, 39 Inc in the United States. All other trademarks or registered trademarks 40 are the property of their respective owners. 41*/ 42 43:-module(sql_parser, [sql_gripe_level/1, 44 sql_parse/4, 45 strip_sql_comments/2]).

SQL Parser

This module contains an SQL parser

sql_parse/4

Parsing is invoked with sql_parse(+Term, -TrailingComments, +Options, +Tokens). Notice that all terms are bound when the predicate is called: you must direct the parser where to start. For a view definition, an example invocation might be sql_tokens(Tokens, "CREATE VIEW foo AS SELECT bar FROM qux", []), sql_parse(view_definition(Definition, Types), TrailingComments, [], Tokens). ---++ Comments Because comments can appear literally anywhere in the input text, every parse node has both a syntax element (such as view_definition/2) and a list of comments which preceed the element. This means comments are pushed as far as possible down the syntax tree. Any transformations of the input with the intention that it should be printed out again need to take the comments into account. Any other uses of the parse tree may pass it to strip_sql_comments(+InTree, -OutTree) to simply remove them all, leaving the tree with just the syntactic elements.

Finally, there may be trailing comments at the end of the input which are not followed by any token. This means they're not absorbed into the parse tree - so that they're not lost, they are returned as a list from sql_parse/4. ---++ Options Current options include:

dbms(+DBMS): If omitted, DBMS will be set to 'Microsoft SQL Server'. For the most part only 'Microsoft SQL Server' views are parseable, but it would not be difficult to extend the parser if this was ever required (just search the source for dbms('Microsoft SQL Server') conditionals)
view_name(+Name): Passed to gripe/6 so that complaints about view syntax can be associated with a view name

Internally used options include (these should not be passed in under normal circumstances)

query_id(-Qid): Used to logically separate distinct parts of the query
subquery: Used to flag whether currently in the top-level query or a subquery

Parse tree

The parse tree returned can be very complicated. The best documentation for this is probably either the sql_write or the sql_check module, which take the tree as an input and do processing on it. ---++ Type inference Type inference makes the parser take almost 4 times longer, but the resulting information is very useful. It is rarely possible to tell as the input is read what the type of each element is. Where possible, the types are defined (for example, the type of count(*) is always native_type(int)) but where the type is unknown, a new variable is created and a constraint is made.

Type inference is done with CHR, and types are in one of three states: 1 Known, and bound (ie committed) 2 Unknown with one unresolved dependency 3 Unknown with two unresolved dependencies

A dependency here refers to something which would influence the eventual type. Some examples of the slightly more complicated case 2:

The type of a column selected from a table that we have not yet resolved
- Consider SELECT foo FROM bar: Until we read the FROM clause we cannot begin to guess what the type of foo is, even though it has only one dependency
The type of a scalar subquery
- SELECT (SELECT TOP 1 foo FROM bar) AS q: In this case, the type of q is not actually a subquery, it is the single element that subquery returns. A constraint-handling rule checks for a type constraint of type scalar(_) and replaces it with the single element it contains.
The type of an set operation (ie aggregation)
- SELECT SUM(foo) AS q FROM bar: The type of q may be coerced to a decimal, depending on the eventual type of foo, which is not known until we have read the FROM clause

Some examples of case 3:

The type of a column which is the union of two selects
The type of an arithmetic expression

Internal Details

Syntax of the grammar

The grammar started out as an EBNF format, and is based roughly on http://savage.net.au/SQL/sql-92.bnf.html {}/1 are escaped Prolog, like in a DCG [...] denote optional clauses | denotes options @foo matches the token foo (case-insensitive matching is employed) #Foo matches the next token with Foo #Foo:Type matches the next token with Foo if it is a literal of type Type

Left factoring types

SQL Server has some very complicated rules for inferring the type of decimal arithmetic (see http://msdn.microsoft.com/en-us/library/ms190476). The crucial, yet sadly missing information from that page deals with overflows. This is half-explained at http://blogs.msdn.com/b/sqlprogrammability/archive/2006/03/29/564110.aspx.

Because we have truncation, the order of operations is crucial: Although (x * y) / z is mathematically equivalent to x * (y / z), the types of the two expressions in SQL Server are actually different due to truncation. The parser is LL, but this means we will always read x * y / z as x * (y / z), whereas SQL Server does the type inference in reverse. This is only a problem for division and multiplication since the handling of addition and subtraction are symmetric, but without a transformation, we will compute the wrong type. After a term/2 is parsed, left_factor_types/3 is called, which translates just the types in the term from LL into LR form.

Uses

Automatic determination of the view interface from the view SQL. These are prone to bit rot:
- Someone changes the SQL but forgets to change the interface
- Someone changes the underlying type of a table or view which is directly or indirectly referenced by the view. It's a burden to find all views which might reference the table which has been changes
Determination of view hierarchies
- This can be used to determine the order in which views should be refreshed or replaced/installed from metadata
- Can also be used with the load dependency analysis
Determination of indices
- Parsing the view allows us to build a set of disjunctions used in WHERE clauses
Sanity checking
- Some of the views contain some very, very weird things. Currently there is no oversight or review. If gripes are enabled at compile-time, the quality of code in views can be brought up to a higher standard
- Some views may contain extremely inefficient logical structures. If the parse-tree can be suitably analysed, more efficient equivalent queries can be automatically generated. In any case, uses of patterns which are known to be inefficient can be reported at compile-time
- DBMS indepdence
  - Transforming the views is relatively simple once we have the tree isolated. This can allow us to customize views to take advantage of features in later version of SQL Server while not alienating clients using older versions
  - If necessary, we can translate views to run on other DBMS, such as Oracle, 'PostgreSQL' or MySQL

Known problems

% It is not practical to determine what + means ahead of time if the source view is MS T-SQL. We would have to guess and backtrack if wrong, and that is horribly inefficient. Instead if we read + in 'Microsoft SQL Server' mode, we should delay determining whether it is really + or actually || until the types of the LHS and RHS are resolved. */

124:-op(95, fx, @). 125:-op(100, fx, #). 126% Disable some operators defined elsewhere! 127:-op(0, xfx, as). 128:-op(0, xfx, on). 129:-op(0, xfx, or). 130:-op(0, xfx, and). 131:-op(0, xfx, not). 132:-op(0, xfx, in). 133:-op(0, xfx, like). 134:-op(100, fx, ??). 135:-op(1200, xfx, --->). 136 137:-use_module(library(chr)). 138:-use_module(library(quintus), [otherwise/0]). 139:-use_module(library(dcg/basics)). 140:-use_module(library(cql/sql_keywords)). 141:-use_module(library(cql/sql_write)). 142:-use_module(library(cql/sql_tokenizer)). 143 144:-use_module(library(cql/cql), [default_schema/1, 145 cql_normalize_name/3, 146 dbms/2, 147 database_attribute/8, 148 domain_database_data_type/2, 149 routine_return_type/3, 150 sql_gripe/3]). 151 152:-chr_option(line_numbers, on). 153:-chr_option(check_guard_bindings, error). 154:-chr_option(debug, off). 155:-chr_option(optimize, full). 156:-chr_option(guard_simplification, off). % Added to stop trail overflowing 157 158:-chr_type 'Table' == any. 159:-chr_type 'Alias' == any. 160:-chr_type 'Type' == any. 161:-chr_type 'Column' == any. 162:-chr_type 'Constraint' == any. 163:-chr_type 'Vars' == any. 164:-chr_type 'N' == any. 165:-chr_type 'QueryId' == any. 166:-chr_type 'Source' == any. 167 168:-chr_constraint type_constraint(-'QueryId', ?'Source', ?'Type', ?'Constraint'). 169:-chr_constraint type_constraint_ready(-'QueryId', ?'Type'). 170:-chr_constraint type_merge_hint(?'Type', ?'Constraint'). 171:-chr_constraint query_table(-'QueryId', ?'Alias', ?'Table'). 172:-chr_constraint derived_query_column(-'QueryId', ?'Alias', ?'Column', ?'Type'). 173:-chr_constraint subquery(-'QueryId', -'QueryId'). 174:-chr_constraint peer_query(-'QueryId', -'QueryId'). 175:-chr_constraint query_is_xml(-'QueryId'). 176:-chr_constraint resolve_types(-'QueryId'). 177:-chr_constraint commit(-'QueryId'). 178:-chr_constraint union_type(-'QueryId', ?'Constraint', ?'Constraint', ?'Constraint'). 179:-chr_constraint derived_table(-'QueryId', +'Table', ?'Constraint'). 180:-chr_constraint find_all_column_types(-'QueryId', ?'Source', ?'Type'). 181:-chr_constraint force_type_not_domain(?'Type'). 182:-chr_constraint frozen_reverse(-'QueryId', ?'Constraint', ?'Constraint'). 183:-chr_constraint cleanup(-'QueryId'). 184 185:-dynamic(cached_gripe_level/1). 186sql_gripe_level(N):- 187 cached_gripe_level(N), !. 188sql_gripe_level(N):- 189 ( getenv('SQL_GRIPE_LEVEL', Atom), 190 atom_number(Atom, N)-> 191 true 192 ; otherwise-> 193 N = 0 % FIXME: Should be 1, Testing only 194 ), 195 assert(cached_gripe_level(N)). 196 197 198stream_to_tokens(Stream, Tokens):- 199 stream_to_lazy_list(Stream, List), 200 sql_tokens(Tokens, List, []), !. 201 202 203sql_parse(Head, TrailingComments, Options, Tokens):- 204 Head =.. [Functor, Arg, Types|Args], 205 reverse(Tokens, TR), 206 trailing_comments_reversed(TR, TrailingCommentsReversed, Tail), 207 reverse(Tail, TokensWithoutTrailingComments), 208 reverse(TrailingCommentsReversed, TrailingComments), 209 Goal =.. [Functor, TokensWithoutTrailingComments, [], [query_id(QueryId)|Options], _, 0, _, Arg, T1 |Args], 210 Goal, 211 !, 212 %chr_show_store(sql_parser), 213 %format(user_error, '---------------~n', []), 214 resolve_types(QueryId), 215 map_nulls_to_ints(T1, Types), 216 consolidate_errors(Arg), 217 cleanup(QueryId), 218 true. 219 220 221map_nulls_to_ints([], []):- !. 222map_nulls_to_ints([merged(A, _, N)|As], [A-native_type(int)|Bs]):- 223 N == {nulltype}, !, 224 map_nulls_to_ints(As, Bs). 225map_nulls_to_ints([merged(A, _, N)|As], [A-native_type(int)|Bs]):- 226 nonvar(N), N = native_type(int(_)), !, 227 map_nulls_to_ints(As, Bs). 228map_nulls_to_ints([merged(A, _, AT)|As], [A-AT|Bs]):- 229 map_nulls_to_ints(As, Bs). 230 231trailing_comments_reversed([], [], []):- !. 232trailing_comments_reversed([comment(A,B)|In], [comment(A,B)|More], Tail):- !, 233 trailing_comments_reversed(In, More, Tail). 234trailing_comments_reversed(Tail, [], Tail):- !. 235 236term_expansion(OldHead ---> OldBody, NewHead :- NewBody):- 237 OldHead =.. [Functor, A1|Args], 238 NewHead =.. [Functor, In, Out, ContextIn, _, P0, P1, meta(Comments, Source):A1|Args], 239 transform_body(OldBody, In, Out, Source, ContextIn, _, Comments, [], P0, P1, NewBody). 240 241transform_body({A}, In, Out, _, C, C, C1, C2, P0, P0, (A, Out = In, C1 = C2)):- !. 242transform_body(\+(X), In, In, Source, C, C, C1, C1, P0, P0, \+G):- 243 transform_body(X, In, _, Source, C, _, C1, _, P0, _, G). 244transform_body(??(X), In, Out, Source, CIn, COut, C1, C2, P0, P1, Transformed):- 245 transform_body(X, In, Out, Source, CIn, COut, C1, C2, P0, P1, G), 246 functor(X, Functor, Arity), 247 Transformed = (( length(First, 20), append(First, _, In)-> true ; otherwise-> First = In), 248 format(user_error, '----- CALL ~w (~q) ~w~n', [Functor/Arity, G, First]), 249 setup_call_catcher_cleanup(true, 250 G, 251 Reason, 252 ( Reason == ! -> 253 ( length(Last, 20), append(Last, _, Out)-> true ; otherwise-> Last = Out), 254 format(user_error, '----- CUT ~w ~w~n', [Functor/Arity, Last]) 255 ; Reason == fail-> 256 format(user_error, '----- FAIL ~w~n', [Functor/Arity]) 257 ; Reason == exit-> 258 ( length(Last, 20), append(Last, _, Out)-> true ; otherwise-> Last = Out), 259 format(user_error, '----- EXIT ~w ~w~n', [Functor/Arity, Last]) 260 )), 261 ( var(Reason) -> 262 ( length(Last, 20), append(Last, _, Out)-> true ; otherwise-> Last = Out), 263 format(user_error, '----- PEND ~w ~w~n', [Functor/Arity, Last]) 264 ; otherwise-> 265 true 266 ) 267 ). 268 269transform_body(get_source(Source), In, In, Source, C, C, C1, C1, P0, P0, true):- !. 270transform_body(get_parameter(P0), In, In, _, C, C, C1, C1, P0, P1, P1 is P0 + 1):- !. 271transform_body(!, InOut, InOut, _, C, C, C1, C2, P0, P0, (!, C1 = C2)):- !. 272transform_body(@Functor, In, Out, Source, C, C, C1, C2, P0, P0, get_token(Token, C, C1, C2, In, Out)):- Functor =.. [Token, Source], !. 273transform_body(@Token, In, Out, _, C, C, C1, C2, P0, P0, get_token(Token, C, C1, C2, In, Out)):- !. 274transform_body(#Identifier : Type, In, Out, _, C, C, C1, C2, P0, P0, get_identifier(Identifier, Type, C, C1, C2, In, Out)):-!. 275transform_body(#Identifier, In, Out, _, C, C, C1, C2, P0, P0, get_identifier(Identifier, any, C, C1, C2, In, Out)):-!. 276 277transform_body((A | B), In, Out, Source, CIn, COut, C1, C2, P0, P1, (C, COut = COut1, P1 = P1a ; D, COut = COut2, P1 = P1b)):- 278 !, 279 transform_body(A, In, Out, Source, CIn, COut1, C1, C2, P0, P1a, C), 280 transform_body(B, In, Out, Source, CIn, COut2, C1, C2, P0, P1b, D). 281transform_body((A,B), In, Out, Source, CIn, COut, C1, C2, P0, P2, (C,D)):- 282 !, 283 transform_body(A, In, Intermediate, Source, CIn, CInt, C1, C1b, P0, P1, C), 284 transform_body(B, Intermediate, Out, Source, CInt, COut, C1b, C2, P1, P2, D). 285 286transform_body(List, In, Out, Source, CIn, COut, C1, C2, P0, P1Out, (Goals, P1Out = P1 ; In = Out, CIn = COut, C1 = C2, P1Out = P0)):- 287 is_list(List), !, 288 transform_list_body(List, In, Out, Source, CIn, COut, C1, C2, P0, P1, Goals). 289transform_body(Rule, In, Out, _, CIn, CX, C1, C2, P0, P1, ( C1 = C2, NewRule )):- 290 Rule =.. [Functor|Args], 291 NewRule =.. [Functor, In, Out, CIn, COut, P0, P1|Args], 292 ( ( Functor == set_qid ; Functor == add_option)-> 293 CX = COut 294 ; otherwise-> 295 CX = CIn 296 ). 297transform_list_body([Tail], In, Out, Source, CIn, COut, C1, C2, P0, P1, Goals):- 298 transform_body(Tail, In, Out, Source, CIn, COut, C1, C2, P0, P1, Goals). 299transform_list_body([Head|Tail], In, Out, Source, CIn, COut, C1, C2, P0, P2, (G, G2)):- 300 transform_body(Head, In, Intermediate, Source, CIn, CInt, C1, C1b, P0, P1, G), 301 transform_list_body(Tail, Intermediate, Out, Source, CInt, COut, C1b, C2, P1, P2, G2). 302 303get_token(Token, Options, [comment(A,B)|C1], C2, [comment(A,B)|X], Out):- !, 304 get_token(Token, Options, C1, C2, X, Out). 305get_token(Token, Options, C1, C1, [TopToken|Out], Out):- 306 ( reverse_lex(TopToken, Options, Token)-> 307 true 308 ; atom(TopToken), 309 downcase_atom(TopToken, Token)-> 310 true 311 ). 312 313get_identifier(Identifier, Type, Options, [comment(A,B)|C1], C2, [comment(A,B)|X], Out):- !, 314 get_identifier(Identifier, Type, Options, C1, C2, X, Out). 315get_identifier(Identifier, Type, Options, C1, C1, [Top|Out], Out):- 316 ( Type == any -> 317 Identifier = Top 318 ; otherwise-> 319 Top = literal(Identifier, Type) 320 ), 321 ( atom(Identifier)-> 322 downcase_atom(Identifier, IdentifierLC), 323 \+reserved_sql_keyword(IdentifierLC) 324 ; otherwise-> 325 true 326 ), 327 \+reverse_lex(Identifier, Options, _). 328 329reverse_lex('*', _Options, asterisk). 330reverse_lex('/', _Options, solidus). 331reverse_lex('+', _Options, plus_sign). 332reverse_lex('-', _Options, minus_sign). 333reverse_lex(',', _Options, comma). 334reverse_lex('.', _Options, period). 335reverse_lex('(', _Options, left_paren). 336reverse_lex(')', _Options, right_paren). 337reverse_lex('{', _Options, left_curly). 338reverse_lex('}', _Options, right_curly). 339reverse_lex('IS', _Options, is_keyword). 340reverse_lex('Is', _Options, is_keyword). 341reverse_lex('iS', _Options, is_keyword). 342reverse_lex('is', _Options, is_keyword). 343reverse_lex('<', _Options, less_than_operator). 344reverse_lex('=', _Options, equals_operator). 345reverse_lex('<>', _Options, not_equals_operator). 346reverse_lex('>', _Options, greater_than_operator). 347reverse_lex('<=', _Options, less_than_or_equals_operator). 348reverse_lex('>=', _Options, greater_than_or_equals_operator). 349reverse_lex('+', Options, concatenation_operator):- dbms([], [], Options, _, _, _, 'Microsoft SQL Server'). 350reverse_lex('||', Options, concatenation_operator):- \+dbms([], [], Options, _, _, _, 'Microsoft SQL Server'). 351 352 353add_option(L, L, O, [X|O], P, P, X). 354set_qid(L, L, O, O2, P, P, X):- 355 change_qid(O, X, O2). 356 357change_qid([query_id(_)|T], Qid, [query_id(Qid)|T]):- !. 358change_qid([A|T], Qid, [A|T2]):- !, change_qid(T, Qid, T2). 359 360get_option(L, L, O, O, P, P, X):- memberchk(X, O). 361qid(L, L, O, O, P, P, Qid):- memberchk(query_id(Qid), O). 362default_precision_and_scale(L, L, O, O, P0, P0, P, S):- 363 ( dbms(L, L, O, O, P0, P0, 'Microsoft SQL Server')-> 364 P = 18, 365 S = 0 366 ; otherwise-> 367 throw(default_unknown) 368 ). 369dbms(L, L, Options, Options, P, P, DBMS):- 370 ( memberchk(dbms(X), Options)-> 371 DBMS = X 372 ; otherwise-> 373 DBMS = 'Microsoft SQL Server' 374 ). 375 376check_order_by_is_in_top_query(L, L, Options, Options, P, P, Source):- 377 ( memberchk(subquery, Options)-> 378 true 379 ; \+memberchk(view_name(_), Options)-> 380 % ORDER BY is fine in the top level of an actual query, of course! 381 true 382 ; otherwise-> 383 semantic_error(Source, order_by(top_level), 1) 384 ). 385 386action(Action, Types)---> 387 query_expression(Action, Types) | delete_statement_searched(Action, Types) | insert_statement(Action, Types) | update_statement_searched(Action, Types). 388delete_statement_searched(delete(TableName, Where), [])---> 389 @delete, @from, !, table_name(TableName), (@where, search_condition(Condition), {Where = where(Condition)} | {Where = {no_where}}). 390insert_statement(insert(TableName, Values), [])---> 391 @insert, @into, !, table_name(TableName), insert_columns_and_source(Values), [dbms('PostgreSQL'), @returning, query_expression(_,_)]. 392insert_columns_and_source(Values)---> 393 from_subquery(Values) | from_constructor(Values) | from_default(Values). 394from_default({default_values})---> @default, @values, !. 395from_subquery(insert_source(Source, Override, Target))---> 396 ( ( @left_paren, insert_column_list(Source), @right_paren) | {Source = {default}} ), 397 ( override_clause(Override) | {Override = {no_override}} ), 398 query_expression(Target, _). 399from_constructor(insert_source(Source, Override, Target))---> 400 ( ( @left_paren, insert_column_list(Source), @right_paren) | {Source = {default}} ), 401 ( override_clause(Override) | {Override = {no_override}} ), 402 table_value_constructor(Target, _). 403update_statement_searched(update(TableName, List, From, Condition), [])---> 404 % Actually should be table_name here. Apparently it is not legal to use an alias? 405 @update, table_reference(TableName), 406 qid(Qid), {strip_sql_comments(TableName, Stripped), determine_tables(Qid, Stripped)}, 407 @set, set_clause_list(List), 408 ( @from, from_clause_1(F), {strip_sql_comments(F, CleanedFrom), ( determine_tables(Qid, CleanedFrom)-> From = from(F) ; otherwise->throw(failed_tables(CleanedFrom)))} 409 | {From = {no_from}}), 410 ( @where, search_condition(Where), {Condition = where(Where)} | {Condition = {no_where}}). 411set_clause_list([Head|Tail])---> 412 set_clause(Head), (@comma, set_clause_list(Tail) | {Tail = []}). 413set_clause(set(Target, Source))---> 414 update_target(Target), @equals_operator, update_source(Source). % Or mutated-set-clause, but we probably dont need to worry about that 415update_target(Target)---> column_name(Target). % Actually also allows foo[expression] 416update_source(Source)---> value_expression(Source, _) | default_specification(Source, _) | null_specification(Source, _). % Also allows ARRAY[] 417insert_column_list(List)---> column_name_list(List). 418override_clause(overriding_user_value)---> @overriding, @user, @value, !. 419override_clause(overriding_system_value)---> @overriding, @system, @value, !. 420 421 422view_definition(view_definition(Name, Columns, Expression, With), Types)---> (@create), @view, table_name(Name), 423 { 424 strip_sql_comments(Name, NameNoComments), 425 ( NameNoComments = table(identifier(schema(_, literal(dbo, identifier)), _))-> 426 true 427 ; otherwise-> 428 throw(illegal_view_name(no_schema)) 429 )}, 430 ( @left_paren, view_column_list(Columns), @right_paren | {Columns = {all}}), with_attribute(With), @as, query_expression(Expression, Types). 431table_name(table(Name))---> qualified_name(Name). 432view_column_list(List)---> column_name_list(List). 433query_expression(Term, T)---> 434 qid(Qid), 435 non_join_query_term(LHS, LT), 436 ( @union, (@all, {Term = union_all(LHS, RHS, Corresponding)} | {Term = union(LHS, RHS, Corresponding)}), (corresponding_spec(Corresponding) | {Corresponding = {no_corresponding}}), set_qid(SubQid), query_expression(RHS, RT), {peer_query(Qid, SubQid), union_type(Qid, LT, RT, T)} 437 | @except, (@all, {Term = except_all(LHS, RHS, Corresponding)} | {Term = except(LHS, RHS, Corresponding)}), (corresponding_spec(Corresponding) | {Corresponding = {no_corresponding}}), query_expression(RHS, RT), {union_type(Qid, LT, RT, T)} 438 | {Term = LHS, T = LT}). 439non_join_query_term(Term, T)---> (non_join_query_primary(LHS, LT) | free_joined_table(LHS, LT)), 440 ( @intersect, ( @all, {Term = intersect_all(LHS, RHS, Corresponding)} | {Term = intersect(LHS, RHS, Corresponding)}), 441 ( corresponding_spec(Corresponding) | {Corresponding = {no_corresponding}}), non_join_query_term(RHS, RT), qid(Qid), {union_type(Qid, LT, RT, T)} | {Term = LHS, T = LT}). 442non_join_query_primary(Primary, T)---> (simple_table(Primary, T) | @left_paren, query_expression(Primary, T), @right_paren). 443simple_table(Table, T)---> query_specification(Query, T), {Table = query(Query)} | table_value_constructor(Values, T), {Table = values(Values)} | explicit_table(Explicit), {Table = explicit_table(Explicit), T = {fixme1}}. 444query_specification(select(Q, Selections, Source, Limit, For), QueryType)---> 445 @select, ( set_quantifier(Q) | {Q = {no_quantifier}} ), [ dbms('Microsoft SQL Server'), top_clause(Limit) ], 446 select_list(Selections, Sources, Types), table_expression(Source), [dbms('PostgreSQL'), limit_clause(Limit)], {var(Limit)->Limit = {no_limit} ; true}, 447 ( dbms('Microsoft SQL Server'), for_clause(For), get_source(S1), {semantic_error(for_clause, S1, 2)} | {For = {no_for}}), 448 {(strip_sql_comments(Selections, S), merge_types(S, Sources, Types, QueryType)-> true ; throw(failed_to_resolve))}. 449select_list(N, S, T)---> (@asterisk, qid(Qid), get_source(Source), {N = all, find_all_column_types(Qid, Source, T1), frozen_reverse(Qid, T1, T)} | select_list_1(N, S, T)). 450select_list_1([Head|Tail], [Source|Sources], [Type|Types])---> select_sublist(Head, Source, Type), (@comma, select_list_1(Tail, Sources, Types) | {Tail = [], Sources = [], Types = []}). 451select_sublist(S, Source, Type)---> get_source(Source), derived_column(Column, Type), {S = Column} | qualifier(Qualifier), @period, @asterisk, {S = all(Qualifier), Type = {fixme3}}. 452derived_column(derived_column(Column, As), Type)---> (illegal_null_specification(Column, Type) | value_expression(Column, Type)), (as_clause(As) | {As = {no_alias}}). % Added @null to allow for SELECT NULL AS foo, since null is not a value 453as_clause(Name)---> [@as], column_name(Name). 454table_expression(source(From, Where, GroupBy, OrderBy, Having))---> 455 from_clause(From), 456 qid(Qid), 457 {((From = _:from(F))-> 458 strip_sql_comments(F, CleanedFrom), 459 ( determine_tables(Qid, CleanedFrom)-> true ; otherwise->throw(failed_tables(CleanedFrom))) 460 ; From = _:{no_from}-> 461 true 462 )}, 463 ( where_clause(Where) | {Where = {no_where}}), 464 ( group_by_clause(GroupBy) | {GroupBy = {no_groupby}}), 465 % Some hacks here. table_expression is not supposed to have an order-by clause, but it is used in a lot of views 466 % Further, some views put the order-by AFTER the having 467 468 % To allow the order-by to refer to expressions in the view, we have to make the entire query become a sub-query of the order-by clause 469 % However, confusingly, the HAVING clause needs to be part of the same query so that things like SUM(x) which resolve in the select list also resolve in the HAVING clause 470 qid(Qid), set_qid(SubqueryId), {subquery(SubqueryId, Qid)}, 471 ( order_by_clause(OrderBy), set_qid(Qid), (having_clause(Having) | {Having = {no_having}}) 472 | set_qid(Qid), having_clause(Having), ( set_qid(SubqueryId), order_by_clause(OrderBy), get_source(Source), {semantic_error(order(having, order_by), Source, 2)} | {OrderBy = {no_orderby}}) 473 | {OrderBy = {no_orderby}, Having = {no_having}}), 474 set_qid(Qid). 475from_clause(from(From))---> @from, from_clause_1(From). 476from_clause({no_from})---> {true}. 477from_clause_1([Head|Tail])---> table_reference(Head), get_source(Source), (@comma, {semantic_error(Source, deprecated('SQL89-style join', 'Explicit JOIN clauses'), 1)}, from_clause_1(Tail) | {Tail = []}). 478 479table_reference(Reference)---> (@left_paren, table_reference(LHS), @right_paren 480 | derived_table(Derivation, T), correlation_specification(Correlation), {LHS = derived_table(Derivation, Correlation, T)} 481 | table_name(Name), (correlation_specification(Correlation) | {Correlation = {no_correlation}}), {LHS = correlated_table(Name, Correlation)}), 482 [ dbms('Microsoft SQL Server'), with_clause(_) ], % TBD: Preserve WITH 483 ( more_join(RHS), {Reference = join(LHS, RHS)} | {Reference = LHS}). 484more_join(X)---> 485 ( cross_join_rhs(LHS), {Reference = cross_join(LHS)} 486 | qualified_join_rhs(Type, LHS, On), {Reference = qualified_join(Type, LHS, On)}), 487 ( more_join(RHS2), {X = join(Reference, RHS2)} | {X = Reference}). 488correlation_specification(correlation(Name, Columns))---> [@as], #NameMC, (@left_paren, derived_column_list(Columns), @right_paren | {Columns = {no_columns}}), {name_from_identifier(NameMC, Name)}. 489derived_column_list(L)--->column_name_list(L). 490derived_table(Table, T)---> table_subquery(Table, T). 491table_subquery(Query, T)---> subquery(Query, T). 492cross_join_rhs(Reference)---> @cross, @join, table_reference(Reference). 493qualified_join_rhs(Type, Reference, Spec)---> ( @natural, {Type = natural(T1)} | {Type = T1} ), ( join_type(T1) | {T1 = join} ), (@join), table_reference(Reference), ( join_specification(Spec) | {Spec = {no_on}} ). 494free_joined_table(Table, {fixme4})---> table_reference(Table). 495join_type(join_type(Type))---> (@inner, {Type = inner} | outer_join_type(T1), {Type = outer(T1)}, [ @outer ] | @union, {Type = union}). 496outer_join_type(T)---> (@left, {T=left} | @right, {T=right} | @full, {T=full}). 497join_specification(Spec)---> ( join_condition(Spec) | named_columns_join(Spec) ). 498join_condition(on(On)) ---> @on, search_condition(On). 499named_columns_join(columns(Columns))---> @using, @left_paren, join_column_list(Columns), @right_paren. 500join_column_list(Columns)---> column_name_list(Columns). 501set_quantifier(Q)---> ( @distinct, {Q = distinct} | @all, {Q = all} ). 502where_clause(where(Where))---> @where, search_condition(Where). 503corresponding_spec(Columns)---> @corresponding, ( @by, @left_paren, corresponding_column_list(Columns), @right_paren | {Columns = {no_columns}} ). 504corresponding_column_list(List)---> column_name_list(List). 505query_primary(Primary, T)---> ( non_join_query_primary(Primary, T) | free_joined_table(Primary, T) ). 506subquery(subquery(Query), T)---> (@left_paren, add_option(subquery), qid(Qid), set_qid(SubqueryId), {subquery(Qid, SubqueryId)}, query_expression(Query, T), @right_paren). 507column_name_list([Head|Tail])---> column_name(Head), (@comma, column_name_list(Tail) | {Tail = []}). 508column_name(Name)---> #Identifier, \+(@left_paren), 509 {( Identifier = literal(Name, identifier)-> 510 true 511 ; Identifier = literal(A,B)-> % Quirks. This is for "SELECT '01' AS foo. The 'column' here is actually the literal 01 512 Name = literal(A,B) 513 ; otherwise-> 514 downcase_atom(Identifier, Name) 515 )}. 516explicit_table(table(Table)) ---> @(table), table_name(Table). 517qualifier(Qualifier)---> qualified_name(Qualifier). 518% If this were a numeric_value_expression followed by /plus/ (as distinct but indistiguishable from /concat/) then it 519% would have been absorbed into the numeric_value_expression. The only time we would exist numeric_value_expression and consume a + 520% is if we knew it was actually a concat! 521value_expression(V, T)---> numeric_value_expression(V, T), \+(@concatenation_operator), \+(@minus_sign), \+(@period) % Hints we might be barking up the wrong parse tree 522 | string_value_expression(V, T), \+(@concatenation_operator), \+(@minus_sign), \+(@period) 523 | datetime_value_expression(V, T) 524 | interval_value_expression(V, T). 525 526% TBD: This should ALSO be applied for subtract, since CURRENT_TIMESTAMP - 1 is not subtract but add_interval(CURRENT_TIMESTAMP, -1) 527 528numeric_value_expression(V, T)---> 529 numeric_value_expression_1(V, T1), 530 qid(Qid), 531 {left_factor_types(Qid, T1, T)}. 532 533numeric_value_expression_1(V, T)---> 534 get_source(LS), term(LHS, LT), qid(Qid), 535 ( ( @plus_sign, {Op = add} | @minus_sign, {Op = subtract} ), 536 get_source(RS), 537 numeric_value_expression_1(RHS, RT), 538 {T = node(LT, LS, Op, RT, RS), 539 % T1 is not the type of the subexpression (since this sub-expression may not even exist in the final result) 540 % but it IS needed to determine whether the operation is +(addition) or +(concatenation) since SQL Server 541 % doesnt distinguish these with syntax 542 % Similarly R1 is not necessarily needed for the SQL, but it IS needed here 543 left_factor_types(Qid, RT, R1), 544 left_factor_types(Qid, LT, L1), 545 most_general_type(Qid, Source, Source, L1, R1, Op, T1), 546 freeze(T1, determine_operation_from_types(T1, L1, R1, Op, LHS, RHS, V))} 547 | {V = LHS, T = LT} 548 ). 549 550 551%TBD: This currently does not always work. See for example le_vw_negative_holdings 552determine_operation_from_types(Type, LT, RT, Op, LHS, RHS, V):- 553 %format(user_error, '--------------------------------- add_or_concat: ~w, ~w, ~w~n', [Type, LT, RT]), 554 native_type_of_type(LT, LTT), 555 native_type_of_type(RT, RTT), 556 ( Op == add -> 557 ( native_type_of_type(Type, native_type(varchar(_)))-> 558 V = concatenate(LHS, RHS) 559 ; native_type_of_type(Type, native_type(nvarchar(_)))-> 560 V = concatenate(LHS, RHS) 561 ; LTT = native_type(datetime), 562 RTT = native_type(int(_))-> 563 V = add_interval(LHS, RHS) 564 ; RTT = native_type(datetime), 565 LTT = native_type(int(_))-> 566 V = add_interval(RHS, LHS) 567 % Believe it or not, there are places where we add numerics to dates as well. I will only implement 568 % the cases that exist as a workaround 569 ; LTT = native_type(datetime), 570 RTT = native_type(decimal(_,_))-> 571 V = add_interval(LHS, RHS) 572 ; otherwise-> 573 V = add(LHS, RHS) 574 ) 575 ; Op == subtract -> 576 ( LTT = native_type(datetime), 577 RTT = native_type(int(_))-> 578 V = add_interval(LHS, negative(RHS)) 579 ; RTT = native_type(datetime), 580 LTT = native_type(int(_))-> 581 V = add_interval(RHS, negative(LHS)) 582 % Believe it or not, there are places where we subtract numerics from dates as well. I will only implement 583 % the cases that exist as a workaround 584 ; LTT = native_type(datetime), 585 RTT = native_type(decimal(_,_))-> 586 V = add_interval(LHS, negative(RHS)) 587 ; otherwise-> 588 V = subtract(LHS, RHS) 589 ) 590 ). 591 592 593native_type_of_type(native_type(X), native_type(X)):- !. 594native_type_of_type(domain(D), native_type(X)):- 595 fetch_domain_data_type(D, X). 596 597term(V, T)---> get_source(LS), factor(LHS, LT), ((@asterisk, {V = multiply(LHS, RHS), Op = multiply} | @solidus, {V = divide(LHS, RHS), Op = divide(RT)}), get_source(RS), term(RHS, RT), {T = node(LT, LS, Op, RT, RS)} | {V = LHS, T = LT}). 598factor(V, T)---> ( (@plus_sign, {V = positive(N)} | @minus_sign, dbms(DBMS), {V = negative(N), (DBMS == 'Microsoft SQL Server' -> force_type_not_domain(T) ; true)}) | { V = N} ), numeric_primary(N, T). % Quirk. This is in contradiction to the T-SQL Reference, but confirmed. 599numeric_primary(N, T)---> value_expression_primary(N, T) | numeric_value_function(N, T). 600value_expression_primary(N, T)---> 601 unsigned_value_specification(N, T) | parameter(N, T) | column_reference(N, T) | set_function_specification(N, T) | case_expression(N, T) | @left_paren, value_expression(N, T), @right_paren | cast_specification(N, T) | scalar_subquery(N, T) | routine_invocation(N, T). 602parameter(parameter(N), native_type(int)) ---> @(?), get_parameter(N). % FIXME: Type of parameter 603column_reference(column(Qualifier, Name), Type)---> ( qualifier(Qualifier), @period | {Qualifier = {no_qualifier}} ), column_name(Name), qid(Qid), get_source(Source), {strip_sql_comments(Qualifier, QS), strip_sql_comments(Name, NS), type_constraint(Qid, Source, Type, typeof(QS, NS)), type_constraint_ready(Qid, Type)}. 604scalar_subquery(S, T)---> subquery(S, ST), qid(Qid), get_source(Source), {type_constraint(Qid, Source, T, scalar(ST)), type_constraint_ready(Qid, T)}. 605having_clause(having(Having))---> @having, search_condition(Having). 606group_by_clause(group_by(List))---> @group, @by, grouping_column_reference_list(List). 607grouping_column_reference_list([Head|Tail])---> grouping_column_reference(Head), ( @comma, grouping_column_reference_list(Tail) | {Tail = []} ). 608grouping_column_reference(group_column(Reference, Collate))---> column_reference(Reference, _), ( collate_clause(Collate) | {Collate = {no_collation}} ). 609collate_clause(collate(Name))---> @collate, collation_name(Name). 610collation_name(collation(Name))---> qualified_name(Name). 611qualified_name(identifier(Qualifier, Name))---> ( schema_name(Qualifier), @period | {Qualifier = {no_schema}}), #Identifier, {name_from_identifier(Identifier, Name)}. % Quirk 612schema_name(schema(Catalog, Schema))---> ( catalog_name(Catalog), @period | {Catalog = {no_catalog}}), #Schema. 613catalog_name(Catalog)---> #Catalog. 614set_function_specification(S, T)---> (@count, @left_paren, @asterisk, @right_paren, {S = count(all), T = native_type(int)} | general_set_function(S, T)). 615set_function_specification(S, T)---> (@count_big, @left_paren, @asterisk, @right_paren, {S = count(all), T = native_type(bigint)} | general_set_function(S, T)). 616 617general_set_function(set_function(S, Q, A), T)---> set_function_type(S), @left_paren, ( set_quantifier(Q) | {Q = {no_quantifier}} ), value_expression(A, AT), @right_paren, qid(Qid), get_source(Source), 618 {S = _:count -> 619 T = native_type(int) 620 ; S = _:sum-> 621 type_merge_hint(T, sum), 622 type_constraint(Qid, Source, T, AT), 623 type_constraint_ready(Qid, T) 624 ; S = _:avg-> 625 type_merge_hint(T, avg), 626 type_constraint(Qid, Source, T, AT), 627 type_constraint_ready(Qid, T) 628 ; otherwise-> 629 T = AT 630 }. 631set_function_type(T)---> @avg, {T = avg} | @max, {T = max} | @min, {T = min} | @sum, {T = sum} | @count, {T = count}. 632search_condition(C, _Types)---> search_condition(C). 633search_condition(C)---> boolean_term(Head), (@or, search_condition(Tail), {C = or(Head, Tail)} | {C = Head}). 634boolean_term(C)---> boolean_factor(Head), (@and, boolean_term(Tail), {C = and(Head, Tail)} | {C = Head}). 635boolean_factor(C)---> ( @not, {C = not(X)} | {C = X} ), boolean_test(X). 636boolean_test(Test)---> boolean_primary(X), ( @is_keyword, ( @not, {Test = isnot(X, T)} | {Test = is(X, T)}), truth_value(T) | {Test = X}). 637truth_value(T)---> (@true, {T = true} | @false, {T = false} | @unknown, {T = unknown}). 638boolean_primary(P)---> @left_paren, search_condition(Search), {P = search(Search)}, @right_paren | predicate(Pr), {P = predicate(Pr)}. 639predicate(Predicate)---> comparison_predicate(Predicate) | between_predicate(Predicate) | in_predicate(Predicate) | like_predicate(Predicate) | null_predicate(Predicate) | quantified_comparison_predicate(Predicate) | exists_predicate(Predicate) | match_predicate(Predicate) | overlaps_predicate(Predicate). 640comparison_predicate(comparison(CompOp, LHS, RHS))---> row_value_constructor(LHS, LT), comp_op(CompOp), row_value_constructor(RHS, RT), {check_types(LT, RT)}. 641row_value_constructor(Row, Types)---> @left_paren, row_value_constructor_list(List, Types), {Row = list(List)}, @right_paren | row_value_constructor_element(Element, Types), {Row = element(Element)} | row_subquery(SubQuery, Types), {Row = query(SubQuery, Types)}. 642row_value_constructor_element(Element, Type)---> value_expression(Element, Type) | null_specification(Element, Type) | default_specification(Element, Type). 643comp_op(Op)---> @equals_operator, {Op = '='} 644 | @not_equals_operator, {Op = '<>'} 645 | @less_than_operator, {Op = '<'} 646 | @greater_than_operator, {Op = '>'} 647 | @less_than_or_equals_operator, {Op = '<='} 648 | @greater_than_or_equals_operator, {Op = '>='}. 649null_specification({null}, T)---> @null, qid(Qid), get_source(Source), {type_constraint(Qid, Source, T, {nulltype}), type_constraint_ready(Qid, T)}. 650illegal_null_specification({null}, T)---> @null, qid(Qid), get_source(Source), {semantic_error(Source, null_value, 1), type_constraint(Qid, Source, T, {nulltype}), type_constraint_ready(Qid, T)}. 651null_predicate(Predicate)---> row_value_constructor(LHS, _), @is_keyword, ( @not, {Predicate = is_not_null(LHS)} | {Predicate = is_null(LHS)} ), @null. % The spec is wrong here 652default_specification({default}, {defaulttype})---> @default. 653row_subquery(S, T)---> subquery(S, T). 654row_value_constructor_list([Head|Tail], [Type|Types])---> row_value_constructor_element(Head, Type), (@comma, row_value_constructor_list(Tail, Types) | {Tail = [], Types = []}) . 655between_predicate(Term)---> row_value_constructor(LHS, TA), ( @not, {Term = not_between(LHS, Min, Max)} | {Term = between(LHS, Min, Max)}), @between, row_value_constructor(Min, TB), @and, row_value_constructor(Max, TC), {check_types(TA, TB), check_types(TA, TC)}. 656exists_predicate(exists(Query))---> @exists, table_subquery(Query, _). 657in_predicate(P) ---> row_value_constructor(Value, T1), ( @not, {P = not_in(Value, List)} | {P = in(Value, List)} ), @in, in_predicate_value(List, T2), {forall(member(T, T2), check_types(T1, T))}. 658in_predicate_value(In, Types)---> @left_paren, in_value_list(List, Types), {In = list(List)}, @right_paren | table_subquery(Query, Types), {In = query(Query)}. 659in_value_list([Head|Tail], [Type|Types])---> value_expression(Head, Type), ( @comma, in_value_list(Tail, Types) | {Tail = [], Types = []} ). 660like_predicate(P)---> match_value(LHS), ( @not, {P = not_like(LHS, Pattern, Escape)} | {P = like(LHS, Pattern, Escape)}), (@like | @ilike), pattern(Pattern), ( @escape, escape_character(Escape) | {Escape = {no_escape}}). 661match_value(P)---> character_value_expression(P, _). 662pattern(P)---> character_value_expression(P, _). 663escape_character(P)---> character_value_expression(P, _). 664character_value_expression(E, T)---> get_source(S1), character_factor(LHS, LT), (@concatenation_operator, get_source(S2), character_value_expression(RHS, RT), qid(Qid), {E = concatenate(LHS, RHS), concatenate_type(Qid, S1, S2, LT, RT, T)} | {E = LHS, T = LT}). 665character_factor(Factor, T)---> character_primary(F, T), ( collate_clause(C), {Factor = collated_factor(F, C)} | {Factor = F} ). 666character_primary(X, T)---> value_expression_primary(X, T) | string_value_function(X, T). 667match_predicate(match(Unique, MatchLevel, LHS, RHS))---> row_value_constructor(LHS, TL), @match, [ @unique, {Unique = unique} ], [ (@partial, {MatchLevel = partial} | @full, {MatchLevel = full}) ], table_subquery(RHS, TR), {check_types(TL, TR)}. 668overlaps_predicate(overlaps(LHS, RHS))---> row_value_constructor(LHS, TL), @overlaps, row_value_constructor(RHS, TR), {check_types(TL, TR)}. 669quantified_comparison_predicate(quantified_comparison(Op, Quantifier, LHS, RHS))---> row_value_constructor(LHS, TL), comp_op(Op), quantifier(Quantifier), table_subquery(RHS, TR), {check_types(TL, TR)}. 670quantifier(Quantifier)---> @all, {Quantifier = all} | @some, {Quantifier = some}. 671table_value_constructor(N, T)---> @values, table_value_constructor_list(N, T). 672table_value_constructor_list([Head|Tail], [Type|Types])---> row_value_constructor(Head, Type), (@comma, table_value_constructor_list(Tail, Types) | {Tail = [], Types = []}). 673case_expression(N, T)---> case_abbreviation(N, T) | case_specification(N, T). 674case_abbreviation(V, T)---> @nullif, @left_paren, value_expression(LHS, LT), @comma, value_expression(RHS, RT), @right_paren, qid(Qid), get_source(Source), {V = nullif(LHS, RHS), most_general_type(Qid, Source, Source, LT, RT, case, T)} 675 | @coalesce, @left_paren, coalesce_list(List, Types, Sources), @right_paren, qid(Qid), {V = coalesce(List), coalesce_type(Qid, Types, Sources, T)}. 676coalesce_list([Head|Tail], [Type|Types], [Source|Sources])---> get_source(Source), (null_specification(Head, Type), get_source(Source), {semantic_error(Source, coalesce(null_argument), 1)} | value_expression(Head, Type)), ( @comma, coalesce_list(Tail, Types, Sources) | {Tail = [], Types = [], Sources = []} ). 677case_specification(N, T)---> searched_case(N, T) | simple_case(N, T). 678simple_case(simple_case(Operand, List, Else), T)---> @case, qid(Qid), case_operand(Operand), simple_when_clause_list(List, Types, Sources), ( get_source(Source), else_clause(Else, ElseType), {coalesce_type(Qid, [ElseType|Types], [Source|Sources], T)} | {Else = {no_else}, coalesce_type(Qid, Types, Sources, T)} ), @end. 679simple_when_clause_list([Head|Tail], [Type|Types], [Source|Sources])---> get_source(Source), simple_when_clause(Head, Type), (simple_when_clause_list(Tail, Types, Sources) | {Tail = [], Types = [], Sources = []}). 680case_operand(X)---> value_expression(X, _). 681simple_when_clause(when(When, Result), T)---> @when, when_operand(When), @then, result(Result, T). 682when_operand(X)---> value_expression(X, _). 683result(X, T)---> null_specification(X, T) | result_expression(X, T). 684result_expression(X, T)---> value_expression(X, T). 685else_clause(else(Else), T)---> @else, result(Else, T). 686searched_case(case(Cases, Else), T)---> @case, qid(Qid), searched_when_clause_list(Cases, Types, Sources), ( get_source(Source), else_clause(Else, ElseT), {coalesce_type(Qid, [ElseT|Types], [Source|Sources], T)} | {Else = {no_else}, /* No domain if no else clause? */ force_type_not_domain(T), coalesce_type(Qid, Types, Sources, T)} ), (@end). 687searched_when_clause_list([Head|Tail], [Type|Types], [Source|Sources])---> get_source(Source), searched_when_clause(Head, Type), (searched_when_clause_list(Tail, Types, Sources) | {Tail = [], Types = [], Sources = []}). 688searched_when_clause(when(searched(Search), Result), T)---> @when, search_condition(Search), @then, result(Result, T). 689numeric_value_function(N, native_type(int))---> position_expression(N) | extract_expression(N) | length_expression(N). 690position_expression(position(A, B))---> @position, @left_paren, character_value_expression(A, _), @in, character_value_expression(B, _), @right_paren. 691extract_expression(extract(Field, Source))---> @extract, @left_paren, extract_field(Field), @from, extract_source(Source), @right_paren. 692length_expression(A)---> char_length_expression(A) | octet_length_expression(A) | bit_length_expression(A). 693char_length_expression(char_length(A))---> ( @char_length | @character_length ), @left_paren, string_value_expression(A, _), @right_paren. 694string_value_expression(X, T)---> character_value_expression(X, T) | bit_value_expression(X, T). 695octet_length_expression(octet_length(A))---> @octet_length, @left_paren, string_value_expression(A, _), @right_paren. 696bit_length_expression(bit_length(A))---> @bit_length, @left_paren, string_value_expression(A, _), @right_paren. 697extract_field(Field)---> datetime_field(Field) | time_zone_field(Field). 698extract_source(V)---> datetime_value_expression(V, _) | interval_value_expression(V, _). 699unsigned_value_specification(Value, native_type(int(X)))---> #Value : int(X). 700routine_invocation(routine(Name, Args), Type)---> qualified_name(Name), @left_paren, dbms(DBMS), {not_a_builtin_function(DBMS, Name)}, (sql_argument_list(Args) | {Args = []}), @right_paren, {routine_type(Name, Type)}. 701sql_argument_list([Head|Tail])---> sql_argument(Head), (@comma, sql_argument_list(Tail) | {Tail = []}). 702sql_argument(Arg)---> value_expression(Arg, _). 703cast_specification(cast(Operand, Target), Type)---> @cast, @left_paren, cast_operand(Operand), @as, cast_target(Target), {strip_sql_comments(Target, Type)}, @right_paren. 704cast_operand(Operand)---> @null, {Operand = {null}} | value_expression(Operand, _). 705cast_target(Target)---> data_type(Type), {Target = native_type(Type)} | domain_name(Domain), {Target = domain(Domain)}. 706domain_name(Name)---> qualified_name(Name). 707data_type(Type)---> character_string_type(Type), ( @character, @set, character_set_specification(_) | {true}) | national_character_string_type(Type) | bit_string_type(Type) | numeric_type(Type) | datetime_type(Type) | interval_type(Type). 708character_string_type(varchar(Length))---> @character, ( @left_paren, length(Length), @right_paren | {Length = 30}) 709 | @char, ( @left_paren, length(Length), @right_paren | {Length = 30}) 710 | @character, @varying, ( @left_paren, length(Length), @right_paren | {Length = 30}) 711 | @char, @varying, ( @left_paren, length(Length), @right_paren | {Length = 30}) 712 | @varchar, ( @left_paren, length(Length), @right_paren | {Length = 30}). 713length(Length)---> #Length : int(_) | (@max, {Length = max}). 714national_character_string_type(nchar_type(Length))---> 715 @national, @character, [ @left_paren, length(Length), @right_paren ] 716 | @national, @char, [ @left_paren, length(Length), @right_paren ] 717 | @nchar, [ @left_paren, length(Length), @right_paren ] 718 | @national, @character, @varying, [ @left_paren, length(Length), @right_paren ] 719 | @national, @char, @varying, [ @left_paren, length(Length), @right_paren ] 720 | @nchar, @varying, [ @left_paren, length(Length), @right_paren ]. 721bit_string_type(bit_type(Length))---> @bit, [ @left_paren, length(Length), @right_paren ] | @bit, @varying, [ @left_paren, length(Length), @right_paren ]. 722numeric_type(Type)---> exact_numeric_type(Type) | approximate_numeric_type(Type). 723exact_numeric_type(Type)---> @numeric, {Type = decimal(Precision, Scale)}, ( @left_paren, precision(Precision), ( @comma, scale(Scale) | {Scale = {no_scale}}), @right_paren | default_precision_and_scale(Precision, Scale)) 724 | @decimal, {Type = decimal(Precision, Scale)}, ( @left_paren, precision(Precision), ( @comma, scale(Scale) | {Scale = {no_scale}}), @right_paren | default_precision_and_scale(Precision, Scale) ) 725 | @dec, {Type = decimal(Precision, Scale)}, ( @left_paren, precision(Precision), ( @comma, scale(Scale) | {Scale = {no_scale}}), @right_paren | default_precision_and_scale(Precision, Scale) ) 726 | @integer, {Type = int} 727 | @int, {Type = int} 728 | @smallint, {Type = smallint} 729 | dbms('Microsoft SQL Server'), @tinyint, {Type = tinyint}. 730precision(Precision)---> #Precision : int(_). 731scale(Scale)---> #Scale : int(_). 732approximate_numeric_type(Type)---> @float, ( @left_paren, precision(Precision), @right_paren | {Precision = {no_precision}}), {Type = float(Precision)} | @real, {Type = real} | @double, precision(Precision), {Type = double(Precision)}. 733datetime_value_expression(V, T)---> (datetime_term(LHS, LT) | interval_value_expression(LHS, LT)), 734 ( ( @plus_sign, {V = add(LHS, RHS), Op = add} | @minus_sign, {V = subtract(LHS, RHS), Op = subtract}), datetime_value_expression(RHS, RT), qid(Qid), get_source(Source), {most_general_type(Qid, Source, Source, LT, RT, Op, T)} | {V = LHS, T = LT}). 735order_by_clause(order_by(List))---> @order, @by, get_source(Source), check_order_by_is_in_top_query(Source), sort_specification_list(List). 736sort_specification_list([Head|Tail])---> sort_specification(Head), ( @comma, sort_specification_list(Tail) | {Tail = []}). 737sort_specification(sort_key(Key, Collate, Order))---> sort_key(Key), ( collate_clause(Collate) | {Collate = {no_collation}} ), ( ordering_specification(Order) | {Order = {no_order}} ). 738% According to SQL92, a sort_key is a column_reference. In SQL99, however, it is a value_expression, which is quite a bit easier. 739%sort_key(Key)---> column_reference(C, _), {Key = sort_column(C)} | #I : int(_), {Key = index(I)}. 740sort_key(Key)---> value_expression(C, _), {Key = sort_column(C)} | #I : int(_), {Key = index(I)}. 741ordering_specification(S)---> @asc, {S = asc} | @desc, {S = desc}. 742 743interval_term(V, T)---> interval_factor(LHS, LT), (((@asterisk, {V = multiply(LHS, RHS), Op = multiply} | @solidus, {V = divide(LHS, RHS), Op = divide(RT)}), interval_term(RHS, RT), qid(Qid), get_source(Source), {most_general_type(Qid, Source, Source, LT, RT, Op, T)}) | {V = LHS, T = LT}) 744 | term(LHS, LT), @asterisk, interval_factor(RHS, RT), qid(Qid), get_source(Source), {T = multiply(LHS, RHS), most_general_type(Qid, Source, Source, LT, RT, multiply, T)}. 745interval_factor(F, T)---> (@plus_sign, {F = positive(F1)} | @minus_sign, {F = negative(F1)} | {F = F1}), interval_primary(F1, T). 746interval_primary(interval(P, Q), T)---> value_expression_primary(P, T), ( interval_qualifier(Q) | {Q = {no_qualifier}} ). 747interval_value_expression(V, T)---> interval_term(LHS, LT), (((@plus_sign, {V = add(LHS, RHS), Op = add} | @minus_sign, {V = subtract(LHS, RHS), Op = subtract}), interval_term(RHS, RT), qid(Qid), get_source(Source), {most_general_type(Qid, Source, Source, LT, RT, Op, T)}) | {V = LHS, T = LT}) 748 | @left_paren, datetime_value_expression(LHS, LT), @minus_sign, datetime_term(RHS, RT), @right_paren, interval_qualifier(Q), qid(Qid), get_source(Source), {T = qualified_subtract(Q, LHS, RHS), most_general_type(Qid, Source, Source, LT, RT, subtract, T)}. 749datetime_term(V, T)---> datetime_factor(V, T). 750datetime_factor(V, T)---> datetime_primary(P, T1), ( time_zone(TZ), qid(Qid), get_source(Source), {V = date_with_timezone(P, TZ), most_general_type(Qid, Source, Source, datetime_with_timezone, T1, add, T)} | {V = P, T = T1}). 751datetime_primary(V, T)---> value_expression_primary(V, T) | datetime_value_function(V, T). 752time_zone(T)---> @at, time_zone_specifier(T). 753time_zone_specifier(time_zone(T))---> @local, {T = local} | @time, @zone, interval_value_expression(T, _). 754time_zone_field(T)---> (@timezone_hour, {T = timezone_hour} | @timezone_minute, {T = timezone_minute}). 755 756datetime_type(T)---> @date, {T = date} 757 | @time, ( @left_paren, time_precision(P), @right_paren | {P = {no_precision}}), [ @with, @time, @zone], {T = time(P)} 758 | @timestamp, ( @left_paren, timestamp_precision(P), @right_paren | {P = {no_precision}}), [ @with, @time, @zone], {T = timestamp(P)}. 759time_precision(P)---> precision(P). 760timestamp_precision(P)---> precision(P). 761character_set_specification(P) ---> #P. 762 763 764% Not implemented 765datetime_field(_)---> {fail}. 766interval_qualifier(_)---> {fail}. 767interval_type(_)---> {fail}. 768string_value_function(_, _)---> {fail}. 769bit_value_expression(_, _)---> {fail}. 770 771% SQL Server 'features' 772:-discontiguous(string_value_function/8). 773string_value_function(ltrim(S), T)---> dbms('Microsoft SQL Server'), @ltrim, @left_paren, value_expression(S, T), @right_paren. 774string_value_function(rtrim(S), T)---> dbms('Microsoft SQL Server'), @rtrim, @left_paren, value_expression(S, T), @right_paren. 775string_value_function(left(S, N), T)---> dbms('Microsoft SQL Server'), @left, @left_paren, value_expression(S, ST), @comma, numeric_value_expression(N, _), @right_paren, get_source(Source), sized_varchar_type(N, Source, ST, T). 776string_value_function(right(S, N), T)---> dbms('Microsoft SQL Server'), @right, @left_paren, value_expression(S, ST), @comma, numeric_value_expression(N, _), @right_paren, get_source(Source), sized_varchar_type(N, Source, ST, T). 777string_value_function(isnull(P, C), T)---> dbms('Microsoft SQL Server'), @isnull(Source), {semantic_error(Source, deprecated(isnull, coalesce), 1)}, @left_paren, value_expression(P, T), @comma, value_expression(C, _), @right_paren. % According to the spec, the type of ISNULL is the first argument. 778string_value_function(replace(S, M, R), T)---> dbms('Microsoft SQL Server'), @replace, @left_paren, string_value_expression(S, ST), @comma, string_value_expression(M, _), @comma, string_value_expression(R, _), @right_paren, 779 get_source(Source), 780 {type_merge_hint(T, max), 781 type_constraint(Qid, Source, T, native_type(varchar(8000))), 782 type_constraint(Qid, Source, T, ST)}. 783 784 string_value_function(upper(S), T)---> dbms('Microsoft SQL Server'), @upper, @left_paren, value_expression(S, T), @right_paren. 785string_value_function(lower(S), T)---> dbms('Microsoft SQL Server'), @lower, @left_paren, value_expression(S, T), @right_paren. 786string_value_function(substring(Source, Start, Length), T)---> 787 dbms('Microsoft SQL Server'), @substring, @left_paren, character_value_expression(Source, ST), @comma, numeric_value_expression(Start, _), @comma, numeric_value_expression(Length, _), @right_paren, get_source(Source), sized_varchar_type(Length, Source, ST, T). 788string_value_function(datename(Type, Source), varchar) ---> dbms('Microsoft SQL Server'), @datename, @left_paren, sql_server_date_part(Type), @comma, datetime_value_expression(Source, _), @right_paren. 789string_value_function(dbname({}), native_type(nvarchar(128)))---> dbms('Microsoft SQL Server'), @db_name, @left_paren, @right_paren. 790string_value_function(permissions(S), varchar)---> dbms('Microsoft SQL Server'), @permissions, @left_paren, character_value_expression(S, _), @right_paren. 791string_value_function(username(String), native_type(nvarchar(128)))---> dbms('Microsoft SQL Server'), @user_name, @left_paren, character_value_expression(String, _), @right_paren. 792string_value_function(str(S), T)---> dbms('Microsoft SQL Server'), @str, @left_paren, qid(Qid), get_source(Source), {type_merge_hint(T, str), type_constraint(Qid, Source, T, native_type(varchar(1))), type_constraint(Qid, Source, T, ST)}, numeric_value_expression(S, ST), @right_paren. 793 794:-discontiguous(datetime_value_function/8). 795datetime_value_function(dateadd(Type, N, Source), native_type(datetime))---> dbms('Microsoft SQL Server'), @dateadd, @left_paren, sql_server_date_part(Type), @comma, numeric_value_expression(N, _), @comma, datetime_value_expression(Source, _), @right_paren. 796 797:-discontiguous(numeric_value_function/8). 798numeric_value_function(current_timestamp, native_type(datetime))---> @current_timestamp. 799numeric_value_function(getdate({}), native_type(datetime))---> dbms('Microsoft SQL Server'), @getdate, @left_paren, @right_paren. 800numeric_value_function(fn_now({}), native_type(datetime))---> dbms('Microsoft SQL Server'), @left_curly, @fn, @now, @left_paren, @right_paren, @right_curly, get_source(Source), {semantic_error(sql_escape, Source, 1)}. 801numeric_value_function(isnull(P, C), T)---> dbms('Microsoft SQL Server'), @isnull(Source), {semantic_error(Source, deprecated(isnull, coalesce), 1)}, @left_paren, value_expression(P, T), @comma, value_expression(C, _), @right_paren. 802numeric_value_function(datediff(Type, LHS, RHS), native_type(int))---> dbms('Microsoft SQL Server'), @datediff, @left_paren, sql_server_date_part(Type), @comma, datetime_value_expression(LHS, _), @comma, numeric_value_expression(RHS, _), @right_paren. 803numeric_value_function(day(S), native_type(int))---> dbms('Microsoft SQL Server'), @day, @left_paren, datetime_value_expression(S, _), @right_paren. 804numeric_value_function(month(S), native_type(int))---> dbms('Microsoft SQL Server'), @month, @left_paren, datetime_value_expression(S, _), @right_paren. 805numeric_value_function(year(S), native_type(int))---> dbms('Microsoft SQL Server'), @year, @left_paren, datetime_value_expression(S, _), @right_paren. 806 807numeric_value_function(datepart(Type, S), native_type(int))---> dbms('Microsoft SQL Server'), @datepart, @left_paren, sql_server_date_part(Type), @comma, datetime_value_expression(S, _), @right_paren. 808numeric_value_function(charindex(Source, Search, Start), native_type(int))---> dbms('Microsoft SQL Server'), @charindex, @left_paren, character_value_expression(Source, _), @comma, string_value_expression(Search, _), ( @comma, numeric_value_expression(Start, _) | {Start = {no_start}}), @right_paren. 809numeric_value_function(len(Source), native_type(int))---> dbms('Microsoft SQL Server'), @len, @left_paren, character_value_expression(Source, _), @right_paren. 810numeric_value_function(abs(S), T)---> dbms('Microsoft SQL Server'), @abs, @left_paren, {force_type_not_domain(T)}, numeric_value_expression(S, T), @right_paren. 811numeric_value_function(round(S, P), T)---> dbms('Microsoft SQL Server'), @round, @left_paren, {force_type_not_domain(T)}, numeric_value_expression(S, T), @comma, numeric_value_expression(P, _), @right_paren. 812numeric_value_function(floor(S), T)---> dbms('Microsoft SQL Server'), @floor, @left_paren, numeric_value_expression(S, ST), @right_paren, qid(Qid), get_source(Source), {type_merge_hint(T, round), type_constraint(Qid, Source, T, ST), type_constraint_ready(Qid, T)}. 813numeric_value_function(ceiling(S), native_type(int))---> dbms('Microsoft SQL Server'), @ceiling, @left_paren, numeric_value_expression(S, _), @right_paren. 814sql_server_date_part(T)---> ( @year | @yy | @yyyy), {T = year} 815 | (@quarter | @qq | @q ), {T = quarter} 816 | (@month | @mm | @m), {T = month} 817 | (@dayofyear | @dy), {T = dayofyear} 818 | (@day | @dd | @d | #literal(day, _), get_source(Source), {semantic_error(superfluous_quote(day), Source, 1)}), {T = day} 819 | (@week | @wk | @ww), {T = week} 820 | (@weekday | @dw | @w), {T = weekday} 821 | (@hour | @hh), {T = hour} 822 | (@minute | @mi | @n), {T = minute} 823 | (@second | @ss | @s), {T = second} 824 | (@millisecond | @ms), {T = millisecond} 825 | (@microsecond | @mcs), {T = microsecond} 826 | (@nanosecond | @ns), {T = nanosecond}. 827 828 829:-discontiguous(cast_specification/8). 830cast_specification(precision_cast(Target, Operand, P), Type)---> dbms('Microsoft SQL Server'), @convert, @left_paren, cast_target(Target), @comma, cast_operand(Operand), ( @comma, precision(P) | {P = {no_precision}} ), @right_paren, {strip_sql_comments(Target, Type)}. 831 832top_clause(top(N))---> @top, numeric_value_expression(NN, _T), ( @percent, get_source(Source), {semantic_error(percent, Source, 1)}, {N = percent(NN)} | {N = NN}). % TBD: Check that T is integer! 833limit_clause(top(N))---> @limit, numeric_value_expression(N, _T). % TBD: Check that T is integer! 834 835with_attribute(With)---> @with, @schemabinding, {With = with(schemabinding)} % schema_bound_view(ViewName) 836 | {With = {no_with}}. % view_attribute can also be ENCRYPTION or VIEW_METADATA, not used 837 838with_clause(with(nolock))---> @with, @left_paren, @nolock, @right_paren. 839with_clause(with(noexpand))---> @with, @left_paren, @noexpand, @right_paren. % eg force use of indexed views 840 841for_clause(for(xml_path(I)))---> @for, @xml, @path, @left_paren, string_value_expression(I, _), @right_paren, qid(Q), {query_is_xml(Q)}. 842:-discontiguous(grouping_column_reference/7). 843grouping_column_reference(group_expression(Expression, Collate))---> dbms('Microsoft SQL Server'), value_expression(Expression, _), ( collate_clause(Collate) | {Collate = {no_collation}} ). 844:-discontiguous(sort_key/7). 845sort_key(sort_expression(Expression))---> dbms('Microsoft SQL Server'), value_expression(Expression, _). 846 847:-discontiguous(datetime_type/7). 848datetime_type(datetime)---> @datetime. 849 850 851sized_varchar_type(L, L, O, O, P0, P0, Length, Source, SourceT, T):- 852 memberchk(query_id(Qid), O), 853 strip_sql_comments(Length, LS), 854 ( integer(LS) -> 855 % substring(foo, 5000) is the min of (5000, len(foo)). Create a new type resolution branch 856 type_merge_hint(T, sized_varchar), 857 type_constraint(Qid, Source, T, native_type(varchar(LS))), 858 type_constraint(Qid, Source, T, SourceT) 859 ; otherwise-> 860 force_type_not_domain(T), 861 type_constraint(Qid, Source, T, SourceT), 862 type_constraint_ready(Qid, T) 863 ). 864 865sql_explain(_). 866user:goal_expansion(sql_explain(_), true). 867%user:goal_expansion(sql_explain(A), (format(user_error, '*** ~w~n', [A]))). 868 869remove_quoted_column @ 870 type_constraint(QueryId, Source, Type, typeof(identifier(A, B), literal(ColumnName, string))) 871 <=> 872 type_constraint(QueryId, Source, Type, typeof(identifier(A, B), ColumnName)). 873 874define_type_from_subquery @ 875 derived_query_column(QueryId, TableAlias, Column, DerivedType) 876 \ 877 type_constraint(QueryId, Source, Type, typeof(identifier(_, TableAlias), Column)) 878 <=> 879 type_constraint(QueryId, Source, Type, DerivedType). 880 881define_type_from_subquery_with_unspecified_column @ 882 derived_query_column(QueryId, _, Column, DerivedType) 883 \ 884 type_constraint(QueryId, Source, Type, typeof({no_qualifier}, Column)) 885 <=> 886 type_constraint(QueryId, Source, Type, DerivedType). 887 888 889define_type_from_literal @ 890 type_constraint(QueryId, Source, Type, typeof({no_qualifier}, literal(Literal, Kind))) 891 <=> 892 ( Literal == '' -> 893 ColumnType = native_type(varchar(1)) % Quirk? 894 ; Kind == string-> 895 atom_length(Literal, L), 896 ColumnType = native_type(varchar(L)) 897 ; otherwise-> 898 ColumnType = native_type(Kind) 899 ), 900 type_constraint(QueryId, Source, Type, ColumnType). 901 902define_type_from_query @ 903 query_table(QueryId, TableAlias, identifier(_, TableName)) 904 \ 905 type_constraint(QueryId, Source, Type, typeof(identifier(_, TableAlias), SourceColumnName)) 906 <=> 907 default_schema(Schema), 908 fetch_database_attribute(_, Schema, TableName, SourceColumnName, ColumnType, _, _, _) 909 | 910 type_constraint(QueryId, Source, Type, ColumnType). 911 912define_type_from_query_with_unnamed_table @ 913 query_table(QueryId, _, identifier(_, TableName)) 914 \ 915 type_constraint(QueryId, Source, Type, typeof({no_qualifier}, SourceColumnName)) 916 <=> 917 % Have to search all tables :-( 918 default_schema(Schema), 919 %writeln(checking(Schema, TableName, SourceColumnName)), 920 fetch_database_attribute(_, Schema, TableName, SourceColumnName, ColumnType, _, _, _) 921 %writeln(found) 922 | 923 type_constraint(QueryId, Source, Type, ColumnType). 924 925define_type_from_uncorrelated_table_with_explicit_reference @ % Yuck! 926 query_table(QueryId, uncorrelated(TableName), _) 927 \ 928 type_constraint(QueryId, Source, Type, typeof(identifier(_, TableName), SourceColumnName)) 929 <=> 930 default_schema(Schema), 931 fetch_database_attribute(_, Schema, TableName, SourceColumnName, ColumnType, _, _, _) 932 | 933 type_constraint(QueryId, Source, Type, ColumnType). 934 935define_type_from_uncorrelated_table @ 936 query_table(QueryId, uncorrelated(TableName), _) 937 \ 938 type_constraint(QueryId, Source, Type, typeof(X, SourceColumnName)) 939 <=> 940 X \= identifier(_,_), 941 % Have to search all tables here, too :-( 942 default_schema(Schema), 943 fetch_database_attribute(_, Schema, TableName, SourceColumnName, ColumnType, _, _, _) 944 | 945 type_constraint(QueryId, Source, Type, ColumnType). 946 947 948crush_xml_subquery_into_scalar @ 949 query_is_xml(SubQueryId), 950 subquery(QueryId, SubQueryId) 951 \ 952 type_constraint(QueryId, Source, Type, scalar([merged(_, _, _Subtype)])) 953 <=> 954 sql_explain(crush_xml), 955 type_constraint(QueryId, Source, Type, native_type(nvarchar(max))). 956 957 958crush_subquery_into_scalar @ 959 type_constraint(QueryId, Source, Type, scalar([merged(_, _, Subtype)])) 960 <=> 961 sql_explain(crush_subquery), 962 type_constraint(QueryId, Source, Type, Subtype). 963 964concatenate_char @ 965 type_merge_hint(Type, Hint), 966 type_constraint(QueryId, Source1, Type, native_type(varchar(N))), 967 type_constraint(QueryId, Source2, Type, native_type(varchar(M))) 968 <=> 969 Hint == add ; Hint == concatenate 970 | 971 ( N == max -> 972 Z = max 973 ; M == max -> 974 Z = max 975 ; otherwise -> 976 Z is min(N+M, 8000) 977 ), 978 sql_explain(concatenate_char), 979 merge_sources(Source1, Source2, Source), 980 type_constraint(QueryId, Source, Type, native_type(varchar(Z))), 981 type_constraint_ready(QueryId, Type). 982 983concatenate_nchar_and_varchar @ 984 type_merge_hint(Type, Hint), 985 type_constraint(QueryId, Source1, Type, native_type(nvarchar(N))), 986 type_constraint(QueryId, Source2, Type, native_type(varchar(M))) 987 <=> 988 Hint == add ; Hint == concatenate 989 | 990 ( N == max -> 991 Z = max 992 ; M == max -> 993 Z = max 994 ; otherwise-> 995 Z is min(N+M, 8000) 996 ), 997 sql_explain(concatenate_nchar_and_varchar), 998 merge_sources(Source1, Source2, Source), 999 type_constraint(QueryId, Source, Type, native_type(nvarchar(Z))), 1000 type_constraint_ready(QueryId, Type). 1001 1002concatenate_nchar_and_nchar @ 1003 type_merge_hint(Type, Hint), 1004 type_constraint(QueryId, Source1, Type, native_type(nvarchar(N))), 1005 type_constraint(QueryId, Source2, Type, native_type(nvarchar(M))) 1006 <=> 1007 Hint == add ; Hint == concatenate 1008 | 1009 ( N == max -> 1010 Z = max 1011 ; M == max -> 1012 Z = max 1013 ; otherwise-> 1014 Z is min(N+M, 8000) 1015 ), 1016 sql_explain(concatenate_nchar_and_nchar), 1017 merge_sources(Source1, Source2, Source), 1018 type_constraint(QueryId, Source, Type, native_type(nvarchar(Z))), 1019 type_constraint_ready(QueryId, Type). 1020 1021 1022merge_sized_chars @ 1023 type_constraint(QueryId, _, Type, native_type(varchar(N))) 1024 \ 1025 type_merge_hint(Type, sized_varchar), 1026 type_constraint(QueryId, _, Type, native_type(varchar(M))) 1027 <=> 1028 ( integer(N), integer(M), N < M ) 1029 | 1030 sql_explain(merge_sized_chars), 1031 type_constraint_ready(QueryId, Type). 1032 1033union_chars @ 1034 type_constraint(QueryId, _, Type, native_type(varchar(N))) 1035 \ 1036 type_merge_hint(Type, Hint), 1037 type_constraint(QueryId, _, Type, native_type(varchar(M))) 1038 <=> 1039 Hint \== concatenate, 1040 ( N == max 1041 ; M == max 1042 ; N >= M 1043 ) 1044 | 1045 sql_explain(union_chars(N, M)), 1046 type_constraint_ready(QueryId, Type). 1047 1048union_nchars @ 1049 type_constraint(QueryId, _, Type, native_type(nvarchar(N))) 1050 \ 1051 type_merge_hint(Type, Hint), 1052 type_constraint(QueryId, _, Type, native_type(nvarchar(M))) 1053 <=> 1054 Hint \== concatenate, 1055 ( N == max 1056 ; M == max 1057 ; N >= M 1058 ) 1059 | 1060 sql_explain(union_nchars), 1061 type_constraint_ready(QueryId, Type). 1062 1063union_nchar_and_varchar @ 1064 type_merge_hint(Type, Hint), 1065 type_constraint(QueryId, Source1, Type, native_type(nvarchar(N))), 1066 type_constraint(QueryId, Source2, Type, native_type(varchar(M))) 1067 <=> 1068 Hint \== concatenate 1069 | 1070 ( N == max-> 1071 Z = max 1072 ; M == max-> 1073 Z = max 1074 ; otherwise-> 1075 Z is max(N, M) 1076 ), 1077 sql_explain(union_nchar_and_varchar), 1078 merge_sources(Source1, Source2, Source), 1079 type_constraint(QueryId, Source, Type, native_type(nvarchar(Z))), 1080 type_constraint_ready(QueryId, Type). 1081 1082 1083 1084expand_precision_integer_to_decimal @ % These come from literals in the query like -1 1085 type_constraint(QueryId, Source1, Type, native_type(decimal(_, _))) 1086 \ 1087 type_constraint(QueryId, Source2, Type, native_type(int(N))) 1088 <=> 1089 sql_explain(precision_integer_to_decimal(N,0)), 1090 merge_sources(Source1, Source2, Source), 1091 type_constraint(QueryId, Source, Type, native_type(decimal(N, 0))). 1092 1093expand_precision_integer_to_general_integer @ 1094 type_constraint(QueryId, _, Type, native_type(int)) 1095 \ 1096 type_merge_hint(Type, _), 1097 type_constraint(QueryId, _, Type, native_type(int(_))) 1098 <=> 1099 sql_explain(precision_integer_to_int), 1100 type_constraint_ready(QueryId, Type). 1101 1102 1103expand_integer_to_decimal @ 1104 type_merge_hint(Type, union), 1105 type_constraint(QueryId, Source1, Type, native_type(decimal(_, _))) 1106 \ 1107 type_constraint(QueryId, Source2, Type, native_type(int)) 1108 <=> 1109 sql_explain(integer_to_decimal_for_union), 1110 merge_sources(Source1, Source2, Source), 1111 type_constraint(QueryId, Source, Type, native_type(decimal(10,0))). 1112 1113expand_tinyint_to_int @ 1114 type_constraint(QueryId, _, Type, native_type(int)) 1115 \ 1116 type_merge_hint(Type, _), 1117 type_constraint(QueryId, _, Type, native_type(tinyint)) 1118 <=> 1119 sql_explain(tinyint_to_int), 1120 type_constraint_ready(QueryId, Type). 1121 1122expand_tinyint_to_precision_int @ 1123 type_constraint(QueryId, _, Type, native_type(int(_))) 1124 \ 1125 type_merge_hint(Type, _), 1126 type_constraint(QueryId, _, Type, native_type(tinyint)) 1127 <=> 1128 sql_explain(tinyint_to_precision_integer), 1129 type_constraint_ready(QueryId, Type). 1130 1131 1132expand_int_to_float @ 1133 type_constraint(QueryId, _, Type, native_type(float(_))) 1134 \ 1135 type_merge_hint(Type, _), 1136 type_constraint(QueryId, _, Type, native_type(int)) 1137 <=> 1138 sql_explain(int_to_float), 1139 type_constraint_ready(QueryId, Type). 1140 1141%Quirk. Note that if the varchar is anything BUT spaces, you get an error when selecting from the view! 1142quirk_tinyint_and_varchar_is_tinyint @ 1143 type_constraint(QueryId, _, Type, native_type(tinyint)) 1144 \ 1145 type_merge_hint(Type, _), 1146 type_constraint(QueryId, _, Type, native_type(varchar(_))) 1147 <=> 1148 sql_explain(tinyint_and_varchar), 1149 type_constraint_ready(QueryId, Type). 1150 1151max_varchars @ 1152 type_constraint(QueryId, _, Type, native_type(varchar(A))) 1153 \ 1154 type_merge_hint(Type, max), 1155 type_constraint(QueryId, _, Type, native_type(varchar(B))) 1156 <=> 1157 integer(A), integer(B), A >= B 1158 | 1159 sql_explain(max_varchars), 1160 type_constraint_ready(QueryId, Type). 1161 1162max_nvarchar_with_varchar @ 1163 type_merge_hint(Type, max), 1164 type_constraint(QueryId, Source1, Type, native_type(nvarchar(A))), 1165 type_constraint(QueryId, Source2, Type, native_type(varchar(B))) 1166 <=> 1167 ( ( A == max ; B == max)-> 1168 C = max 1169 ; otherwise-> 1170 C is max(A, B) 1171 ), 1172 sql_explain(max_vvarchar_with_varchar), 1173 merge_sources(Source1, Source2, Source), 1174 type_constraint(QueryId, Source, Type, native_type(nvarchar(C))), 1175 type_constraint_ready(QueryId, Type). 1176 1177 1178str_expression_with_int @ 1179 type_merge_hint(Type, str), 1180 type_constraint(QueryId, Source1, Type, native_type(int)), 1181 type_constraint(QueryId, Source2, Type, native_type(varchar(A))) 1182 <=> 1183 ( A == max -> 1184 B = max 1185 ; otherwise -> 1186 B is max(A, 10) 1187 ), 1188 sql_explain(str_expression_with_int), 1189 merge_sources(Source1, Source2, Source), 1190 type_constraint(QueryId, Source, Type, native_type(varchar(B))), 1191 type_constraint_ready(QueryId, Type). 1192 1193%Quirk. Note that if the varchar is anything BUT spaces, you get an error when selecting from the view! 1194quirk_int_and_varchar_is_int @ 1195 type_constraint(QueryId, Source1, Type, native_type(int)) 1196 \ 1197 type_merge_hint(Type, _), 1198 type_constraint(QueryId, Source2, Type, native_type(varchar(_))) 1199 <=> 1200 sql_explain(int_and_varchar), 1201 type_mismatch(Source1, Source2, int, varchar), 1202 type_constraint_ready(QueryId, Type). 1203 1204quirk_int_and_varchar_is_int @ 1205 type_constraint(QueryId, Source1, Type, native_type(int(_))) 1206 \ 1207 type_merge_hint(Type, _), 1208 type_constraint(QueryId, Source2, Type, native_type(varchar(_))) 1209 <=> 1210 sql_explain(int_and_varchar), 1211 type_mismatch(Source1, Source2, int, varchar), 1212 type_constraint_ready(QueryId, Type). 1213 1214 1215%Quirk 1216quirk_datetime_and_int_is_int @ 1217 type_constraint(QueryId, _, Type, native_type(datetime)) 1218 \ 1219 type_merge_hint(Type, _), 1220 type_constraint(QueryId, _, Type, native_type(int)) 1221 <=> 1222 sql_explain(datetime_and_int), 1223 type_constraint_ready(QueryId, Type). 1224 1225%Quirk 1226quirk_datetime_and_precision_int_is_precision_int @ 1227 type_constraint(QueryId, _, Type, native_type(datetime)) 1228 \ 1229 type_merge_hint(Type, _), 1230 type_constraint(QueryId, _, Type, native_type(int(_))) 1231 <=> 1232 sql_explain(datetime_and_precision_int), 1233 type_constraint_ready(QueryId, Type). 1234 1235 1236integer_addition @ 1237 type_constraint(QueryId, _, Type, native_type(int)) 1238 \ 1239 type_merge_hint(Type, Hint), 1240 type_constraint(QueryId, _, Type, native_type(int)) 1241 <=> 1242 memberchk(Hint, [add, subtract, concatenate]) 1243 | 1244 sql_explain(integer_addition), 1245 type_constraint_ready(QueryId, Type). 1246 1247integer_multiplication_requires_promotion @ 1248 type_merge_hint(Type, Hint) 1249 \ 1250 type_constraint(QueryId, Source, Type, native_type(int)) 1251 <=> 1252 memberchk(Hint, [multiply, divide(_)]) 1253 | 1254 sql_explain(promote_int_for_multiplication), 1255 type_constraint(QueryId, Source, Type, native_type(decimal(10,0))). 1256 1257 1258integer_and_decimal_arithmetic @ 1259 type_merge_hint(Type, Hint), 1260 type_constraint(QueryId, Source1, Type, native_type(decimal(_, _))) 1261 \ 1262 type_constraint(QueryId, Source2, Type, native_type(int)) 1263 <=> 1264 Hint \== union 1265 | 1266 sql_explain(promote_int_to_decimal_for_arithmetic(Hint)), 1267 merge_sources(Source1, Source2, Source), 1268 type_constraint(QueryId, Source, Type, native_type(decimal(10,0))). 1269 1270 1271expand_type_scope_decimal_with_hint @ 1272 type_merge_hint(Type, Hint), 1273 type_constraint(QueryId, Source1, Type, native_type(decimal(P1, S1))), 1274 type_constraint(QueryId, Source2, Type, native_type(decimal(P2, S2))) 1275 <=> 1276 ( Hint == multiply-> 1277 P is P1 + P2 + 1, 1278 S is S1 + S2 1279 ; ( Hint == add ; Hint == concatenate) -> % TBD: This indicates a parse failure, surely... 1280 P is max(S1, S2) + max(P1-S1, P2-S2) + 1, 1281 S is max(S1, S2) 1282 ; Hint == subtract -> 1283 P is max(S1, S2) + max(P1-S1, P2-S2) + 1, 1284 S is max(S1, S2) 1285 ; Hint = divide(DA)-> 1286 ( DA = node(Divisor, _, _, _, _) -> 1287 true 1288 ; otherwise-> 1289 Divisor = DA 1290 ), 1291 % We need to know which is the divisor and which is the quotient to 1292 % calculate this correctly 1293 ( Divisor = domain(D) -> 1294 fetch_domain_data_type(D, V) 1295 ; Divisor = native_type(V)-> 1296 true 1297 ; otherwise-> 1298 throw(bad_divisor(Divisor)) 1299 ), 1300 ( ( ( V = int(P1), S1 == 0) % Promoted from int(P1) -> decimal(P1, 0) 1301 ; 1302 V == decimal(P1, S1))-> 1303 1304 % Oops, round the wrong way! 1305 P is P2 - S2 + S1 + max(6, S2 + P1 + 1), 1306 S is max(6, S2 + P1 + 1) 1307 ; otherwise-> 1308 % Proceed as usual 1309 P is P1 - S1 + S2 + max(6, S1 + P2 + 1), 1310 S is max(6, S1 + P2 + 1) 1311 ) 1312 ; Hint == union-> 1313 P is max(S1, S2) + max(P1-S1, P2-S2), 1314 S is max(S1, S2) 1315 ; otherwise-> 1316 throw(unhandled_scope(Hint)) 1317 ), 1318 ( P > 38 -> 1319 Px = 38, 1320 % I determined this for addition by trial and error :-( 1321 % For multiplication, see http://blogs.msdn.com/b/sqlprogrammability/archive/2006/03/29/564110.aspx 1322 % eg SELECT (cast(1 as decimal(38, 6))) - (cast(1 as decimal(14, 2))) 1323 ( memberchk(Hint, [add, concatenate, subtract])-> 1324 Sx is max(0, S - (P-39)) 1325 ; Hint == union-> 1326 Sx is max(0, S - (P-38)) 1327 ; max(0, S-(P-38)) < 6-> 1328 Sx is min(S, 6) 1329 ; otherwise-> 1330 Sx is max(0, S - (P-38)) 1331 ) 1332 ; otherwise-> 1333 Px = P, 1334 Sx = S 1335 ), 1336 sql_explain(decimal_arithmetic(Hint, Px, Sx)), 1337 merge_sources(Source1, Source2, Source), 1338 type_constraint(QueryId, Source, Type, native_type(decimal(Px, Sx))), 1339 type_constraint_ready(QueryId, Type). 1340 1341resolve_types @ 1342 resolve_types(QueryId) 1343 <=> 1344 commit(QueryId). 1345 1346resolve_unions @ 1347 commit(QueryId) 1348 \ 1349 union_type(QueryId, L, R, T) 1350 <=> 1351 is_list(L), 1352 is_list(R) 1353 | 1354 ( resolve_union_type(QueryId, L, R, T)-> 1355 true 1356 ; otherwise-> 1357 format(atom(Message), 'Could not determine the type of the union of ~w and ~w', [L, R]), 1358 throw(cql_error(failed_to_resolve_union, Message)) 1359 ). 1360 1361resolve_derived_tables @ 1362 commit(QueryId), 1363 derived_table(QueryId, Table, Constraint) 1364 <=> 1365 is_list(Constraint) 1366 | 1367 create_derived_table(QueryId, Table, Constraint), 1368 commit(QueryId). 1369 1370union_of_type_decimal_domains_is_not_a_domain @ 1371 commit(QueryId), % Have to suspend commits until all the new constraints are in 1372 type_merge_hint(Type, union), 1373 type_constraint(QueryId, Source1, Type, domain(D1)), 1374 type_constraint(QueryId, Source2, Type, domain(D2)) 1375 <=> 1376 fetch_domain_data_type(D1, decimal(A1, A2)), 1377 fetch_domain_data_type(D2, decimal(B1, B2)) 1378 | 1379 sql_explain(union_domains), 1380 most_general_type(QueryId, Source1, Source2, decimal(A1, A2), decimal(B1, B2), union, Type), 1381 commit(QueryId). 1382 1383merge_domains @ 1384 commit(QueryId), % Have to suspend commits until all the new constraints are in 1385 type_merge_hint(Type, Hint), 1386 type_constraint(QueryId, Source1, Type, domain(D1)), 1387 type_constraint(QueryId, Source2, Type, domain(D2)) 1388 <=> 1389 fetch_domain_data_type(D1, T1), 1390 fetch_domain_data_type(D2, T2) 1391 | 1392 ( T1 = decimal(_, _), 1393 T2 = decimal(_, _)-> 1394 most_general_type(QueryId, Source1, Source2, T1, T2, Hint, Type) 1395 ; T1 = datetime, T2 = datetime, memberchk(Hint, [add, subtract, concatenate])-> 1396 type_constraint(QueryId, Source1, Type, native_type(datetime)), 1397 type_constraint_ready(QueryId, Type) 1398 ; D1 == D2, Hint \== concatenate-> 1399 type_constraint(QueryId, Source1, Type, domain(D1)), 1400 type_constraint_ready(QueryId, Type) 1401 ; otherwise-> 1402 most_general_type(QueryId, Source1, Source1, T1, T2, Hint, Type) 1403 ), 1404 sql_explain(join_domains_for(Hint, D1, D2, Type)), 1405 commit(QueryId). 1406 1407merge_domain_to_native @ 1408 commit(QueryId), % Have to suspend commits until all the new constraints are in 1409 type_merge_hint(Type, Hint), 1410 type_constraint(QueryId, Source1, Type, domain(D)), 1411 type_constraint(QueryId, Source2, Type, native_type(NT)) 1412 <=> 1413 fetch_domain_data_type(D, T) 1414 | 1415 sql_explain(join_domain_to_native), 1416 most_general_type(QueryId, Source1, Source2, T, NT, Hint, Type), 1417 commit(QueryId). 1418 1419drop_nulltype_in_favour_of_domain @ 1420 % SQL Server drops the domain here too for some reason 1421 type_merge_hint(Type, _Anything), 1422 type_constraint(QueryId, Source1, Type, domain(D)), 1423 type_constraint(QueryId, Source2, Type, {nulltype}) 1424 <=> 1425 sql_explain(drop_nulltype_for_domain), 1426 fetch_domain_data_type(D, T), 1427 merge_sources(Source1, Source2, Source), 1428 type_constraint(QueryId, Source, Type, native_type(T)), 1429 type_constraint_ready(QueryId, Type). 1430 1431drop_nulltype_in_favour_of_native_type @ 1432 type_constraint(QueryId, _, Type, native_type(T)) 1433 \ 1434 type_merge_hint(Type, Hint), 1435 type_constraint(QueryId, _, Type, {nulltype}) 1436 <=> 1437 sql_explain(drop_nulltype_for_native(T, Hint)), 1438 type_constraint_ready(QueryId, Type). 1439 1440 1441% This is actually wrong. In fact, NULL is not a valid type in ANSI SQL. 1442% SQL Server appears to treat nulltypes as integers (ultimately) but delays resolving them 1443% until it has to. 'PostgreSQL' takes a very hard line on the standard, and says that the union 1444% of two nulltypes is a varchar. This means if you do this: 1445% SELECT NULL UNION SELECT NULL UNION SELECT 1 1446% you will get a type error, but if you do this: 1447% SELECT NULL UNION SELECT 1 UNION SELECT NULL 1448% you will not. In reality, the nicest thing to do is not to return an untyped NULL by changing 1449% SELECT NULL AS foo -> SELECT CAST(NULL AS datetime) AS foo 1450% in the cases where NULL even makes sense. 1451drop_nulltype_in_favour_of_another_nulltype @ 1452 type_constraint(QueryId, _, Type, {nulltype}) 1453 \ 1454 type_merge_hint(Type, Hint), 1455 type_constraint(QueryId, _, Type, {nulltype}) 1456 <=> 1457 ( Hint == union -> 1458 ( prolog_load_context(file, _Filename)-> 1459 true 1460 %format(user_error, '~w: Union of nulls is illegal. Use CAST() to make the type explicit~n', [Filename, Hint]) 1461 ; otherwise-> 1462 true 1463 ) 1464 ; otherwise-> 1465 true 1466 ), 1467 sql_explain(drop_nulltype_for_nulltype(Hint)), 1468 type_constraint_ready(QueryId, Type). 1469 1470force_type_not_domain @ 1471 % SQL Server seems to abandon domains very quickly in numeric expressions 1472 force_type_not_domain(Type), 1473 type_constraint(QueryId, Source, Type, domain(D)) 1474 <=> 1475 fetch_domain_data_type(D, decimal(P, S)) 1476 | 1477 sql_explain(forcing_decimal_not_domain(D)), 1478 type_constraint(QueryId, Source, Type, native_type(decimal(P, S))). 1479 1480force_type_not_domain @ 1481 % SQL Server seems to abandon domains in string operations too (eg substring(domain, 1) -> varchar) 1482 force_type_not_domain(Type), 1483 type_constraint(QueryId, Source, Type, domain(D)) 1484 <=> 1485 fetch_domain_data_type(D, varchar(L)) 1486 | 1487 sql_explain(forcing_varchar_not_domain(D)), 1488 type_constraint(QueryId, Source, Type, native_type(varchar(L))). 1489 1490force_type_not_domain @ 1491 force_type_not_domain(Type), 1492 type_constraint(QueryId, Source, Type, domain(D)) 1493 <=> 1494 fetch_domain_data_type(D, datetime) 1495 | 1496 sql_explain(forcing_datetime_not_domain(D)), 1497 type_constraint(QueryId, Source, Type, native_type(datetime)). 1498 1499 1500rounded_int_is_decimal @ /* is it? */ 1501 type_merge_hint(Type, round), 1502 type_constraint(QueryId, Source, Type, native_type(int)) 1503 <=> 1504 sql_explain(rounded_int_is_decimal), 1505 type_constraint(QueryId, Source, Type, native_type(decimal(10,0))). 1506 1507rounded_decimal_has_no_scale @ 1508 type_merge_hint(Type, round), 1509 type_constraint(QueryId, Source, Type, native_type(decimal(P, _))) 1510 <=> 1511 sql_explain(rounded_decimal_has_no_scale), 1512 type_constraint(QueryId, Source, Type, native_type(decimal(P,0))). 1513 1514 1515force_domain_type_to_sum @ 1516 % SQL Server always turns decimal(_, S) -> decimal(38, S) when adding 1517 type_merge_hint(Type, sum), 1518 type_constraint(QueryId, Source, Type, domain(D)) 1519 <=> 1520 fetch_domain_data_type(D, decimal(_, S)) 1521 | 1522 sql_explain(forcing_domain_to_sum(D)), 1523 type_constraint(QueryId, Source, Type, native_type(decimal(38, S))). 1524 1525force_domain_type_to_avg @ 1526 % SQL Server always turns decimal(_, S) -> decimal(38, 6) when averaging 1527 type_merge_hint(Type, avg), 1528 type_constraint(QueryId, Source, Type, domain(D)) 1529 <=> 1530 sql_explain(forcing_domain_to_avg(D)), 1531 type_constraint(QueryId, Source, Type, native_type(decimal(38, 6))). 1532 1533force_native_type_to_sum @ 1534 % SQL Server always turns decimal(_, S) -> decimal(38, S) when adding 1535 type_merge_hint(Type, sum), 1536 type_constraint(QueryId, Source, Type, native_type(decimal(_, S))) 1537 <=> 1538 sql_explain(forcing_native_to_sum), 1539 type_constraint(QueryId, Source, Type, native_type(decimal(38, S))). 1540 1541force_native_type_to_avg @ 1542 % SQL Server always turns decimal(_, S) -> decimal(38, S) when averaging 1543 type_merge_hint(Type, avg), 1544 type_constraint(QueryId, Source, Type, native_type(decimal(_, _))) 1545 <=> 1546 sql_explain(forcing_native_to_avg), 1547 type_constraint(QueryId, Source, Type, native_type(decimal(38, 6))). 1548 1549union_precision_ints @ 1550 type_merge_hint(Type, union), 1551 type_constraint(QueryId, Source1, Type, native_type(int(_))), 1552 type_constraint(QueryId, Source2, Type, native_type(int(_))) 1553 <=> 1554 sql_explain(union_precision_ints), 1555 merge_sources(Source1, Source2, Source), 1556 type_constraint(QueryId, Source, Type, native_type(int)), 1557 type_constraint_ready(QueryId, Type). 1558 1559union_identical_natives @ 1560 type_constraint(QueryId, _, Type, native_type(T)) 1561 \ 1562 type_merge_hint(Type, union), 1563 type_constraint(QueryId, _, Type, native_type(T)) 1564 <=> 1565 sql_explain(union_identical_natives(T)), 1566 type_constraint_ready(QueryId, Type). 1567 1568merge_datetime_and_int @ 1569 type_merge_hint(Type, _Hint), 1570 type_constraint(QueryId, Source1, Type, native_type(datetime)), 1571 type_constraint(QueryId, Source2, Type, native_type(decimal(_,_))) 1572 <=> 1573 sql_explain(operation_with_datetime_and_decimal), 1574 merge_sources(Source1, Source2, Source), 1575 type_constraint(QueryId, Source, Type, native_type(datetime)), 1576 type_constraint_ready(QueryId, Type). 1577 1578union_of_datetime_and_date_is_datetime @ 1579 type_merge_hint(Type, union), 1580 type_constraint(QueryId, Source, Type, native_type(datetime)) 1581 \ 1582 type_constraint(QueryId, Source, Type, native_type(date)) 1583 <=> 1584 sql_explain(union_of_datetime_and_date), 1585 type_constraint_ready(QueryId, Type). 1586 1587 1588accept_domain @ 1589 type_constraint(QueryId, _, Type, domain(Domain)), 1590 type_constraint_ready(QueryId, Type) 1591 <=> 1592 sql_explain(accepting(domain(Domain), Type)), 1593 Type = domain(Domain). 1594 1595accept_native_type @ 1596 type_constraint(QueryId, _, Type, native_type(Native)), 1597 type_constraint_ready(QueryId, Type) 1598 <=> 1599 sql_explain(accepting(native_type(Native), Type)), 1600 Type = native_type(Native). 1601 1602accept_nulltype @ 1603 type_constraint(QueryId, _, Type, {nulltype}), 1604 type_constraint_ready(QueryId, Type) 1605 <=> 1606 sql_explain(accepting_nulltype(Type)), 1607 Type = {nulltype}. 1608 1609 1610find_column_types @ 1611 query_table(QueryId, _, identifier(_, TableName)), 1612 find_all_column_types(QueryId, Source, Tail) 1613 <=> 1614 default_schema(Schema), 1615 findall(merged(ColumnName, Source, Domain), 1616 fetch_database_attribute(_, Schema, TableName, ColumnName, Domain, _, _, _), 1617 Columns), 1618 create_column_constraints(QueryId, Source, Columns, Tail, NewTail), 1619 find_all_column_types(QueryId, Source, NewTail). 1620 1621find_column_types @ 1622 derived_query_column(QueryId, _, Column, ColumnType), 1623 find_all_column_types(QueryId, Source, Tail) 1624 <=> 1625 Tail = [merged(Column, Source, ColumnType)|NewTail], 1626 find_all_column_types(QueryId, Source, NewTail). 1627 1628share_commit @ 1629 commit(Parent), 1630 subquery(Parent, Child) 1631 ==> 1632 commit(Child). 1633 1634commit_peers @ 1635 commit(Qid), 1636 peer_query(Qid, Sibling) 1637 ==> 1638 commit(Sibling). 1639 1640 1641share_table @ 1642 subquery(Parent, Child), 1643 query_table(Parent, A, B) 1644 ==> 1645 query_table(Child, A, B). 1646 1647%share_derived_table @ 1648% subquery(Parent, Child), 1649% derived_query_column(Parent, A, B, C) 1650% ==> 1651% derived_query_column(Child, A, B, C). 1652 1653finished_finding @ 1654 commit(QueryId) 1655 \ 1656 find_all_column_types(QueryId, _, Tail) 1657 <=> 1658 Tail = []. 1659 1660/* 1661 Unfortunately this isnt ALWAYS indicative of failure. The test case was 1662SELECT x FROM y WHERE NOT EXISTS (SELECT a FROM b UNION SELECT c FROM d WHERE y.z = 1) 1663fail_to_resolve @ 1664 commit_phase(QueryId, _), 1665 type_constraint(QueryId, _, _, typeof(A, B)) 1666 <=> 1667 throw(cannot_resolve_type(A, B)). 1668*/ 1669 1670frozen_reverse @ 1671 frozen_reverse(_, A, B) 1672 <=> 1673 is_list(A) 1674 | 1675 A = B. 1676% reverse(A, B). 1677 1678cleanup @ cleanup(QueryId) \ frozen_reverse(QueryId, _, _) <=> true. 1679cleanup @ cleanup(QueryId) \ type_constraint(QueryId, _, _, _) <=> true. % This should be an error, I think? 1680cleanup @ cleanup(_QueryId) \ type_constraint_ready(_, _) <=> true. 1681cleanup @ cleanup(_QueryId) \ type_merge_hint(_, _) <=> true. 1682cleanup @ cleanup(QueryId) \ query_table(QueryId, _, _) <=> true. 1683cleanup @ cleanup(QueryId) \ derived_query_column(QueryId, _, _, _) <=> true. 1684cleanup @ cleanup(QueryId) \ subquery(QueryId, SubQueryId) <=> cleanup(SubQueryId). 1685cleanup @ cleanup(QueryId) \ peer_query(QueryId, PeerQueryId) <=> cleanup(PeerQueryId). 1686cleanup @ cleanup(QueryId) \ query_is_xml(QueryId) <=> true. 1687cleanup @ cleanup(QueryId) \ commit(QueryId) <=> true. 1688cleanup @ cleanup(QueryId) \ find_all_column_types(QueryId, _, _) <=> true. 1689cleanup @ cleanup(QueryId) \ subquery(SubQueryId, QueryId) <=> cleanup(SubQueryId). 1690%cleanup @ cleanup(QueryId) \ force_type_not_domain(_) <=> true. 1691cleanup @ cleanup(_) <=> true. 1692 1693create_column_constraints(_QueryId, _Source, [], NewTail, NewTail):- !. 1694create_column_constraints(QueryId, Source, [merged(Name, Source, Domain)|Columns], [merged(Name, Source, Type)|T], NewTail):- 1695 type_constraint(QueryId, Source, Type, Domain), 1696 type_constraint_ready(QueryId, Type), 1697 create_column_constraints(QueryId, Source, Columns, T, NewTail). 1698 1699 1700resolve_union_type(_QueryId, [], [], []):- !. 1701resolve_union_type(_QueryId, A, [], []):- throw(union_mismatch(left(A))). 1702resolve_union_type(_QueryId, [], A, []):- throw(union_mismatch(right(A))). 1703resolve_union_type(QueryId, [merged(NameA, SourceA, TypeA)|As], [merged(NameB, SourceB, TypeB)|Bs], [merged(Name, SourceA, C)|Cs]):- 1704 % We can just pick either A or B for the source, I guess? 1705 % A and B are already CHR-resolved. Calling most_general_type/3 would add native_type/1 wrappers. 1706 % Quirk 1707 ( nonvar(TypeA), 1708 TypeA = domain(X), 1709 nonvar(TypeB), 1710 TypeB = domain(X), 1711 fetch_domain_data_type(X, decimal(P, S))-> 1712 C = native_type(decimal(P,S)) 1713 ; otherwise-> 1714 type_merge_hint(C, union), 1715 type_constraint(QueryId, SourceA, C, TypeA), 1716 type_constraint(QueryId, SourceB, C, TypeB) 1717 ), 1718 ( NameA == {no_alias} -> 1719 name_from_identifier(NameB, Name) 1720 ; otherwise-> 1721 name_from_identifier(NameA, Name) 1722 ), 1723 resolve_union_type(QueryId, As, Bs, Cs). 1724 1725 1726name_from_identifier(literal(NameMC, _), Name):- !, 1727 downcase_atom(NameMC, Name). 1728name_from_identifier({no_alias}, {no_alias}):- !. 1729name_from_identifier(Identifier, Name):- 1730 downcase_atom(Identifier, Name). 1731 1732 1733 1734 1735check_types(_, _). 1736most_general_type(QueryId, S1, S2, A, B, Op, C):- 1737 type_merge_hint(C, Op), 1738 ( var(A)-> 1739 type_constraint(QueryId, S1, C, A) 1740 ; otherwise-> 1741 strip_sql_comments(A, AA), 1742 ( AA = native_type(AAA)-> 1743 type_constraint(QueryId, S1, C, native_type(AAA)) 1744 ; AA = domain(AAA)-> 1745 type_constraint(QueryId, S1, C, domain(AAA)) 1746 ; AA == {nulltype}-> 1747 type_constraint(QueryId, S1, C, {nulltype}) 1748 ; otherwise-> 1749 type_constraint(QueryId, S1, C, native_type(AA)) 1750 ) 1751 ), 1752 ( var(B)-> 1753 type_constraint(QueryId, S2, C, B) 1754 ; otherwise-> 1755 strip_sql_comments(B, BB), 1756 ( BB = native_type(BBB)-> 1757 type_constraint(QueryId, S2, C, native_type(BBB)) 1758 ; BB = domain(BBB)-> 1759 type_constraint(QueryId, S2, C, domain(BBB)) 1760 ; BB == {nulltype}-> 1761 type_constraint(QueryId, S2, C, {nulltype}) 1762 ; otherwise-> 1763 type_constraint(QueryId, S2, C, native_type(BB)) 1764 ) 1765 ). 1766 1767% The type system expects everything to have at most 2 parents. Make a tree. 1768coalesce_type(QueryId, [A, B], [S1, S2], T):- !, 1769 most_general_type(QueryId, S1, S2, A, B, union, T). 1770coalesce_type(QueryId, [A], [S1], T):- !, 1771 type_constraint(QueryId, S1, T, A), 1772 type_constraint_ready(QueryId, T). 1773coalesce_type(QueryId, [A, B|Xs], [S1, S2|Ss], T):- 1774 most_general_type(QueryId, S1, S2, A, B, union, AB), 1775 coalesce_type(QueryId, Xs, Ss, XT), 1776 most_general_type(QueryId, S2, S2, AB, XT, union, T). 1777 1778concatenate_type(QueryId, S1, S2, A, B, C):- 1779 type_merge_hint(C, concatenate), 1780 type_constraint(QueryId, S1, C, A), 1781 type_constraint(QueryId, S2, C, B). 1782 1783 1784merge_types(all, _, Types, Types):- !. 1785merge_types([], [], [], []):- !. 1786merge_types([derived_column(From, Alias)|As], [S|Ss], [B|Bs], [merged(Name, S, B)|Cs]):- 1787 ( Alias == {no_alias}, 1788 From = column(_Qualifier, Name)-> 1789 true 1790 ; otherwise-> 1791 name_from_identifier(Alias, Name) 1792 ), 1793 merge_types(As, Ss, Bs, Cs). 1794 1795 1796determine_tables(_, Var):- var(Var), !, throw(var). 1797determine_tables(_, []):- !. 1798determine_tables(QueryId, [A|B]):- !, 1799 determine_tables(QueryId, A), 1800 determine_tables(QueryId, B). 1801 1802determine_tables(QueryId, join(LHS, RHS)):- !, 1803 determine_tables(QueryId, LHS), 1804 determine_tables(QueryId, RHS). 1805determine_tables(QueryId, correlated_table(table(Identifier), {no_correlation})):- !, 1806 Identifier = identifier(_, TableName), !, 1807 query_table(QueryId, uncorrelated(TableName), Identifier). 1808determine_tables(QueryId, correlated_table(table(Identifier), correlation(Correlation, _))):- !, 1809 query_table(QueryId, Correlation, Identifier). 1810determine_tables(QueryId, qualified_join(_Type, RHS, _On)):- !, 1811 determine_tables(QueryId, RHS). 1812determine_tables(QueryId, cross_join(RHS)):- !, 1813 determine_tables(QueryId, RHS). 1814determine_tables(QueryId, derived_table(_Derivation, correlation(DerivedTableName, _), T)):- !, 1815 derived_table(QueryId, DerivedTableName, T). 1816 1817determine_tables(_, X):- throw(determine_tables(X)). 1818 1819 1820create_derived_table(_QueryId, _DerivedTableName, []):- !. 1821create_derived_table(QueryId, DerivedTableName, [merged(Name, _, Type)|Columns]):- !, 1822 derived_query_column(QueryId, DerivedTableName, Name, Type), 1823 create_derived_table(QueryId, DerivedTableName, Columns). 1824 1825% Explicitly named domains in views MAY have a schema. Just ignore it 1826fetch_domain_data_type(identifier(_, Domain), Type):- 1827 !, 1828 fetch_domain_data_type(Domain, Type). 1829fetch_domain_data_type(Domain, Type):- 1830 domain_database_data_type(Domain, Type). 1831 1832fetch_database_attribute(_, Schema, TableName, _, _, _, _, _):- 1833 \+system_table(TableName, _, _), 1834 \+database_attribute(_, Schema, TableName, _, _, _, _, _), 1835 !, 1836 format(atom(Message), 'View references entity ~w which does not exist', [TableName]), 1837 throw(cql_error(no_such_entity, Message)). 1838 1839fetch_database_attribute(_, Schema, TableName, ColumnName, Domain, _, _, _):- 1840 system_table(TableName, ColumnName, Domain) 1841 ; 1842 database_attribute(_, Schema, TableName, ColumnName, Domain, _, _, _). 1843 1844 1845system_table(sysobjects, name, native_type(nvarchar(128))). 1846system_table(sysobjects, name, native_type(nvarchar(128))). 1847system_table(sysobjects, xtype, native_type(char(2))). 1848system_table(sysobjects, xtype, native_type(nvarchar(128))). 1849system_table(sysobjects, id, native_type(int)). 1850system_table(sysobjects, uid, native_type(smallint)). 1851system_table(sysobjects, parent_obj, native_type(int)). 1852system_table(syscolumns, name, native_type(nvarchar(128))). 1853system_table(syscolumns, colid, native_type(smallint)). 1854system_table(syscolumns, id, native_type(int)). 1855system_table(sysconstraints, colid, native_type(smallint)). 1856system_table(sysconstraints, constid, native_type(int)). 1857system_table(syscomments, id, native_type(int)). 1858system_table(syscomments, text, native_type(nvarchar(4000))). 1859system_table(syscomments, colid, native_type(smallint)). 1860 1861system_table(table_constraints, constraint_catalog, native_type(nvarchar(128))). 1862system_table(table_constraints, constraint_schema, native_type(nvarchar(128))). 1863system_table(table_constraints, constraint_name, native_type(nvarchar(128))). 1864system_table(table_constraints, constraint_type, native_type(varchar(11))). 1865system_table(table_constraints, table_catalog, native_type(nvarchar(128))). 1866system_table(table_constraints, table_schema, native_type(nvarchar(128))). 1867system_table(table_constraints, table_name , native_type(nvarchar(128))). 1868 1869system_table(key_column_usage, constraint_name, native_type(nvarchar(128))). 1870system_table(key_column_usage, table_catalog, native_type(nvarchar(128))). 1871system_table(key_column_usage, table_schema, native_type(nvarchar(128))). 1872system_table(key_column_usage, table_name, native_type(nvarchar(128))). 1873system_table(key_column_usage, column_name, native_type(nvarchar(128))). 1874system_table(key_column_usage, ordinal_position, native_type(int)). 1875 1876system_table(referential_constraints, constraint_name, native_type(nvarchar(128))). 1877system_table(referential_constraints, constraint_catalog, native_type(nvarchar(128))). 1878system_table(referential_constraints, constraint_schema, native_type(nvarchar(128))). 1879system_table(referential_constraints, unique_constraint_name, native_type(nvarchar(128))). 1880system_table(referential_constraints, unique_constraint_catalog, native_type(nvarchar(128))). 1881system_table(referential_constraints, unique_constraint_schema, native_type(nvarchar(128))). 1882 1883 1884left_factor_types(Qid, In, T):- 1885 left_factor2(In, Left), 1886 resolve_factored_types(Qid, Left, T). 1887 1888resolve_factored_types(_Qid, Var, Var):- var(Var), !. 1889resolve_factored_types(Qid, node(A, AS, Op, B, BS), T):- !, 1890 most_general_type(Qid, AS, BS, AT, B, Op, T), 1891 resolve_factored_types(Qid, A, AT). 1892resolve_factored_types(_Qid, T, T). 1893 1894% Turn an LR node/3 tree into an LL node/3 tree 1895left_factor2(A, A):- var(A), !. 1896left_factor2(node(A, AS, Op, B, BS), Y):- !, left_factor_appending2(B, BS, A, AS, Op, Y). 1897left_factor2(A, A). 1898left_factor_appending2(Var, VarS, T, TS, Op, node(T, TS, Op, Var, VarS)):- var(Var), !. 1899left_factor_appending2(node(A, AS, Op1, B, BS), NS, T, TS, Op2, Z):- !, left_factor_appending2(B, BS, node(T, TS, Op2, A, AS), NS, Op1, Z). 1900left_factor_appending2(X, XS, Y, YS, Op, node(Y, YS, Op, X, XS)). 1901 1902 1903 1904strip_sql_comments(Var, Var):- var(Var), !. % Frozen variables 1905strip_sql_comments(meta(_,_):A, B):- !, 1906 strip_sql_comments(A, B). 1907 1908strip_sql_comments(A, B):- 1909 A =.. [Functor|Args], Args \== [], !, 1910 strip_sql_comments_list(Args, Args2), 1911 B =.. [Functor|Args2]. 1912 1913strip_sql_comments(A, A):- !. 1914 1915strip_sql_comments_list([], []):- !. 1916strip_sql_comments_list([A|B], [C|D]):- !, strip_sql_comments(A, C), strip_sql_comments_list(B, D). 1917 1918consolidate_errors(Var):- 1919 var(Var), 1920 throw(instantiation_error(Var)). 1921consolidate_errors(meta(_Comments, ErrorGroup):A):- !, 1922 ( ErrorGroup = {null} -> 1923 true 1924 ; otherwise-> 1925 true %format(user_error, 'Found error: ~w~n', [ErrorGroup]) 1926 ), 1927 consolidate_errors(A). 1928consolidate_errors(A):- 1929 A =.. [_|Args], Args \== [], !, 1930 consolidate_errors_list(Args). 1931consolidate_errors(_):- !. 1932 1933consolidate_errors_list([]):- !. 1934consolidate_errors_list([A|B]):- !, consolidate_errors(A), consolidate_errors_list(B). 1935 1936 1937not_a_builtin_function(DBMS, Function):- 1938 strip_sql_comments(Function, identifier(Schema, Functor)), 1939 \+once(builtin_function(DBMS, Schema, Functor)). 1940 1941builtin_function('Microsoft SQL Server', _, month). 1942builtin_function('Microsoft SQL Server', _, day). 1943builtin_function('Microsoft SQL Server', _, year). 1944builtin_function('Microsoft SQL Server', _, round). 1945builtin_function('Microsoft SQL Server', _, floor). 1946builtin_function('Microsoft SQL Server', _, ceiling). 1947builtin_function('Microsoft SQL Server', _, len). 1948builtin_function('Microsoft SQL Server', _, rtrim). 1949builtin_function('Microsoft SQL Server', _, str). 1950builtin_function('Microsoft SQL Server', _, datename). 1951builtin_function('Microsoft SQL Server', _, getdate). 1952 1953builtin_function('Microsoft SQL Server', _, db_name). 1954builtin_function('Microsoft SQL Server', _, permissions). 1955builtin_function('Microsoft SQL Server', _, user_name). 1956 1957routine_type(Name, Type):- 1958 % Note that this is not the DBMS we are reading IN, but the one we will eventually USE. This is why 1959 % I call default_schema here. 1960 default_schema(Schema), 1961 dbms(Schema, DBMS), 1962 strip_sql_comments(Name, identifier(_, Identifier)), 1963 ( cql_normalize_name(DBMS, Identifier, NormalizedName), 1964 routine_return_type(Schema, NormalizedName, Type), 1965 Type \== void-> 1966 true 1967 ; otherwise-> 1968 format(atom(Message), 'Could not determine the type of SQL function ~w', [Identifier]), 1969 throw(cql_error(cannot_determine_function_type, Message)) 1970 ). 1971 1972merge_sources(S, S, S):- !. 1973merge_sources(A, B, _):- 1974 format(atom(Message), '~w vs ~w', [A, B]), 1975 throw(cql_error(could_not_merge_sources, Message)). 1976 1977type_mismatch(type_mismatch(Link, Type1, Type2), type_mismatch(Link, Type1, Type2), Type1, Type2):- 1978 % Note that just because the two unify does not mean that one of them is not already unified to a DIFFERENT mismatch of Type1 and Type2! 1979 % Therefore we have to do the flag/3 BEFORE the cut. 1980 flag(sql_error, Link, Link+1), 1981 !. 1982 1983% This clause means that we will ignore subsequent errors which occur in the same place and just display the first one 1984type_mismatch(_, _, _, _):- !. 1985 1986%type_mismatch(A, B, _, _):- 1987% throw_exception(could_not_merge_type_errors, '~w vs ~w', [A, B]). 1988 1989 1990semantic_error(Error, Error, Level):- 1991 % Always add the error to the view tree, but only PRINT it if the level is lower than the gripe_level 1992 %sql_gripe_level(L), 1993 %Level =< L, 1994 !, 1995 format_sql_error(Error, _, Message), 1996 sql_gripe(Level, Message, []). 1997 1998semantic_error(_, _, _)