Bug with Select count(distinct)
James Milne
james.milne at mac.com
Tue Mar 23 20:52:23 CST 2004
Hi all,
I've had a problem with Valentina crashing when I perform the following
operation:
select distinct( count shotIndex ) from take where sceneIndex = '1'
If I insert some records but don't populate all of the text fields and
varchar fields,
the above query will crash in the method with the following call stack:
Thread 0 Crashed:
0 Plugin 0x01133010
CalculateDistinctCountOnTable__19OBL_CursorTable_TmpFP13FBL_BaseFieldR24
OBL_CursorTable_Indirect + 0x20
1 Plugin 0x01132128
BuildSingleRecord__19OBL_CursorTable_TmpFP10OBL_ParserR24OBL_CursorTable
_Indirect + 0x128
2 Plugin 0x01131688
BuildRecords__19OBL_CursorTable_TmpFP10OBL_ParserR24OBL_CursorTable_Indi
rect + 0x98
3 Plugin 0x011309e0
BuildMultiplicationTable__19OBL_CursorTable_TmpFP10OBL_Parser + 0x70
4 Plugin 0x01130540
BuildTable__19OBL_CursorTable_TmpFP10OBL_Parser + 0x30
5 Plugin 0x010b4c80
BuildCursorTable__10OBL_CursorFv + 0x30
6 Plugin 0x010b4224 Execute__10OBL_CursorFv
+ 0x64
7 Plugin 0x010b5758
ExecuteSql_WithNoUnion__10VDK_CursorFPCc + 0x48
8 Plugin 0x010b56c4
ExecuteSQL__10VDK_CursorFPCc + 0x24
9 Plugin 0x010a4fd8
SqlSelect__12VDK_DataBaseFPCc15ECursorLocation9ELockType16ECursorDirecti
on + 0x68
10 Plugin 0x0100d7c8
DataBase_SqlSelect_aux__FP16REALobjectStructP16REALstringStructiii +
0xa8
11 Plugin 0x0100d53c
DataBase_SqlSelect__FP16REALobjectStructP16REALstringStruct + 0x1c
12 main 0x024ce160 0x2008000 + 0x4c6160
13 main 0x0224e73c 0x2008000 + 0x24673c
14 main 0x02252438 0x2008000 + 0x24a438
15 main 0x026752d8 0x2008000 + 0x66d2d8
16 main 0x025588a8 0x2008000 + 0x5508a8
17 main 0x0255add0 0x2008000 + 0x552dd0
18 main 0x024c00e4 0x2008000 + 0x4b80e4
19 main 0x02251500 0x2008000 + 0x249500
20 main 0x02262dfc 0x2008000 + 0x25adfc
21 main 0x022478cc 0x2008000 + 0x23f8cc
22 main 0x025488c4 0x2008000 + 0x5408c4
23 HXRuntime 0x0288d288 0x27a3840 + 0xe9a48
24 HXRuntime 0x027d5f18 0x27a3840 + 0x326d8
25 HXRuntime 0x028977c8 0x27a3840 + 0xf3f88
26 HXRuntime 0x02832ff8 0x27a3840 + 0x8f7b8
27 HXRuntime 0x02832958 0x27a3840 + 0x8f118
28 HXRuntime 0x02830d40 0x27a3840 + 0x8d500
29 HXRuntime 0x02830f94 0x27a3840 + 0x8d754
30 HXRuntime 0x0282d760 0x27a3840 + 0x89f20
31 main 0x026cf598 0x2008000 + 0x6c7598
32 main 0x02008580 0x2008000 + 0x580
33 ...ple.CoreServices.CarbonCore 0x902d9d74 CCFM_LaunchApplication +
0x1e8
34 MyApp 0x00008840 main + 0x168
35 MyApp 0x00008408 _start + 0x188
36 MyApp 0x0000827c start + 0x30
My schema looks like this:
statement = "create table take ( "
statement = statement + "takeIndex varchar(16) indexed, " ' take
number relative to shot
statement = statement + "shotIndex varchar(16) indexed, " ' shot
number
statement = statement + "sceneIndex varchar(16) indexed, " ' index of
the scene we are in
statement = statement + "takeSortIndex varchar(16) method(
lpad(takeIndex, 6, '0') ), "
statement = statement + "shotSortIndex varchar(16) method(
lpad(shotIndex, 6, '0') ), "
statement = statement + "sceneSortIndex varchar(16) method(
lpad(sceneIndex, 6, '0') ), "
statement = statement + "dayIndex varchar(16) indexed, " ' index of
the day on which the take was made
statement = statement + "daySortIndex varchar(16) method(
lpad(dayIndex, 6, '0') ), "
statement = statement + "cameraID varchar(16), "
statement = statement + "rollNumber varchar(16), "
statement = statement + "frameRate long, "
statement = statement + "dropFrame boolean, "
statement = statement + "recordRate long, "
statement = statement + "playbackRate long, "
statement = statement + "stamp datetime, "
statement = statement + "inPoint double, "
statement = statement + "outPoint double, "
statement = statement + "duration double, "
statement = statement + "identPoint double, "
statement = statement + "refPoint double, "
statement = statement + "loop boolean, "
statement = statement + "inTimecode double, "
statement = statement + "outTimecode double, "
statement = statement + "comment text(255) indexed, "
statement = statement + "commentUpr text(255) method( upper(comment)
), "
statement = statement + "goodTake boolean, "
statement = statement + "motionOmitSound boolean, "
statement = statement + "path text(255), "
statement = statement + "pathState long, "
statement = statement + "backupImage text(255), "
statement = statement + "hasTimecodeTrack boolean, "
statement = statement + "hasTachoTrack boolean, "
statement = statement + "hasSoundTrack boolean, "
statement = statement + "useRecordedTach boolean, "
statement = statement + "useManualTach boolean, "
statement = statement + "inPointPicture picture(1024) " ' image from
in-point of movie
statement = statement + ")"
Yes it is rather a large schema :-) At some point I will refactor it
into a number of
smaller tables which link back to one main take table.
Is there anything missing from my column definitions which might be
causing the
above SQL query to crash? For instance, am I missing some sort of NULL
behaviour
criteria from the column constraints?
It seems there are a few areas where Valentina is a bit shoddy about
validation
when it comes to checking what your doing is correct. For instance,
V4RB will crash
if you try to call VCursor.TextField() on a varchar data type.
Admittedly, this is not
the correct thing to do, but it certainly shouldn't crash hard,
wouldn't you agree?
I can provide a small project which 100% repeatably demonstrates the
crash.
I'm using V4RB 1.10 (dated Dec 23, 2003) and REALbasic 5.2.4.
Also, when using Valentina Studio, the following query
Select distinct shotIndex from take where sceneIndex = '1'
returns the same results as this:
Select shotIndex from take where sceneIndex = '1'
This strikes me a completely wrong, no? Am I misunderstanding some
subtleties of the
Distinct qualifier?
--
James Milne
More information about the Valentina
mailing list