Date: 2012-03-15 16:29:32 +0100
From: Erietta Liarou <<Erietta.Liarou>>
To: SQL devs <>
Version: -- development
CC: @njnes, @drstmane

Last updated: 2012-05-25 12:58:45 +0200

Comment 17082

Date: 2012-03-15 16:29:32 +0100
From: Erietta Liarou <<Erietta.Liarou>>

User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_6_7; en-us) AppleWebKit/533.21.1 (KHTML, like Gecko) Version/5.0.5 Safari/533.21.1
Build Identifier:

mserver does not load datacell functions when restarted on an old database

Reproducible: Always

Steps to Reproduce:

0. remove demo database from /dbfarm
1. mserver5 --dbname=demo

2. sql.start();

3. select * from datacell.queries();

4. \q

5. mserver5 --dbname=demo

6. sql.start();

7. select * from datacell.queries();

Actual Results:

select * from datacell.queries();
!SELECT: no such operator 'queries'

Expected Results:

select * from datacell.queries();
&1 0 0 8 0
% ., ., ., ., ., ., ., . table_name
% nme, status, seen, cycles, events, time, error, def name
% clob, clob, timestamp, int, int, bigint, clob, clob type
% 0, 0, 26, 1, 1, 1, 0, 0 length

Comment 17083

Date: 2012-03-15 17:13:34 +0100
From: @grobian

not limited to datacell, udf.reverse doesn't work either

Comment 17089

Date: 2012-03-15 19:36:28 +0100
From: @drstmane

IMHO, files in createdb are only executed on creation of a newly create database.

Of course, between release, we should provide an upgrade path.

This is most probably no available for datacell --- but then, datacell has never been released formally --- and might indeed also not correctly exist for UDF.

I'll check the latter tomorrow ...

Comment 17117

Date: 2012-04-05 13:23:44 +0200
From: @sjoerdmullender

Stefan's and Fabian's comments are, I'm afraid, not relevant. As Erietta says, if you start with a non-existing database which gets initialized when the server starts first up, the query works. If you then stop the server and start it again on the already initialized database, the query does not work.
The problem doesn't have to do anything with upgrades.

Comment 17118

Date: 2012-04-05 13:27:34 +0200
From: @sjoerdmullender

By the way, I can reproduce the problem, even though when the server is started the second time, I get:

sql>select f.* from functions f, schemas s where f.name = 'queries' and f.schema_id = s.id and s.name = 'datacell';
+------+---------+--------------------+----------+-------+------+-------+------+
| id | name | func | mod | sql | type | side_ | sche |
: : : : : : : effec : ma_i :
: : : : : : : t : d :
+======+=========+====================+==========+=======+======+=======+======+
| 5897 | queries | create function da | datacell | true | 1 | false | 5803 |
: : : tacell.queries() : : : : : :
: : : returns table( nme : : : : : :
: : : string, status st : : : : : :
: : : ring, seen times...> : : : : :
+------+---------+--------------------+----------+-------+------+-------+------+
1 tuple (5.560ms) !1 field truncated!

Comment 17119

Date: 2012-04-05 13:28:58 +0200
From: @grobian

my comment still stands, I can reproduce with udf.reverse -- thus making the problem non-datacell specific ;) I changed the subject, see history.

Comment 17159

Date: 2012-04-25 15:50:51 +0200
From: @njnes

fabian could you show howto reproduce this using udf.reverse?

Comment 17203

Date: 2012-05-01 11:00:11 +0200
From: @grobian

I can't reproduce any more, select udf.reverse('bla') works fine now.

Comment 17205

Date: 2012-05-02 09:40:02 +0200
From: @grobian

The problem only occurs with datacell. I've done quite some patching, but all in vain. I suspect that the definition of datacell.queries is not available somehow. Hence I moved definitions of datacell before sql, but that didn't help either (with fixing the runtime symbol lookup problems).

Comment 17207

Date: 2012-05-02 11:52:43 +0200
From: @njnes

the table returning functions require a 'table definition'. These are stored in the _tables table. Somehow with private schema's this goes wrong.

Comment 17208

Date: 2012-05-02 11:56:28 +0200
From: @njnes

it seems the tables types are stored in the sys._tables table instead of the datacell tables table.

Comment 17209

Date: 2012-05-02 13:36:18 +0200
From: @njnes

properly store the functions return type into the tables table in the schema of the function

Comment 17210

Date: 2012-05-02 13:54:08 +0200
From: @njnes

correction, all return types should stay in the sys.tables table (only tmp also
has a tmp.tables table). Now we correctly lookup the return types / tables in the sys schema.

Comment 17211

Date: 2012-05-02 16:00:14 +0200
From: @njnes

Changeset 4abb5d147a2d made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=4abb5d147a2d

Changeset description:

added test for bug #3063
fixed bug by using sys schema for all table return types