You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@arrow.apache.org by al...@apache.org on 2023/01/15 13:33:32 UTC
[arrow-datafusion] branch master updated: Minor: Port information schema tests to sqllogictest (#4905)
This is an automated email from the ASF dual-hosted git repository.
alamb pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/arrow-datafusion.git
The following commit(s) were added to refs/heads/master by this push:
new f3762700d Minor: Port information schema tests to sqllogictest (#4905)
f3762700d is described below
commit f3762700d5d7433667c8248d4d19ffeb1c6861ff
Author: Andrew Lamb <an...@nerdnetworks.org>
AuthorDate: Sun Jan 15 14:33:27 2023 +0100
Minor: Port information schema tests to sqllogictest (#4905)
---
datafusion/core/tests/sql/information_schema.rs | 365 ---------------------
.../test_files/information_schema.slt | 213 ++++++++++++
2 files changed, 213 insertions(+), 365 deletions(-)
diff --git a/datafusion/core/tests/sql/information_schema.rs b/datafusion/core/tests/sql/information_schema.rs
index 9cd5fdb4e..5927f36e1 100644
--- a/datafusion/core/tests/sql/information_schema.rs
+++ b/datafusion/core/tests/sql/information_schema.rs
@@ -26,8 +26,6 @@ use datafusion::{
};
use datafusion_expr::Expr;
-use rstest::rstest;
-
use super::*;
#[tokio::test]
@@ -144,277 +142,6 @@ async fn information_schema_tables_table_types() {
assert_batches_sorted_eq!(expected, &result);
}
-#[tokio::test]
-async fn information_schema_show_tables_no_information_schema() {
- let ctx = SessionContext::with_config(SessionConfig::new());
-
- ctx.register_table("t", table_with_sequence(1, 1).unwrap())
- .unwrap();
-
- // use show tables alias
- let err = plan_and_collect(&ctx, "SHOW TABLES").await.unwrap_err();
-
- assert_eq!(err.to_string(), "Error during planning: SHOW TABLES is not supported unless information_schema is enabled");
-}
-
-#[rstest]
-#[case("datafusion.public.some_table")]
-#[case("public.some_table")]
-#[case("some_table")]
-#[tokio::test]
-async fn information_schema_describe_table(#[case] table_name: &str) {
- let ctx =
- SessionContext::with_config(SessionConfig::new().with_information_schema(true));
-
- let sql = format!("CREATE OR REPLACE TABLE {table_name} AS VALUES (1,2),(3,4);");
- ctx.sql(sql.as_str()).await.unwrap();
-
- let sql_all = format!("DESCRIBE {table_name};");
- let results_all = execute_to_batches(&ctx, sql_all.as_str()).await;
-
- let expected = vec![
- "+-------------+-----------+-------------+",
- "| column_name | data_type | is_nullable |",
- "+-------------+-----------+-------------+",
- "| column1 | Int64 | YES |",
- "| column2 | Int64 | YES |",
- "+-------------+-----------+-------------+",
- ];
-
- assert_batches_eq!(expected, &results_all);
-}
-
-#[tokio::test]
-async fn information_schema_describe_table_not_exists() {
- let ctx = SessionContext::with_config(SessionConfig::new());
-
- let sql_all = "describe table;";
- let err = plan_and_collect(&ctx, sql_all).await.unwrap_err();
- assert_eq!(
- err.to_string(),
- "Error during planning: table 'datafusion.public.table' not found"
- );
-}
-
-#[tokio::test]
-async fn information_schema_show_tables() {
- let ctx =
- SessionContext::with_config(SessionConfig::new().with_information_schema(true));
-
- ctx.register_table("t", table_with_sequence(1, 1).unwrap())
- .unwrap();
-
- // use show tables alias
- let result = plan_and_collect(&ctx, "SHOW TABLES").await.unwrap();
-
- let expected = vec![
- "+---------------+--------------------+-------------+------------+",
- "| table_catalog | table_schema | table_name | table_type |",
- "+---------------+--------------------+-------------+------------+",
- "| datafusion | information_schema | columns | VIEW |",
- "| datafusion | information_schema | df_settings | VIEW |",
- "| datafusion | information_schema | tables | VIEW |",
- "| datafusion | information_schema | views | VIEW |",
- "| datafusion | public | t | BASE TABLE |",
- "+---------------+--------------------+-------------+------------+",
- ];
- assert_batches_sorted_eq!(expected, &result);
-
- let result = plan_and_collect(&ctx, "SHOW tables").await.unwrap();
-
- assert_batches_sorted_eq!(expected, &result);
-}
-
-#[tokio::test]
-async fn information_schema_show_columns_no_information_schema() {
- let ctx = SessionContext::with_config(SessionConfig::new());
-
- ctx.register_table("t", table_with_sequence(1, 1).unwrap())
- .unwrap();
-
- let err = plan_and_collect(&ctx, "SHOW COLUMNS FROM t")
- .await
- .unwrap_err();
-
- assert_eq!(err.to_string(), "Error during planning: SHOW COLUMNS is not supported unless information_schema is enabled");
-}
-
-#[tokio::test]
-async fn information_schema_show_columns_like_where() {
- let ctx = SessionContext::with_config(SessionConfig::new());
-
- ctx.register_table("t", table_with_sequence(1, 1).unwrap())
- .unwrap();
-
- let expected =
- "Error during planning: SHOW COLUMNS with WHERE or LIKE is not supported";
-
- let err = plan_and_collect(&ctx, "SHOW COLUMNS FROM t LIKE 'f'")
- .await
- .unwrap_err();
- assert_eq!(err.to_string(), expected);
-
- let err = plan_and_collect(&ctx, "SHOW COLUMNS FROM t WHERE column_name = 'bar'")
- .await
- .unwrap_err();
- assert_eq!(err.to_string(), expected);
-}
-
-#[tokio::test]
-async fn information_schema_show_columns() {
- let ctx =
- SessionContext::with_config(SessionConfig::new().with_information_schema(true));
-
- ctx.register_table("t", table_with_sequence(1, 1).unwrap())
- .unwrap();
-
- let result = plan_and_collect(&ctx, "SHOW COLUMNS FROM t").await.unwrap();
-
- let expected = vec![
- "+---------------+--------------+------------+-------------+-----------+-------------+",
- "| table_catalog | table_schema | table_name | column_name | data_type | is_nullable |",
- "+---------------+--------------+------------+-------------+-----------+-------------+",
- "| datafusion | public | t | i | Int32 | YES |",
- "+---------------+--------------+------------+-------------+-----------+-------------+",
- ];
- assert_batches_sorted_eq!(expected, &result);
-
- let result = plan_and_collect(&ctx, "SHOW columns from t").await.unwrap();
- assert_batches_sorted_eq!(expected, &result);
-
- // This isn't ideal but it is consistent behavior for `SELECT * from "T"`
- let err = plan_and_collect(&ctx, "SHOW columns from \"T\"")
- .await
- .unwrap_err();
- assert_eq!(
- err.to_string(),
- // Error propagates from SessionState::get_table_provider
- "Error during planning: table 'datafusion.public.T' not found"
- );
-}
-
-// test errors with WHERE and LIKE
-#[tokio::test]
-async fn information_schema_show_columns_full_extended() {
- let ctx =
- SessionContext::with_config(SessionConfig::new().with_information_schema(true));
-
- ctx.register_table("t", table_with_sequence(1, 1).unwrap())
- .unwrap();
-
- let result = plan_and_collect(&ctx, "SHOW FULL COLUMNS FROM t")
- .await
- .unwrap();
- let expected = vec![
- "+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+",
- "| table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type |",
- "+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+",
- "| datafusion | public | t | i | 0 | | YES | Int32 | | | 32 | 2 | | | |",
- "+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+",
- ];
- assert_batches_sorted_eq!(expected, &result);
-
- let result = plan_and_collect(&ctx, "SHOW EXTENDED COLUMNS FROM t")
- .await
- .unwrap();
- assert_batches_sorted_eq!(expected, &result);
-}
-
-#[tokio::test]
-async fn information_schema_show_table_table_names() {
- let ctx =
- SessionContext::with_config(SessionConfig::new().with_information_schema(true));
-
- ctx.register_table("t", table_with_sequence(1, 1).unwrap())
- .unwrap();
-
- let result = plan_and_collect(&ctx, "SHOW COLUMNS FROM public.t")
- .await
- .unwrap();
-
- let expected = vec![
- "+---------------+--------------+------------+-------------+-----------+-------------+",
- "| table_catalog | table_schema | table_name | column_name | data_type | is_nullable |",
- "+---------------+--------------+------------+-------------+-----------+-------------+",
- "| datafusion | public | t | i | Int32 | YES |",
- "+---------------+--------------+------------+-------------+-----------+-------------+",
- ];
- assert_batches_sorted_eq!(expected, &result);
-
- let result = plan_and_collect(&ctx, "SHOW columns from datafusion.public.t")
- .await
- .unwrap();
- assert_batches_sorted_eq!(expected, &result);
-
- let err = plan_and_collect(&ctx, "SHOW columns from t2")
- .await
- .unwrap_err();
- assert_eq!(
- err.to_string(),
- // Error propagates from SessionState::get_table_provider
- "Error during planning: table 'datafusion.public.t2' not found"
- );
-
- let err = plan_and_collect(&ctx, "SHOW columns from datafusion.public.t2")
- .await
- .unwrap_err();
- assert_eq!(
- err.to_string(),
- // Error propagates from SessionState::get_table_provider
- "Error during planning: table 'datafusion.public.t2' not found"
- );
-}
-
-// FIXME once we support raise error while show non-existing variable, we could add this back
-//#[tokio::test]
-//async fn show_unsupported() {
-// let ctx = SessionContext::with_config(SessionConfig::new());
-//
-// let err = plan_and_collect(&ctx, "SHOW SOMETHING_UNKNOWN")
-// .await
-// .unwrap_err();
-//
-// assert_eq!(err.to_string(), "This feature is not implemented: SHOW SOMETHING_UNKNOWN not implemented. Supported syntax: SHOW <TABLES>");
-//}
-
-// FIXME
-// currently we cannot know whether a variable exists, this will output 0 row instead
-// one way to fix this is to generate a ConfigOptions and get options' key to compare
-// however config.rs is currently in core lib, could not be used by datafusion_sql due to the dependency cycle
-#[tokio::test]
-async fn show_non_existing_variable() {
- let ctx =
- SessionContext::with_config(SessionConfig::new().with_information_schema(true));
-
- let result = plan_and_collect(&ctx, "SHOW SOMETHING_UNKNOWN")
- .await
- .unwrap();
-
- assert_eq!(result.len(), 0);
-}
-
-#[tokio::test]
-async fn show_unsupported_when_information_schema_off() {
- let ctx = SessionContext::with_config(SessionConfig::new());
-
- let err = plan_and_collect(&ctx, "SHOW SOMETHING").await.unwrap_err();
-
- assert_eq!(err.to_string(), "Error during planning: SHOW [VARIABLE] is not supported unless information_schema is enabled");
-}
-
-#[tokio::test]
-async fn information_schema_columns_not_exist_by_default() {
- let ctx = SessionContext::new();
-
- let err = plan_and_collect(&ctx, "SELECT * from information_schema.columns")
- .await
- .unwrap_err();
- assert_eq!(
- err.to_string(),
- "Error during planning: table 'datafusion.information_schema.columns' not found"
- );
-}
-
fn table_with_many_types() -> Arc<dyn TableProvider> {
let schema = Schema::new(vec![
Field::new("int32_col", DataType::Int32, false),
@@ -487,98 +214,6 @@ async fn information_schema_columns() {
assert_batches_sorted_eq!(expected, &result);
}
-#[tokio::test]
-async fn show_create_view() {
- let ctx =
- SessionContext::with_config(SessionConfig::new().with_information_schema(true));
- let table_sql = "CREATE TABLE abc AS VALUES (1,2,3), (4,5,6)";
- plan_and_collect(&ctx, table_sql).await.unwrap();
- let view_sql = "CREATE VIEW xyz AS SELECT * FROM abc";
- plan_and_collect(&ctx, view_sql).await.unwrap();
-
- let results_sql = "SHOW CREATE TABLE xyz";
- let results = plan_and_collect(&ctx, results_sql).await.unwrap();
- assert_eq!(results[0].num_rows(), 1);
-
- let expected = vec![
- "+---------------+--------------+------------+--------------------------------------+",
- "| table_catalog | table_schema | table_name | definition |",
- "+---------------+--------------+------------+--------------------------------------+",
- "| datafusion | public | xyz | CREATE VIEW xyz AS SELECT * FROM abc |",
- "+---------------+--------------+------------+--------------------------------------+",
- ];
- assert_batches_eq!(expected, &results);
-}
-
-#[tokio::test]
-async fn show_create_view_in_catalog() {
- let ctx =
- SessionContext::with_config(SessionConfig::new().with_information_schema(true));
- let table_sql = "CREATE TABLE abc AS VALUES (1,2,3), (4,5,6)";
- plan_and_collect(&ctx, table_sql).await.unwrap();
- let schema_sql = "CREATE SCHEMA test";
- plan_and_collect(&ctx, schema_sql).await.unwrap();
- let view_sql = "CREATE VIEW test.xyz AS SELECT * FROM abc";
- plan_and_collect(&ctx, view_sql).await.unwrap();
-
- let result_sql = "SHOW CREATE TABLE test.xyz";
- let results = plan_and_collect(&ctx, result_sql).await.unwrap();
- assert_eq!(results[0].num_rows(), 1);
-
- let expected = vec![
- "+---------------+--------------+------------+-------------------------------------------+",
- "| table_catalog | table_schema | table_name | definition |",
- "+---------------+--------------+------------+-------------------------------------------+",
- "| datafusion | test | xyz | CREATE VIEW test.xyz AS SELECT * FROM abc |",
- "+---------------+--------------+------------+-------------------------------------------+",
- ];
- assert_batches_eq!(expected, &results);
-}
-
-#[tokio::test]
-async fn show_create_table() {
- let ctx =
- SessionContext::with_config(SessionConfig::new().with_information_schema(true));
- let table_sql = "CREATE TABLE abc AS VALUES (1,2,3), (4,5,6)";
- plan_and_collect(&ctx, table_sql).await.unwrap();
-
- let result_sql = "SHOW CREATE TABLE abc";
- let results = plan_and_collect(&ctx, result_sql).await.unwrap();
-
- let expected = vec![
- "+---------------+--------------+------------+------------+",
- "| table_catalog | table_schema | table_name | definition |",
- "+---------------+--------------+------------+------------+",
- "| datafusion | public | abc | |",
- "+---------------+--------------+------------+------------+",
- ];
-
- assert_batches_eq!(expected, &results);
-}
-
-#[tokio::test]
-async fn show_external_create_table() {
- let ctx =
- SessionContext::with_config(SessionConfig::new().with_information_schema(true));
-
- let table_sql =
- "CREATE EXTERNAL TABLE abc STORED AS CSV WITH HEADER ROW LOCATION '../../testing/data/csv/aggregate_test_100.csv'";
- plan_and_collect(&ctx, table_sql).await.unwrap();
-
- let result_sql = "SHOW CREATE TABLE abc";
- let results = plan_and_collect(&ctx, result_sql).await.unwrap();
-
- let expected = vec![
- "+---------------+--------------+------------+-------------------------------------------------------------------------------------------------+",
- "| table_catalog | table_schema | table_name | definition |",
- "+---------------+--------------+------------+-------------------------------------------------------------------------------------------------+",
- "| datafusion | public | abc | CREATE EXTERNAL TABLE abc STORED AS CSV LOCATION ../../testing/data/csv/aggregate_test_100.csv |",
- "+---------------+--------------+------------+-------------------------------------------------------------------------------------------------+",
- ];
-
- assert_batches_eq!(expected, &results);
-}
-
/// Execute SQL and return results
async fn plan_and_collect(ctx: &SessionContext, sql: &str) -> Result<Vec<RecordBatch>> {
ctx.sql(sql).await?.collect().await
diff --git a/datafusion/core/tests/sqllogictests/test_files/information_schema.slt b/datafusion/core/tests/sqllogictests/test_files/information_schema.slt
index 4fd08bac9..b1e8733c8 100644
--- a/datafusion/core/tests/sqllogictests/test_files/information_schema.slt
+++ b/datafusion/core/tests/sqllogictests/test_files/information_schema.slt
@@ -45,6 +45,9 @@ set datafusion.catalog.information_schema = false
statement error DataFusion error: Error during planning: table 'datafusion.information_schema.tables' not found
SELECT * from information_schema.tables
+statement error Error during planning: table 'datafusion.information_schema.columns' not found
+SELECT * from information_schema.columns;
+
############
## Enable information schema for the rest of the test
@@ -152,3 +155,213 @@ query R
SHOW TIMEZONE
----
datafusion.execution.time_zone +00:00
+
+
+# information_schema_describe_table
+
+## some_table
+statement ok
+CREATE OR REPLACE TABLE some_table AS VALUES (1,2),(3,4);
+
+query R
+DESCRIBE some_table
+----
+column1 Int64 YES
+column2 Int64 YES
+
+statement ok
+DROP TABLE public.some_table;
+
+## public.some_table
+
+statement ok
+CREATE OR REPLACE TABLE public.some_table AS VALUES (1,2),(3,4);
+
+query R
+DESCRIBE public.some_table
+----
+column1 Int64 YES
+column2 Int64 YES
+
+statement ok
+DROP TABLE public.some_table;
+
+## datafusion.public.some_table
+
+statement ok
+CREATE OR REPLACE TABLE datafusion.public.some_table AS VALUES (1,2),(3,4);
+
+query R
+DESCRIBE datafusion.public.some_table
+----
+column1 Int64 YES
+column2 Int64 YES
+
+statement ok
+DROP TABLE datafusion.public.some_table;
+
+# information_schema_describe_table_not_exists
+
+statement error Error during planning: table 'datafusion.public.table' not found
+describe table;
+
+
+# information_schema_show_tables
+query CCCC
+SHOW TABLES
+----
+datafusion information_schema tables VIEW
+datafusion information_schema views VIEW
+datafusion information_schema columns VIEW
+datafusion information_schema df_settings VIEW
+
+
+# information_schema_show_tables_no_information_schema
+
+statement ok
+set datafusion.catalog.information_schema = false;
+
+statement error Error during planning: SHOW TABLES is not supported unless information_schema is enabled
+SHOW TABLES
+
+statement ok
+set datafusion.catalog.information_schema = true;
+
+
+# information_schema_show_columns
+statement ok
+CREATE TABLE t AS SELECT 1::int as i;
+
+statement error Error during planning: SHOW COLUMNS with WHERE or LIKE is not supported
+SHOW COLUMNS FROM t LIKE 'f';
+
+statement error Error during planning: SHOW COLUMNS with WHERE or LIKE is not supported
+SHOW COLUMNS FROM t WHERE column_name = 'bar';
+
+query CCCCCC
+SHOW COLUMNS FROM t;
+----
+datafusion public t i Int32 NO
+
+# This isn't ideal but it is consistent behavior for `SELECT * from "T"`
+statement error Error during planning: table 'datafusion.public.T' not found
+SHOW columns from "T"
+
+# information_schema_show_columns_full_extended
+query CCCCCC
+SHOW FULL COLUMNS FROM t;
+----
+datafusion public t i 0 NULL NO Int32 NULL NULL 32 2 NULL NULL NULL
+
+# expect same as above
+query CCCCCC
+SHOW EXTENDED COLUMNS FROM t;
+----
+datafusion public t i 0 NULL NO Int32 NULL NULL 32 2 NULL NULL NULL
+
+# information_schema_show_columns_no_information_schema
+
+statement ok
+set datafusion.catalog.information_schema = false;
+
+statement error Error during planning: SHOW COLUMNS is not supported unless information_schema is enabled
+SHOW COLUMNS FROM t
+
+statement ok
+set datafusion.catalog.information_schema = true;
+
+
+# information_schema_show_columns_names()
+query CCCCCC
+SHOW columns from public.t
+----
+datafusion public t i Int32 NO
+
+query CCCCCC
+SHOW columns from datafusion.public.t
+----
+datafusion public t i Int32 NO
+
+statement error Error during planning: table 'datafusion.public.t2' not found
+SHOW columns from t2
+
+statement error Error during planning: table 'datafusion.public.t2' not found
+SHOW columns from datafusion.public.t2
+
+
+# show_non_existing_variable
+# FIXME
+# currently we cannot know whether a variable exists, this will output 0 row instead
+query C
+SHOW SOMETHING_UNKNOWN;
+----
+
+statement ok
+DROP TABLE t;
+
+# show_unsupported_when_information_schema_off
+
+statement ok
+set datafusion.catalog.information_schema = false;
+
+statement error Error during planning: SHOW \[VARIABLE\] is not supported unless information_schema is enabled
+SHOW SOMETHING
+
+statement ok
+set datafusion.catalog.information_schema = true;
+
+
+
+# show_create_view()
+statement ok
+CREATE TABLE abc AS VALUES (1,2,3), (4,5,6);
+
+statement ok
+CREATE VIEW xyz AS SELECT * FROM abc
+
+query CCCC
+SHOW CREATE TABLE xyz
+----
+datafusion public xyz CREATE VIEW xyz AS SELECT * FROM abc
+
+statement ok
+DROP TABLE abc;
+
+statement ok
+DROP VIEW xyz;
+
+# show_create_view_in_catalog
+statement ok
+CREATE TABLE abc AS VALUES (1,2,3), (4,5,6)
+
+statement ok
+CREATE SCHEMA test;
+
+statement ok
+CREATE VIEW test.xyz AS SELECT * FROM abc;
+
+query CCCC
+SHOW CREATE TABLE test.xyz
+----
+datafusion test xyz CREATE VIEW test.xyz AS SELECT * FROM abc
+
+statement error DataFusion error: This feature is not implemented: Only `DROP TABLE/VIEW
+DROP SCHEMA test;
+
+statement ok
+DROP TABLE abc;
+
+statement ok
+DROP VIEW test.xyz
+
+
+# show_external_create_table()
+statement ok
+CREATE EXTERNAL TABLE abc
+STORED AS CSV
+WITH HEADER ROW LOCATION '../../testing/data/csv/aggregate_test_100.csv';
+
+query CCCC
+SHOW CREATE TABLE abc;
+----
+datafusion public abc CREATE EXTERNAL TABLE abc STORED AS CSV LOCATION ../../testing/data/csv/aggregate_test_100.csv
\ No newline at end of file