Table() function with array variable not working

Help Procedural SQL
1

My below code is giving an error. I know this date generation can be done using recursive CTE, but my use case prevents the use of recursive CTE. So could someone please help me convert array variable into table.

DELIMITER //
do declare
start_date date = ‘2023-01-15’;
end_date date = ‘2023-01-17’;
date_array array(date) = create_array(datediff(end_date,start_date)+1);
i int = 0;
begin
while start_date <= end_date loop
date_array[i] = start_date;
start_date = date_add(start_date, interval 1 day);
i = i + 1;
end loop;
echo select t.* from table(date_array) t;
end //
DELIMITER ;

Error Code: 1054. Unhandled exception Type: ER_BAD_FIELD_ERROR (1054) Message: Unknown column ‘date_array’ in ‘function in from clause’ 1 warning Callstack: #0 Line 12 in dataops._$!$_anon_4496441090386677757

2

It appears you can’t use local variables inside the argument to TABLE().

Consider instead using a temp table and inserting all the values in the array into there, then ECHO SELECT the temp table contents. You could insert the data in a loop to the temp table, or maybe use INSERT_ALL (though that requires an array of records).

Or don’t use an array at all, just insert the values into a temp table directly, then ECHO SELECT from it.

3

I’ll open a feature request to allow passing an array variable to TABLE.

1 Like
4

I think this is more than just TABLE.

DELIMITER //
DO DECLARE
a ARRAY(INT);
BEGIN
a = [1,2,3];
echo select LENGTH(a);
END //
DELIMITER ;

Error Code: 1054. Unhandled exception Type: ER_BAD_FIELD_ERROR (1054) Message: Unknown column ‘a’ in ‘field list’ 1 warning Callstack: #0 Line 5 in _$!$_anon_12334713666679535742

Will work in a function but not a procedure. Example is from
ARRAY · SingleStore Documentation just converted to anonymous code block.

5

I got the same error when I converted that to a procedure. Seems like it’s arguably a bug since variable substitution is supposed to work for local variables into queries. I’ll open a bug and follow up with develpers.

1 Like
6

Our current design does not support substituting arrays through variables into SQL. Some workarounds would be to get the information you need in the top level of control, like do

b = length(a)

then pass b in to the SQL statement.

Or, convert the array to a JSON array and pass that into the SQL. Then convert it back to an MPSQL array with a function inside the SQL. Or pass collection-oriented information via a table or temp table.

We’ll consider adding full support for array variable substitution into SQL in the future.

7

Do we have an way without loops to take an array and convert it to a JSON?

8

Not currently. You’d have to build the JSON array by concatenating strings together, or perhaps using JSON_ARRAY_PUSH.

9

JSON_ARRAY_PUSH won’t take a array variable. Only thing that seems to be possible is setting a RECORD in the shape of the Array.

DECLARE
r RECORD(Reference TINYTEXT, Details JSON, Comments TINYTEXT, ModifiedDate DATETIME);
BEGIN
r =records[i];
…

anything else
Error Code: 1054. Unhandled exception Type: ER_BAD_FIELD_ERROR (1054) Message: Unknown column ‘records’ in ‘field list’ 1 warning Callstack: #0 Line 10 in …_$!$_anon_15784459900392139442

10

You can pass in an array as a string containing an array in JSON format. E.g. this works:

singlestore> do declare 
    -> s text;
    -> begin
    -> s = '[1, 2]';
    -> echo select length(json_to_array(s));
    -> end //
+--------------------------+
| length(json_to_array(s)) |
+--------------------------+
|                        2 |
+--------------------------+
11

Hi, are there any updates on supporting an array variable for the table() function?

12

Though this is not optimal the workaround @hanson suggested does work. Going back to this threads original question. Below will give what I believe was the questions desired output.

DELIMITER //
DO DECLARE
	start_date DATE ='2023-01-15';
	end_date DATE ='2023-01-17';
	dates LONGTEXT =CONCAT('"',start_date:>TINYTEXT,'"');
BEGIN
	WHILE start_date < end_date LOOP
		start_date =DATE_ADD(start_date, INTERVAL 1 DAY);
		dates =CONCAT(dates,',"',start_date:>TINYTEXT,'"');
	END LOOP;
	ECHO SELECT JSON_EXTRACT_STRING(JSON_BUILD_OBJECT('val',t.table_col),'val'):>DATE Dates
	FROM TABLE(JSON_TO_ARRAY(CONCAT('[',dates,']'))) t;
END; //
DELIMITER ;

Dates
2023-01-15
2023-01-16
2023-01-17

13

I know there are some workarounds but @hanson mentioned that SingleStore would consider adding support for array variable substitution, I was just wondering if there are any updates on that.

It doesn’t really feel right to be converting to json back and forth just to be able to convert it to a table. I’m just thinking that it might have some performance overhead. I’ve solved my problem by creating a temp table and then used INSERT_ALL.

1 Like
14

No news yet on whether we will support TABLE applied to a array variable. Thanks for the feedback. We’ll consider it for the future. I’m glad you have some workarounds.

1 Like