Regression test for SS-193 where COPYing to a table from STDIN would store values without rounding them to the destination column's scale. The same rounding must also apply to the COPY FROM S3 paths, so we additionally round-trip scale-3 values through both a CSV and a parquet
(c: Composition)
| 460 | |
| 461 | |
| 462 | def workflow_test_ss_193(c: Composition): |
| 463 | """ |
| 464 | Regression test for SS-193 where COPYing to a table from STDIN would |
| 465 | store values without rounding them to the destination column's scale. |
| 466 | |
| 467 | The same rounding must also apply to the COPY FROM S3 paths, so we |
| 468 | additionally round-trip scale-3 values through both a CSV and a parquet |
| 469 | file on S3 and assert they are rounded to the destination column's scale |
| 470 | on read. |
| 471 | """ |
| 472 | c.up("materialized", "minio") |
| 473 | conn = c.sql_connection() |
| 474 | with conn.cursor() as cur: |
| 475 | cur.execute( |
| 476 | "CREATE TABLE numbers_with_precision (a DECIMAL(10, 2), b NUMERIC(10, 2))" |
| 477 | ) |
| 478 | with cur.copy("COPY numbers_with_precision FROM STDIN") as copy: |
| 479 | copy.write("10.447\t10.447\n") |
| 480 | with cur.copy("COPY numbers_with_precision FROM STDIN (FORMAT CSV)") as copy: |
| 481 | copy.write("10.447,10.447\n") |
| 482 | |
| 483 | cur.execute("SELECT a, b FROM numbers_with_precision") |
| 484 | rows = cur.fetchall() |
| 485 | assert rows == [ |
| 486 | (Decimal("10.45"), Decimal("10.45")), |
| 487 | (Decimal("10.45"), Decimal("10.45")), |
| 488 | ], f"COPY FROM STDIN did not round values to the column scale: {rows}" |
| 489 | |
| 490 | # Round-trip scale-3 values through CSV and parquet files on S3 and |
| 491 | # assert COPY FROM rounds them to the destination column's scale on read. |
| 492 | cur.execute("CREATE SECRET aws_secret_ss_193 AS 'minioadmin'") |
| 493 | cur.execute(""" |
| 494 | CREATE CONNECTION aws_conn_ss_193 TO AWS ( |
| 495 | ACCESS KEY ID = 'minioadmin', |
| 496 | SECRET ACCESS KEY = SECRET aws_secret_ss_193, |
| 497 | ENDPOINT = 'http://minio:9000/', |
| 498 | REGION = 'us-east-1' |
| 499 | ) |
| 500 | """) |
| 501 | |
| 502 | # Source carries scale-3 values that don't round evenly to scale 2. |
| 503 | cur.execute("CREATE TABLE numbers_scale3 (a DECIMAL(10, 3), b NUMERIC(10, 3))") |
| 504 | cur.execute("INSERT INTO numbers_scale3 VALUES (10.447, 10.447)") |
| 505 | |
| 506 | # The dynamic SQL below is encoded to bytes so it satisfies psycopg's |
| 507 | # LiteralString-typed query parameter. |
| 508 | for format in ["csv", "parquet"]: |
| 509 | cur.execute( |
| 510 | f"COPY (SELECT a, b FROM numbers_scale3) " |
| 511 | f"TO 's3://copytos3/test/ss_193/{format}' " |
| 512 | f"WITH (AWS CONNECTION = aws_conn_ss_193, FORMAT = '{format}')".encode() |
| 513 | ) |
| 514 | cur.execute( |
| 515 | f"CREATE TABLE numbers_from_{format} (a DECIMAL(10, 2), b NUMERIC(10, 2))".encode() |
| 516 | ) |
| 517 | cur.execute( |
| 518 | f"COPY INTO numbers_from_{format} " |
| 519 | f"FROM 's3://copytos3/test/ss_193/{format}' " |