Hi Thomas,
even if i manually set the timeout to 10000000 the exception occurs.
I can not provide you with a simple test case for this since the
process involved is non trivial.
If you want I could provide you with instructions how to reproduce
this behaviour using our test cases - please let me know.
Daniel, another developer of flexive also ran into this issue:
H2 currently chokes on unions of queries with row limits like this:
SELECT * FROM (
(SELECT DISTINCT cd.id,cd.ver,cd.lang FROM
FLEXIVETEST.FX_CONTENT_DATA cd WHERE UFCLOB like 'FOLDER COMMENT 1'
AND TPROP=83 AND cd.ismax_ver=TRUE
LIMIT 10000 )
UNION
(SELECT DISTINCT cd.id,cd.ver,cd.lang FROM
FLEXIVETEST.FX_CONTENT_DATA cd WHERE UFCLOB like 'FOLDER COMMENT 2'
AND TPROP=83 AND cd.ismax_ver=TRUE
LIMIT 10000 )
)
When the subquery limits are removed, the query returns correct
results. With subquery limits, only one row is returned.
> Thanks for the query! Unfortunately it is very hard for me to
> reproduce the problem because I don't have the CREATE TABLE
> statements. Could you send them please? The easiest way to get them is
> running SCRIPT NO DATA TO 'db.sql' and then post the file db.sql (it
> doesn't contain any data).
Here are the create table statements without any constraints, etc.
needed to produce a test case.
Thanks a lot for the time and effort you already put into this, very
appreciated!
Slightly off topic: Do you think it is possible to list flexive as one
of the projects using H2?
Cheers,
Markus
drop schema if exists test;
create schema test;
set schema test;
CREATE CACHED TABLE TEST.FX_CONTENT_DATA COMMENT 'Content data detail
table'(
ID BIGINT NOT NULL,
VER INTEGER NOT NULL,
POS INTEGER COMMENT 'Positioning relative to current hierarchy level
maintained by flexive',
LANG INTEGER NOT NULL,
TPROP BIGINT COMMENT 'FXS_TYPEPROPS id unless its a group',
ASSIGN BIGINT NOT NULL COMMENT 'Id of the assignment',
XDEPTH INTEGER COMMENT 'XPath hierarchy depth',
XPATH TEXT NOT NULL,
XPATHMULT TEXT NOT NULL,
XMULT VARCHAR(255) NOT NULL COMMENT 'Multiplicities for each element
like 1,1,2,5,etc. preceeded by 1 for the root group',
XINDEX INTEGER NOT NULL COMMENT 'Multiplicity of this element',
PARENTXMULT VARCHAR(255) NOT NULL COMMENT 'XMULT of the parent group
(needed for unique positioning), always 1 for entries in the root
group',
PARENTXPATH TEXT NOT NULL,
ISMAX_VER BOOLEAN NOT NULL,
ISLIVE_VER BOOLEAN NOT NULL,
ISGROUP BOOLEAN NOT NULL,
ISMLDEF BOOLEAN NOT NULL COMMENT 'Is this field the multilanguage
default?',
FSELECT BIGINT DEFAULT 0 NOT NULL,
FREF BIGINT DEFAULT NULL COMMENT 'Reference to other content
instances',
FDATE1 DATETIME DEFAULT NULL,
FDATE1_Y INTEGER,
FDATE1_M SMALLINT,
FDATE1_D SMALLINT,
FDATE1_HH SMALLINT,
FDATE1_MM SMALLINT,
FDATE1_SS SMALLINT,
FDATE2 DATETIME DEFAULT NULL,
FDATE2_Y INTEGER,
FDATE2_M SMALLINT,
FDATE2_D SMALLINT,
FDATE2_HH SMALLINT,
FDATE2_MM SMALLINT,
FDATE2_SS SMALLINT,
FBLOB INTEGER,
FCLOB LONGTEXT,
UFCLOB LONGTEXT COMMENT 'Clob with uppercase values',
FINT INTEGER COMMENT 'Integer and for references',
FBIGINT BIGINT,
FTEXT1024 VARCHAR(1024),
UFTEXT1024 VARCHAR(1024),
FDOUBLE DOUBLE,
FFLOAT DOUBLE,
FBOOL BOOLEAN
);
CREATE CACHED TABLE TEST.FXS_TYPEDEF COMMENT 'Type definitions'(
ID BIGINT NOT NULL,
ACL BIGINT NOT NULL COMMENT 'ACL for the type, only checked if
enabled in permissions',
WORKFLOW BIGINT NOT NULL,
NAME VARCHAR(255) NOT NULL COMMENT 'name of the type',
PARENT BIGINT DEFAULT NULL COMMENT 'parent for derived types, null
if this is the parent, cyclic checks have to be performed by the
application',
STORAGE_MODE INTEGER NOT NULL COMMENT 'how are data tables
organized \n0=inbound\n1=outbound\n2=external table (no versioning,
permissions, etc)\n3=memory (nothing persisted, for small amounts of
data only, no query engine available!)',
CATEGORY INTEGER NOT NULL COMMENT '0=system, 1=user',
TYPE_MODE INTEGER NOT NULL COMMENT '0=Content, 1=Relation',
LANG_MODE INTEGER NOT NULL COMMENT '0=no multi language, 1=one
language per instance, 2=multiple languages per field',
TYPE_STATE INTEGER NOT NULL COMMENT '0=available, 1=temporary
unavailable, 2=permanently unavailable\ntables can be unavailable if
structural changes resulted in errors and manual intervention is
needed',
SECURITY_MODE INTEGER NOT NULL COMMENT 'Bit coded field that
indicates which permissions are used. 0=none, 0x01=instance,
0x02=property, 0x04=step, 0x08=type',
TRACKHISTORY BOOLEAN NOT NULL COMMENT 'track changes on instance
data? HISTORY_AGE tells for how long they are kept.',
HISTORY_AGE BIGINT COMMENT 'Days to keep histories for instance
changes if TRACKHISTORY is true. 0=for ever',
MAX_VERSIONS INTEGER NOT NULL COMMENT 'maximum number of versions
to keep (if the storage mode supports versions)\n-1 = unlimited\n0 =
no versioning\n> 0 = number of versions to keep',
REL_TOTAL_MAXSRC BIGINT NOT NULL COMMENT 'If not 0 used to
override the MAXSRC from FXS_TYPERELS for a total amount',
REL_TOTAL_MAXDST BIGINT NOT NULL COMMENT 'If not 0 used to
override the MAXDST from FXS_TYPERELS for a total amount',
ICON_REF BIGINT COMMENT 'Optional reference to a content instance
for a preview icon',
CREATED_BY BIGINT NOT NULL,
CREATED_AT BIGINT NOT NULL,
MODIFIED_BY BIGINT DEFAULT NULL,
MODIFIED_AT BIGINT
);
CREATE MEMORY TABLE TEST.FXS_SEARCHCACHE_MEMORY COMMENT 'memory search
result holder'(
SEARCH_ID BIGINT NOT NULL,
ID BIGINT NOT NULL,
VER INTEGER NOT NULL,
TDEF BIGINT NOT NULL,
CREATED_BY BIGINT NOT NULL
);
SELECT
filter.rownr,filter.id,filter.ver,filter.created_by ,filter.ID
prop_0_0
,filter.VER prop_0_1
,filter.ID prop_1_0
,(SELECT sub.FTEXT1024 FROM FX_CONTENT_DATA sub WHERE sub.id=filter.id
AND sub.ver=filter.ver AND TPROP=101 AND (sub.lang=1 OR
sub.ismldef=true) ORDER BY sub.ismldef LIMIT 1 ) prop_2_0
,concat(filter.xpathPref,(SELECT sub.XPATHMULT FROM FX_CONTENT_DATA
sub WHERE sub.id=filter.id AND sub.ver=filter.ver AND TPROP=101 AND
(sub.lang=1 OR sub.ismldef=true) ORDER BY sub.ismldef LIMIT 1 ))
propX_2_1
,filter.prop_3_0
,concat(filter.xpathPref,(SELECT sub.XPATHMULT FROM FX_CONTENT_DATA
sub WHERE sub.id=filter.id AND sub.ver=filter.ver AND TPROP=105 AND
(sub.lang=1 OR sub.ismldef=true) ORDER BY sub.ismldef LIMIT 1 ))
propX_3_1
FROM (SELECT SET(@rownr,@rownr+1) rownr, * FROM (select
filter.id,filter.ver,filter.created_by,concat(concat(concat(concat
(concat(t.name,'[...@pk='),filter.id),'.'),filter.ver),']') xpathPref ,
(SELECT sub.FINT FROM FX_CONTENT_DATA sub WHERE sub.id=filter.id AND
sub.ver=filter.ver AND TPROP=105 AND (sub.lang=1 OR sub.ismldef=true) /
*offending part start*/ORDER BY sub.ismldef LIMIT 1/*offending part
end*/ ) prop_3_0
,null
FROM FXS_SEARCHCACHE_MEMORY filter, FXS_TYPEDEF t WHERE search_id=410
AND filter.tdef=t.id AND filter.TDEF=6 ORDER BY 5 desc)) filter
ORDER BY 1
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
