Hamilton Ulmer

I’ve been on parental leave from early July to October 2024. The overwhelming majority of my time was spent doing childcare-related stuff (I also have a toddler who just started preschool) & supporting my partner’s needs. In the margins, however, I’ve also worked on a few interesting things. I’ve decided to do a writeup of everything pertinent to my field that I did / read.

From the outset of leave, I knew I wanted to make sure to prioritize one big research project: Instant Preview Mode. This feature represents a new paradigm in ad hoc SQL editing. The idea here is that rather than having to run a SQL query to verify that you got the logic right, what if you could just see an instant preview of the results? And what if you can see the results from any select node, not just the top level one? See it in action here:

What I worked on

Below is a rundown of the projects and experiments I worked on that led to Instant Preview Mode.

AST range information (src/range). I backfilled the AST with full range information for all AST nodes. Why? Because, like many DBMSs, DuckDB uses a yacc parser (really, it’s a fork of the Postgres parser). As far as parsers go, it’s a fairly antequated and totally ignorant of all the other possible uses other than traditional parsing and binding. BUT – if we can find a way to map the cursor location in a SQL query to a path in the AST, then we can do really interesting things. The first step is using the tokenizer & AST, along with the new query_location field, to determine the bounds of a node in the query itself. This was made possible by work I requested from DuckDB Labs back in January.

This was a multi-week project, and it might be throwaway if and when DuckDB moves to a PEG parser (which could give us an actual CST), but it enabled me to prototype many other features and ideas. Well worth the time! And I learned a great deal about the edge cases of DuckDB SQL.

AST path generation (src/path). Given a cursor location, generate a path through the AST. Builds on the.AST range functionality described above. This AST path can be used for a variety of things, including semantic context menus wherever the cursor is and a variety of other cool features. Demo.

CodeMirror widget portal technique. Render the contents of an inline CodeMirror widget in React. Useful for context menus and inline blocks (like an inline codegen widget). You use a block widget just for the container DOM element.

CodeMirror token highlight layering system. A basic system for layering multiple background highlights to support node, parent, and clause regions.

Canvas cursor in CodeMirror. Enables users to track the cursor better when it moves. It might be a little too flashy, but it is a nice way to enable users to see where the cursor goes when they move the cursor. Maybe worth using when the user is moving more than 1 position with the cursor, or the euclidean distance of start to end is some amount d. Demo.

Editor context menu. A simple AST-aware menu command menu. Implemented “create alias” action, which uses the AST range information & an LLM endpoint to generate an alias based on the expression. Demo.

Inline chat widget. A cursor-style inline chat. Uses CodeMirror’s merge extension + the portal. Remarkably easy to build something like this with CodeMirror. Demo.

⭐ Instant preview mode. Show results as the user types based on samples of table refs. Basic implementation only works with SELECT statement. The central challenge is figuring out (1) what to cache, and (2) when to invalidate it. Demo.

MotherDuck AMS offsite lightning talk. Since I couldn’t be there, I sent a video demoing Instant Preview along with the LLM features that benefit from it and the AST work. It was received with enthusiasm. This said, I do work at a startup, and sometimes the most important thing to work on isn’t the most innovative thing. Unless I get a strong signal to keep going, it is likely I will have to shelve this research. This video is available upon request.

Instant Preview: CTE inspection. Show a preview of the CTE results when cursor is in range. This enables people to actually iterate on CTEs without having to duct-tape together a bunch of SQL first. Does not yet support joins. Demo. Yet another big payoff of local sampling and rewriting via DuckDB’s parser.

Join sampling (synopses, etc.). If you’re familiar with probability theory, you can probably reason that joining two samples is an ineffective way to get a sample of the joins. After some conversations with Peter Boncz (CWI / MotherDuck), three paths for sampling joins: naive (“just join then sample”), minhash, and join synopses. The synopsis approach works with equijoins with foreign keys. If you sample the root of the foreign key DAG of the schema (the most upstream table) and then join, you get a sample. Most tables don’t have foreign keys, so we could use an LLM to guess for a given join (or the whole schema). If Instant Preview ships, the v1 will likely not include any sophisticated join sampling. Minhashing on the join keys might also work, but it’s unclear how complex it’ll be for multi-joins.

Building the Query DAG. Another (or complementary) approach to caching is to cache samples of the table ref DAG. For instance if a CTE A has a simple isomorphic transformation, and the main select selects from A, then caching the main select can use the cache of A, given the right conditions. The challenge arises when a select node (or its parent) is not isomorphic (and for joins, one would just skip this technique entirely). I discussed again with Peter how we could handle this. We could extend the work of Niclas Haderer’s (CWI / MotherDuck) RESULT to allow results-on-results (link to Peter’s talk here). This would pass the complexity of dependent sample queries to the DB where it can be further optimized. The results operator can also be extended to cover approximate queries.

What I’ve been reading

Below are some of the papers & books I read, some of which influenced this work. Some of these were read from the worrydream papers repository. Others through happenstance.

Books, mostly listened to while rocking our newborn to sleep and driving my toddler to preschool:

• Shoe Dog by Phil Knight. A highly engaging and candid account of the messes of an early Nike.
• The Man Who Solved the Market: How Jim Simons Launched the Quant Revolution by Gregory Zuckman. A fantastic history of the origins of modern hedge funds, focusing on the now-famous (or infamous) Renaissance Technologies. Critically, Robert Mercer, the Trump megadonor, played a pivotal role in RenTech’s success.

“Should Computers Be Easy To Use? Questioning the Doctrine of Simplicity in User Interface Design” by Advait Sarkar. A great paper about some of the things we miss out on when “simplicity” is the main operational paradigm in design. There are many types of software – and many other human endeavors – that are rich, complex, and take time to learn. Software like Excel, Pro Tools, CAD software, Figma, Photoshop, and numerous other pieces of software require learning the underlying worldview of the software. Once you’ve learned it, you have enormous expressive power. Sarkar calls this type of software “praxisware”. It mirrors many real-world endeavors where expertise must be acquired by learning in order to reach mastery (such as playing a sport or an instrument).

I believe this is probably one of the most important essays in my niche. I’m sure I’ll revisit it and share it quite a bit.

“The Strategic Use of Complex Computer Systems” by Suresh K. Bhavnani and Bonnie E. John. ”[…] in addition to task and tool knowledge, users must also learn an intermediate layer of knowledge that lies between the layers of tasks and tools.” A great study of “strategy development” for using a complex tool (CAD software). Strategy development exist for all sorts of other complex endeavors. Identifying these (and potentially aiding users toward the most efficient strategies) is a great idea.

Direct Manipulation. I re-read Schneiderman’s classic and Hutchins et al discussion of “directness” and “distance”. Being able to write code is a powerful thing, so I’m not sure if a pure direct manipulation SQL editor for instance makes the most sense. But somewhere between code and UI is probably something useful (something Schneiderman called “Programming In the UI (PITUI)” in a follow-up to his first paper). Projectional editors like Hazel have a lot of interesting ideas. These ideas have guided my work on Instant Preview mode. To some extent, the AST is the source of truth for the UI. But the job of the UI is there to interact with the code.

“The Mechanism of the Einstellung (Set) Effect: A Pervasive Source of Cognitive Bias” by Bilalác et al. The Einstellung effect occurs when a familiar solution to a problem comes to mind quickly, preventing consideration of potentially better alternatives. The researchers found that even expert chess players often failed to find the shorter solution in the 2-solution problem.

They suggest that once a schema (pattern of thought) is activated, it directs attention towards information consistent with it and away from inconsistent information, creating a self-fulfilling cycle. Ironically, this is a central part of of the story behind the book The Man Who Solved The Market (see above); traditional investors relied on instinct and institutional knowledge to make trades, while the quants originally ignored all details about a stock and instead just relied on building reliable signals from the data.

The Einstellung effect is likely related to other cognitive biases, from confirmation bias to cognitive inertia. Yet I think the concept has a special place for endeavors that require well-honed patterns of thought (e.g. using a complex application).

“I have no words and I must design” by Greg Costikyan. Goes through a definition of “game” – an interactive structure of endogenous meaning that requires players to struggle toward a goal. Fantastic analogies for application design throughout.

“Powerful ideas need love too!” by Alan Kay. People often lack a fundamental understanding of basic scientific concepts. This is likely due to people thinking in terms of stories (“bricolage”) rather than logical arguments or systems dynamics. Of course, to some extent this is fine; stories make our lives richer. But rapid progress can be made when we move into a more rigorous scientific paradigm. This transformation begins in school, where children learn difficult skills primarily when they see them as integral to their culture. Kay, for instance, grew up in a small town in Massachusetts where everyone learned how to play an instrument. “The reason is that understanding—like civilization, happiness, music, science and a host of other great endeavors—is not a state of being, but a manner of traveling. And the main goal of helping children learn is to find ways to show them that great road which has no final destination, and that manner of traveling in which the journey itself is the reward.”

This viewpoint resonates with other works I read during this period around complex tools and applications, because “learning to do a hard thing” is intrinsic to the process of building expertise and achieving mastery. And of course, it’s just a lovely essay that resonates with my own upbringing and life experience. So take that as strong confirmation bias!

“Analogy as the Core of Cognition” by Doug Hofstadter. Every concept we hold in our head is really a “bundle of analogies”. We build these bundles through “chunking” - building up larger concepts from smaller ones in our lives, slowly, over time. He argues that this may be one reason why we feel that time moves faster as we age. It also explains why translation is itself a complex process of analogy-making across languages and culture. Hofstadter outlines the “central cognitive loop”, where long-term memory nodes are accessed, unpacked in short-term memory, and then activating further bundles.