PDOException error when using Sales Tax Rates of a fraction of a penny on PostgreSQL when adding items to the cart.

Error:

PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "2153.6" LINE 1: ...e_order', '7', '7', 'commerce_order', '0', 'und', '2153.6', ... ^: INSERT INTO field_data_commerce_order_total (entity_type, entity_id, revision_id, bundle, delta, language, commerce_order_total_amount, commerce_order_total_currency_code, commerce_order_total_data) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8); Array ( ) in field_sql_storage_field_storage_write() (line 425 of /modules/field/modules/field_sql_storage/field_sql_storage.module).

Steps to reproduce:

  1. Using PostgreSQL 8.4
  2. Add a Sales Tax Rate of 0.0768
  3. Product Price is $30.00
  4. Click "Add to cart"

It seems to work fine with tax rates of 0.07 and 0.073, but not 0.0737. The SQL is trying to insert "2153.6" inter table "field_data_commerce_total" column "commerce_total_amount" which has a data type of integer and cannot accept a decimal value; "2153.6".

Product Price = $20.00
Sales Tax Percent = 0.0768
Sales Tax Total = $1.536
Grand Total = $21.536

CommentFileSizeAuthor
#4 1071082.rounding_1.patch506 bytesrszrama
#2 1071082.rounding.patch482 bytesrszrama

Comments

rszrama’s picture

Weird - it should be getting converted in presave to an integer. Do you get the same error in MySQL? It should be getting converted to an integer in commerce_price_field_presave().

rszrama’s picture

Status: Active » Needs review
StatusFileSize
new482 bytes

Does the attached patch fix it for you?

bendiy’s picture

Title: SQL Error: Sales Tax Rate data type conversion in PostgreSQL » SQL Error: Pricing Rule Rounding Issue data type conversion in PostgreSQL
Status: Needs review » Active

I'm getting the same error with other pricing rules. But I think Ryan know what's wrong now.

rszrama’s picture

Status: Active » Needs review
Issue tags: -PostgreSQL, -pdo_pgsql
StatusFileSize
new506 bytes

Yeah, that was a bad patch. I was supposed to pass a currency, not a currency code. : P

bendiy’s picture

Status: Needs review » Reviewed & tested by the community

That worked. Thanks.

rszrama’s picture

Status: Reviewed & tested by the community » Fixed

Excellent. Committed.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.