#  Archer *): A Human-Labeled Text-to-SQL Dataset with
Arithmetic, Commonsense and Hypothetical Reasoning

Danna Zheng1, Mirella Lapata1, Jeff Z. Pan1,2
1 School of Informatics, University of Edinburgh, UK
2 Huawei Edinburgh Research Centre, CSI, UK
<EMAIL_ADDRESS><EMAIL_ADDRESS>http://knowledge-
representation.org/j.z.pan

###### Abstract

We present Archer, a challenging bilingual text-to-SQL dataset specific to
complex reasoning, including arithmetic, commonsense and hypothetical
reasoning. It contains 1,042 English questions and 1,042 Chinese questions,
along with 521 unique SQL queries, covering 20 English databases across 20
domains. Notably, this dataset demonstrates a significantly higher level of
complexity compared to existing publicly available datasets. Our evaluation
shows that Archer challenges the capabilities of current state-of-the-art
models, with a high-ranked model on the Spider leaderboard achieving only
6.73% execution accuracy on Archer test set. Thus, Archer presents a
significant challenge for future research in this field.

Archer *): A Human-Labeled Text-to-SQL Dataset with
Arithmetic, Commonsense and Hypothetical Reasoning

Danna Zheng1, Mirella Lapata1, Jeff Z. Pan1,2 1 School of Informatics,
University of Edinburgh, UK 2 Huawei Edinburgh Research Centre, CSI, UK
<EMAIL_ADDRESS><EMAIL_ADDRESS>http://knowledge-
representation.org/j.z.pan/

##  1 Introduction

Figure 1: Archer examples with three reasoning types: arithmetic, commonsense,
and hypothetical reasoning. (See more examples in Appendix D)

The text-to-SQL task is an important NLP task, which maps input questions to
meaningful and executable SQL queries, enabling users to interact with
databases in a more intuitive and user-friendly manner. State-of-the-art
methods Pourreza and Rafiei (2024); Li et al. (2023a, b); Scholak et al.
(2021) relying on large language models have achieved execution accuracy above
75% on the Spider datasetYu et al. (2018), which encompasses complex SQL
grammar and cross-domain settings. Recently, Pourreza and Rafiei (2024)
achieved remarkable results with an impressive 85.3% execution accuracy on the
Spider dataset, leveraging the enhanced capabilities of GPT-4.

However, previous text-to-SQL datasets Yu et al. (2018); Finegan-Dollak et al.
(2018); Yaghmazadeh et al. (2017); Iyer et al. (2017); Zhong et al. (2017); Li
and Jagadish (2014); Giordani and Moschitti (2012); Popescu et al. (2003);
Tang and Mooney (2000); Dahl et al. (1994), have limitations that prevent them
from capturing complex reasoning effectively. For example, Spider Yu et al.
(2018) purposely excludes questions that would require external knowledge,
like common-sense inferences or mathematical calculations. This exclusion
limits Spider’s ability to properly test how well models can handle real-world
scenarios, which often require a deeper level of reasoning capabilities.

In this paper, we present Archer, an innovative dataset designed to
incorporate three distinct types of reasoning: arithmetic, commonsense, and
hypothetical reasoning. By including such varied reasoning skills, Archer
seeks to challenge and expand the capabilities of text-to-SQL models,
equipping them to manage more intricate and nuanced queries. Figure 1
showcases data examples from Archer that demonstrate these three reasoning
abilities.

To evaluate the challenge posed by Archer, we conducted experiments with both
large language models (LLMs) and fine-tuned models. However, all models
demonstrated inferior performance when dealing with Archer. Even the model
that achieved a high place on the Spider leaderboard managed only 6.73%
execution accuracy on Archer test sets. These findings highlight substantial
potential for improvement, indicating that Archer indeed provides a
significant challenge to current models.

##  2 Reasoning Types

In this section, we present the three different types of reasoning in Archer:
arithmetic, commonsense, and hypothetical reasoning.

#### Arithmetic reasoning

Arithmetic reasoning pertains to the act of resolving mathematical problems
through logical and analytical thought processes. According to an analysis of
SQL queries from practical applications like the Baidu search engine and
customer service and data analysis robots by Wang et al. (2020), mathematical
calculations account for a significant portion across SQL applications.
However, previous high-quality datasets contain very few questions that
involve calculations, and such questions are typically auto-generated with
simple grammar. In contrast, all question-SQL pairs included in Archer
necessitate arithmetic reasoning and are manually annotated to ensure high
quality.

#### Commonsense reasoning

Commonsense reasoning refers to the capacity to make logical deductions based
on implicit knowledge and a broad understanding of how things function in the
world. Archer includes questions that necessitate models to comprehend the
database, infer missing details, and generate logical inferences to create
accurate SQL queries. As illustrated in Figure 1, for the question "Which
4-cylinder car needs the most fuel to drive 300 miles? List how many gallons
it needs, and its make and model.", the database does not provide an explicit
schema about the fuel used to travel 300 miles for each car. It only provides
each car’s fuel consumption in MPG. Solving this question requires commonsense
knowledge, specifically the understanding of "Fuel used is calculated by
dividing distance driven by fuel consumption" to derive the correct SQL.

#### Hypothetical reasoning

Hypothetical reasoning takes the complexity a step further, requiring models
to have counterfactual thinking ability, which is the ability to imagine and
reason over unseen cases based on the seen facts and counterfactual
assumptions. Archer includes questions that involve hypothetical situations,
requiring the model to understand and reason about conditional relationships.
As illustrated in Figure 1, consider the hypothetical question "If all cars
produced by the Daimler Benz company have 4-cylinders, then in all 4-cylinder
cars, which one needs the most fuel to drive 300 miles? Please list how many
gallons it needs, along with its make and model.". In this question, the
underlying assumption contradicts the factual information stored in the
database. The model must comprehend this assumption and convert it into the
SQL condition d.fullname = "Daimler Benz" or a.cylinders = "4".

##  3 Corpus Construction

Figure 2: The annotation process of our Archer.

As illustrated in Figure 2, we create Archer in the following six steps,
spending around 300 hours of human labor in total: §3.1 Database Collection,
§3.2 Question Annotation, §3.3 SQL Annotation, §3.4 SQL review, §3.5 Question
Review and Paraphrase, §3.6 Final Review and Process.

###  3.1 Database Collection

In a noteworthy research study conducted by Yu et al. (2018), a total of 200
high-quality databases across various domains were meticulously collected and
created, requiring approximately 150 man-hours. Out of these, 166 databases
were made publicly available.

Since not all Spider databases support the proposed reasoning types, we
carefully selected 20 databases across 20 domains from the Spider 166
databases based two criteria. Firstly, we applied a script to keep only
databases with a minimum of 3 tables and 20 columns within each database, as
well as a minimum of 6 columns with time or numeric data types. Secondly, we
manually checked the filtered databases. These two steps ensure that each
selected database contains sufficient information to support complex
reasoning.

###  3.2 Question Annotation

Two bilingual (English and Chinese) Ph.D. students with SQL experience were
assigned the task of generating questions based on 20 databases. The
annotators were required to propose 25-30 questions for each database,
ensuring that the questions met the following four requirements:

#### 1) Arithmetic Reasoning:

Each question should incorporate arithmetic reasoning. The annotators were
expected to include a minimum of five questions for each arithmetic reasoning
type (addition, subtraction, multiplication, division).

#### 2) Hypothetical Reasoning:

At least five questions should involve hypothetical reasoning. For each
question using hypothetical reasoning, the annotators were also required to
propose a corresponding factual question.

#### 3) Commonsense Reasoning:

The annotators were encouraged to propose questions that involve commonsense
reasoning. However, the number of questions with commonsense reasoning was not
strictly limited. This flexibility acknowledged that not all databases support
commonsense reasoning, and not all arithmetic calculations necessitate it.

#### 4) Complex SQL Grammar:

The annotators were encouraged to propose questions that require the
utilization of complex SQL grammar, such as GROUP BY, ORDER BY, and JOIN.

####

The annotators were asked to write each question in both English and Chinese.
Besides, they were instructed to indicate the reasoning types involved
(arithmetic: addition, subtraction, multiplication, division; hypothetical;
commonsense), and provide the relevant knowledge or formulation if the
question incorporated commonsense reasoning.

###  3.3 SQL Annotation

In order to mitigate cognitive bias, we employed a diverse set of annotators
for the tasks of generating questions and writing SQL queries. Two Ph.D.
students, who possess strong SQL skills, were specifically chosen to translate
the natural language questions into SQL queries. Their responsibilities
encompassed the following:

#### 1) Clarity Ensuring:

The annotators reviewed both English and Chinese questions to identify any
ambiguity and restructure them accordingly.

#### 2) SQL Writing:

The annotators were instructed to use consistent SQL patterns when multiple
equivalent queries are applicable for similar questions.

#### 3) Verification and Correction:

The annotators were also responsible for reviewing the annotations pertaining
to reasoning types and the common knowledge necessary to solve each question.

###  3.4 SQL Review

To ensure the correctness of the annotated SQL for each question, we employed
a professional SQL expert to review all the SQL queries and rectify any
incorrect ones. Subsequently, the original SQL annotators were responsible for
verifying the SQL queries corrected by the expert. In cases where there are
differences of opinion between the expert and the annotators regarding the
corrected queries, they were required to engage in a discussion and reach a
consensus to finalize the SQL annotation.

###  3.5 Question Review and Paraphrase

We employed two native English speakers and two native Chinese speakers to
review and paraphrase English and Chinese questions, respectively. Initially,
their task was to assess the naturalness and grammatical accuracy of the
questions. Subsequently, the annotators were requested to provide a
paraphrased version of each question in order to enhance the dataset’s
robustness.

###  3.6 Final Review and Processing

In the final stage of our process, we assigned the task of reviewing the
English and Chinese questions, SQL, and annotations relating to reasoning
types and commonsense knowledge to our most seasoned annotator. Once this
comprehensive review was completed, we ran a script to ensure that all SQL
queries are executable.

##  4 Dataset Statistics and Comparison

Dataset | Scale | Complexity | Reasoning Distribution | Lang
---|---|---|---|---
#Q | #SQL | #DB | #Dom | T/DB | C/DB | QL | SQLL | VS | TM | NL | GB | OB | A(+) | A(-) | A(*) | A(/) | H | C | C+H
ATIS | 5280 | 947 | 1 | 1 | 25 | 131 | 10.53 | 99.75 | 3.14 | 4.66 | 0.39 | 0.01 | 0.00 |  2717 |  2717 |  2717 |  2717 |  2717 |  2717 |  2717 | en
GeoQuery | 877 | 246 | 1 | 1 | 8 | 31 | 7.48 | 26.76 | 0.82 | 1.46 | 1.04 | 0.18 | 0.07 |  2717 |  2717 |  2717 | 0.2% |  2717 |  2717 |  2717 | en
Scholar | 817 | 193 | 1 | 1 | 12 | 28 | 6.59 | 38.03 | 1.36 | 3.26 | 0.02 | 0.37 | 0.28 |  2717 | 0.5% |  2717 |  2717 |  2717 |  2717 |  2717 | en
Academic | 196 | 185 | 1 | 1 | 15 | 42 | 13.33 | 36.85 | 1.30 | 3.23 | 0.04 | 0.21 | 0.12 |  2717 |  2717 |  2717 |  2717 |  2717 |  2717 |  2717 | en
IMDB | 131 | 89 | 1 | 1 | 16 | 65 | 10.23 | 29.51 | 1.20 | 2.84 | 0.01 | 0.07 | 0.11 |  2717 |  2717 |  2717 |  2717 |  2717 |  2717 |  2717 | en
Yelp | 128 | 120 | 1 | 1 | 7 | 38 | 9.87 | 28.33 | 1.68 | 2.25 | 0.00 | 0.10 | 0.08 |  2717 |  2717 |  2717 |  2717 |  2717 |  2717 |  2717 | en
Advising | 4387 | 205 | 1 | 1 | 18 | 124 | 10.90 | 48.08 | 3.06 | 3.13 | 0.17 | 0.03 | 0.07 | 3.4% |  2717 |  2717 |  2717 |  2717 |  2717 |  2717 | en
Restaurant | 378 | 23 | 1 | 1 | 3 | 12 | 10.13 | 29.57 | 2.26 | 2.26 | 0.17 | 0.00 | 0.00 |  2717 |  2717 |  2717 |  2717 |  2717 |  2717 |  2717 | en
WikiSQL | 80654 | 51159 | 26531 | - | 1 | 6.33 | 12.46 | 13.32 | 0.53 | 1.00 | 0.00 | 0.00 | 0.00 |  2717 |  2717 |  2717 |  2717 |  2717 |  2717 |  2717 | en
DuSQL | 25003 | 20308 | 208 | - | 4.04 | 21.38 | 19.20 | 20.63 | 1.16 | 1.33 | 0.20 | 0.42 | 0.30 | 2.4% | 9.5% | 1.0% | 4.4% |  2717 | - |  2717 | zh
BIRD | 10962 | 10841 | 80 | - | 7.68 | 54.71 | 15.81 | 23.85 | 1.16 | 2.20 | 0.08 | 0.10 | 0.19 | 0.8% | 5.0% | 7.9% | 10.0% |  2717 | - |  2717 | en
Cspider | 9693 | 5275 | 166 | 99 | 5.28 | 27.13 | 11.90 | 24.37 | 0.93 | 1.69 | 0.10 | 0.23 | 0.21 | 0.1% | 0.1% |  2717 | 0.0% |  2717 |  2717 |  2717 | zh
Spider | 9693 | 5275 | 166 | 99 | 5.28 | 27.13 | 13.29 | 24.37 | 0.93 | 1.69 | 0.10 | 0.23 | 0.21 | 0.1% | 0.1% |  2717 | 0.0% |  2717 |  2717 |  2717 | en
KaggleDBQA | 272 | 249 | 8 | 8 | 2.13 | 22.38 | 9.83 | 13.80 | 0.54 | 1.18 | 0.00 | 0.44 | 0.50 | 0.0% | 0.0% |  2717 | 0.0% |  2717 |  2717 |  2717 | en
|  Archer *)
---
(Ours)
1042 | 521 | 20 | 20 | 7.55 | 45.25 |  | en-29.94
---
zh-25.99
79.71 | 6.21 | 2.17 | 1.08 | 0.59 | 0.26 | 34.0% | 47.8% | 62.0% | 40.7% | 44.0% | 51.4% | 22.1% |  | en
---
zh

Table 1: Comparison of public text-to-SQL datasets. The abbreviations used are
as follows: #Q for the number of unique questions, #SQL for the number of
unique SQLs, #DB for the number of databases, #Dom for the number of domains,
T/DB for the number of tables per database, C/DB for the number of columns per
database, QL for the average question length, SQLL for the average SQL length,
VS for the average number of value slots per question, TM for the average
number of tables mentioned in each SQL, NL for the average nested level per
SQL, GB and OB for the average number of GROUP BY and ORDER BY clauses per SQL
respectively. A, H, C, and Lang represent arithmetic, hypothetical,
commonsense, and language, respectively. The cross mark, - denote absence and
presence respectively. The statistics for BIRD, CSpider, and Spider is based
on training and dev sets as their test sets are unavailable. Language is
represented as en for English databases and questions, zh for Chinese
databases and questions, and zh for English databases and Chinese questions.

In Table 1, we present a summary of the statistics for Archer as well as other
publicly available text-to-SQL datasets. We conducted a comparative analysis
of Archer and other datasets based on four key perspectives: scale,
complexity, reasoning distribution, and language.

###  4.1 Scale

Archer consists of 1,042 Chinese questions, 1,042 English questions, and 521
corresponding SQL queries, covering a wide range of 20 distinct databases
spanning 20 domains. Each database in Archer, on average, consists of 7.55
tables and 45.25 columns. Archer stands out for its inclusion of multiple
domains and a higher average number of tables and columns.

It is worth noting that WikiSQL Zhong et al. (2017) and DuSQL Wang et al.
(2020) are exceptionally large databases generated automatically. Inspired by
them, Archer has the potential to serve as a valuable resource for summarizing
SQL templates and training SQL-to-text generators to create large-scale
datasets in line with our reasoning setting. In this project, we do not
utilize Archer for automatic question-SQL pairs generation. This possibility
is a potential future direction.

###  4.2 Complexity

Archer distinguishes itself by its considerably higher level of complexity
compared to existing text-to-SQL datasets. Several factors contribute to this
complexity:

Firstly, the average question length in Archer is significantly longer than
that in other datasets. This poses a challenge to models because longer inputs
increase the likelihood of misunderstandings or misinterpretations of specific
question details.

Secondly, the average SQL length in Archer stands at 79.71, which is
significantly longer than that of other datasets except for ATIS, which
contains only one table. Longer SQL statements increase the likelihood of
generating incorrect code.

Thirdly, value prediction, which is crucial in SQL generation, is often
undervalued in current research. Interestingly, Pourreza and Rafiei (2024)
achieved an execution accuracy of 85.3% on the Spider dataset without
utilizing database content. This is primarily because Spider SQL queries
typically contain an average of only 0.93 value slots, with most values
explicitly quoted in the question. In contrast, Archer emphasizes the
importance of values, with an average of 6.21 value slots per SQL.
Furthermore, Archer questions do not explicitly quote exact values; instead,
they naturally mention value information, mirroring real-world scenarios.

Fourthly, SQL queries in Archer refer to an average of 2.17 tables, suggesting
that a substantial number of the questions require the use of information from
multiple tables to derive SQLs.

Fifthly, the level of SQL statement nesting in Archer is higher than that in
other datasets, indicating a greater degree of reasoning complexity required
to answer Archer questions, which often necessitates the use of multiple
subqueries.

Finally, Archer exhibits a high usage rate of complex SQL grammar features
such as GROUP BY and ORDER BY in each SQL, surpassing the frequency of usage
seen in nearly all other datasets.

###  4.3 Reasoning Distribution

All questions in Archer require arithmetic reasoning. This means that
mathematical calculations and operations are essential in understanding and
answering these questions effectively. Additionally, 44.0% of the questions
involve hypothetical reasoning, where the model needs to reason about
hypothetical scenarios to derive the correct SQL. Furthermore, 51.4% of the
questions require commonsense reasoning, where the model needs to utilize
general knowledge and commonsense understanding to produce the correct SQL.

It is worth noting that the majority of previous text-to-SQL datasets do not
incorporate arithmetic and commonsense reasoning. Moreover, none of the
previous datasets contain questions that involve hypothetical reasoning.
Therefore, the inclusion of these types of reasoning tasks in Archer sets it
apart from previous datasets and presents new challenges for models in the
field of text-to-SQL understanding and generation.

###  4.4 Language

Unlike most previous text-to-SQL datasets that focus solely on English, Archer
provides both English and Chinese questions. This bilingual feature of Archer
enhances the evaluation and training capabilities of text-to-SQL models,
catering to the needs of users in both English and Chinese languages, while
forming a solid base for potential support of more languages for Archer, which
is left as a future work.

Models |  EN Questions, EN databases |  ZH Questions, EN databases
---|---|---
Full | Test | Full | Test
VA | EX | VA | EX | VA | EX | VA | EX
LLMs
GPT-3.5 + API Doc | 82.63 | 13.24 | 83.65 | 3.85 | 86.18 | 10.65 | 85.58 | 3.85
GPT-3.5 + CT-3 | 84.17 | 13.34 | 80.77 | 3.85 | 91.17 | 12.86 | 91.35 | 1.92
GPT-3.5 + CT-3 + COT | 75.14 | 13.24 | 74.04 | 4.81 | 72.84 | 12.19 | 65.38 | 3.85
GPT-4 + DIN-SQL | - | - | 96.15 | 6.73 | - | - | - | -
Fine-tuned Models
T5-base/mT5-base | - | - | 11.54 | 0.00 | - | - | 9.62 | 0.00
T5-large/mT5-large | - | - | 15.38 | 0.00 | - | - | 14.42 | 0.00
T5-3B/mT5-xl | - | - | 19.23 | 0.00 | - | - | 17.31 | 0.00
T5-base/mT5-base + Aug | - | - | 25.00 | 0.00 | - | - | 24.03 | 0.00
T5-large/mT5-large + Aug | - | - | 33.65 | 3.84 | - | - | 30.77 | 0.96
T5-3B/mT5-xl + Aug | - | - | 50.00 | 4.81 | - | - | 61.54 | 1.92

Table 2: Baseline performance on Archer. GPT-4+DIN-SQL was tested only on the
English set due to cost and its English-specific design. We only report the
fine-tuned model’s performance on the test set.

##  5 Experiments

###  5.1 Baseline Models

We benchmark the performance of two types of presentative text-to-SQL models
on Archer: LLMs and finetuned Models.

#### LLMs

LLMs have shown strong performance on commonly used text-to-SQL benchmarks,
such as Spider. To analyze the difficulty of the whole Archer, we provide the
zero-shot results of GPT-3.5 (gpt-3.5-turbo) with different prompt settings:
API Doc, CT-3, CT-3+COT. API Doc follows the style of the Text-to-SQL example
provided by OpenAI, which includes the schema information in a comment style.
CT-3, introduced by Rajkumar et al. (2022), includes the CREATE TABLE commands
for each table and the results of executing a SELECT * FROM T LIMIT 3 query on
each table. Compared with API Doc, CT-3 provides more information like
declarations of column types and foreign keys, and a small amount (3) of
content examples. CT-3+COT implement the Chain-Of-Thought (COT) technique on
top of the CT-3 prompt by appending the prompt sentence "Let’s think step by
step." before the SQL generation. Following the work of Li et al. (2023c), we
provide a 1-shot pseudo example for LLMs to learn the procedure of thinking
and output format. Furthermore, we evaluate the performance of GPT-4+DIN-SQL
Pourreza and Rafiei (2024) on Archer. As a highly-ranked solution on the
Spider leaderboard at the time of writing, it consists of four modules: (1)
schema linking, (2) query classification and decomposition, (3) SQL
generation, and (4) self-correction. The initial three modules exploit the in-
context learning ability of GPT-4 with ten shots, while the self-correction is
conducted by GPT-4 in a zero-shot setting. Note that we do not evaluate
GPT-4+DIN-SQL on Archer Chinese questions because it is designed for English
datasets. More details on the prompts can be found in Appendix A.

#### Fine-tuned Models

T5-based fine-tuned models have shown promising results on the Spider
leaderboard. It is, however, worth mentioning that many top-tier models on the
leaderboard are customized specifically for the limited SQL grammars present
in the Spider dataset. Given that our dataset contains more complex
grammatical structures compared to Spider, these specialized models may not be
suitable for our needs. As a result, we select vanilla T5 models as our
baselines instead of the aforementioned variants. We evaluate English
questions using T5-base, T5-large, T5-3B, and evaluate Chinese questions using
mT5-base, mT5-large, mT5-xl. We concatenate the natural question Q and
database schema into a sequence as input in a format as below:

$x=[q_{1},...,q_{|Q|}|t_{1}\mathbin{:}c_{1}^{t_{1}},...,c_{|t_{1}|}^{t_{1}}|...|t_{|\mathcal{T}|}\mathbin{:}c_{1}^{t_{|\mathcal{T}|}},...,c_{|t_{|\mathcal{T}|}|}^{t_{|\mathcal{T}|}}]$
(1)

where $q_{i}$ is the $i^{th}$ question token, $t_{j}$ is the $j^{th}$ table,
and $c_{k}^{t_{j}}$ is the $k^{th}$ in the $j^{th}$ table. Following the works
of Li et al. (2023a); Lin et al. (2020), we extract the potential database
cell values and append them to their corresponding columns.

###  5.2 Evaluation Metrics

We employ two evaluation metrics: VAlid SQL (VA) and EXecution accuracy (EX).
VA is the proportion of the predicted SQL statements that can be executed
successfully, no matter with correct or incorrect answers. EX is the
proportion of the predicted SQL statements where the execution results match
those of the gold SQL statements. We computed EX of each instance use a new
evaluation script as shown in Algorithm 1, which mitigates the false-negative
issue in present publicly available evaluation scripts caused by permutations
of columns and rows.

Figure 3: GPT-3.5 + CT-3 execution accuracy comparison across and within
different reasoning types. A refers to arithmetic. H refers to hypothetic. C
refers to commonsense.

###  5.3 Experiments Setup

#### Data Split

Among the 20 databases, we split 16, 2, and 2 databases as training, dev, and
test sets, respectively. The databases for Archer training set are collected
from the Spider training set, and the databases for Archer dev set and test
set are collected from the Spider dev set. We strive to introduce as few new
SQL keywords as possible during SQL annotation to facilitate the integration
of our dataset with the Spider and CSpider datasets. We also report the
performance of T5 finetuned on the augmented training set which consists of
Archer training set and Spider/CSpider training set. For LLM baselines, we
assess the zero-shot performance of GPT-3.5 on the full Archer to evaluate the
dataset’s overall difficulty. As for GPT-4+DINSQL, due to its high cost and
extended response time, we only test it on Archer test sets.

#### Hyper-Parameters

For GPT-3.5 baselines, we set stop sequence to [‘- -’, ‘;’, ‘#’] and the
temperature to 0. In the case of GPT-4+DIN-SQL, we adhere to the default
setting as outlined in Pourreza and Rafiei (2024). For T5 baselines, we employ
the Adafactor optimizer with a learning rate of 5e-5. For T5-base/mT5-base and
T5-large/mT5-large, we adopt a batch size of 6 and a gradient descent step of
5. For T5-3b and mT5-xl, we use a batch size of 2 and a gradient descent step
of 16. To adjust the learning rate, we utilize linear warm-up with a warm-up
rate of 0.1, followed by cosine decay. During inference, we set the beam size
to 8. We set the maximum epoch to 128, having checkpoints every 10 epochs as
well as the last epoch. We then select the optimal checkpoints based on their
EX performance on the development set.

##  6 Results and Discussion

###  6.1 Overall Evaluation

We summarize the performance of LLMs and finetuned models in Table 2. The low
performance of these models on Archer suggests that Archer presents a
significant challenge. This underscores the considerable potential for future
improvement in this domain.

#### LLM

GPT-4+DIN-SQL obtain EX score of 6.73% on Archer test set, while it is able to
achieve 85.3% test-suite execution performance on Spider test set Pourreza and
Rafiei (2024). To evaluate the overall difficulty of Archer, we test the zero-
shot performance of GPT-3.5 with API Doc, CT-3, CT-3+COT prompts on the full
Archer data. Among the three kinds of prompts, CT-3 achieves the highest EX
scores on both English data (EX: 13.34%) and Chinese data (EX: 12.86%). As
expected, CT-3 performed slightly better than API Doc, likely due to its
inclusion of more useful information, such as declarations of column types and
foreign keys. However, the addition of COT in CT-3+COT did not outperform CT-3
on the complete Archer. On the other hand, for the Test set only, CT-3+COT
slightly outperform CT-3. From Table 2, we observe a significant decrease in
VA when using COT, suggesting that COT suffers from having more syntax errors
in the generated SQL. Although CT-3+COT achieved a higher EX score than CT-3
and API Doc specifically for questions involving arithmetic and commonsense
reasoning, it performed less effectively on questions that require
hypothetical reasoning (cf. Table 3 in Appendix C).

#### Finetuned Models

From Table 2. we observe that T5 from scale base to 3B (XL) trained on Archer
training set achieve 0.00% EX scores. This outcome could be attributed to the
small-scale nature of Archer combined with its high complexity. However, when
Archer training set was augmented with the Spider/CSpider training set, the VA
scores of T5 models exhibited a substantial improvement. Specifically, the
T5-3B model trained on the augmented training set achieved an EX score of
4.81% on the English test (matching the performance of GPT-3.5+CT-3+COT) set
and 1.92% on the Chinese test set (matching the performance of GPT-3.5+CT-3).

These results suggest that Archer has the potential to advance the development
of text-to-SQL systems with complex reasoning.

###  6.2 Different Reasoning Analysis

To gain a comprehensive understanding of the difficulty levels within the
complete Archer across various reasoning types, we conducted a thorough
analysis using the GPT-3.5 model with the CT-3 prompt, which demonstrated the
highest performance on the full dataset. Additional results for GPT-3.5 with
alternative prompts can be found in Appendix C.

#### Overall Comparison

Figure 3-(1) shows the performance on questions with different kinds of
reasoning. The results reveal that questions solely based on arithmetic
reasoning exhibit significantly higher performance compared to those involving
additional forms of reasoning. Specifically, hypothetical reasoning presents a
greater challenge than commonsense reasoning. Moreover, questions that require
the integration of all three reasoning types exhibit the poorest performance.

#### Arithmetic Reasoning

The performance on questions that exclusively require arithmetic reasoning
across various arithmetic operations is presented in Figure 3-(2). The
findings indicate that subtraction and division pose greater difficulty
compared to addition and multiplication.

#### Commonsense Reasoning

On commonsense reasoning, in Figure 3-(3), we compare the performance of
GPT-3.5+CT-3 on such questions under two settings. The first setting involves
directly inputting the question itself, while the second setting involves
inputting the concatenation of the knowledge and the question. The results
reveal that explicitly stating the knowledge within the question can aid in
generating correct SQL queries. This suggests that leveraging external
knowledge bases could be beneficial in solving similar questions. However,
incorporating external knowledge into text-to-SQL tasks presents significant
challenges in general. Firstly, models need to compare information from
natural language questions with the relational database to determine if
external knowledge is required. Secondly, models need to extract the most
relevant knowledge from external knowledge bases. Last but not least, the
process of integrating this knowledge into the text-to-SQL generation process
remains largely unexplored.

#### Hypothetical reasoning

On hypothetical reasoning, in Figure 3-(4), we compare the performance of
these questions and observe a significant performance gap. The EX performance
on factual questions exceeds 17%, whereas the performance on hypothetical
questions falls below 7%, confirming the difficulties involved.

###  6.3 Complexity Factors Analysis

Figure 4: GPT-3.5 + CT-3 execution accuracy performance w.r.t different
complexity level. The abbreviations used are as follows: QL for the average
question length (1: [0,15)], 2: [15,20), 3:[30,45), 4: [45,)), SQLL for the
average SQL length (1: [0,50)], 2: [50,100), 3:[100,150), 4: [150,)), VS for
the average number of value slots per question (1: [0,3)], 2: [3,6), 3:[6,9),
4: [9,)), TM for the average number of tables mentioned in each SQL (1:
[0,2)], 2: [2,3), 3:[3,5), 4: [5,)), NL for the average nested level per SQL
(1: [0,1)], 2: [1,2), 3:[2,3), 4: [3,)).

To gain insights into the SQL complexity within Archer, Figure 4 illustrates
the relationship between the EX score and various factors, including question
length, SQL length, number of value slots, number of tables mentioned in SQL,
and SQL nested level. The performance demonstrates a decreasing trend as the
question becomes longer, the SQL length increases, the number of value slots
rises, the number of tables mentioned in the SQL grows, or the SQL nested
level escalates. As shown in Table 1, Archer exhibits considerably higher
complexity across these factors when compared to other publicly available
text-to-SQL datasets.

###  6.4 Bad Case Analysis

We randomly selected 50 executable but incorrect examples generated by GPT-4 +
DIN-SQL and identified the following common error types:

#### Incorrect Logic

: GPT-4 sometimes struggles with hypothetical questions that involve complex
logic. For instance, when asked "If all cars produced by Daimler Benz company
are 4-cylinders, which 4-cylinder car needs the most fuel to drive 300
miles?", the model might generate SQL queries like WHERE T1.Cylinders = 4 AND
T4.Maker = ’Daimler Benz’. However, the correct query should be WHERE
T1.Cylinders = 4 OR T4.Maker = ‘Daimler Benz’ as there could be other
4-cylinder cars aside from Mercedes-Benz. This reveals a limitation in
comprehending the hypothetical nature of the question.

#### Incorrect Knowledge

: GPT-4 may make commonsense errors when generating the SQL, such like unit
conversions. For example, if a question requests fuel consumption in liters
per hundred kilometers, but the database only contains fuel efficiency data in
miles per gallon, the accurate conversion formula is
liters_per_hundred_kilometers = 235.2145 / MPG. However, GPT-4 employs an
incorrect formula like (100 * 3.78541) / MPG.

#### Incorrect Schema Understanding

: GPT-4 sometimes struggles to correctly link query entities to the
corresponding database columns. For example, when asked about the "average
single cylinder displacement of an 8-cylinder car", GPT-4 might generate a
query like SELECT avg(Edispl) FROM cars_data WHERE Cylinders = 8. However, in
this case, the query should calculate the average single cylinder
displacement, like SELECT AVG(1.0 * Edispl / Cylinders) AS avg_displ FROM
cars_data WHERE Cylinders = 8. This error highlights the need for the model to
understand database column names, especially when they involve abbreviations
commonly used in real-world databases. (Note that in the Spider dataset,
annotators tend to use exact column names in their queries, e.g., What is the
average edispl for all Volvos?)

#### Other Detail Errors

: For example, GPT-4 may also exhibit minor errors such as forgetting to
multiply 1.0 for float calculations.

##  7 Related Work

The earliest text-to-SQL datasets, including ATIS Dahl et al. (1994); Iyer et
al. (2017), GeoQuery Zelle and Mooney (1996); Iyer et al. (2017), Scholar Iyer
et al. (2017), Academic Li and Jagadish (2014), IMDB Yaghmazadeh et al.
(2017), Yelp Yaghmazadeh et al. (2017), Advising Finegan-Dollak et al. (2018)
and Restaurants Giordani and Moschitti (2012); Tang and Mooney (2000); Popescu
et al. (2003), were limited to a single database. Consequently, models trained
on these datasets struggled to generalize to unseen databases as they were
tested on the same database used for training. To address such limitations,
Zhong et al. (2017) introduced WikiSQL in which the databases in the test set
were not present in the training set. However, the SQL queries in WikiSQL were
generated automatically using simplified assumptions, which may not fully
capture the complexity of real-world queries.

For a comprehensive cross-domain text-to-SQL dataset, Yu et al. (2018)
presented Spider dataset, which is currently the most widely used text-to-SQL
datasets. However, Spider excludes questions that require external knowledge,
like commonsense reasoning and mathematical calculations, which are often
essential for real-world applications.

Then, Wang et al. (2020) proposed DuSQL, a Chinese cross-domain text-to-SQL
dataset that includes math-related questions. However, DuSQL’s queries and
questions are relatively simple due to automatic generation and grammar
restrictions. Dou et al. (2022) extended DuSQL with external knowledge in
their KnowSQL dataset. Unfortunately, KnowSQL is not publicly unavailable.

In real-life scenarios, databases can be dirtier with abbreviated and obscure
naming of tables, columns, and data values. To address this, Lee et al. (2021)
proposed KaggleDBQA with realistic databases. Li et al. (2023c) proposed BIRD
benchmark for the text-to-SQL task on big and dirty databases with a total
size of 33.4 GB.

In contrast to these existing text-to-SQL datasets, Archer focuses
specifically on questions involving complex reasoning and offers both English
and Chinese questions to query English databases across various domains.
Notably, all questions and SQL queries in Archer are manually annotated by
humans and thoroughly reviewed by professionals, ensuring high-quality
annotations for training and evaluation purposes.

##  8 Conclusion

In this paper, we present Archer, a complex bilingual text-to-SQL dataset with
three distinct reasoning types: arithmetic, commonsense, and hypothetical
reasoning. Experimental results on Archer, obtained from both LLMs and fine-
tuned models, suggest plenty of space for improvement.

## Acknowledgement

This work is supported by Huawei’s Dean’s Funding (C-00006589) and the UKRI
Centre for Doctoral Training in Natural Language Processing, funded by the
UKRI (grant EP/S022481/1).

## Limitations

The evaluation metric used in Archer is execution accuracy. This metric may be
perceived as an upper-bound performance measure, as SQL queries producing the
same execution results on a single database may still possess different
semantic meanings. To overcome this limitation, we plan to release a test
suite in the future that evaluates SQL queries on multiple databases, allowing
for a more comprehensive assessment of semantic accuracy.

## Ethics Statement

As mentioned in the submission, we select our databases from Spider Yu et al.
(2018), which is public for academic use and does not contain sensitive
information. The construction of our dataset involved the active involvement
of human participants. We recruited and provided training to five annotators
who possessed backgrounds in databases. These annotators were assigned the
tasks of generating questions based on the databases, writing SQL queries, and
paraphrasing the questions. Importantly, no sensitive personal information was
involved throughout this process. Our human annotation study underwent
evaluation by the departmental ethics panel, which deemed it exempt from
ethical approval. This exemption was based on the fact that all participants
were employees of the University of Edinburgh and were therefore protected by
employment law. Furthermore, participants received compensation at the
standard hourly rate designated for tutors and demonstrators at the
university. To promote academic usage, we intend to freely release the dataset
online.

## References

  * Dahl et al. (1994) Deborah A. Dahl, Madeleine Bates, Michael Brown, William Fisher, Kate Hunicke-Smith, David Pallett, Christine Pao, Alexander Rudnicky, and Elizabeth Shriberg. 1994.  Expanding the scope of the ATIS task: The ATIS-3 corpus.  In _Human Language Technology: Proceedings of a Workshop held at Plainsboro, New Jersey, March 8-11, 1994_.
  * Dou et al. (2022) Longxu Dou, Yan Gao, Xuqi Liu, Mingyang Pan, Dingzirui Wang, Wanxiang Che, Dechen Zhan, Min-Yen Kan, and Jian-Guang Lou. 2022.  Towards knowledge-intensive text-to-SQL semantic parsing with formulaic knowledge.  In _Proceedings of the 2022 Conference on Empirical Methods in Natural Language Processing_ , pages 5240–5253, Abu Dhabi, United Arab Emirates. Association for Computational Linguistics.
  * Finegan-Dollak et al. (2018) Catherine Finegan-Dollak, Li Zhang Jonathan K. Kummerfeld, Karthik Ramanathan, Sesh Sadasivam, Rui Zhang, and Dragomir Radev. 2018.  Improving text-to-sql evaluation methodology.  In _Proceedings of the 56th Annual Meeting of the Association for Computational Linguistics (Volume 1: Long Papers)_ , pages 351–360.
  * Giordani and Moschitti (2012) Alessandra Giordani and Alessandro Moschitti. 2012.  Automatic generation and reranking of sql-derived answers to nl questions.  In _Proceedings of the Second International Conference on Trustworthy Eternal Systems via Evolving Software, Data and Knowledge_ , pages 59–76.
  * Iyer et al. (2017) Srinivasan Iyer, Ioannis Konstas, Alvin Cheung, Jayant Krishnamurthy, and Luke Zettlemoyer. 2017.  Learning a neural semantic parser from user feedback.  _arXiv preprint arXiv:1704.08760_.
  * Lee et al. (2021) Chia-Hsuan Lee, Oleksandr Polozov, and Matthew Richardson. 2021.  KaggleDBQA: Realistic evaluation of text-to-SQL parsers.  In _Proceedings of the 59th Annual Meeting of the Association for Computational Linguistics and the 11th International Joint Conference on Natural Language Processing (Volume 1: Long Papers)_ , pages 2261–2273, Online. Association for Computational Linguistics.
  * Li and Jagadish (2014) Fei Li and H. V. Jagadish. 2014.  Constructing an interactive natural language interface for relational databases.  _Proceedings of the VLDB Endowment_ , 8(1):73–84.
  * Li et al. (2023a) Haoyang Li, Jing Zhang, Cuiping Li, and Hong Chen. 2023a.  Resdsql: Decoupling schema linking and skeleton parsing for text-to-sql.  _arXiv preprint arXiv:2302.05965_.
  * Li et al. (2023b) Jinyang Li, Binyuan Hui, Reynold Cheng, Bowen Qin, Chenhao Ma, Nan Huo, Fei Huang, Wenyu Du, Luo Si, and Yongbin Li. 2023b.  Graphix-t5: Mixing pre-trained transformers with graph-aware layers for text-to-sql parsing.  _arXiv preprint arXiv:2301.07507_.
  * Li et al. (2023c) Jinyang Li, Binyuan Hui, Ge Qu, Binhua Li, Jiaxi Yang, Bowen Li, Bailin Wang, Bowen Qin, Rongyu Cao, Ruiying Geng, et al. 2023c.  Can llm already serve as a database interface? a big bench for large-scale database grounded text-to-sqls.  _arXiv preprint arXiv:2305.03111_.
  * Lin et al. (2020) Xi Victoria Lin, Richard Socher, and Caiming Xiong. 2020.  Bridging textual and tabular data for cross-domain text-to-sql semantic parsing.  In _Findings of the Association for Computational Linguistics: EMNLP 2020_ , pages 4870–4888.
  * Popescu et al. (2003) Ana-Maria Popescu, Oren Etzioni, and Henry Kautz. 2003.  Towards a theory of natural language interfaces to databases.  In _Proceedings of the 8th International Conference on Intelligent User Interfaces_ , pages 149–157.
  * Pourreza and Rafiei (2024) Mohammadreza Pourreza and Davood Rafiei. 2024.  Din-sql: Decomposed in-context learning of text-to-sql with self-correction.  In _Proc. of NeurIPS 2024_.
  * Rajkumar et al. (2022) Nitarshan Rajkumar, Raymond Li, and Dzmitry Bahdanau. 2022.  Evaluating the text-to-sql capabilities of large language models.  _arXiv preprint arXiv:2204.00498_.
  * Scholak et al. (2021) Torsten Scholak, Nathan Schucher, and Dzmitry Bahdanau. 2021.  PICARD: Parsing incrementally for constrained auto-regressive decoding from language models.  In _Proceedings of the 2021 Conference on Empirical Methods in Natural Language Processing_ , pages 9895–9901, Online and Punta Cana, Dominican Republic. Association for Computational Linguistics.
  * Tang and Mooney (2000) Lappoon R. Tang and Raymond J. Mooney. 2000.  Automated construction of database interfaces: Intergrating statistical and relational learning for semantic parsing.  In _2000 Joint SIGDAT Conference on Empirical Methods in Natural Language Processing and Very Large Corpora_ , pages 133–141.
  * Wang et al. (2020) Lijie Wang, Ao Zhang, Kun Wu, Ke Sun, Zhenghua Li, Hua Wu, Min Zhang, and Haifeng Wang. 2020.  DuSQL: A large-scale and pragmatic Chinese text-to-SQL dataset.  In _Proceedings of the 2020 Conference on Empirical Methods in Natural Language Processing (EMNLP)_ , pages 6923–6935, Online. Association for Computational Linguistics.
  * Yaghmazadeh et al. (2017) Navid Yaghmazadeh, Yuepeng Wang, Isil Dillig, , and Thomas Dillig. 2017.  Sqlizer: Query synthesis from natural language.  In _International Conference on Object-Oriented Programming, Systems, Languages, and Applications, ACM_ , pages 63:1–63:26.
  * Yu et al. (2018) Tao Yu, Rui Zhang, Kai Yang, Michihiro Yasunaga, Dongxu Wang, Zifan Li, James Ma, Irene Li, Qingning Yao, Shanelle Roman, Zilin Zhang, and Dragomir Radev. 2018.  Spider: A large-scale human-labeled dataset for complex and cross-domain semantic parsing and text-to-SQL task.  In _Proceedings of the 2018 Conference on Empirical Methods in Natural Language Processing_ , pages 3911–3921, Brussels, Belgium. Association for Computational Linguistics.
  * Zelle and Mooney (1996) John M Zelle and Raymond J Mooney. 1996.  Learning to parse database queries using inductive logic programming.  In _Proceedings of the national conference on artificial intelligence_ , pages 1050–1055.
  * Zhong et al. (2017) Victor Zhong, Caiming Xiong, and Richard Socher. 2017.  Seq2sql: Generating structured queries from natural language using reinforcement learning.  _arXiv preprint arXiv:1709.00103_.

##  Appendix A Prompts

Figure 5: The example of API Doc prompt, CT-3 prompt, and CT-3+COT prompt.

##  Appendix B Execution Accuracy Algorithm

Result: Check if the execution results of p_sql and g_sql against db are
equivalent

Input: p_sql, g_sql, db

1 if  _p_sql is not valid for execution against db_ then

2 return False;

3

4else

5 Connect to the database db;

6 Execute p_sql and store the results in pred_res;

7 Execute g_sql and store the results in gold_res;

8 Close the database connection;

9 if  _pred_res is exactly equal to gold_res_ then

10 return True;

11

12 else if  _the number of rows or columns in pred_res and gold_res are
different_ then

13 return False;

14

15 else if  _g_sql contains an outermost ORDER BY clause_ then

16 Compare sets of columns in pred_res and gold_res;

17 return True if equivalent, False otherwise;

18

19 else

20 Calculate element frequency of each row and column in both pred_res and
gold_res;

21 Check if every frequency in pred_res is present in gold_res for both rows
and columns;

22 return True if all frequencies match, False otherwise;

23

24 end if

25

26 end if

Algorithm 1 Execution Match Check

##  Appendix C Performance w.r.t Different Reasoning

Reasoning Types | EN | ZH
---|---|---
GPT-3.5 + API Doc | GPT-3.5 + CT-3 | GPT-3.5 + CT-3 + COT | GPT-3.5 + API Doc | GPT-3.5 + CT-3 | GPT-3.5 + CT-3 + COT
VA | EX | VA | EX | VA | EX | VA | EX | VA | EX | VA | EX
A | 78.78 | 23.02 | 83.81 | 24.46 | 79.50 | 25.54 | 84.17 | 19.06 | 88.85 | 21.58 | 77.34 | 19.42
A+C | 86.60 | 14.05 | 85.95 | 13.73 | 78.43 | 15.69 | 89.87 | 12.09 | 91.18 | 16.67 | 76.80 | 16.99
A+H | 78.95 | 9.21 | 83.77 | 7.46 | 67.54 | 4.82 | 83.77 | 6.58 | 92.54 | 6.14 | 64.47 | 4.82
A+C+H | 85.65 | 4.35 | 82.61 | 5.22 | 73.04 | 3.48 | 86.09 | 2.61 | 92.61 | 3.91 | 70.43 | 4.35

Table 3: Performance with respect to different reasoning types.

Reasoning Types | EN | ZH
---|---|---
GPT-3.5 + API Doc | GPT-3.5 + CT-3 | GPT-3.5 + CT-3 + COT | GPT-3.5 + API Doc | GPT-3.5 + CT-3 | GPT-3.5 + CT-3 + COT
VA | EX | VA | EX | VA | EX | VA | EX | VA | EX | VA | EX
Addition | 80.95 | 33.33 | 88.10 | 23.81 | 73.81 | 16.67 | 92.86 | 30.95 | 95.24 | 26.19 | 78.57 | 19.05
Subtraction | 72.41 | 15.52 | 80.17 | 22.41 | 75.00 | 23.28 | 81.03 | 16.38 | 87.93 | 13.79 | 80.17 | 13.79
Multiplication | 84.52 | 26.19 | 85.12 | 26.19 | 81.55 | 28.57 | 85.71 | 20.24 | 89.88 | 25.60 | 74.40 | 20.24
Division | 81.25 | 16.07 | 79.46 | 18.75 | 76.79 | 28.57 | 80.36 | 12.50 | 85.71 | 18.75 | 68.75 | 16.96

Table 4: Performance with respect to different arithmetic operations on data
with arithmetic reasoning only.

Reasoning Types | EN | ZH
---|---|---
GPT-3.5 + API Doc | GPT-3.5 + CT-3 | GPT-3.5 + CT-3 + COT | GPT-3.5 + API Doc | GPT-3.5 + CT-3 | GPT-3.5 + CT-3 + COT
VA | EX | VA | EX | VA | EX | VA | EX | VA | EX | VA | EX
w/o knowledge | 86.19 | 9.89 | 84.51 | 10.07 | 76.12 | 10.45 | 88.25 | 8.02 | 91.79 | 11.19 | 74.07 | 11.57
w/ knowledge | 83.58 | 9.89 | 87.13 | 12.31 | 74.81 | 13.43 | 85.07 | 9.70 | 87.69 | 12.13 | 73.32 | 13.62

Table 5: Performance for questions needed commonsense reasoning with and
without explicit knowledge input.

Reasoning Types | EN | ZH
---|---|---
GPT-3.5 + API Doc | GPT-3.5 + CT-3 | GPT-3.5 + CT-3 + COT | GPT-3.5 + API Doc | GPT-3.5 + CT-3 | GPT-3.5 + CT-3 + COT
VA | EX | VA | EX | VA | EX | VA | EX | VA | EX | VA | EX
Hypothetical | 82.31 | 6.77 | 83.19 | 6.33 | 70.31 | 4.15 | 84.93 | 4.59 | 92.58 | 5.02 | 67.47 | 4.59
Factual | 82.53 | 17.25 | 83.84 | 18.12 | 79.48 | 20.09 | 87.77 | 15.5 | 89.52 | 17.47 | 79.48 | 16.81

Table 6: Performance comparison for hypothetical questions and corresponding
factual questions.

##  Appendix D Archer Examples

Figure 6: The example of Archer data requiring Arithmetic Reasoning. Figure 7:
The example of Archer data requiring Commonsense Reasoning. Figure 8: The
example of Archer data requiring Hypothetic Reasoning.


# Photonic chip-based low noise microwave oscillator

Igor Kudelin  National Institute of Standards and Technology, 325 Broadway,
Boulder, CO 80305, USA  Department of Physics, University of Colorado Boulder,
440 UCB Boulder, CO 80309, USA  William Groman  National Institute of
Standards and Technology, 325 Broadway, Boulder, CO 80305, USA  Department of
Physics, University of Colorado Boulder, 440 UCB Boulder, CO 80309, USA  Qing-
Xin Ji  T. J. Watson Laboratory of Applied Physics, California Institute of
Technology, Pasadena, CA 91125, USA  Joel Guo  Department of Electrical and
Computer Engineering, University of California, Santa Barbara, Santa Barbara,
California 93106, USA  Megan L. Kelleher  National Institute of Standards and
Technology, 325 Broadway, Boulder, CO 80305, USA  Department of Physics,
University of Colorado Boulder, 440 UCB Boulder, CO 80309, USA  Dahyeon Lee
National Institute of Standards and Technology, 325 Broadway, Boulder, CO
80305, USA  Department of Physics, University of Colorado Boulder, 440 UCB
Boulder, CO 80309, USA  Takuma Nakamura  National Institute of Standards and
Technology, 325 Broadway, Boulder, CO 80305, USA  Department of Physics,
University of Colorado Boulder, 440 UCB Boulder, CO 80309, USA  Charles A.
McLemore  National Institute of Standards and Technology, 325 Broadway,
Boulder, CO 80305, USA  Department of Physics, University of Colorado Boulder,
440 UCB Boulder, CO 80309, USA  Pedram Shirmohammadi  Department of Electrical
and Computer Engineering, University of Virginia, Charlottesville, VA 22904,
USA  Samin Hanifi  Department of Electrical and Computer Engineering,
University of Virginia, Charlottesville, VA 22904, USA  Haotian Cheng
Department of Applied Physics, Yale University, New Haven, Connecticut 06520,
USA  Naijun Jin  Department of Applied Physics, Yale University, New Haven,
Connecticut 06520, USA  Sam Halliday  Department of Applied Physics, Yale
University, New Haven, Connecticut 06520, USA  Zhaowei Dai  Department of
Applied Physics, Yale University, New Haven, Connecticut 06520, USA  Lue Wu
T. J. Watson Laboratory of Applied Physics, California Institute of
Technology, Pasadena, CA 91125, USA  Warren Jin  Department of Electrical and
Computer Engineering, University of California, Santa Barbara, Santa Barbara,
California 93106, USA  Yifan Liu  National Institute of Standards and
Technology, 325 Broadway, Boulder, CO 80305, USA  Department of Physics,
University of Colorado Boulder, 440 UCB Boulder, CO 80309, USA  Wei Zhang  Jet
Propulsion Laboratory, California Institute of Technology, Pasadena,
California, USA  Chao Xiang  Department of Electrical and Computer
Engineering, University of California, Santa Barbara, Santa Barbara,
California 93106, USA  Vladimir Iltchenko  Jet Propulsion Laboratory,
California Institute of Technology, Pasadena, California, USA  Owen Miller
Department of Applied Physics, Yale University, New Haven, Connecticut 06520,
USA  Andrey Matsko  Jet Propulsion Laboratory, California Institute of
Technology, Pasadena, California, USA  Steven Bowers  Department of Electrical
and Computer Engineering, University of Virginia, Charlottesville, VA 22904,
USA  Peter T. Rakich  Department of Applied Physics, Yale University, New
Haven, Connecticut 06520, USA  Joe C. Campbell  Department of Electrical and
Computer Engineering, University of Virginia, Charlottesville, Virginia 22904,
USA  John E. Bowers  Department of Electrical and Computer Engineering,
University of California, Santa Barbara, Santa Barbara, California 93106, USA
Kerry Vahala  T. J. Watson Laboratory of Applied Physics, California Institute
of Technology, Pasadena, CA 91125, USA  Franklyn Quinlan  National Institute
of Standards and Technology, 325 Broadway, Boulder, CO 80305, USA  Scott A.
Diddams  National Institute of Standards and Technology, 325 Broadway,
Boulder, CO 80305, USA  Department of Physics, University of Colorado Boulder,
440 UCB Boulder, CO 80309, USA  Electrical Computer and Energy Engineering,
University of Colorado, Boulder, CO 80309, USA

###### Abstract

Numerous modern technologies are reliant on the low-phase noise and exquisite
timing stability of microwave signals. Substantial progress has been made in
the field of microwave photonics, whereby low noise microwave signals are
generated by the down-conversion of ultra-stable optical references using a
frequency comb. Such systems, however, are constructed with bulk or fiber
optics and are difficult to further reduce in size and power consumption. Our
work addresses this challenge by leveraging advances in integrated photonics
to demonstrate low-noise microwave generation via two-point optical frequency
division. Narrow linewidth self-injection locked integrated lasers are
stabilized to a miniature Fabry-Pérot cavity, and the frequency gap between
the lasers is divided with an efficient dark-soliton frequency comb. The
stabilized output of the microcomb is photodetected to produce a microwave
signal at 20 GHz with phase noise of -96 dBc/Hz at 100 Hz offset frequency
that decreases to -135 dBc/Hz at 10 kHz offset–values which are unprecedented
for an integrated photonic system. All photonic components can be
heterogeneously integrated on a single chip, providing a significant advance
for the application of photonics to high-precision navigation, communication
and timing systems.

## Introduction

Low noise microwave signals with high timing stability are a critical enabler
of modern science and multiple technologies of broad societal impact.
Positioning and navigation, advanced communications, high-fidelity radar and
sensing, and high-performance atomic clocks are all dependent upon low-phase
noise microwave signals. These rapidly developing technologies are constantly
intensifying the demand for microwave sources beyond current capabilities,
while imposing harsher restrictions on system size and power consumption. In
this landscape, photonic lightwave systems provide unique advantages over more
conventional electronic approaches for generating low-noise microwaves. In
particular, the extremely low loss and high quality factors of photonic
resonators are fundamental to electromagnetic (EM) oscillators with the lowest
noise and highest spectral purity matei2017 . Coupled to this is the
introduction and rapid development of frequency combs in the last few decades
that enable seamless coherent synthesis across the full EM spectrum
diddams2020optical . This includes the frequency division of a 200-500 THz
optical carrier down to a 10 GHz microwave with unrivaled long- and short-term
stability nakamura2020coherent ; xie2017photonic ; fortier2011generation ;
kalubovilage2022x .

Figure 1: Concept of 2-point optical frequency division for low-noise
microwave generation. (a) Two semiconductor lasers are self-injection locked
(SIL) to chip-based spiral resonators. The optical modes of the spiral
resonators are aligned, using temperature control, to the modes of the high-
finesse Fabry-Pérot (F-P) cavity for PDH locking. (b) A microcomb is generated
in a coupled dual-ring resonator and is heterodyned with the two stabilized
lasers. The beat-notes are mixed to produce an intermediate frequency,
$f_{IF}$, that is phase locked by feedback to the current supply of the
microcomb seed laser. (c) A modified uni-travelling carrier (MUTC)
photodetector chip is used to convert the microcomb’s optical output to a 20
GHz microwave signal.

However, a significant challenge of these approaches is the relatively large
size and power consumption that restrict their use to laboratory environments.
Greater impact and widespread use can be realized with a low-noise microwave
generator that has a compact and portable form factor for operation in remote
and mobile platforms. Our work addresses and overcomes this challenge through
the optimal implementation of 2-point optical frequency division (2P-OFD) with
integrated photonic components. Figure 1 illustrates the principles and
advantages of the 2P-OFD system we introduce. Our work provides a means to
significantly reduce microwave phase noise in a volume of 10’s of milliliters
instead of 10’s of liters, while similarly reducing the required power by a
factor $10^{3}$ to the 1 Watt level.

All OFD systems start with a stable optical frequency reference. Typically
this is a laboratory fiber or solid state laser that is frequency-stabilized
to a large evacuated Fabry-Pérot (F-P) cavity matei2017 ; Martin2012 .
Instead, we introduce an optimal combination of low-noise chip-integrated
semiconductor lasers jin2021hertz ; xiang2021high  and a new F-P concept that
can be miniaturized to $<1\rm{cm}^{3}$ and chip-integrated without the need
for high-vacuum enclosure jin2022micro ; McLemore2022 ; kelleher2023compact ;
liu2023cmr . The frequency noise of two semiconductor lasers near 1560 nm is
reduced by 40 dB through self-injection locking (SIL) to high-Q Si3N4 spiral
resonators Li:21 ; guo2022chip . This passive stabilization of the SIL laser
enables further noise reduction, by up to 60 dB, through Pound–Drever–Hall
(PDH) locking to a miniature F-P cavity, reaching the cavity’s thermal noise
limit drever1983laser ; guo2022chip .

In OFD, the optical phase noise of the reference is then reduced by the square
of the ratio of its frequency to that of the microwave output. This is a
powerful means for noise reduction by a factor as large as $(2\times
10^{14}/1\times 10^{10})^{2}=4\times 10^{8}$, or equivalently 86 dB. However,
significant electrical power is required for generating a frequency comb
spanning $\sim$200 THz with mode spacing of 10-20 GHz. Instead, we employ the
simplified approach of 2P-OFD swann2011microwave ; papp2014microresonator ;
li2014electro ; kwon2022ultrastable , with comb bandwidth on the order of 1
THz. This results in a lower division factor, but also significantly reduced
size and power requirements. Such a trade-off still allows us to reach an
unprecedented microwave phase noise level with integrated photonics due to the
intrinsic low noise of the optical references employed.

In our system, the frequency division is implemented with another injection-
locked laser that generates a microcomb in a zero group velocity dispersion
(GVD) resonator, engineered by two coupled rings in a Vernier configuration
ji2023engineered . The microcomb operates without the need for optical
amplification, and $\sim$30% of the input pump power of $\sim$100 mW is
efficiently transferred to the comb which spans nearly 10 nm. 2P-OFD in then
implemented by heterodyning the two SIL lasers with the closest comb teeth to
produce two beat-notes. These are mixed to provide a servo control signal at
an intermediate frequency which is independent of the microcomb center
frequency. Upon phase locking of the intermediate frequency, the noise of the
microcomb is dramatically reduced. Photodetection of the stabilized microcomb
output with a high-power and high-linearity modified uni-traveling-carrier
(MUTC) photodetector zang2018reduction  provides a 20 GHz microwave signal
with phase noise of -135 dBc/Hz at 10 kHz offset frequency. This level of
noise has not been achieved previously for a system that employs integrated
photonic components. We note that the critical photonic devices used in our
system can be further integrated to a single chip without the need for fiber
or semiconductor amplifiers or optical isolators, providing ultrastable
microwave generation in a compact form-factor. This advance is important for
future applications of high-performance microwave sources with compact size
and low-power usage that will operate beyond the research labs.

Figure 2: Experimental setup. Two distributed feedback (DFB) lasers at 1557.3
nm and 1562.5 nm are self-injection locked to Si3N4 spiral resonators,
amplified, and locked to the same miniature Fabry-Pérot cavity. A 6 nm broad
frequency comb with a $\sim$20 GHz repetition rate is generated in a coupled-
rings resonator. The microcomb is seeded by an integrated DFB laser, which is
self-injection locked to the coupled-ring microresonator. The frequency comb
passes through a notch filter to suppress the central line and then amplified
to 60 mW total optical power. The frequency comb is split to beat with each of
the PDH-locked SIL CW references. Two beat-notes are amplified, filtered and
then mixed together to produce $f_{IF}$, which is phase locked to a reference
frequency. The feedback for microcomb stabilization is provided to the current
supply of the microcomb seed laser. Lastly, part of the generated microcomb is
detected in a modified uni-travelling carrier (MUTC) detector to extract the
low-noise 20 GHz signal. Bottom row: Photographs of the key photonic
components used in low noise microwave generation.

## Experiment and Results

A technical illustration of the setup used for frequency comb stabilization
and stable microwave generation is shown in Fig. 2. Here we elaborate on the
operation and characteristics of the key components, concluding with a
description of how they fit together cohesively to produce low phase noise
microwave signals.

Miniature Fabry-Pérot cavity: The phase and frequency stability of the
generated microwave signal is ultimately derived from that of the ultrastable
optical reference. The lowest noise optical references are lasers locked to
vacuum-gap F-P cavities, where fractional frequency stability as low as
4$\times$ 10-17 has been demonstrated with 212 mm long, cryogenic cavity
systems matei2017 . Instead, we employ an integrable cavity design based on a
compact, rigidly held cylindrical F-P optical reference cavity that supports
fractional frequency stability at the 10-14 level kelleher2023compact . Ultra-
low expansion glass with 1 m radius of curvature and an ultra-low expansion
glass spacer compose the 6.3 mm long cavity with finesse of $\sim$900,000
(Q$\sim$5 billion) and overall volume of less than 9 cm3. The cavity is
thermal noise limited for offset frequencies ranging from 1 Hz to 10 kHz.
Moreover, the relative phase noise between two reference lasers locked to the
same cavity takes advantage of large common-mode rejection (CMR), reaching 40
dB rejection for cavity modes spaced by 1 THz liu2023cmr . When combined with
2P-OFD, the cavity noise is expected to be reduced by $\sim$80 dB when
projected onto our microwave carrier.

Figure 3: Microcomb characterisation. (a) Single side-band (SSB) Phase noise
scaled to 10 GHz of free-running 20 GHz microcomb (blue), locked 20 GHz
microwave (red), and locked 20 GHz microwave after regenerative frequency
division by 2 (green). (b) Optical spectrum of microcomb (grey), and SIL
lasers (green and turquoise). RF spectra of 20 GHz signal (c) free-running
(RBW 100 Hz) and (d) locked (RBW 1 Hz).

Self-injection locked lasers: To achieve high stability performance in the
proposed system, it is crucial to use narrow-linewidth and frequency-stable
lasers. This is because electrical noise from the individual laser locking
circuits does not experience CMR, and this noise is reduced only by the
2P-OFD. To address this issue and reach the thermal noise floor of the F-P
cavity, we employ SIL lasers that are both integrable and have noise
performance equivalent to much larger laboratory fiber lasersjin2021hertz ;
Li:21 ; guo2022chip . With reference to Fig. 2, two semiconductor DFBs are
pre-stabilized by self-injection locking to a high-Q Si3N4 spiral resonator
Li:21 . When the forward and backward fields between the DFB lasers and the
Si3N4 resonators are in phase, resonant backscattered light is then fed back
to the DFB, anchoring each laser wavelength to the corresponding Si3N4
resonance and significantly suppressing its frequency noise. The length and Q
of the integrated resonator determines the ultimate phase noise of the SIL
laser li1989analysis , and here we employ a spiral with length of 1.41 m that
is fabricated on $\sim 1{\rm cm^{2}}$ of silicon. The free-spectral range
(FSR) is 135 MHz. The intrinsic and loaded Q factors of the two spiral
resonators are 164 and 126 million, respectively, enabling phase noise of -85
dBc/Hz at 10 kHz offset Li:21 . The output of the SIL lasers are amplified
using commercial EDFAs to $\sim$30 mW and then further stabilized via PDH
locking to the miniature F-P cavity. In this setup, the primary actuator for
PDH stabilization is an acousto-optic modulator; however, the PDH error signal
is also fed back to the electro-optical modulator (EOM) to further increase
the bandwidth and noise reduction of the PDH servo endo2018residual . The in-
loop phase noise of the PDH locking of SIL lasers are presented in
Supplementary Information.

Microcomb: Robust and low-noise optical frequency comb generation with 10-20
GHz repetition rate and broad optical coverage is challenging. Here, we use a
Si3N4 microresonator fabricated at a CMOS foundry to generate mode-locked
microcombs jin2021hertz . To produce dark soliton microcombs with higher
bandwidth, we use a dual coupled-ring resonator with FSR of 20 GHz
ji2023engineered , where the zero GVD wavelength is tuned to $\sim$1560 nm
using integrated heaters ji2023integrated . In addition, such microcomb states
have high pump-to-comb conversion efficiency, benefiting microwave generation
in a low-SWaP system. To generate the comb, a commercial DFB laser without
optical amplification is self-injection locked to the dual coupled-ring
resonator, which narrows the linewidth of the pump laser and generates a
reasonably stable 20 GHz comb pavlov2018narrow ; shen2020integrated ;
jin2021hertz . Following the coupled rings, a notch filter is used to suppress
the central (seed) comb line in order to avoid saturation in an EDFA, which
amplifies the frequency comb up to 60 mW (Fig. 2). A typical comb spectrum
after the EDFA is shown in Fig. 3(b).

Figure 4: Phase noise comparison of microwave generations based on microcomb,
scaled to 10 GHz. The platforms are categorized based on the integration
capability of microcomb generator and the reference laser source, excluding
the inter-connecting optical/electrical parts. Filled (blank) squares are
based on the OFD (stand-alone microcomb) approach. (i) 22 GHz on-chip silica
microcomb yi2015soliton , (ii) 5 GHz hybridly integrated Si3N4 microcomb
jin2021hertz , (iii) 10.8 GHz photonic chip-based Si3N4 microcomb
liu2020photonic , (iv) 22 GHz on-chip silica microcomb li2012low , (v)
miniaturized MgF2 microcomb liang2015high , (vi) 100 GHz photonic chip-based
Si3N4 microcomb sun2023integrated , (vii) 22 GHz Fibre-stabilised SiO2
microcomb kwon2022ultrastable , (viii) miniaturized MgF2 microcomb
matsko2016turn , (ix) 14 GHz MgF2 microcomb pumped by an ultrastable laser
weng2019spectral , (x) 14 GHz microcomb-based transfer oscillator
lucas2020ultralow . .

Microcomb stabilization and microwave generation: As outlined above, in this
work we use 2-point locking to realize optical frequency division for phase
noise reduction. With appropriate fiber-optic couplers and filters, we design
a receiver system to separate heterodyne beat-notes and 20 GHz microwave
generation. The two beat-notes between the microcomb and each CW laser are
given by $f_{\rm b1}=\nu_{0}-n\cdot f_{\rm rep}-\nu_{\rm CW1}$ and $f_{\rm
b2}=\nu_{\rm CW2}-\nu_{0}-m\cdot f_{\rm rep}$ (see Fig. 1). These beats are
filtered, amplified, and mixed together to produce the intermediate frequency
($f_{\rm IF}$), which is then phase-locked to a stable microwave reference,
via feedback to the current of the microcomb seed laser ji2023engineered . The
stabilization of $f_{\rm IF}$ is the final step to generate a low-noise
microwave via 2P-OFD:

$f_{\rm IF}=f_{\rm b1}+f_{\rm b2}=\nu_{\rm CW2}-\nu_{\rm CW1}+(n+m)f_{\rm
rep}$

in which $\nu_{CW1}$ and $\nu_{CW1}$ are the carrier frequencies of the
optical references, and $n+m$ is the value of the optical frequency division
(32 in our case), which amounts to 30 dB of noise reduction. Note that 2-point
locking does not depend on the noise of the microcomb central frequency
$\nu_{0}$. Thus, the instability of the microcomb repetition rate can be
represented as:

$\delta f_{\rm rep}^{2}=\frac{\delta(\nu_{\rm CW2}-\nu_{\rm CW1})^{2}+\delta
f_{\rm IF}^{2}}{(n+m)^{2}}.$

While the phase noise of $f_{IF}$, $\nu_{\rm CW2}$ and $\nu_{\rm CW2}$ are all
reduced by 2P-OFD, their servo control and residual noise can be limiting
factors in the achievable microwave phase noise. These, and other limitations
to the achievable phase, noise are discussed in greater detail in the
Supplementary Information.

The stabilized microcomb output is directed to a MUTC photodiode, which
provides exceptional linearity and large microwave powers peng2020photonic ;
Xie:14 . We tune the bias voltage of the MUTC operation for $\approx 40$ dB
rejection of amplitude-to-phase noise conversion, while generating a 20 GHz
power of -10 dBm at 5 mA of average photocurrent. The 20 GHz microwave signal
is filtered and amplified to +3 dBm and sent to a measurement system where the
20 GHz signal is first downconverted to 47 MHz by mixing with an ultra-stable
microwave from a self-referenced Er:fiber mode-locked laser
nakamura2020coherent . The 47 MHz signal is characterized with a commercial
phase noise analyzer (53100A Microchip) and results are presented in Fig.
3(a). Here we have scaled the measured 20 GHz phase noise to 10 GHz by
subtracting 6 dB. This yields -102 dBc/Hz at 100 Hz, which decreases to -141
dBc/Hz at 10 kHz and reaches its minimum of -145 dBc/Hz at 40 kHz frequency
offset. We also compare with a 10 GHz carrier that we generate from 20 GHz
with a regenerative divide-by-2 circuit. Compared to the free-running
microcomb generation, we achieved more than 50 dB phase noise improvement for
offset frequencies below 10 kHz. Additional details on the phase noise
measurement and divider are in the Supplementary Information.

## Discussion and further integration

Figure 5: Schematic design of a photonic microwave oscillator on a single
chip. The integrated system employs the same key photonic elements used in
this work. Two spiral-resonator SIL lasers are PDH-locked to the same micro
F-P cavity with two EOMs in series for each SIL laser—the first for fast phase
correction and the second for PDH sidebands. The right side of the schematic
shows the F-P cavity interface, where the two SIL laser paths are fed through
an interferometer with an embedded polarization splitting grating. This serves
as a reflection cancellation circuit while also shaping the planar waveguide
mode to match the F-P mode cheng2022chip . The reflection from the F-P cavity
is then detected by the right-most detector. Inset: Photo of the miniature F-P
cavity, consisted of micro-fabricated mirrors jin2022micro , with overall
volume of $\sim$1 cm3.

Figure 4 places the level of phase noise we achieve in context with other
photonic approaches, including recent works based on microcombs and mode-
locked laser frequency combs. The comparison is classified by level of
photonic integration of the microcomb source and pumping/reference lasers, as
applicable. It is also noted that some of the microcomb systems require the
assistance of a fiber-based frequency comb (Fig. 4 (ix), (x) weng2019spectral
; lucas2020ultralow ). The phase noise performance of other systems, which
could be chip integrated (Fig. 4 (ii), (iii) jin2021hertz ; liu2020photonic ),
is more than 30 dB greater than the results we present, with the exception of
the recent work by Sun, et al. (vi) sun2023integrated . Other notable works on
low-noise microwave generation in a low-SWaP systems, which are not shown in
Fig. 4, include ’quite point’ operation yi2017single ; lucas2020ultralow ;
yang2021dispersive ; yao2022soliton , single laser optical frequency division
zhao2023all , and commercial products OEwaves ; li2023small ; Quantx .

To the best of our knowledge, this work provides the best phase noise
performance in the frequency range of 200 Hz - 40 kHz for microcomb-based
systems. Importantly, it does so with integrated photonic components that can
all be further combined onto a single chip with total volume of the photonic
components of approximately $\rm{1cm^{3}}$. A concept of such a fully-
integrated system is shown in Fig. 5(a) and would consist of heterogeneously
integrated lasers at 1560 nm guo2022chip , spiral resonators Li:21  for SIL, a
coupled-ring microcomb resonator ji2023engineered , photodetectors
zang2018reduction  and a micro-fabricated F-P cavity that does not require
high vacuum jin2022micro ; liu2023cmr .

Previous work already lays out the steps for heterogeneous integration of
lasers, resonators and photodiodes. For example, InP lasers and Si3N4
resonators have been integrated on the same chip with coupling between the
optical gain and low-loss waveguide layers facilitated by adiabatic tapers,
reaching down to 0.5 dB/m with a second deeply buried Si3N4 waveguide layer
xiang2021high ; xiang2023three . This same heterogeneously integration
promises isolator-free operation xiang2023three . A similar strategy has been
employed for laser integration with thick Si3N, anomalous dispersion
microcombs on the same chip xiang2021laser , which can be applied to the zero-
dispersion microcombs used in this work. Laser integration with modulators,
detectors, and optical amplifiers based on the heterogeneous InP/Si platform
has also been previously demonstrated xie2019heterogeneous ;
davenport2016heterogeneous  and can be utilized for full integration of the
optical components comprising the PDH locking system idjadi2017integrated .
Note that previously, the various laser, modulator, and detector InP epitaxial
stacks were modified so that they could be etched simultaneously; to account
for the MUTC photodiodes zang2018reduction , similar modifications will need
to be done to ensure process compatibility.

The integration of the active and passive components on a single platform
greatly reduces loss (between fiber and chip) and removes the need for optical
amplifiers we have employed in the present work. In such a case, a few tens of
mW of optical power is required to pump the resonator such that a comb with a
few mW of optical power and several $\mu$W per mode is realistic.
Additionally, for the SIL lasers only several mW of DFB optical power is
required to provide a hundred $\mu$W of optical power to heterodyne with the
comb and achieve the SNR necessary to match the performance presented in this
work. For recent integrated lasers, these powers are realistic xiang2021high .
Additional considerations on required optical power are discussed in
Supplementary Information.

Integration of the FP cavity has been an outstanding challenge, but recent
developments in micro-fabricated mirrors jin2022micro  and compact thermal-
noise limited F-P designs McLemore2022  provide new integration opportunities.
Critically, it has been shown that 2P-OFD does not require FP operation in
high-vacuum due to common mode rejection liu2023cmr , significantly
simplifying future integration. Fig. 5 shows a $\rm{1cm^{3}}$ cavity with
fabricated micro-mirrors and details on an integration strategy with the the
SIL lasers and microcomb. A planar waveguide feeds an inverse-designed
polarization splitting grating embedded in an interferometer, which serves to
shape the beam for coupling light to the cavity while also providing the
cavity-reflected PDH locking signal and laser isolation cheng2022chip .
Preliminary measurements described in the Supplement demonstrate the
feasibility of this approach. The F-P cavity and a GRIN lens can be bonded on
top of the polarization splitting grating in a hybrid flip-chip fashion for a
single-chip, cavity-integrated low-noise microwave generator unit.

In this integration scheme, the AOMs can be replaced with a combniation of
slow feedback to the integrated heaters in the spirals and fast feedback to an
external EOM. The thermal tuning can reach bandwidth of a few kHz
joshi2016thermally ; xiang2023three , while the fast feedback with nearly 1
MHz of bandwidth could be provided by the EOM endo2018residual . We estimate
that this combination can provide 40 dB feedback gain at 10 kHz offset
frequency to match the phase noise presented in this work.

In summary, we have demonstrated an integrated photonic approach to OFD that
produces 20 GHz microwave signals with phase noise of -135 dBc/Hz at 10 kHz
offset. This is a value typical of much larger existing commercial systems.
This is accomplished with a unique combination of low-noise integrated lasers,
an efficient dark-soliton frequency comb and new advances in a miniature F-P
optical cavity. Significantly, our approach provides a route to full
integration on single chip with volume of the photonic components on the order
of ${\rm 1cm^{3}}$. This advance in integrated photonic low noise microwave
generation holds promise for compact, portable and low-cost microwave
synthesis for a wide variety of demanding applications in navigation,
communications and precise timing.

## References

  * (1) D. Matei, T. Legero, S. Häfner, C. Grebing, R. Weyrich, W. Zhang, L. Sonderhouse, J. Robinson, J. Ye, F. Riehle, et al., “1.5 $\mu$m lasers with sub-10 mhz linewidth,” Physical Review Letters, vol. 118, no. 26, p. 263202, 2017.
  * (2) S. A. Diddams, K. Vahala, and T. Udem, “Optical frequency combs: Coherently uniting the electromagnetic spectrum,” Science, vol. 369, no. 6501, p. eaay3676, 2020.
  * (3) T. Nakamura, J. Davila-Rodriguez, H. Leopardi, J. A. Sherman, T. M. Fortier, X. Xie, J. C. Campbell, W. F. McGrew, X. Zhang, Y. S. Hassan, D. Nicolodi, K. Beloy, A. D. Ludlow, S. A. Diddams, and F. Quinlan, “Coherent optical clock down-conversion for microwave frequencies with $10^{-18}$ instability,” Science, vol. 368, no. 6493, pp. 889–892, 2020.
  * (4) X. Xie, R. Bouchand, D. Nicolodi, M. Giunta, W. Hänsel, M. Lezius, A. Joshi, S. Datta, C. Alexandre, M. Lours, et al., “Photonic microwave signals with zeptosecond-level absolute timing noise,” Nature Photonics, vol. 11, no. 1, pp. 44–47, 2017.
  * (5) T. M. Fortier, M. S. Kirchner, F. Quinlan, J. Taylor, J. Bergquist, T. Rosenband, N. Lemke, A. Ludlow, Y. Jiang, C. Oates, and S. A. Diddams, “Generation of ultrastable microwaves via optical frequency division,” Nature Photonics, vol. 5, no. 7, pp. 425–429, 2011.
  * (6) M. Kalubovilage, M. Endo, and T. R. Schibli, “X-band photonic microwaves with phase noise below- 180 dbc/hz using a free-running monolithic comb,” Optics Express, vol. 30, no. 7, pp. 11266–11274, 2022.
  * (7) M. J. Martin and J. Ye, “High-precision laser stabilization via optical cavities,” Optical Coatings and Thermal Noise in Precision Measurement, GM Harry, T. Bodiya, R. DeSalvo., Eds, pp. 237–258, 2012.
  * (8) W. Jin, Q.-F. Yang, L. Chang, B. Shen, H. Wang, M. A. Leal, L. Wu, M. Gao, A. Feshali, M. Paniccia, K. J. Vahala, and J. E. Bowers, “Hertz-linewidth semiconductor lasers using cmos-ready ultra-high-q microresonators,” Nature Photonics, vol. 15, no. 5, pp. 346–353, 2021.
  * (9) C. Xiang, J. Guo, W. Jin, L. Wu, J. Peters, W. Xie, L. Chang, B. Shen, H. Wang, Q.-F. Yang, D. Kinghorn, M. Paniccia, K. J. Vahala, P. A. Morton, and J. E. Bowers, “High-performance lasers for fully integrated silicon nitride photonics,” Nature Communications, vol. 12, no. 1, pp. 1–8, 2021.
  * (10) N. Jin, C. A. McLemore, D. Mason, J. P. Hendrie, Y. Luo, M. L. Kelleher, P. Kharel, F. Quinlan, S. A. Diddams, and P. T. Rakich, “Micro-fabricated mirrors with finesse exceeding one million,” Optica, vol. 9, no. 9, pp. 965–970, 2022.
  * (11) C. A. McLemore, N. Jin, M. L. Kelleher, J. P. Hendrie, D. Mason, Y. Luo, D. Lee, P. Rakich, S. A. Diddams, and F. Quinlan, “Miniaturizing ultrastable electromagnetic oscillators: Sub-$10^{14}$ frequency instability from a centimeter-scale fabry-perot cavity,” Physical Review Applied, vol. 18, p. 054054, 11 2022.
  * (12) M. L. Kelleher, C. A. McLemore, D. Lee, J. Davila-Rodriguez, S. A. Diddams, and F. Quinlan, “Compact, portable, thermal-noise-limited optical cavity with low acceleration sensitivity,” Optics Express, vol. 31, no. 7, pp. 11954–11965, 2023.
  * (13) Y. Liu, C. A. McLemore, M. Kelleher, D. Lee, T. Nakamura, N. Jin, S. Schima, P. Rakich, S. A. Diddams, and F. Quinlan, “Thermal-noise-limited, compact optical reference cavity operated without a vacuum enclosure,” arXiv preprint arXiv:2307.04758, 2023.
  * (14) B. Li, W. Jin, L. Wu, L. Chang, H. Wang, B. Shen, Z. Yuan, A. Feshali, M. Paniccia, K. J. Vahala, and J. E. Bowers, “Reaching fiber-laser coherence in integrated photonics,” Opt. Lett., vol. 46, pp. 5201–5204, Oct 2021\.
  * (15) J. Guo, C. A. McLemore, C. Xiang, D. Lee, L. Wu, W. Jin, M. Kelleher, N. Jin, D. Mason, L. Chang, et al., “Chip-based laser with 1-hertz integrated linewidth,” Science Advances, vol. 8, no. 43, p. eabp9006, 2022.
  * (16) R. W. Drever, J. L. Hall, F. V. Kowalski, J. Hough, G. Ford, A. Munley, and H. Ward, “Laser phase and frequency stabilization using an optical resonator,” Applied Physics B, vol. 31, pp. 97–105, 1983.
  * (17) W. C. Swann, E. Baumann, F. R. Giorgetta, and N. R. Newbury, “Microwave generation with low residual phase noise from a femtosecond fiber laser with an intracavity electro-optic modulator,” Optics Express, vol. 19, no. 24, pp. 24387–24395, 2011.
  * (18) S. B. Papp, K. Beha, P. Del’Haye, F. Quinlan, H. Lee, K. J. Vahala, and S. A. Diddams, “Microresonator frequency comb optical clock,” Optica, vol. 1, no. 1, pp. 10–14, 2014.
  * (19) J. Li, X. Yi, H. Lee, S. A. Diddams, and K. J. Vahala, “Electro-optical frequency division and stable microwave synthesis,” Science, vol. 345, no. 6194, pp. 309–313, 2014.
  * (20) D. Kwon, D. Jeong, I. Jeon, H. Lee, and J. Kim, “Ultrastable microwave and soliton-pulse generation from fibre-photonic-stabilized microcombs,” Nature Communications, vol. 13, no. 1, p. 381, 2022.
  * (21) Q.-X. Ji, W. Jin, L. Wu, Y. Yu, Z. Yuan, W. Zhang, M. Gao, B. Li, H. Wang, C. Xiang, et al., “Engineered zero-dispersion microcombs using cmos-ready photonics,” Optica, vol. 10, no. 2, pp. 279–285, 2023.
  * (22) J. Zang, X. Xie, Q. Yu, Z. Yang, A. Beling, and J. C. Campbell, “Reduction of amplitude-to-phase conversion in charge-compensated modified unitraveling carrier photodiodes,” Journal of Lightwave Technology, vol. 36, no. 22, pp. 5218–5223, 2018.
  * (23) H. Li and N. Abraham, “Analysis of the noise spectra of a laser diode with optical feedback from a high-finesse resonator,” IEEE Journal of Quantum Electronics, vol. 25, no. 8, pp. 1782–1793, 1989.
  * (24) M. Endo and T. R. Schibli, “Residual phase noise suppression for pound-drever-hall cavity stabilization with an electro-optic modulator,” OSA Continuum, vol. 1, no. 1, pp. 116–123, 2018.
  * (25) Q. Ji, W. Jin, J. Guo, L. Wu, P. Liu, A. Feshali, M. Paniccia, J. Bowers, and K. Vahala, “Integrated microcomb with broadband tunable normal and anomalous dispersion,” in Optica Nonlinear Optics Topical Meeting, 2023.
  * (26) N. Pavlov, S. Koptyaev, G. Lihachev, A. Voloshin, A. Gorodnitskiy, M. Ryabko, S. Polonsky, and M. Gorodetsky, “Narrow-linewidth lasing and soliton kerr microcombs with ordinary laser diodes,” Nature Photonics, vol. 12, no. 11, pp. 694–698, 2018.
  * (27) B. Shen, L. Chang, J. Liu, H. Wang, Q.-F. Yang, C. Xiang, R. N. Wang, J. He, T. Liu, W. Xie, J. Guo, D. Kinghorn, L. Wu, Q.-X. Ji, T. J. Kippenberg, K. Vahala, and J. E. Bowers, “Integrated turnkey soliton microcombs,” Nature, vol. 582, no. 7812, pp. 365–369, 2020.
  * (28) X. Yi, Q.-F. Yang, K. Y. Yang, M.-G. Suh, and K. Vahala, “Soliton frequency comb at microwave rates in a high-q silica microresonator,” Optica, vol. 2, no. 12, pp. 1078–1085, 2015.
  * (29) J. Liu, E. Lucas, A. S. Raja, J. He, J. Riemensberger, R. N. Wang, M. Karpov, H. Guo, R. Bouchand, and T. J. Kippenberg, “Photonic microwave generation in the x-and k-band using integrated soliton microcombs,” Nature Photonics, vol. 14, no. 8, pp. 486–491, 2020.
  * (30) J. Li, H. Lee, T. Chen, and K. J. Vahala, “Low-pump-power, low-phase-noise, and microwave to millimeter-wave repetition rate operation in microcombs,” Physical Review Letters, vol. 109, no. 23, p. 233901, 2012.
  * (31) W. Liang, D. Eliyahu, V. S. Ilchenko, A. A. Savchenkov, A. B. Matsko, D. Seidel, and L. Maleki, “High spectral purity kerr frequency comb radio frequency photonic oscillator,” Nature communications, vol. 6, no. 1, p. 7957, 2015.
  * (32) S. Sun, B. Wang, K. Liu, M. Harrington, F. Tabatabaei, R. Liu, J. Wang, S. Hanifi, J. S. Morgan, M. Jahanbozorgi, et al., “Integrated optical frequency division for stable microwave and mmwave generation,” arXiv preprint arXiv:2305.13575, 2023.
  * (33) A. Matsko, A. Savchenkov, D. Eliyahu, W. Liang, E. Dale, V. Ilchenko, and L. Maleki, “Turn-key operation and stabilization of kerr frequency combs,” in 2016 IEEE International Frequency Control Symposium (IFCS), pp. 1–5, IEEE, 2016.
  * (34) W. Weng, E. Lucas, G. Lihachev, V. E. Lobanov, H. Guo, M. L. Gorodetsky, and T. J. Kippenberg, “Spectral purification of microwave signals with disciplined dissipative kerr solitons,” Physical Review Letters, vol. 122, no. 1, p. 013902, 2019.
  * (35) E. Lucas, P. Brochard, R. Bouchand, S. Schilt, T. Südmeyer, and T. J. Kippenberg, “Ultralow-noise photonic microwave synthesis using a soliton microcomb-based transfer oscillator,” Nature Communications, vol. 11, no. 1, p. 374, 2020.
  * (36) Y. Peng, K. Sun, Y. Shen, A. Beling, and J. C. Campbell, “Photonic generation of pulsed microwave signals in the x-, ku-and k-band,” Optics Express, vol. 28, no. 19, pp. 28563–28572, 2020.
  * (37) X. Xie, Q. Zhou, K. Li, Y. Shen, Q. Li, Z. Yang, A. Beling, and J. C. Campbell, “Improved power conversion efficiency in high-performance photodiodes by flip-chip bonding on diamond,” Optica, vol. 1, pp. 429–435, Dec 2014.
  * (38) H. Cheng, Z. Dai, Y. Zhou, F. Ruesink, N. Jin, D. Mason, O. Miller, and P. Rakich, “On-chip poor man’s isolator,” in Frontiers in Optics, pp. FM5D–1, Optica Publishing Group, 2022.
  * (39) X. Yi, Q.-F. Yang, X. Zhang, K. Y. Yang, X. Li, and K. Vahala, “Single-mode dispersive waves and soliton microcomb dynamics,” Nature communications, vol. 8, no. 1, p. 14869, 2017.
  * (40) Q.-F. Yang, Q.-X. Ji, L. Wu, B. Shen, H. Wang, C. Bao, Z. Yuan, and K. Vahala, “Dispersive-wave induced noise limits in miniature soliton microwave sources,” Nature communications, vol. 12, no. 1, p. 1442, 2021.
  * (41) L. Yao, P. Liu, H.-J. Chen, Q. Gong, Q.-F. Yang, and Y.-F. Xiao, “Soliton microwave oscillators using oversized billion q optical microresonators,” Optica, vol. 9, no. 5, pp. 561–564, 2022.
  * (42) Y. Zhao, J. K. Jang, K. J. McNulty, X. Ji, Y. Okawachi, M. Lipson, and A. L. Gaeta, “All-optical frequency division on-chip using a single laser,” arXiv preprint arXiv:2303.02805, 2023.
  * (43) “Hi-Q X-band OEO.” https://www.oewaves.com/oe3700.  Accessed: 2023-06-18.
  * (44) J. Li and K. Vahala, “Small-sized, ultra-low phase noise photonic microwave oscillators at x-ka bands,” Optica, vol. 10, no. 1, pp. 33–34, 2023.
  * (45) “Ultra-low-noise microwave oscillator.” https://www.quantxlabs.com/capabilities/product-development/ultra-low-phase-noise-oscillators/.  Accessed: 2023-07-03.
  * (46) C. Xiang, W. Jin, O. Terra, B. Dong, H. Wang, L. Wu, J. Guo, T. J. Morin, E. Hughes, J. Peters, et al., “Three-dimensional integration enables ultra-low-noise, isolator-free si photonics,” arXiv preprint arXiv:2301.09989, 2023.
  * (47) C. Xiang, J. Liu, J. Guo, L. Chang, R. N. Wang, W. Weng, J. Peters, W. Xie, Z. Zhang, J. Riemensberger, J. Selvidge, T. J. Kippenberg, and J. E. Bowers, “Laser soliton microcombs heterogeneously integrated on silicon,” Science, vol. 373, no. 6550, pp. 99–103, 2021.
  * (48) W. Xie, T. Komljenovic, J. Huang, M. Tran, M. Davenport, A. Torres, P. Pintus, and J. Bowers, “Heterogeneous silicon photonics sensing for autonomous cars,” Optics express, vol. 27, no. 3, pp. 3642–3663, 2019.
  * (49) M. L. Davenport, S. Skendžić, N. Volet, J. C. Hulme, M. J. Heck, and J. E. Bowers, “Heterogeneous silicon/iii–v semiconductor optical amplifiers,” IEEE Journal of Selected Topics in Quantum Electronics, vol. 22, no. 6, pp. 78–88, 2016.
  * (50) M. H. Idjadi and F. Aflatouni, “Integrated pound- drever- hall laser stabilization system in silicon,” Nature communications, vol. 8, no. 1, pp. 1–9, 2017.
  * (51) C. Joshi, J. K. Jang, K. Luke, X. Ji, S. A. Miller, A. Klenner, Y. Okawachi, M. Lipson, and A. L. Gaeta, “Thermally controlled comb generation and soliton modelocking in microresonators,” Optics letters, vol. 41, no. 11, pp. 2565–2568, 2016.

Corresponding authors<EMAIL_ADDRESS>and
<EMAIL_ADDRESS>
Funding: This research was supported by DARPA GRYPHON program
(HR0011-22-2-0009) and NIST.

Acknowledgments: The authors thank Brian Long for the artistic illustration in
Figure 5, and Kristina Chang and Nazanin Hoghooghi for comments on the
manuscript. Commercial equipment and trade names are identified for scientific
clarity only and does not represent an endorsement by NIST.