> **Can't find what you're looking for?** Use `search_docs` on the docs MCP server at `https://docs.ampdb.io/api/mcp` to find what you need.

# EVM Functions

EVM chains store most of what contracts do as raw bytes: the events they emit, the calls transactions make to them, and
the values they return. These functions turn those bytes into columns you can read, filter, and aggregate, using the
contracts' Solidity signatures. They also convert token amounts between their raw values and their decimals.

They're built into Amp, so batch queries, [streaming queries](/querying/streaming-queries), and
[derived datasets](/datasets/derived-datasets) can all call them, with no setup. To call a contract's read-only
functions from SQL, see [`eth_call`](/querying/functions/eth-call).

| Function                                  | What it does                                               |
| ----------------------------------------- | ---------------------------------------------------------- |
| [`evm_decode_hex`](/querying/functions/evm#evm_decode_hex)       | Turns an address or a hash into a `0x` string.             |
| [`evm_encode_hex`](/querying/functions/evm#evm_encode_hex)       | Turns a `0x` string into an address or a hash.             |
| [`evm_topic`](/querying/functions/evm#evm_topic)                 | Computes an event's `topic0` from its signature.           |
| [`evm_decode_log`](/querying/functions/evm#evm_decode_log)       | Decodes an event log's parameters.                         |
| [`evm_decode_params`](/querying/functions/evm#evm_decode_params) | Decodes the arguments of a function call.                  |
| [`evm_encode_params`](/querying/functions/evm#evm_encode_params) | Encodes a function call: its selector, then its arguments. |
| [`evm_decode_type`](/querying/functions/evm#evm_decode_type)     | Decodes an ABI-encoded value of any Solidity type.         |
| [`evm_encode_type`](/querying/functions/evm#evm_encode_type)     | ABI-encodes a value as a Solidity type.                    |
| [`shift_units`](/querying/functions/evm#shift_units)             | Moves a number's decimal point, to convert token amounts.  |

## Prerequisites

* A [connection](/querying/connecting) to a query server. The examples use `ampsql`.
* To run the examples, the [example datasets](/querying/connecting#example-datasets). Their `logs` and `transactions`
  tables hold the raw data these functions decode (see the [EVM table schemas](/datasets/tables/evm-rpc)).

## Hex

Amp stores addresses and hashes as bytes: an address is a `FixedSizeBinary(20)`, and a block hash, a transaction hash,
or a log topic is a `FixedSizeBinary(32)`. `ampsql` shows them in hexadecimal, without a `0x`. These two functions
convert between them and the `0x` strings that block explorers and wallets use.

### `evm_decode_hex`

Converts an address or a hash to a `0x` string.

```sql
evm_decode_hex(bytes)
```

* **`bytes`**: `FixedSizeBinary(20)` or `FixedSizeBinary(32)`, like `miner`, `hash`, or `topic1`.
* **Returns**: `Utf8`, the bytes in lowercase hexadecimal, starting with `0x`. NULL when `bytes` is NULL.
* **Errors**: any other type, including `Binary` columns like `input` and `data`, fails with
  `Failed to coerce arguments to satisfy a call to 'evm_decode_hex' function`. For those, use DataFusion's `encode`:
  `'0x' || encode(input, 'hex')`.

```sql
SELECT block_num, evm_decode_hex(miner) AS builder, evm_decode_hex(hash) AS hash
FROM "acme/eth_mainnet".blocks
ORDER BY block_num
LIMIT 2
```

```text [Output]
+-----------+--------------------------------------------+--------------------------------------------------------------------+
| block_num | builder                                    | hash                                                               |
+-----------+--------------------------------------------+--------------------------------------------------------------------+
| 24000000  | 0xdadb0d80178819f2319190d340ce9a924f783711 | 0x738cc1716ea1f08adac2d4e2230aedcee2d5cd3f65d66d5d3597e05d710a3d50 |
| 24000001  | 0xdadb0d80178819f2319190d340ce9a924f783711 | 0x1353cf08daa372d4f13d8a95721dbd406650c78a602c0bfa1867042275637c3c |
+-----------+--------------------------------------------+--------------------------------------------------------------------+
```

### `evm_encode_hex`

Converts a hexadecimal string to an address or a hash, to compare columns with it. Comparing bytes this way converts
your value once, where `evm_decode_hex(address) = '0x…'` converts every row.

```sql
evm_encode_hex(hex)
```

* **`hex`**: `Utf8`, a string literal of 40 or 64 hexadecimal digits, with or without `0x`, in any case.
* **Returns**: `FixedSizeBinary(20)` for 40 digits, `FixedSizeBinary(32)` for 64.
* **Errors**:
  * Another number of digits: `evm_encode_hex: expected 20 or 32 bytes, got 2`.
  * A character that isn't hexadecimal: `evm_encode_hex: invalid hex string: invalid character 'z' at position 0`.
  * NULL: `evm_encode_hex: input cannot be null`.
  * A column or a computed value instead of a literal: `evm_encode_hex: expected scalar argument but got Utf8`.

```sql
SELECT COUNT(*) AS weth_logs
FROM "acme/eth_mainnet".logs
WHERE address = evm_encode_hex('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')
```

```text [Output]
+-----------+
| weth_logs |
+-----------+
| 6635      |
+-----------+
```

## Event logs

A contract emits an event as a log. The `logs` table has its parts:

* `topic0`: what event it is, the Keccak-256 hash of the event's [signature](/querying/functions/evm#signatures).
* `topic1`, `topic2`, and `topic3`: up to three of its parameters, those its signature marks `indexed`. A log with fewer
  has NULL in the others.
* `data`: the rest of its parameters, ABI-encoded.

### `evm_topic`

Computes the `topic0` of an event, to find its logs.

```sql
evm_topic(signature)
```

* **`signature`**: `Utf8`, a string literal: the event's [signature](/querying/functions/evm#signatures). Parameter names and `indexed` don't
  change the result.
* **Returns**: `FixedSizeBinary(32)`, the Keccak-256 hash of the event's name and parameter types, like
  `Transfer(address,address,uint256)`.
* **Errors**:
  * A signature that doesn't parse: `parse event signature: parser error`, followed by where and why.
  * An event marked `anonymous`, which has no `topic0`: `anonymous event Foo has no topic0`.
  * A column or a computed value instead of a literal:
    `evm_topic: expected scalar argument for the signature but got Utf8`.

```sql
SELECT evm_decode_hex(evm_topic('Transfer(address indexed from, address indexed to, uint256 value)')) AS topic0
```

```text [Output]
+--------------------------------------------------------------------+
| topic0                                                             |
+--------------------------------------------------------------------+
| 0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef |
+--------------------------------------------------------------------+
```

Since `indexed` isn't part of the hash, events with the same name and types share a `topic0`. ERC-20 and ERC-721
`Transfer` events both have this one: an ERC-721 transfer has its token ID in `topic3`, where an ERC-20 transfer has
nothing.

### `evm_decode_log`

Decodes a log's parameters into a struct.

```sql
evm_decode_log(topic1, topic2, topic3, data, signature)
```

| Argument                     | Type                  | Description                                                                                                |
| ---------------------------- | --------------------- | ---------------------------------------------------------------------------------------------------------- |
| `topic1`, `topic2`, `topic3` | `FixedSizeBinary(32)` | The log's topics after `topic0`. Pass all three, even if some are NULL.                                    |
| `data`                       | `Binary`              | The log's data.                                                                                            |
| `signature`                  | `Utf8`                | A string literal: the event's [signature](/querying/functions/evm#signatures), with `indexed` on the parameters stored in topics. |

* **Returns**: a struct with a field for each parameter, named after it: the indexed parameters first, then the others.
  Their types follow the [Solidity types](/querying/functions/evm#solidity-types). For an event without parameters, NULL.
* **Bad input**: a field is NULL when its topic is NULL, or when `data` is too short or isn't valid for the signature.
  The query doesn't fail.
* **Errors**:
  * More than three indexed parameters: `event can only have 3 indexed parameters but has 4`.
  * A column or a computed value instead of a literal: `evm_decode_log: expected a string literal for the signature`.
  * A signature that doesn't parse: `parse event signature: parser error`, followed by where and why.

`evm_decode_log` doesn't check `topic0`: it decodes any log you give it with the layout of the signature. Filter on
`topic0 = evm_topic(…)`, and on the contract's `address`, so that it only sees the event you mean:

```sql
SELECT block_num, log_index,
  evm_decode_log(topic1, topic2, topic3, data, 'Transfer(address indexed from, address indexed to, uint256 value)') AS transfer
FROM "acme/eth_mainnet".logs
WHERE address = evm_encode_hex('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48')
  AND topic0 = evm_topic('Transfer(address indexed from, address indexed to, uint256 value)')
ORDER BY block_num, log_index
LIMIT 2
```

```text [Output]
+-----------+-----------+--------------------------------------------------------------------------------------------------------------------+
| block_num | log_index | transfer                                                                                                           |
+-----------+-----------+--------------------------------------------------------------------------------------------------------------------+
| 24000000  | 1         | {from: 5050e08626c499411b5d0e0b5af0e83d3fd82edf, to: 88e6a0c2ddd26feeb64f039a2c41296fcb3f5640, value: 13587372645} |
| 24000000  | 9         | {from: 5050e08626c499411b5d0e0b5af0e83d3fd82edf, to: 52aa899454998be5b000ad077a46bbe360f4e497, value: 13462155827} |
+-----------+-----------+--------------------------------------------------------------------------------------------------------------------+
```

To use the fields, name them after the struct, like `transfer.value`, quoting those that are SQL keywords. Here, with
the amounts in USDC, which has 6 decimals:

```sql
WITH transfers AS (
  SELECT block_num, log_index,
    evm_decode_log(topic1, topic2, topic3, data, 'Transfer(address indexed from, address indexed to, uint256 value)') AS transfer
  FROM "acme/eth_mainnet".logs
  WHERE address = evm_encode_hex('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48')
    AND topic0 = evm_topic('Transfer(address indexed from, address indexed to, uint256 value)')
)
SELECT
  evm_decode_hex(transfer."from") AS sender,
  evm_decode_hex(transfer."to") AS recipient,
  shift_units(transfer.value, -6) AS usdc
FROM transfers
ORDER BY block_num, log_index
LIMIT 3
```

```text [Output]
+--------------------------------------------+--------------------------------------------+--------------+
| sender                                     | recipient                                  | usdc         |
+--------------------------------------------+--------------------------------------------+--------------+
| 0x5050e08626c499411b5d0e0b5af0e83d3fd82edf | 0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640 | 13587.372645 |
| 0x5050e08626c499411b5d0e0b5af0e83d3fd82edf | 0x52aa899454998be5b000ad077a46bbe360f4e497 | 13462.155827 |
| 0x05ff6964d21e5dae3b1010d5ae0465b3c450f381 | 0x33a3a62bda3c338c1ec9cf69e4e2fff908dcf20b | 89118.973142 |
+--------------------------------------------+--------------------------------------------+--------------+
```

Decoding ERC-721 transfers takes their own signature, with the token ID indexed, and `topic3 IS NOT NULL` to leave out
ERC-20 transfers:

```sql
SELECT evm_decode_hex(address) AS collection,
  evm_decode_log(topic1, topic2, topic3, data, 'Transfer(address indexed from, address indexed to, uint256 indexed tokenId)') AS transfer
FROM "acme/eth_mainnet".logs
WHERE topic0 = evm_topic('Transfer(address indexed from, address indexed to, uint256 indexed tokenId)')
  AND topic3 IS NOT NULL
ORDER BY block_num, log_index
LIMIT 2
```

```text [Output]
+--------------------------------------------+------------------------------------------------------------------------------------------------------------------+
| collection                                 | transfer                                                                                                         |
+--------------------------------------------+------------------------------------------------------------------------------------------------------------------+
| 0xc36442b4a4522e871399cd717abdd847ab11fe88 | {from: 0000000000000000000000000000000000000000, to: 5c97a2b1ca2ba399271b209a8c39010fe83744dc, tokenId: 1151418} |
| 0x059edd72cd353df5106d2b9cc5ab83a52287ac3a | {from: da369b13839897988cf2bb13040cd63db91fc646, to: 44fd2eb9b3d009e15170b5dd35c432b4f694023a, tokenId: 2595}    |
+--------------------------------------------+------------------------------------------------------------------------------------------------------------------+
```

An indexed parameter of type `string`, `bytes`, array, or tuple doesn't hold its value: the topic holds its Keccak-256
hash, so its field is that hash, a `FixedSizeBinary(32)`.

`evm_decode` is an older name for `evm_decode_log`. It still works, but use `evm_decode_log` in new queries.

## Function calls

A transaction that calls a contract carries the call in its `input` column, its calldata: a 4-byte selector, which is
the start of the Keccak-256 hash of the function's name and parameter types, followed by the ABI-encoded arguments.

### `evm_decode_params`

Decodes the arguments of a function call into a struct.

```sql
evm_decode_params(data, signature)
```

* **`data`**: `Binary`, calldata that starts with the selector, like `input`.
* **`signature`**: `Utf8`, a string literal: the function's [signature](/querying/functions/evm#signatures), with every parameter named.
* **Returns**: a struct with a field for each parameter, named after it. Their types follow the
  [Solidity types](/querying/functions/evm#solidity-types). For a function without parameters, an empty struct.
* **Bad input**: NULL when `data` is NULL, shorter than 4 bytes, starts with another function's selector, or has
  arguments that don't decode. The query doesn't fail, so `IS NOT NULL` finds the calls to a function.
* **Errors**:
  * A parameter without a name: `function transfer has unnamed input parameter`.
  * A column or a computed value instead of a literal: `evm_decode_params: expected a string literal for the signature`.
  * A signature that doesn't parse: `parse function signature: parser error`, followed by where and why.

```sql
SELECT tx_index, evm_decode_hex("to") AS token,
  evm_decode_params(input, 'transfer(address to, uint256 amount)') AS call
FROM "acme/eth_mainnet".transactions
WHERE block_num = 24000000
  AND evm_decode_params(input, 'transfer(address to, uint256 amount)') IS NOT NULL
ORDER BY tx_index
LIMIT 3
```

```text [Output]
+----------+--------------------------------------------+----------------------------------------------------------------------------------+
| tx_index | token                                      | call                                                                             |
+----------+--------------------------------------------+----------------------------------------------------------------------------------+
| 3        | 0x1f573d6fb3f13d689ff844b4ce37794d79a7ff1c | {to: 8bbd04733c4527b7fc72aa656a0cb8e45627e8ff, amount: 125000000000000000000}    |
| 6        | 0x45e02bc2875a2914c4f585bbf92a6f28bc07cb70 | {to: d1ccd2087b4f4609884223b8f2e2e0ec1019f856, amount: 8391140000000000000000}   |
| 7        | 0x16cda4028e9e872a38acb903176719299beaed87 | {to: d862cdcfeb856c32b3c4f7563f4811d8ddfd42e2, amount: 642078100000000000000000} |
+----------+--------------------------------------------+----------------------------------------------------------------------------------+
```

This finds direct calls only: a transfer that a transaction makes through another contract isn't in its `input`. Its
`Transfer` log is, so decode logs to find every transfer.

### `evm_encode_params`

Encodes a function call: its selector, then its arguments. Use it to build calldata for
[`eth_call`](/querying/functions/eth-call), or to compare `input` with a known call.

```sql
evm_encode_params(argument, …, signature)
```

* **`argument, …`**: one value per parameter, in order. Each must convert to its parameter's type (see
  [SQL values](/querying/functions/evm#sql-values)). A function without parameters takes none.
* **`signature`**: `Utf8`, a string literal, last: the function's [signature](/querying/functions/evm#signatures), with every parameter named.
* **Returns**: `Binary`, the calldata. For a function without parameters, only its selector.
* **Errors**:
  * Too many or too few arguments: `evm_encode_params: expected 2 arguments for the function signature but got 1`.
  * A value that doesn't convert, like a signed integer for a `uint256`: `expected int type, got uint256` (see
    [SQL values](/querying/functions/evm#sql-values) for the others).
  * A parameter without a name: `function transfer has unnamed input parameter`.

The calldata of `balanceOf` for each of the blocks' builders:

```sql
SELECT DISTINCT evm_decode_hex(miner) AS builder, evm_encode_params(miner, 'balanceOf(address owner)') AS calldata
FROM "acme/eth_mainnet".blocks
ORDER BY builder
LIMIT 2
```

```text [Output]
+--------------------------------------------+--------------------------------------------------------------------------+
| builder                                    | calldata                                                                 |
+--------------------------------------------+--------------------------------------------------------------------------+
| 0x18bb896994283bd9c16aa2072777a97c12f1b290 | 70a0823100000000000000000000000018bb896994283bd9c16aa2072777a97c12f1b290 |
| 0x27cf32b7ea02f1fbf96b5390cd4e3212eaae97c5 | 70a0823100000000000000000000000027cf32b7ea02f1fbf96b5390cd4e3212eaae97c5 |
+--------------------------------------------+--------------------------------------------------------------------------+
```

`evm_decode_params` reads back what `evm_encode_params` writes, given the same signature. Tuples decode to structs, with
fields named `c0`, `c1`, and so on, and arrays to lists:

```sql
SELECT evm_decode_params(
  evm_encode_params(struct(7, 'gm'), [1, 2], 'f((int256,string) order, int64[] ids)'),
  'f((int256,string) order, int64[] ids)'
) AS call
```

```text [Output]
+---------------------------------------+
| call                                  |
+---------------------------------------+
| {order: {c0: 7, c1: gm}, ids: [1, 2]} |
+---------------------------------------+
```

## ABI values

Contracts encode values in a standard format, the ABI encoding: in log data, in calldata, and in what their functions
return. These two functions decode and encode any value in it, given its Solidity type.

### `evm_decode_type`

Decodes an ABI-encoded value.

```sql
evm_decode_type(data, type)
```

* **`data`**: `Binary`, the encoded value. Convert a `FixedSizeBinary` column, like a topic, first:
  `arrow_cast(topic1, 'Binary')`.
* **`type`**: `Utf8`, a string literal: a Solidity type, like `uint256`, `address`, `string[]`, or
  `(uint256, address, bool)` for a tuple.
* **Returns**: the value, as its [Solidity type's Arrow type](/querying/functions/evm#solidity-types). A tuple is a struct with fields named
  `c0`, `c1`, and so on.
* **Bad input**: unlike the other decoding functions, a row whose `data` is NULL or isn't a valid encoding of the type,
  like empty data, fails the whole query. To get NULL for those rows instead,
  [decode inside a `CASE`](/querying/functions/evm#decode-data-that-may-not-match).
* **Errors**:
  * A row that doesn't decode: `Column 'value' is declared as non-nullable but contains null values`, where `value` is
    the result's name.
  * A type that doesn't parse: `failed to parse Solidity type: invalid type string: foo`.
  * A column or a computed value instead of a literal:
    `evm_decode_type: expected scalar argument for the Solidity type`.

The data of a Uniswap v3 `Swap` log is a tuple of its five parameters that aren't indexed:

```sql
SELECT log_index, evm_decode_type(data, '(int256, int256, uint160, uint128, int24)') AS swap
FROM "acme/eth_mainnet".logs
WHERE topic0 = evm_topic('Swap(address indexed sender, address indexed recipient, int256 amount0, int256 amount1, uint160 sqrtPriceX96, uint128 liquidity, int24 tick)')
ORDER BY block_num, log_index
LIMIT 2
```

```text [Output]
+-----------+--------------------------------------------------------------------------------------------------------------------------+
| log_index | swap                                                                                                                     |
+-----------+--------------------------------------------------------------------------------------------------------------------------+
| 2         | {c0: 13587372645, c1: -4390449020958779887, c2: 1424397008595664404064310177661188, c3: 1208986767454552710, c4: 195948} |
| 7         | {c0: -2469029013265237271, c1: 7638830869, c2: 4406312411585734906752943, c3: 552602188399771836, c4: -195951}           |
+-----------+--------------------------------------------------------------------------------------------------------------------------+
```

#### Decode data that may not match

The filter above keeps only logs that hold a `Swap`'s data. Without one that's as exact, some rows may not decode.
ERC-20 and ERC-721 tokens share the `Transfer` event's `topic0`, for example, but an ERC-721 `Transfer` indexes its last
parameter, so its `data` is empty. Decoding the `data` of every `Transfer` fails the query, because of the NFT
transfers:

:::terminal
```bash
ampsql "SELECT log_index, evm_decode_type(data, 'uint256') AS value
  FROM \"acme/eth_mainnet\".logs
  WHERE block_num = 24000000 AND log_index BETWEEN 384 AND 389
    AND topic0 = evm_topic('Transfer(address indexed from, address indexed to, uint256 value)')
  ORDER BY log_index"
```

```text
✗ query failed
  → gRPC status error
    → code: 'Internal error', message: "query execution error | Caused by: Arrow error: Invalid argument error: Column 'value' is declared as non-nullable but contains null values -> Invalid argument error: Column 'value' is declared as non-nullable but contains null values"
```
:::

Inside a `CASE`, a row that doesn't decode returns NULL, and the query keeps going:

:::terminal
```bash
ampsql "SELECT log_index, CASE WHEN data <> X'' THEN evm_decode_type(data, 'uint256') END AS value
  FROM \"acme/eth_mainnet\".logs
  WHERE block_num = 24000000 AND log_index BETWEEN 384 AND 389
    AND topic0 = evm_topic('Transfer(address indexed from, address indexed to, uint256 value)')
  ORDER BY log_index"
```

```text
+-----------+---------------------------+
| log_index | value                     |
+-----------+---------------------------+
| 384       | 2452957655560666812817717 |
| 388       |                           |
| 389       |                           |
+-----------+---------------------------+
```
:::

* The `CASE` needs a condition that can be false for some rows, like `data <> X''`. Amp drops a condition it knows is
  always true, like `data IS NOT NULL` on a column that's never NULL, and with it the `CASE`, so the query still fails.
* Data that passes the condition but still doesn't decode returns NULL too.
* Test the `CASE`'s result for NULLs, not `evm_decode_type`'s: Amp assumes the function never returns NULL, so
  `WHERE evm_decode_type(data, 'uint256') IS NULL` matches no rows, even when some don't decode.

Decoding what [`eth_call`](/querying/functions/eth-call) returns works the same way: a failed call returns NULL or empty
`data` (see [handle failed calls](/querying/functions/eth-call#handle-failed-calls)).

### `evm_encode_type`

ABI-encodes a value as a Solidity type.

```sql
evm_encode_type(value, type)
```

* **`value`**: a SQL value that converts to the type (see [SQL values](/querying/functions/evm#sql-values)), or a column of them.
* **`type`**: `Utf8`, a string literal: a Solidity type.
* **Returns**: `Binary`, the encoded value.
* **Errors**:
  * A value that doesn't convert: like `cannot convert string to solidity type uint256`, or
    `cannot convert int to solidity int8: 300 not in [-128, 127]` (see [SQL values](/querying/functions/evm#sql-values)).
  * A NULL literal: `Unsupported type Null for Solidity type uint64`.
  * A type that doesn't parse: `failed to parse Solidity type: invalid size for type: uint7`.

:::warning
A NULL in a column encodes as zero, without an error. Filter out NULLs first, with `WHERE value IS NOT NULL`.
:::

SQL's integer literals are signed, so cast them to encode an unsigned type:

```sql
SELECT evm_encode_type(CAST(12345 AS BIGINT UNSIGNED), 'uint256') AS encoded
```

```text [Output]
+------------------------------------------------------------------+
| encoded                                                          |
+------------------------------------------------------------------+
| 0000000000000000000000000000000000000000000000000000000000003039 |
+------------------------------------------------------------------+
```

Composite values work too, and `evm_decode_type` reads them back given the same type:

```sql
SELECT evm_decode_type(
  evm_encode_type(struct(1, 2, ['a', 'b']), '(int256, int8, string[])'),
  '(int256, int8, string[])'
) AS decoded
```

```text [Output]
+----------------------------+
| decoded                    |
+----------------------------+
| {c0: 1, c1: 2, c2: [a, b]} |
+----------------------------+
```

## Token amounts

Tokens store amounts as whole numbers, in their smallest unit, and declare how many decimals to show: 1 ETH is
1,000,000,000,000,000,000 wei, with 18 decimals, and 1 USDC is 1,000,000 of its units, with 6.

| Token              | Decimals | Raw to readable           | Readable to raw          |
| ------------------ | -------- | ------------------------- | ------------------------ |
| ETH, WETH, and DAI | 18       | `shift_units(value, -18)` | `shift_units(value, 18)` |
| USDC and USDT      | 6        | `shift_units(value, -6)`  | `shift_units(value, 6)`  |
| WBTC               | 8        | `shift_units(value, -8)`  | `shift_units(value, 8)`  |

### `shift_units`

Moves a number's decimal point: right to convert a readable amount to its raw value, left to convert it back. It's
exact, whatever the number's size.

```sql
shift_units(value, decimals)
```

* **`value`**: the number: an integer, a float, a decimal, or a `Utf8` string of a number, like `'1.5'` or the
  `value` column of transactions.
* **`decimals`**: an integer, or a `Utf8` string of one, from `-32` to `32`. A positive number multiplies `value` by
  10 to its power, and a negative one divides it.
* **Returns**: `Utf8`, the result in base 10, without trailing zeros. Cast it to compute with it, like
  `CAST(shift_units(value, -18) AS DOUBLE)`, which rounds it. NULL where a `value` column is NULL.
* **Errors**:
  * A `value` that isn't a number: `shift_units: failed to parse value: invalid digit found in string`.
  * `decimals` out of range: `shift_units: units must be between -32 and 32 (got 33)`.
  * `decimals` that isn't an integer: `shift_units: failed to parse integer: invalid digit found in string` for a
    string, `shift_units: expected numeric type for second argument, got Float64` for a float.
  * A NULL literal, or another type: `shift_units: expected numeric type or Utf8 for first argument, got Null`.

:::warning
A NULL in a column of `decimals` counts as `1`. Filter out NULLs first.
:::

```sql
SELECT
  shift_units('1.5', 18) AS wei,
  shift_units('1500000000000000000', -18) AS eth,
  shift_units(100, 6) AS raw_usdc,
  shift_units(13587372645, -6) AS usdc
```

```text [Output]
+---------------------+-----+-----------+--------------+
| wei                 | eth | raw_usdc  | usdc         |
+---------------------+-----+-----------+--------------+
| 1500000000000000000 | 1.5 | 100000000 | 13587.372645 |
+---------------------+-----+-----------+--------------+
```

The ETH that transactions sent, from their `value` in wei:

```sql
SELECT tx_index, value AS wei, shift_units(value, -18) AS eth
FROM "acme/eth_mainnet".transactions
WHERE block_num = 24000000 AND value <> '0'
ORDER BY tx_index
LIMIT 3
```

```text [Output]
+----------+----------+----------------------+
| tx_index | wei      | eth                  |
+----------+----------+----------------------+
| 1        | 862996   | 0.000000000000862996 |
| 4        | 15459352 | 0.000000000015459352 |
| 5        | 7140648  | 0.000000000007140648 |
+----------+----------+----------------------+
```

## Signatures

The decoding functions read signatures as Solidity writes them: the event's or function's name, then its parameters in
parentheses, each with its type and name, like `Transfer(address indexed from, address indexed to, uint256 value)`.

* **Names**: name every parameter, since they name the struct's fields. `evm_decode_params` and `evm_encode_params`
  fail without them, and `evm_decode_log` gives the fields empty names.
* **`indexed`**: marks the event parameters stored in topics, which `evm_decode_log` needs. A signature can have three
  at most.
* **Keywords**: a leading `event` or `function` is optional. Write `anonymous` after an event's parameters if it was
  declared so: it has no `topic0`.
* **Literals**: the signature is a string literal, written in the SQL. The functions work out their result's type from
  it before running the query, so it can't come from a column.

A signature that doesn't parse fails with a message pointing at the problem:

```text
parse event signature: parser error:
Transfer(address from
                     ^
expected `)`
```

## Solidity types

Decoded values get the Arrow type that holds every value of their Solidity type:

| Solidity type                    | Arrow type                                           |
| -------------------------------- | ---------------------------------------------------- |
| `bool`                           | `Boolean`                                            |
| `uint8`, `uint16`                | `UInt8`, `UInt16`                                    |
| `uint24` to `uint32`             | `UInt32`                                             |
| `uint40` to `uint64`             | `UInt64`                                             |
| `uint72` to `uint120`            | `Decimal128(38, 0)`                                  |
| `uint128` to `uint248`           | `Decimal256(76, 0)`                                  |
| `uint256`                        | `Utf8`, the number in base 10                        |
| `int8` to `int256`               | The same as `uint`, with signed integers (`Int8`, …) |
| `address`                        | `FixedSizeBinary(20)`                                |
| `bytes1` to `bytes32`            | `FixedSizeBinary(1)` to `FixedSizeBinary(32)`        |
| `bytes`                          | `Binary`                                             |
| `string`                         | `Utf8`                                               |
| `T[]`                            | `List` of `T`'s type                                 |
| `T[N]`                           | `FixedSizeList` of `N` of `T`'s type                 |
| Tuple, like `(uint256, address)` | `Struct`, with fields `c0`, `c1`, and so on          |

`uint256` and `int256` values don't fit in any Arrow number, so they're strings. `shift_units` takes them as they are.
To compare or sort them as numbers, cast them: `CAST(value AS DECIMAL(76, 0))`.

### SQL values

`evm_encode_type` and `evm_encode_params` convert each SQL value to its Solidity type. They don't convert between kinds
of values, like strings and numbers, and fail on a value that doesn't fit:

| Solidity type         | SQL value                                                                                       |
| --------------------- | ----------------------------------------------------------------------------------------------- |
| `uint…`               | An unsigned integer, like `CAST(12345 AS BIGINT UNSIGNED)`, or a decimal without decimal places |
| `int…`                | A signed integer, like `12345`, or a decimal without decimal places                             |
| `address`             | A `FixedSizeBinary(20)`, like an address column or `evm_encode_hex('0x…')`                      |
| `bytes1` to `bytes32` | A `FixedSizeBinary` of the same size                                                            |
| `bytes`               | A `Binary`, like `X'cafe'`                                                                      |
| `string`              | A string                                                                                        |
| `bool`                | `true` or `false`                                                                               |
| `T[]`                 | A list, like `[1, 2, 3]`                                                                        |
| `T[N]`                | A fixed-size list of `N` values, like `arrow_cast([1, 2, 3], 'FixedSizeList(3, Int64)')`        |
| Tuple                 | A struct with as many fields, like `struct(1, 'a')`                                             |

* SQL's integer literals are signed (`Int64`): a literal for a `uint` fails with `expected int type, got uint256`, and
  an unsigned integer for an `int` fails with `expected uint type, got int256`.
* Literals too large for `Int64` are floats, which no type accepts. Write them as decimals instead:
  `CAST('1000000000000000000000' AS DECIMAL(38, 0))`, or `DECIMAL(76, 0)` for up to 76 digits.
* A decimal with decimal places fails, like `Unsupported type Decimal128(10, 1) for Solidity type uint256`: Amp doesn't
  round it. Use [`shift_units`](/querying/functions/evm#shift_units) to convert amounts to whole numbers first.
* A negative decimal for a `uint` fails the query.
* A number out of the type's range fails, like `cannot convert int to solidity int8: 300 not in [-128, 127]`.