Support nullable boolean values

Oct 13, 2025 by wbrum.org 104 views

Understanding the Issue with df_markdown in DuckDB

When working with DuckDB, a column-store database, users often rely on the df_markdown magic command to display query results in a markdown format. However, in certain situations, this command may fail to produce the expected output, resulting in a TypeError. In this article, we will delve into the issue and explore possible solutions.

To reproduce the issue, let's create a sample table and execute a query using the df_markdown magic command.

%%dql -t df_markdown
CREATE OR REPLACE TABLE outcomes (
    outcome VARCHAR(50)
);

INSERT INTO outcomes (outcome) VALUES 
('nonexistent'),
('failure'),
('success'),
('failure');

FROM outcomes;

Expected Output

The expected output should be a markdown table displaying the outcome column.

outcome
nonexistent
failure
success
failure

Executing the Query

However, when we execute the query using the df_markdown magic command, the output is empty.

%%dql -t df_markdown
FROM outcomes
SELECT
  outcome,
  new_outcome: outcome.replace('failure', 'False').replace('success', 'True').replace('nonexistent', NULL);

Error Message

The error message indicates a TypeError with the following stacktrace:

TypeError: boolean value of NA is ambiguous

Investigating the Issue

The error message suggests that the issue is related to the handling of nullable boolean values in the tabulate library, which is used by the df_markdown magic command.

Possible Solutions

Use the try_cast function: One possible solution is to use the try_cast function to cast the nullable boolean values to a specific type, such as BOOLEAN.
Use the Nullable Boolean DTYPE: Another solution is to use the Nullable Boolean DTYPE provided by pandas, which can handle nullable boolean values.
Update the tabulate library: If the issue is specific to the tabulate library, updating to the latest version may resolve the problem.

In conclusion, the df_markdown magic command in DuckDB may fail to produce the expected output due to issues with nullable boolean values. By using the try_cast function or the Nullable Boolean DTYPE provided by pandas, users can work around this issue. Additionally, updating the tabulate library may also resolve the problem.

To further improve the df_markdown magic command, it would be beneficial to:

Improve error handling: Enhance the error handling mechanism to provide more informative error messages.
Support nullable boolean values: Update the tabulate library to support nullable boolean values.
Provide additional options: Offer additional options for customizing the output, such as formatting and styling.

By addressing these issues, users can enjoy a more seamless and productive experience when working with DuckDB and the df_markdown magic command.
Q&A: Understanding the Issue with df_markdown in DuckDB

In our previous article, we explored the issue with the df_markdown magic command in DuckDB, a column-store database. The command fails to produce the expected output due to issues with nullable boolean values. In this article, we will provide a Q&A section to address common questions and concerns related to this issue.

Q: What is the cause of the `TypeError` in `df_markdown`?

A: The TypeError is caused by the handling of nullable boolean values in the tabulate library, which is used by the df_markdown magic command.

Q: How can I reproduce the issue?

A: To reproduce the issue, create a sample table with a nullable boolean column and execute a query using the df_markdown magic command.

%%dql -t df_markdown
CREATE OR REPLACE TABLE outcomes (
    outcome VARCHAR(50)
);

INSERT INTO outcomes (outcome) VALUES 
('nonexistent'),
('failure'),
('success'),
('failure');

FROM outcomes;

Q: What is the expected output of the `df_markdown` command?

A: The expected output is a markdown table displaying the outcome column.

outcome
nonexistent
failure
success
failure

Q: How can I work around the issue?

A: You can work around the issue by using the try_cast function to cast the nullable boolean values to a specific type, such as BOOLEAN.

%%dql -t df_markdown
FROM outcomes
SELECT
  outcome,
  new_outcome: try_cast(outcome.replace('failure', 'False').replace('success', 'True').replace('nonexistent', NULL) AS BOOLEAN);

Q: Is there a way to update the `tabulate` library to support nullable boolean values?

A: Yes, you can update the tabulate library to the latest version, which may resolve the issue.

Q: Can I customize the output of the `df_markdown` command?

A: Yes, you can customize the output by providing additional options, such as formatting and styling.

Q: How can I improve the error handling mechanism of the `df_markdown` command?

A: You can improve the error handling mechanism by providing more informative error messages and handling specific exceptions.

In conclusion, the df_markdown magic command in DuckDB may fail to produce the expected output due to issues with nullable boolean values. By using the try_cast function or updating the tabulate library, users can work around this issue. Additionally, customizing the output and improving error handling can enhance the user experience.

To further improve the df_markdown magic command, it would be beneficial to:

Improve error handling: Enhance the error handling mechanism to provide more informative error messages.
Support nullable boolean values: Update the tabulate library to support nullable boolean values.
Provide additional options: Offer additional options for customizing the output, such as formatting and styling.

By addressing these issues, users can enjoy a more seamless and productive experience when working with DuckDB and the df_markdown magic command.