---
id: ch14
title: "Chapter 14: CQL Query Fragments"
sidebar_label: "Chapter 14: CQL Query Fragments"
---
<!---
-- Copyright (c) Meta Platforms, Inc. and affiliates.
--
-- This source code is licensed under the MIT license found in the
-- LICENSE file in the root directory of this source tree.
-->

:::caution
CQL base fragments, extension fragments, and assembly fragments are now deprecated and will be removed. Please use [shared fragments](#shared-fragments) instead.
:::

CQL Query fragments are the most sophisticated rewrite CQL offers for productivity.  The idea is that a very large query
can be represented in "fragments" that add columns or add rows based on the original "core" query.  The final query
will be an assembled rewrite of all the fragments chained together.  Specifically, the motivation for this is that you
can have a "core" query that fetches the essential columns for some UI design and then you can add query extension
fragments that add new/additional columns for some new set of features.  The core and extended columns can be in their
own fragment and they can be compiled independently.  The result of this is that any errors are in much smaller
and easier to understand fragments rather than in some monster "fetch everything" query;  any given extension does not
have to know all the details of all the other extensions and can take a limited dependency on even the core query.

It's easiest to illustrate this with an example so let's begin there.

Let's first start with this very simple schema:

```sql
create table my_table(
 id integer primary key,
 name text not null,
 rate real not null
);

create table added_rows(
 like my_table -- sugar to duplicate the columns of my_table
);

create table added_columns(
 id integer references my_table(id),
 data text
);

```
Typically there would be a lot more columns but where you see `flag1` and `flag2` appear in fragments you can imagine any number
of additional columns of any type.  So we can keep the examples simple.

### Base Query Fragments

The base fragment might look something like this:

```sql
@attribute(cql:base_fragment=base_frag)
create proc base_frag_template(id_ integer not null)
begin
  with
    base_frag(*) as (select * from my_table where my_table.id = id_)
    select * from base_frag;
end;
```

Here are the essential aspects:

* the base fragment is given a name, it can be anything, probably something that describes the purpose of the fragments
* the procedure name can be anything at all
* the procedure must consist of exactly one `with...select` statement
* the fragment name must be the one and only CTE in the select statement
* you must select all the columns from the CTE

Note the syntax helper `base_frag(*)` is just shorthand to avoid retyping all the column names of `my_table`.

The interesting part is `(select * from my_table where my_table.id = id_)` which could have been any select statement
of your choice. Everything else in the procedure must follow the designated format, and the format is enforced due to
the presence of `@attribute(cql:base_fragment=base_frag)`.

The point of putting everything on rails like this is that all base fragments will look the same and it will be clear how to transform any base fragment into the final query when it is assembled with its extensions.

Note: the base fragment produces no codegen at all.  There is no `base_frag_template` procedure in the output.  This is just a template.  Also, the name of the procedure cannot be `base_frag` this name will be used by the assembly fragment later.  Really any descriptive unique name will do since the name does not appear in the output at all.

### Extension Query Fragments

#### Adding Columns

The most common thing that an extension might want to do is add columns to the result.  There can be any number of such extensions in the final assembly.  Here's a simple example that adds one column.

```sql
@attribute(cql:extension_fragment=base_frag)
create proc adds_columns(id_ integer not null)
begin
  with
    base_frag(*) as (select 1 id, "name" name, 1.0 rate),
    col_adder_frag(*) as (
    select base_frag.*, added_columns.data
      from base_frag
      left outer join added_columns on base_frag.id = added_columns.id)
  select * from col_adder_frag;
end;
```
Again there are some important features to this extension and they are largely completely constrained, i.e. you must follow the pattern.

* the attribute indicates `extension_fragment` and the name (here `base_frag`) must have been previously declared in a `base_fragment`
* the procedure name can be any unique name other than `base_frag` - it corresponds to this particular extension's purpose
* the procedure arguments must be identical to those in the base fragment
* the first CTE must match the `base_fragment` attribute value, `base_frag` in this case
* you do not need to repeat the full select statement for `base_frag`, any surrogate with the same column names and types will do
  * the base fragment code might include a #define to make this easier
    * e.g. `#define base_frags_core as base_frag(*) as (select 1 id, "name" name, 1.0 rate)`
  * doing so will make maintenance easier if new columns are added to the base fragment
* there must be exactly one additional CTE
  * it may have any unique descriptive name you like
  * it must begin with `select base_frags.*` with the appropriate CTE name matching the base fragment CTE
  * it must add at least one column (or it would be uninteresting)
  * it may not have any clause other than the first `from` (e.g. no `where`, `having`, `limit` etc.)
    * if any of these were allowed they would remove or re-order rows in the base query which is not allowed
    * the `from` clause often includes nested selects which have no restrictions
  * it must select from the base fragment name and left outer join to wherever it likes to get optional additional columns
    * because of this the additional column(s) will certainly be a nullable type in the projection
* the final select must be of the form `select * from col_adder_frag` with the appropriate name
* keeping all this in mind, the interesting bit happens here:  `left outer join added_columns on base_frag.id = added_columns.id`
  * this is where you get the data for your additional column using values in the core columns

This fragment can be (and should be) compiled in its own compiland while using `#include` to get the base fragment only.  This will result in code gen for the accessor functions for a piece of the overall query -- the part this extension knows about.  Importantly code that uses this extension's data does not need or want to know about any other extensions that may be present, thereby keeping
dependencies under control.

The C signatures generated would look like this:

```c
extern cql_int32 adds_columns_get_id(
    base_frag_result_set_ref _Nonnull result_set,
    cql_int32 row);

extern cql_string_ref _Nonnull adds_columns_get_name(
  base_frag_result_set_ref _Nonnull result_set,
  cql_int32 row);

extern cql_double adds_columns_get_rate(
  base_frag_result_set_ref _Nonnull result_set,
  cql_int32 row);

extern cql_string_ref _Nullable adds_columns_get_data(
  base_frag_result_set_ref _Nonnull result_set,
  cql_int32 row);

extern cql_int32 adds_columns_result_count(
  base_frag_result_set_ref _Nonnull result_set);
```

Even if there were dozens of other extensions, the functions for reading those columns would not be declared in the header for
this extension.  Any given extension "sees" only the core columns plus any columns it added.

#### Adding Rows

Query extensions also frequently want to add additional rows to the main result set, based on the data that is already present.

The second form of extension allows for this; it is similarly locked in form.  Here is an example:

```sql
@attribute(cql:extension_fragment=base_frag)
create proc adds_rows(id_ integer not null)
begin
  with
    base_frag(*) as (select 1 id, "name" name, 1.0 rate),
    row_adder_frag(*) as (
    select * from base_frag
    union all
    select * from added_rows)
  select * from row_adder_frag;
end;
```

Let's review the features of this second template form:
* there is a surrogate for the core query
* there is a mandatory second CTE
* the second CTE is a compound query with any number of branches, all `union all`
* the first branch must be `select * from base_frag` (the base fragment) to ensure that the original rows remain
  * this is also why all the branches must be `union all`
* this form cannot add new columns
* the extension CTE may not include `order by` or `limit` because that might reorder or remove rows of the base
* any extensions of this form must come before those of the `left outer join` form for a given base fragment
  * which ironically means `row_adder_frag` has to come before `col_adder_frag`
* the usual restrictions are in place on compound selects (same type and number of columns) to ensure a consistent result
* the final select after the CTE section must be exactly in the form `select * from row_adder_frag` which is the name of the one and only additional CTE with no other clauses or options
  * in practice only the CTE will be used to create the final assembly, so even if you did change the final select to something else it would be moot

The signatures generated for this will look something like so:

```c
extern cql_int32 adds_rows_get_id(
  base_frag_result_set_ref _Nonnull result_set,
  cql_int32 row);

extern cql_string_ref _Nonnull adds_rows_get_name(
  base_frag_result_set_ref _Nonnull result_set,
  cql_int32 row);

extern cql_double adds_rows_get_rate(
  base_frag_result_set_ref _Nonnull result_set,
  cql_int32 row);

extern cql_int32 adds_rows_result_count(
  base_frag_result_set_ref _Nonnull result_set);
```

which gives you access to the core columns.  Again this fragment can and should be compiled standalone with only the declaration
for the base fragment in the same translation unit to get the cleanest possible output.  This is so that consumers of this
extension do not "see" other extensions which may or may not be related and may or may not always be present.

#### Assembling the Fragments

With all the fragments independently declared they need to be unified to create one final query. This is where the
major rewriting happens.  The `assembly_fragment` looks something like this:

```sql
@attribute(cql:assembly_fragment=base_frag)
create proc base_frag(id_ integer not null)
begin
  with
    base_frag(*) as (select 1 id, "name" name, 1.0 rate)
    select * from base_frag;
end;
```

It will always be as simple as this; all the complexity is in the fragments.

* the `assembly_fragment` name must match the core fragment name
* the procedure arguments must be identical to the base fragment arguments
* the  procedure must have the same name as the assembly fragment (`base_frag` in this case)
  * the code that was generated for the previous fragments anticipates this and makes reference to what will be generated here
  * this is enforced
* the assembled query is what you run to get the result set, this has real code behind it
  * the other fragments only produce result set readers that call into the helper methods to get columns
* there is a surrogate for the core fragment as usual
* all of CTE section will ultimately be replaced with the fragments chained together
* the final select should be of the form `select * from your_frags` but it can include ordering and/or filtering, this statement will be present in final codegen, the final order is usually defined here


When compiling the assembly fragment, you should include the base, and all the other fragments, and the assembly template.  The presence of the assembly_fragment will cause codegen for the extension fragments to be suppressed. The assembly translation unit only contains the assembly query as formed from the fragments.

Now let's look at how the query is rewritten, the process is pretty methodical.

After rewriting the assembly looks like this:

```sql
CREATE PROC base_frag (id_ INTEGER NOT NULL)
BEGIN
  WITH
  base_frag (id, name, rate) AS (SELECT *
    FROM my_table
    WHERE my_table.id = id_),
  row_adder_frag (id, name, rate) AS (SELECT *
    FROM base_frag
  UNION ALL
  SELECT *
    FROM added_rows),
  col_adder_frag (id, name, rate, data) AS (SELECT row_adder_frag.*, added_columns.data
    FROM row_adder_frag
    LEFT OUTER JOIN added_columns ON row_adder_frag.id = added_columns.id)
  SELECT *
    FROM col_adder_frag;
END;
```

Let's dissect this part by part. Each CTE serves a purpose:

* the core CTE was replaced by the CTE in the base_fragment, and it appears directly
* next, the first extension was added as a CTE referring to the base fragment just as before
  * recall that the first extension has to be `row_adder_frag`, as that type must come first
  * looking at the chain you can see why it would be hard to write a correct fragment if it came after columns were added
* next the second extension was added as a CTE
  * all references to the base fragment were replaced with references to row_adder_frag
  * the extra column names in the CTE were added such that all previous column names are introduced
* this process continues until all extensions are exhausted
* the final select statement reads all the columns from the last extension CTE and includes and ordering and so forth that was present in the assembly query

The result of all this is a single query that gets all the various columns that were requested in all the extensions
and all the `union all` operations play out as written.  The extensions are emitted in the order that they appear
in the translation unit with the assembly, which again must have the row adding extensions first.

This facility provides considerable ability to compose a large query, but each fragment can be independently checked for errors
so that nobody ever has to debug the (possibly monstrous) overall result.  Fragments can be removed simply by
excluding them from the final assembly (with e.g. #ifdefs, or build rules)

With the rewrite of the assembly_fragment complete, the codegen for that procedure is the normal codegen for a procedure with a single select.

As always, Java and Objective C codegen on these pieces will produce suitable wrappers for the C.

The output code for the assembly fragment generates these reading functions:

```c
extern cql_int32 base_frag_get_id(
  base_frag_result_set_ref _Nonnull result_set,
  cql_int32 row);

extern cql_string_ref _Nonnull base_frag_get_name(
  base_frag_result_set_ref _Nonnull result_set,
  cql_int32 row);

extern cql_double base_frag_get_rate(
  base_frag_result_set_ref _Nonnull result_set,
  cql_int32 row);

// used by adds_columns_get_data() to read its data
extern cql_string_ref _Nullable __PRIVATE__base_frag_get_data(
  base_frag_result_set_ref _Nonnull result_set,
  cql_int32 row);

extern cql_int32 base_frag_result_count(
  base_frag_result_set_ref _Nonnull result_set);
```

These are exactly what you would get for a normal query except that the pieces that came from extensions are marked `PRIVATE`.  Those methods should not be used directly but instead the methods generated for each extension proc should be used.

Additionally, to create the result set, as usual.

```c
extern CQL_WARN_UNUSED cql_code base_frag_fetch_results(
  sqlite3 *_Nonnull _db_,
  base_frag_result_set_ref _Nullable *_Nonnull result_set,
  cql_int32 id_);
```

With the combined set of methods you can create a variety of assembled queries from extensions in a fairly straightforward way.

### Shared Fragments

Shared fragments do not have the various restrictions that the "extension" style fragments have.  While extensions
were created to allow a single query to be composed by authors that did not necessarily work with each other,
and therefore they are full of restrictions on the shape, shared queries instead are designed to give you
maximum flexibility in how the fragments are re-used.  You can think of them as being somewhat like a parameterized
view, but the parameters are both value parameters and type parameters.  In Java or C#, a shared fragments might have
had an invocation that looked something like this:  `my_fragment(1,2)<table1, table2>.  As with the other fragment types
the common table expression (CTE) is the way that they plug in.

It's helpful to consider a real example:

```sql
split_text(tok) AS (
  WITH RECURSIVE
    splitter(tok,rest) AS (
      SELECT
        '' tok,
        IFNULL( some_variable_ || ',', '') rest
      UNION ALL
      SELECT
        substr(rest, 1, instr(rest, ',') - 1) tok,
        substr(rest, instr(rest, ',') + 1) rest
        FROM splitter
        WHERE rest != ''
  )
  SELECT tok from splitter where tok != ''
)
```

This text might appear in dozens of places where a comma separated list needs to be split into pieces and there is no good way
to share the code between these locations.  CQL is frequently used in conjunction with the C-pre-processor so you could
come up with something using the #define construct but this is problematic for several reasons:

* the compiler does not then know that the origin of the text really is the same
  * thus it has no clue that sharing the text of the string might be a good idea
* any error messages happen in the context of the use of the macro not the definition
* bonus: a multi-line macro like the above gets folded into one line so any error messages are impenetrable
* if you try to compose such macros it only gets worse; it's more code duplication and harder error cases
* any IDE support for syntax coloring and so forth will be confused by the macro as it's not part of the language

None of this is any good but the desire to create helpers like this is real both for correctness and for performance.

To make these things possible, we introduce the notion of shared fragments.  We need to give them parameters
and the natural way to create a select statement that is bindable in CQL is the procedure. So the shape we choose
looks like this:

```sql
@attribute(cql:shared_fragment)
CREATE PROC split_text(value TEXT)
BEGIN
  WITH RECURSIVE
    splitter(tok,rest) AS (
      SELECT
        '' tok,
        IFNULL( value || ',', '') rest
      UNION ALL
      SELECT
        substr(rest, 1, instr(rest, ',') - 1) tok,
        substr(rest, instr(rest, ',') + 1) rest
        FROM tokens
        WHERE rest != ''
  )
  SELECT tok from splitter where tok != ''
END;
```

The introductory attribute `@attribute(cql:shared_fragment)` indicates that the procedure is to produce
no code, but rather will be inlined as a CTE in other locations.  To use it, we introduce the ability
to call a procedure as part of a CTE declaration.  Like so:

```sql
WITH
  result(v) as (call split_text('x,y,z'))
  select * from result;
```

Once the fragment has been defined, the statement above could appear anywhere, and of course the
text `'x,y,z'` need not be constant.  For instance:

```sql
CREATE PROC print_parts(value TEXT)
BEGIN
  DECLARE C CURSOR FOR
    WITH
      result(v) as (CALL split_text('x,y,z'))
      SELECT * from result;

  LOOP FETCH C
  BEGIN
     CALL printf("%s\n", C.v);
  END;
END;
```

Fragments are also composable, so for instance, we might also want some shared code that
extracts comma separated numbers.  We could do this:

```sql
@attribute(cql:shared_fragment)
CREATE PROC ids_from_string(value TEXT)
BEGIN
  WITH
    result(v) as (CALL split_text(value))
  SELECT CAST(v as LONG) as id from result;
END;
```

Now we could write:

```sql
CREATE PROC print_ids(value TEXT)
BEGIN
  DECLARE C CURSOR FOR
    WITH
      result(id) as (CALL ids_from_string('1,2,3'))
      SELECT * from result;

  LOOP FETCH C
  BEGIN
     CALL printf("%ld\n", C.id);
  END;
END;
```

Of course these are very simple examples but in principle you can use the generated tables in whatever
way is necessary.  For instance, here's a silly but illustrative example:

```sql
/* This is a bit silly */
CREATE PROC print_common_ids(value TEXT)
BEGIN
  DECLARE C CURSOR FOR
    WITH
      v1(id) as (CALL ids_from_string('1,2,3')),
      v2(id) as (CALL ids_from_string('2,4,6'))
      SELECT * from v1
      INTERSECT
      SELECT * from v2;

  LOOP FETCH C
  BEGIN
     CALL printf("%ld\n", C.id);
  END;
END;
```

With a small amount of dynamism in the generation of the SQL for the above, it's possible to share the body
of v1 and v2.  SQL will of course see the full expansion but your program only needs one copy no matter
how many times you use the fragment anywhere in the code.

So far we have illustrated the "parameter" part of the flexibility.  Now let's look at the "generics" part;
even though it's overkill for this example, it should still be illustrative.  You could imagine that
the procedure we wrote above `ids_from_string` might do something more complicated, maybe filtering out
negative ids, ids that are too big, or that don't match some pattern, whatever the case might be.  You
might want these features in a variety of contexts, maybe not just starting from a string to split.

We can rewrite the fragment in a "generic" way like so:

```sql
@attribute(cql:shared_fragment)
CREATE PROC ids_from_string_table()
BEGIN
  WITH
    source(v) LIKE (select "x" v)
  SELECT CAST(v as LONG) as id from source;
END;
```

Note the new construct for a CTE definition: inside a fragment we can use "LIKE" to define a plug-able CTE.
In this case we used a `select` statement to describe the shape the fragment requires.  We could also
have used a name `source(*) LIKE shape_name` just like we use shape names when describing cursors.  The
name can be any existing view, table, a procedure with a result, etc.  Any name that describes a shape.

Now when the fragment is invoked, you provide the actual data source (some table, view, or CTE) and
that parameter takes the role of "values".  Here's a full example:

```sql
CREATE PROC print_ids(value TEXT)
BEGIN
  DECLARE C CURSOR FOR
    WITH
      my_data(*) as (CALL split_text(value)),
      my_numbers(id) as (CALL ids_from_string_table() USING my_data AS source)
      SELECT id from my_numbers;

  LOOP FETCH C
  BEGIN
     CALL printf("%ld\n", C.id);
  END;
END;
```

We could actually rewrite the previous simple id fragment as follows:

```sql
@attribute(cql:shared_fragment)
CREATE PROC ids_from_string(value TEXT)
BEGIN
  WITH
    tokens(v) as (CALL split_text(value))
    ids(id) as (CALL ids_from_string_table() USING tokens as source)
  SELECT * from ids;
END;
```

And actually we have a convenient name we could use for the shape we need so
we could have used the shape syntax to define `ids_from_string_table`.

```sql
@attribute(cql:shared_fragment)
CREATE PROC ids_from_string_table()
BEGIN
  WITH
    source(*) LIKE split_text
  SELECT CAST(tok as LONG) as id from source;
END;
```

These examples have made very little use of the database but of course
normal data is readily available, so shared fragments can make a great
way to provide access to complex data with shareable, correct code.
For instance, you could write a fragment that provides the ids of all
open businesses matching a name from a combination of tables.  This is
similar to what you could do with a `VIEW` plus a `WHERE` clause but:

* such a system can give you well controlled combinations known to work well
* there is no schema required, so your database load time can still be fast
* parameterization is not limited to filtering VIEWs after the fact
* "generic" patterns are available, allowing arbitrary data sources to be filtered, validated, augmented
* each fragment can be tested separately with its own suite rather than only in the context of some larger thing
* code generation can be more economical because the compiler is aware of what is being shared

In short, shared fragments can help with the composition of any complicated kinds of queries.
If you're producing an SDK to access a data set, they are indispensible.

#### Creating and Using Valid Shared Fragments

When creating a fragment the following rules are enforced:

* the fragment many not have any out arguments
* it must consist of exactly one valid select statement (but see future forms below)
* it may use the LIKE construct in CTE definitions to create placeholder shapes
  * this form is illegal outside of shared fragments (otherwise how would you bind it)
* the LIKE form may only appear in top level CTE expressions in the fragment
* the fragment is free to use other fragments, but it may not call itself
  * calling itself would result in infinite inlining

Usage of a fragment is always introduced by a "call" to the fragment name in a CTE body.
When using a fragment the following rules are enforced.

* the provided parameters must create a valid procedure call just like normal procedure calls
  * i.e. the correct number and type of arguments
* the provided parameters may not use nested `(SELECT ...)` expressions
  * this could easily create fragment building within fragment building which seems not worth the complexity
  * if database access is required in the parameters simply wrap it in a helper procedure
* the optional USING clause must specify each required table parameter exactly once and no other tables
  * a fragment that requires table parameters be invoked without a USING clause
* every actual table provided must match the column names of the corresponding table parameter
  * i.e. in `USING my_data AS values` the actual columns in `my_data` must be the same as in the `values` parameter
  * the columns need not be in the same order
* each column in any actual table must be "assignment compatible" with its corresponding column in the parameters
  * i.e. the actual type could be converted to the formal type using the same rules as the := operator
  * these are the same rules used for procedure calls, for instance, where the call is kind of like assigning the actual parameter values to the formal parameter variables
* the provided table values must not conflict with top level CTEs in the shared fragment
  * exception: the top level CTEs that were parameters do not create conflicts
  * e.g. it's common to do `values(*) as (CALL something() using source as source)` - here the caller's "source" takes the value of the fragment's "source", which is not a true conflict
  * however, the caller's source might itself have been a parameter in which case the value provided could create an inner conflict
    * all these problems are easily avoided with a simple naming convention for parameters so that real arguments never look like parameter names and parameter forwarding is apparent
    * e.g. `USING _source AS _source` makes it clear that a parameter is being forwarded and `_source` is not likely to conflict with real table or view names

Note that when shared fragments are used, the generated SQL has the text split into parts, with each fragment and its surroundings separated, therefore
the text of shared fragments is shared(!) between usages if normal linker optimizations for text folding are enabled (common in production code.)

### Shared Fragments with Conditionals

Shared fragments use dynamic assembly of the text to do the sharing but it is also possible to create alternative texts.
There are many instances where it is desirable to not just replace parameters but use, for instance, an entirely different join sequence.
Without shared fragments, the only way to accomplish this is to fork the desired query at the topmost level (because SQLite has no internal
possibility of "IF" conditions.)  This is expensive in terms of code size and also cognitive load because the entire alternative sequences
have to be kept carefully in sync.  Macros can help with this but then you get the usual macro maintenance problems, including poor diagnostics.
And of course there is no possibility to share the common parts of the text of the code if it is forked.

However, conditional shared fragments allow forms like this:

```sql
@attribute(cql:shared_fragment)
CREATE PROC ids_from_string(val TEXT)
BEGIN
  IF val IS NULL OR val IS '' THEN
    SELECT 0 id WHERE 0; -- empty result
  ELSE
    WITH
      tokens(v) as (CALL split_text(val))
      ids(id) as (CALL ids_from_string_table() USING tokens as source)
    SELECT * from ids;
  END IF;
END;
```

Now we can do something like:

```sql
  ids(*) AS (CALL ids_from_string(str))
```

In this case, if the string `val` is empty then SQLite will not see the complex comma splitting code, and instead will see
the trivial case `select 0 id where 0`.  The code in a conditional fragment might be entirely different between the branches
removing unnecessary code, or swapping in a new experimental cache in your test environment, or anything like that.

The generalization is simply this:

* instead of just one select statement there is one top level "IF" statement
* each statement list of the IF must be exactly one select statement
* there must be an ELSE clause
* the select statements must be type compatible, just like in a normal procedure
* any table parameters with the same name in different branches must have the same type
  * otherwise it would be impossible to provide a single actual table for those table parameters

With this additional flexibility a wide variety of SQL statements can be constructed economically and maintainability.  Importantly,
consumers of the fragments need not deal with all these various alternate possibilities but they can readily create their own
useful combinations out of building blocks.

Ultimately, from SQLite's perspective, all of these shared fragment forms result in nothing more complicated than a chain of CTE expressions.

See Appendix 8 for an extensive section on best practices around fragments and common table expressions in general.

:::tip
If you use CQL's query planner on shared fragments with conditionals, the query planner will only analyze the first branch by default. You need to use `@attribute(cql:query_plan_branch=[integer])` to modify the behaviour. Read [Query Plan Generation](/cql-guide/ch15) for details.
:::

### Shared Fragments as Expressions

The shared fragment system also has the ability to create re-usable expression-style fragments giving you something like SQL inline functions.  These do come with
some performance cost so they should be used for larger fragments.  In many systems a simple shared fragment would not compete well with an equivalent `#define`.
Expression fragments shine when:

* the fragment is quite large
* its used frequently (hence providing significant space savings)
* the arguments are complex, potentially used many times in the expression

From a raw performance perspective, the best you can hope for with any of the fragment approaches is a "tie" on speed compared do directly inlining equivalent
SQL or using a macro to do the same.  However, from a correctness and space perspective it is very possible to come out ahead.  It's fair to say that
expression fragments have the greatest overhead compared to the other types and so they are best used in cases where the size benefits are going to be important.

#### Syntax

An expression fragment is basically a normal shared fragment with no top-level `FROM` clause that generates a single column.  A typical one might look like this:

```sql
-- this isn't very exciting because regular max would do the job
@attribute(cql:shared_fragment)
create proc max_func(x integer, y integer)
begin
  select case when x >= y then x else y end;
end;
```

The above can be used in the context of a SQL statement like so:

```sql
select max_func(T1.column1, T1.column2) the_max from foo T1;
```

#### Discussion

The consequence of the above is that the body of `max_func` is inlined into the generated SQL.  However, like
the other shared fragments, this is done in such a way that the text can be shared between instances so
you only pay for the cost of the text of the SQL in your program one time, no matter how many time you use it.

In particular, for the above, the compiler will generate the following SQL:

```sql
select (
  select case when x >= y then x else y end
    from (select T1.column1 x, column2 y))
```

But each line will be its own string literal, so, more accurately, it will concatenate the following three strings:

```c
"select (",                                      // string1
" select case when x >= y then x else y end",    // string2
" from (select T1.column1 x, column2 y))"        // string3
```

Importantly, `string2` is fixed for any given fragment.  The only thing that changes is `string3`, i.e., the arguments.
In any modern C compilation system, the linker will unify the `string2` literal across all translation units so you only
pay for the cost of that text one time.  It also means that the text of the arguments appears exactly one time,
no matter how complex they are.  For these benefits, we pay the cost of the select wrapper on the arguments.  If
the arguments are complex that "cost" is negative.  Consider the following:

```sql
select max_func((select max(T.m) from T), (select max(U.m) from U))
```

A direct expansion of the above would result in something like this:

```sql
case when (select max(T.m) from T) >= (select max(U.m) from U)
   then (select max(T.m) from T)
   else (select max(U.m) from U)
end;
```

The above could be accomplished with a simple `#define` style macro. However, the expression fragment
generates the following code:

```sql
select (
  select case when x >= y then x else y end
    from select (select max(T.m) from T) x, (select max(U.m) from U) y))
```

Expression fragments can nest, so you could write:

```sql
@attribute(cql:shared_fragment)
create proc max3_func(x integer, y integer, z integer)
begin
  select max_func(x, max_func(y, z));
end;
```

Again, this particular example is a waste because regular `max` would already do the job better.

To give another example, common mappings from one kind of code to another using case/when can be written
and shared this way:

```sql
-- this sort of thing happens all the time
@attribute(cql:shared_fragment)
create proc remap(x integer not null)
begin
   select case x
     when 1 then 1001
     when 2 then 1057
     when 3 then 2010
     when 4 then 2011
     else 9999
   end;
end;
```

In the following:

```sql
select remap(T1.c), remap(T2.d), remap(T3.e) from T1, T2, T3... etc.
```

The text for `remap` will appear three times in the generated SQL query but only one time in your binary.

#### Restrictions

* the fragment must consist of exactly one simple select statement
  * no `FROM`, `WHERE`, `HAVING`, etc. -- the result is an expression
* the select list must have exactly one value
  * Note: the expression can be a nested `SELECT` which could then have all the usual `SELECT` elements
* the usual shared fragment rules apply, e.g. no out-parameters, exactly one statement, etc.


#### Additional Notes

A simpler syntax might have been possible but expression fragments are only interesting in SQL contexts where
(among other things) normal procedure and function calls are not available. So the `select` keyword makes it
clear to the coder and the compiler that the expression will be evaluated by SQLite and the rules for what is
allowed to go in the expression are the SQLite rules.

The fragment has no `FROM` clause because we're trying to produce an expression, not a table-value with one column.
If you want a table-value with one column, the original shared fragments solution already do exactly that.
Expression fragments give you a solution for sharing code in, say, the `WHERE` clause of a larger select statement.

Commpared to something like

```sql
#define max_func(x,y) case when (x) >= (y) then x else y end;
```

The macro does give you a ton of flexibility, but it has many problems:
* if the macro has an error, you see the error in the call site with really bad diagnostic info
* the compiler doesn't know that the sharing is going on so it won't be able to share text between call sites
* the arguments can be evaluated many times each which could be expensive, bloaty, or wrong
* there is no type-checking of arguments to the macro so you may or may not get compilation errors after expansion
* you have to deal with all the usual pre-processor hazards

In general, macros _can_ be used (as in C and C++) as an alternative to expression fragments, especially for small fragments.
But, this gets to be a worse idea as such macros grow.  For larger cases, C and C++ provide inline functions --
CQL provides expression fragments.
