View source with raw comments or as raw
    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:

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

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:

Some examples of case 3:

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

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