Join the SingleStore Community Today
Get expert advice, develop skills, and connect with others.

# NOPARAM

Disables the parameterization of constants in a query before a query plan is compiled.

# Syntax

``````NOPARAM (expression)
``````

# Arguments

• expression: any expression. This may be a constant (most common), column name, the result of another function, or a math operation.

# Remarks

• When `NOPARAM` is used, the constants in a query are not converted into parameters. So, a separate query plan is generated even for the queries with the same structure.
• Parameterization can only be disabled for constants in a query.

# Examples

Example 1: Let us look at few queries and their query plans when `NOPARAM` function is used. A query plan can be viewed in the `QUERY_TEXT` field of the `information_schema.PLANCACHE` table.

For example, the following two queries with different parameter values,

``````SELECT * from t WHERE x = 5;
SELECT * from t WHERE x = 30;
``````

use the same query plan:

``````QUERY_TEXT: SELECT * from t WHERE x = @
``````

Whereas, when the constants in these queries are enclosed in `NOPARAM`, a separate plan is generated for each query. For example, for the following queries,

``````SELECT * from t WHERE x = NOPARAM(5);
SELECT * from t WHERE x = NOPARAM(30);
``````

different query plans are generated:

``````QUERY_TEXT: SELECT * from t WHERE x = NOPARAM(5)
QUERY_TEXT: SELECT * from t WHERE x = NOPARAM(30)
``````

Example 2: Let us look at an example where `NOPARAM` is used for variable offsets. Consider a table `orders` with two fields `orderid` and `orderdate`.

The following queries with different parameter values generate the same query plan, which can be viewed in the `QUERY_TEXT` field of the `information_schema.PLANCACHE` table.

``````SELECT COUNT(orderid) FROM orders WHERE orderdate <= DATE_ADD(CURRENT_DATE(), INTERVAL -30 DAY);
SELECT COUNT(orderid) FROM orders WHERE orderdate <= DATE_ADD(CURRENT_DATE(), INTERVAL -20 DAY);
``````
``````QUERY_TEXT: SELECT COUNT(orderid) FROM orders WHERE orderdate <= DATE_ADD(CURRENT_DATE(), INTERVAL @ DAY)
``````

Whereas when `NOPARAM` function is used for the same queries,

``````SELECT COUNT(orderid) FROM orders WHERE orderdate <= NOPARAM(DATE_ADD(CURRENT_DATE(), INTERVAL -30 DAY));
SELECT COUNT(orderid) FROM orders WHERE orderdate <= NOPARAM(DATE_ADD(CURRENT_DATE(), INTERVAL -20 DAY));
``````

different query plans are generated:

``````QUERY_TEXT: SELECT COUNT(orderid) FROM orders WHERE orderdate <= NOPARAM(DATE_ADD(CURRENT_DATE(), INTERVAL -30 DAY))
QUERY_TEXT: SELECT COUNT(orderid) FROM orders WHERE orderdate <= NOPARAM(DATE_ADD(CURRENT_DATE(), INTERVAL -20 DAY))
``````

Few other examples of `NOPARAM` function usage are:

``````INSERT INTO t(a) VALUES (NOPARAM(20));

SELECT NOPARAM(x) + NOPARAM(SUM(x)) FROM t;
``````