You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Ян Программист <we...@gmail.com> on 2010/04/12 16:14:11 UTC
SQL again
I have finally found out that my understanding of JOIN in MySQL is not yet
other one that should be in Derby:
select SYS.SYSCONSTRAINTS.*, SYS.SYSCOLUMNS.COLUMNNAME from
SYS.SYSCONSTRAINTS right outer join SYS.SYSCOLUMNS on
SYS.SYSCONSTRAINTS.TABLEID = SYS.SYSCOLUMNS.REFERENCEID;
I wanted to force L-/R- JOINs to properly be interpreted. And got this:
CONSTRAINTID |TABLEID
|CONSTRAINTNAME
|TYPE |SCHEMAID
|STA& |REFERENCEC&|COLUMNNAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
... ... ...
NULL |NULL
|NULL
|NULL
|NULL |NUL L|NULL |ISDEF
cbf5809d-0127-df6f-bee8-000000547e10|2ba7c09b-0127-df6f-bee8-000000547e10|SQL100409011100670
|P |80000000-00d2-b38f-4cda-000a0a412c00|E |1
|ID
cbf5809d-0127-df6f-bee8-0000005|2ba7c09b-0127-df6f-bee8-000000547e10|SQL100409011100670
|P |80000000-00d2-b38f-4cda-000a0a412c00|E |1
|NAME
e50d80a4-0127-df6f-bee8-000000547e102-0127-df6f-bee8-000000547e10|SQL100409011755320
|P |80000000-00d2-b38f-4cda-000a0a412c00|E |0
|RECORD_ID
4d3680a5-0127-df6f-bee8-000000547e10|94bc40af6f-bee8-000000547e10|SQL100409011755321
|F |80000000-00d2-b38f-4cda-000a0a412c00|E |0
|RECORD_ID
e50d80a4-0127-df6f-bee8-000000547e10|94bc40a2-0127-d-000000547e10|SQL100409011755320
|P |80000000-00d2-b38f-4cda-000a0a412c00|E |0
|SHUTDOWN_TYPE
4d3680a5-0127-df6f-bee8-000000547e10|94bc40a2-0127-df6f-bee847e10|SQL100409011755321
|F |80000000-00d2-b38f-4cda-000a0a412c00|E |0
|SHUTDOWN_TYPE
e50d80a4-0127-df6f-bee8-000000547e10|94bc40a2-0127-df6f-bee8-0000005L100409011755320
|P |80000000-00d2-b38f-4cda-000a0a412c00|E |0
|ADMIN_RECORDS
4d3680a5-0127-df6f-bee8-000000547e10|94bc40a2-0127-df6f-bee8-000000547e10|SQ11755321
|F |80000000-00d2-b38f-4cda-000a0a412c00|E |0
|ADMIN_RECORDS
NULL |NULL
|NULL
|NULL|NULL
|NULL|NULL |PARAM1
Hence NULLs all over the places before and after some main records. So
R-JOINs works as expected.
I tried to correctly collect column-per-table pairs;
select SYS.SYSCOLUMNS.COLUMNNAME, SYS.SYSTABLES.TABLENAME from
SYS.SYSCOLUMNS, SYS.SYSTABLES, SYS.SYSCONSTRAINTS where
SYS.SYSTABLES.TABLEID = SYS.SYSCOLUMNS.REFERENCEID and
SYS.SYSTABLES.TABLENAME like 'SERVER%' and not
SYS.SYSTABLES.TABLEID=SYS.SYSCONSTRAINTS.TABLEID;
COLUMNNAME
|TABLENAME
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ADMIN_RECORDS
|SERVER_SHUTDOWN_LOG
RECORD_ID
|SERVER_SHUTDOWN_LOG
SHUTDOWN_TYPE
|SERVER_SHUTDOWN_LOG
ID
|SERVER_SHUTDOWN_TYPE
NAME
|SERVER_SHUTDOWN_TYPE
ID
|SERVER_SHUTDOWN_TYPE
NAME
|SERVER_SHUTDOWN_TYPE
That worked fine. No duplicates...
That two tables appear in SYSCONSTRAINTS:
select SYS.SYSCONSTRAINTS.TABLEID from SYS.SYSCONSTRAINTS;
TABLEID
------------------------------------
2ba7c09b-0127-df6f-bee8-000000547e10
94bc40a2-0127-df6f-bee8-000000547e10
94bc40a2-0127-df6f-bee8-000000547e10
So:
SELECT CONSTRAINTNAME, COLUMNNAME FROM SYS.SYSTABLES t, SYS.SYSCOLUMNS col,
SYS.SYSCONSTRAINTS cons, SYS.SYSCHECKS checks WHERE t.TABLENAME like
'SERVER%' AND t.TABLEID = col.REFERENCEID AND t.TABLEID = cons.TABLEID AND
cons.CONSTRAINTID = checks.CONSTRAINTID ORDER BY CONSTRAINTNAME;
CONSTRAINTNAME
|COLUMNNAME
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Выбрано строк: 0
ij> SELECT CONSTRAINTNAME, COLUMNNAME FROM SYS.SYSTABLES t, SYS.SYSCOLUMNS
col, SYS.SYSCONSTRAINTS cons, SYS.SYSCHECKS checks where t.TABLEID =
col.REFERENCEID AND t.TABLEID = cons.TABLEID AND cons.CONSTRAINTID =
checks.CONSTRAINTID ORDER BY CONSTRAINTNAME;
CONSTRAINTNAME
|COLUMNNAME
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Выбрано строк: 0
But again - I do not see rows because constraints not appear to be mapped to
columns. John