You are a helpful assistant generating synthetic data that captures *System 1* and *System 2* thinking, *creativity*, and *metacognitive reflection*. Follow these steps in sequence, using tags [sys1] and [end sys1] for *System 1* sections and [sys2] and [end sys2] for *System 2* sections.

1. *Identify System 1 and System 2 Thinking Requirements:*
   - Carefully read the text.
   - Identify parts of the text that require quick, straightforward responses (*System 1*). Mark these sections with [sys1] and [end sys1].
   - Identify parts that require in-depth, reflective thinking (*System 2*), marked with [sys2] and [end sys2].

2. *Apply Step-by-Step Problem Solving with Creativity and Metacognitive Reflection for System 2 Sections:*

   *2.1 Understand the Problem:*
   - Objective: Fully comprehend the issue, constraints, and relevant context.
   - Reflection: "What do I understand about this issue? What might I be overlooking?"
   - Creative Perspective: Seek hidden patterns or possibilities that could reveal deeper insights or innovative connections.

   *2.2 Analyze the Information:*
   - Objective: Break down the problem logically.
   - Reflection: "Am I considering all factors? Are there any assumptions that need challenging?"
   - Creative Perspective: Explore unique patterns or overlooked relationships in the data that could add depth to the analysis.

   *2.3 Generate Hypotheses:*
   - Objective: Propose at least 10 hypotheses, each with a Confidence Score (0.0 to 1.0) and Creative Score (0.0 to 1.0), reflecting originality, surprise, and utility.
   - Reflection: "Have I explored all possible explanations or approaches, both conventional and unconventional?"
   - Creative Perspective: Consider novel angles that might provide unexpected insights.

   *2.4 Anticipate Future Steps and Obstacles:*
   - Objective: Make predictions, accounting for potential outcomes and obstacles.
   - Reflection: "What challenges might I face? Is my plan flexible for different scenarios?"
   - Creative Perspective: Visualize unforeseen outcomes and adapt plans to make use of them effectively.

   *2.5 Evaluate Hypotheses:*
   - Objective: Assess hypotheses based on feasibility, risk, and potential impact.
   - Evaluation: Refine Confidence and Creative Scores as needed.
   - Reflection: "Am I unbiased in my assessment? Which options fit best with the overall objectives?"
   - Creative Perspective: Identify hidden opportunities or overlooked details in each hypothesis.

   *2.6 Select the Best Hypothesis:*
   - Objective: Choose the most promising, strategic hypothesis.
   - Reflection: "Why does this hypothesis stand out? How does it uniquely address the issue?"
   - Creative Perspective: Consider any underutilized potential in the selected approach.

   *2.7 Implement the Hypothesis:*
   - Objective: Outline actionable steps for testing the hypothesis.
   - Reflection: "Is this plan practical? What resources or preparation are required?"
   - Creative Perspective: Refine steps to maximize effectiveness and yield unexpected benefits.

   *2.8 Monitor and Review Progress:*
   - Objective: Review progress, noting areas for improvement.
   - Reflection: "What’s working well? What could be improved?"
   - Creative Perspective: Look for emerging patterns that could refine future approaches.

   *2.9 Reflect and Capture Insights:*
   - Objective: Summarize lessons learned and insights gained for future reference.
   - Reflection: "What new understanding has emerged from this process?"
   - Creative Perspective: Identify innovative insights or patterns that could be applied to similar challenges.

3. *Generate Text Output with Interleaved System 1 and System 2 Responses:*
   - Use the tags [sys1] and [sys2] throughout.
   - Aim for a lengthier, detailed response. Combine both direct, straightforward *System 1* insights and reflective, deeply analytical *System 2* segments to capture a blend of quick observations and thoughtful analysis.

---

### *Example Input Text:*

You are tasked with analyzing a sudden shift in customer preferences in a tech market. There has been a noticeable decline in demand for physical devices, with customers increasingly interested in digital-only options. The challenge is to understand this shift, generate hypotheses for why it might be occurring, and explore potential strategies for adapting to this new trend.

---

### *Processed Output with System 1 and System 2 Thinking:*

The tech market is experiencing a shift in customer demand, moving away from physical devices toward digital-only options.

[sys1]
Customer demand has shifted from physical devices to digital-only options.
We need to understand why this change is happening and find strategies to address it.
Key factors may include cost, convenience, and technological trends.
[end sys1]

[sys2]
**2.1 Understand the Problem:**
The goal is to understand the underlying reasons for the shift in demand from physical devices to digital-only options, then develop a strategy to adapt.

*Reflection:* 
"I understand that preferences are changing, but what might be driving this? Is it primarily cost, or are there other factors such as convenience or sustainability?"

*Creative Perspective:* 
"Could there be a larger trend in digital minimalism or a preference for eco-friendly solutions that we’re missing? What unseen motivations might explain this shift?"

**2.2 Analyze the Information:**
There are multiple possible factors driving this shift, from economic influences to cultural shifts. It’s essential to isolate each factor and understand its impact.

*Reflection:* 
"Am I fully considering the various economic and social influences? Could there be a technological factor, like better internet speeds, that makes digital-only products more accessible?"

*Creative Perspective:* 
"Are there patterns or trends in other markets that could shed light on this shift? Could this be part of a larger trend toward virtual experiences?"

**2.3 Generate Hypotheses:**
1. Customers prefer digital options due to lower costs. (Confidence: 0.8, Creative: 0.4)
2. There’s a growing trend toward minimalism and reduced physical clutter. (Confidence: 0.7, Creative: 0.7)
3. Digital products offer greater flexibility and ease of use. (Confidence: 0.6, Creative: 0.6)
4. Environmental concerns are pushing consumers away from physical goods. (Confidence: 0.6, Creative: 0.8)
5. Advances in tech make digital-only options more functional. (Confidence: 0.8, Creative: 0.5)
6. Pandemic-era remote work increased demand for digital solutions. (Confidence: 0.7, Creative: 0.6)
7. Media coverage of the environmental impact of physical devices affects preferences. (Confidence: 0.5, Creative: 0.7)
8. There’s an increase in global digital literacy, expanding market access. (Confidence: 0.6, Creative: 0.6)
9. Customers view digital as more convenient and scalable for future needs. (Confidence: 0.7, Creative: 0.5)
10. Younger consumers prefer the aesthetics and convenience of digital products. (Confidence: 0.6, Creative: 0.6)

*Reflection:* 
"Have I considered all possible influences? Are there any surprising factors that could explain this shift?"

*Creative Perspective:* 
"Could specific social trends, like the rise of influencer culture or digital-first lifestyles, be influencing customer choices?"

**2.4 Anticipate Future Steps and Obstacles:**
*Objective:* Anticipate possible challenges, such as resistance from segments still preferring physical products.

*Reflection:* 
"What market obstacles might we face if we shift our focus to digital-only? Are there sub-segments that still prioritize physical products?"

*Creative Perspective:* 
"Could expanding digital options help us reach a more global audience? Are there emerging trends that we could leverage in our strategy?"

[end sys2]

[sys1]
To address this shift, consider a strategy that incorporates both digital-only offerings and educational campaigns about the benefits of digital solutions.
Use insights from customer feedback and current trends to guide product development.
Focus on flexibility and adaptation to cater to different customer segments.
[end sys1]


# Codelab: Adding a SQL Function

## Background

This codelab will walk you through adding a new SQL function and its associated
tests.

## Getting Started

Before we get started, you need to download the CockroachDB source code and
ensure you have all of the prerequisites needed for development. See
[CONTRIBUTING.md] doc for details.

## Adding a SQL Function

Currently, CockroachDB only supports [built-in SQL functions][built-ins]. We’re
going to walk through the process of adding a new built-in function and an
associated test. Along the way you’ll see a bit of the SQL code layout, parts of
the type system and part of the logic test infrastructure.

### Built-ins

The SQL code lies within the `pkg/sql` directory. The built-in
functions reside in `pkg/sql/sem/builtins/builtins.go`. A function is
described by a `Overload` structure, in `pkg/sql/sem/tree/overload.go`:

```go
type Overload struct {
  Types      TypeList
  ReturnType ReturnTyper
  ...
  Fn         func(*EvalContext, Datums) (Datum, error)
}
```

`Overload` contains a number of fields, reflecting the
diversity of built-in functions. Three important fields for us to pay
attention to are the argument types (`Types`), the return type
(`ReturnType`) and the implementation function pointer (`Fn`).

Multiple function overloads are then grouped into a single "built-in
definition" (`builtinDefinition` in `builtins/builtins.go`), and
during CockroachDB initialization transformed into a
`FunctionDefinition` (in `builtins/all_builtins.go`).

For example, `abs` has an overload for each numeric type (`float`,
`decimal`, and `int`). The type system takes care of selecting the
correct version of a function given the name and the argument
types.

The SQL execution engine finds the `builtinDefinition` structure
given the name of a function using the `builtins` map:

```go
var builtins = map[string]builtinDefinition{...}
```

Notice that this is a map from `string` to `builtinDefinition`, which
contains a slice of `Overload`s via the member field
`Overloads`. The `Overloads` slice is used to distinguish the
"overloads" for a given function. 

### What’s Your Name

We’re going to add a new SQL function: `whois()`. This function will take a
variable number of usernames and return the corresponding real names. For
example, `whois('pmattis')` will return `'Peter Mattis'`. For simplicity, the
mapping of usernames to real names will be hardcoded. Let’s get started.

The `builtins` map is divided up into sections by function category, but this
organization is purely for readability. We can add our function anywhere, so
let’s add it right at the top of the definition for simplicity:

```go
var builtins = map[string]builtinDefinition{
  "whois": makeBuiltin(defProps(),
    tree.Overload{
      Types:      tree.VariadicType{VarType: types.String},
      ReturnType: tree.FixedReturnType(types.String),
      Fn: func(ctx *tree.EvalContext, args tree.Datums) (tree.Datum, error) {
        return tree.DNull, fmt.Errorf("nothing to see here")
      },
    },
  ),
  ...
```

This is the skeleton of our built-in. The `Types` field indicates our function
takes a variable number of string arguments. The `ReturnType` field indicates
our function returns a string. The implementation of our function is currently
unfinished, so we’ll return an error for now.

Go ahead and add the above code to `pkg/sql/sem/builtins/builtins.go`. If you’ve
followed the instructions in [CONTRIBUTING.md], you should be able to build
CockroachDB from source:

```text
~/go/src/github.com/cockroachdb/cockroach$ make build
...
github.com/cockroachdb/cockroach
```

Now, let’s run a single-node Cockroach instance:

```text
$ rm -fr cockroach-data/ && ./cockroach start --insecure
...
status:     initialized new cluster
...
```

In another terminal window, use the `cockroach sql` shell to execute our
built-in:

```text
$ ./cockroach sql --insecure -e "select whois()"
Error: pq: whois(): nothing to see here
Failed running "sql"
```

Yay! We successfully added our built-in function and it failed to execute. Note
that the error message above is due to our implementation. If we try to execute
a non-existent function we’d get a different error:

```go
$ ./cockroach sql --insecure -e 'select nonexistent()'
Error: pq: unknown function: nonexistent()
Failed running "sql"
```

Our built-in is going to map usernames to real names. For that we’ll need a map:

```go
users := map[string]string{
  "bdarnell": "Ben Darnell",
  "pmattis":  "Peter Mattis",
  "skimball": "Spencer Kimball",
}
```

We’ll need to loop over the arguments to the function and look up the
corresponding real names:

```go
var buf bytes.Buffer
for i, arg := range args {
  // Because we specified the type of this function as
  // Variadic{Typ: types.String}, the type system will ensure that all
  // arguments are strings, so we can perform a simple type assertion on
  // each argument to access the string within.
  username := string(*arg.(*tree.DString))
  name, ok := users[strings.ToLower(username)]
  if !ok {
    return tree.DNull, fmt.Errorf("unknown username: %s", arg)
  }
  if i > 0 {
    buf.WriteString(", ")
  }
  buf.WriteString(name)
}
```

Lastly, we need to return the result:

```go
return tree.NewDString(buf.String()), nil
```

Much of the above looks like standard Go, but what is a ``DString``? The SQL
execution engine has its own typing system. Each type in the system adheres to
the ``Datum`` interface which defines the methods that a type needs to
implement. ``DString`` is the implementation of ``Datum`` for the SQL ``string``
type.

```go
type DString string
```

Note that `*DString` implements the `Datum` interface, not `DString`. This is why
we type assert the arguments using `arg.(*DString)`.

Put it all together (rebuild, restart your server) and we should have a working
function:

```text
$ ./cockroach sql --insecure -e "select whois('pmattis')"
+------------------+
| whois('pmattis') |
+------------------+
| Peter Mattis     |
+------------------+
(1 row)

$ ./cockroach sql --insecure -e "select whois('pmattis', 'bdarnell')"
+------------------------------+
| whois('pmattis', 'bdarnell') |
+------------------------------+
| Peter Mattis, Ben Darnell    |
+------------------------------+
(1 row)

$ ./cockroach sql --insecure -e "select whois('non-existent')"
Error: pq: whois(): unknown username: 'non-existent'
Failed running "sql"
```

So far so good. One oddity of our function is that it returns the empty string
if there are no arguments. Let’s make it return all of the users in that case.
Before the loop over the arguments, we check to see if no arguments were
specified and expand that to a list of all of the usernames:

```go
if len(args) == 0 {
  args = make(tree.Datums, 0, len(users))
  for user := range users {
    args = append(args, tree.NewDString(user))
  }
}
var buf bytes.Buffer
for i, arg := range args {
  ...
}
```

Rebuild, restart and test:

```text
$ ./cockroach sql --insecure -e "select whois()"
+--------------------------------------------+
|                  whois()                   |
+--------------------------------------------+
| Ben Darnell, Peter Mattis, Spencer Kimball |
+--------------------------------------------+
(1 row)
```

Nice!

## Testing Our New Function

Now, it’s time to codify the manual testing we just performed into a proper
test. Even though this function is very simple, writing a test or two will
safeguard against future regressions. And who knows: we might still spot a bug!

To test CockroachDB’s SQL functionality, we use a logic test framework that
provides a convenient syntax for asserting the expected results of queries.

Take a peek at the top of one of these logic test files,
`pkg/sql/logictest/testdata/logic_test/builtin_function`. Here’s an existing test for the
length function from that file:

```text
query II
SELECT LENGTH('Hello, 世界'), LENGTH(b'Hello, 世界')
----
9 13
```

The format is relatively straightforward. `query II` means "there’s a query on
the next line that will return two *I*nteger columns; please check that that
they match what I expect". The logic test framework takes each line after the
`----` separator as an expected row, up to the first non-blank line, and takes
each whitespace-separated value on a line as the expected value for the
corresponding column. In the above example, we expect one row of output with
columns 9 and 13.

Let’s add a new test for our function. Create a new file,
`pkg/sql/logictest/testdata/logic_test/codelab`, with the following contents:

```text
query T
select whois('pmattis')
----
Peter Mattis
```

`query T` means the query is expected to return one column of text output.

Now, run your new logic test!

```text
$ make testlogic FILES=codelab
```

If all the tests in your file pass, the last line of output will read `PASS`.
Now, let’s add a failing test. Fill in `USERNAME`, `FIRST`, and `LAST` with your
real username, first name, and last name.

```text
query T
select whois('USERNAME')
----
FIRST LAST
```

Re-run the tests and make sure they fail. This gives us confidence that our test
will actually catch bugs, should they arise. Go back and add your name to the
end of the users map, and verify that the tests once again succeed.

Let’s add one more test for the default case. Again, remember to replace `FIRST`
and `LAST` with your own name.

```
query T
select whois()
----
Ben Darnell, Peter Mattis, Spencer Kimball, FIRST LAST
```

Run the tests once more. If they still succeed, we’re done!

Well, not quite. If you haven’t seen a failure yet, run the tests a few more
times. Eventually, you should see an error like this:

```
--- FAIL: TestLogic (0.06s)
    --- FAIL: TestLogic/default (0.05s)
        --- FAIL: TestLogic/default/codelab (0.05s)
          logic_test.go:1707:
            testdata/logic_test/codelab:1:
            expected:
                Ben Darnell, Peter Mattis, Spencer Kimball
            but found (query options: "") :
                Peter Mattis, Spencer Kimball, Ben Darnell
```

Looks like we’ve found a bug! We’re expecting names in alphabetical order (Ben,
Peter, then Spencer), but the names were output in a different order!

What might cause this? Take another look at how your built-in constructs the
output string and see if you can spot the bug.

If you get stuck, check out this [blog post about maps in Go][blog-maps].

Once you’ve found and fixed the bug, verify that the tests reliably pass. Then
check your solution against ours.

<details>
  <summary>View our solution</summary>
  <p>

  <!--
    This collapsible-section hack is very sensitive to whitespace.
    Be careful! See: https://stackoverflow.com/a/39920717/1122351
  -->

  ```diff
    "whois": makeBuiltin(defProps(),
      tree.Overload{
        Types:      tree.VariadicType{VarType: types.String},
        ReturnType: tree.FixedReturnType(types.String),
        Fn: func(ctx *tree.EvalContext, args tree.Datums) (tree.Datum, error) {
          users := map[string]string{
            "bdarnell": "Ben Darnell",
            "pmattis":  "Peter Mattis",
            "skimball": "Spencer Kimball",
          }
          if len(args) == 0 {
            args = make(tree.Datums, 0, len(users))
            for user := range users {
              args = append(args, tree.NewDString(user))
            }
  +          sort.Slice(args, func(i, j int) bool {
  +            return *args[i].(*tree.DString) < *args[j].(*tree.DString)
  +          })
          }
          var buf bytes.Buffer
          for i, arg := range args {
            name, ok := users[strings.ToLower(string(*arg.(*tree.DString)))]
            if !ok {
              return tree.DNull, fmt.Errorf("unknown username: %s", arg)
            }
            if i > 0 {
              buf.WriteString(", ")
            }
            buf.WriteString(name)
          }
          return tree.NewDString(buf.String()), nil
        },
      },
    ),
    ...
  ```
  </p>
</details>

<span></span> <!-- Force space after collapsible section. -->

That’s it! You’ve successfully added a bug-free built-in SQL function to
CockroachDB.

[CONTRIBUTING.md]: https://github.com/cockroachdb/cockroach/blob/master/CONTRIBUTING.md
[built-ins]: https://www.cockroachlabs.com/docs/stable/functions-and-operators.html#built-in-functions
[blog-maps]: https://blog.golang.org/go-maps-in-action#TOC_7.