dbplyr

Amazon Redshift does not support the IS NOT DISTINCT FROM operator, which is commonly used for null-safe comparisons in JOIN ON clauses. In Redshift, standard equality (=) returns NULL if either operand is NULL, meaning rows with NULL values in join columns will not match even if both sides are NULL.

To perform a null-safe join in Redshift, use one of the following two workarounds:

1. Explicit NULL check: Use an OR condition to check for equality or for cases where both columns are NULL.
2. COALESCE with a sentinel value: Use COALESCE to replace NULL with a unique value that does not exist in your actual data.

Note that for multi-column joins, you must apply these handling patterns to every column involved in the join.

-- WORKAROUND 1: Explicit NULL check
SELECT *
FROM a
JOIN b ON (a.col = b.col) OR (a.col IS NULL AND b.col IS NULL)

-- WORKAROUND 2: Using COALESCE with a sentinel value
SELECT *
FROM a
JOIN b ON COALESCE(a.col, 'sentinel_value') = COALESCE(b.col, 'sentinel_value')

To use dbplyr, you first establish a connection using the DBI package. You can then use tbl() to create a remote table reference. Once created, you can use standard dplyr verbs on this object.

All dplyr calls are evaluated lazily, meaning they generate SQL that is only sent to the database when you explicitly request the data (e.g., via collect()).

library(dplyr, warn.conflicts = FALSE)

# 1. Connect to a database (example using SQLite in-memory)
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con, mtcars)

# 2. Retrieve a table using tbl()
mtcars2 <- tbl(con, "mtcars")

# 3. Perform lazy dplyr operations
summary <- mtcars2 |> 
  group_by(cyl) |> 
  summarise(mpg = mean(mpg, na.rm = TRUE)) |> 
  arrange(desc(mpg))

# 4. View the generated SQL
summary |> show_query()

# 5. Execute the query and retrieve results as a local data frame
result <- summary |> collect()

MySQL does not support IEEE 754 infinity values (positive or negative infinity) for FLOAT or DOUBLE data types. This limitation affects arithmetic, insertion, and selection of special numeric values.

Arithmetic Behavior

Division operations that would produce infinity in IEEE 754 return NULL instead of Inf or NaN:

SELECT 1/0, 0/0;
-- Result: NULL, NULL

Insertion Behavior

Attempting to insert infinity string literals (e.g., 'Inf', '+Inf', '-Inf', or 'Infinity') typically results in 0.0 being stored. Depending on your SQL mode, this may trigger Error Code 1265: "Data truncated for column" or cause the INSERT to fail entirely in strict mode.

Selection Behavior

When selecting extremely large values (e.g., 1e+52), behavior is platform-dependent. Some platforms return inf/-inf, while others return 0/-0.

Recommended Workarounds

Since MySQL lacks native infinity support, use one of the following strategies:

1. Large numbers: Use arbitrarily large values like 1e308 for positive infinity and -1e308 for negative infinity.
2. Language constants: Use application-level constants like Double.MAX_VALUE or Double.MIN_VALUE.
3. NULL with flags: Represent infinity as NULL and use an additional flag column to indicate if the NULL represents infinity.
4. Separate VARCHAR column: Store the numeric value in a numeric column (using NULL for infinity) and store the text representation (e.g., 'Infinity') in a separate VARCHAR column.
5. Application layer conversion: Convert infinity values to/from alternative representations in your application code before storage and after retrieval.

Best Practices

• Use the DECIMAL type if you require exact numeric values (though DECIMAL does not support special values like infinity).
• Handle infinity cases at the application layer before sending data to MySQL.
• Clearly document how infinity is represented within your database schema.