SQLite Forum

JOIN query behaves differently with/without trailing ON?
Login

JOIN query behaves differently with/without trailing ON?

(1) By skywalk on 2020-04-06 21:45:22 [source]

I have racked my brain to understand why the newish SQLite3.dll behaves differently for this query?
I only want to retrieve 1 row from this table.
2   12000.0   31.2      26.12    -12.11   -12.22   1.12  
If I comment out the ON clause, it works.
But, I never had to do that in earlier versions of SQLite3.dll.

Please advise why I see 3 rows now?

I also confirmed this behavior manually with DB Browser For SQLite.
Older versions yield only 1 row.
Newest version yields 3 rows! :(

-- Create SQLite database --
CREATE TABLE TD(`n(id)` INTEGER NOT NULL PRIMARY KEY, `TI(id)` INTEGER DEFAULT -999, `DieSN` TEXT DEFAULT '-999', `Die(n)` INTEGER DEFAULT -999, `Die(wm)` DOUBLE DEFAULT -999, `DieX(um)` INTEGER DEFAULT -999, `DieY(um)` INTEGER DEFAULT -999, `DieZ(um)` INTEGER DEFAULT -999, `DieM(mm)` DOUBLE DEFAULT -999, `DieA(°)` DOUBLE DEFAULT -999, `Bin(n)` INTEGER DEFAULT -999, `TT(s)` DOUBLE DEFAULT -999, `TTp(s)` DOUBLE DEFAULT -999, `n(n)` INTEGER DEFAULT -999, `TPG(n)` INTEGER DEFAULT -999, `T(C)` DOUBLE DEFAULT -999, `Vdd(V)` DOUBLE DEFAULT -999, `Idd(A)` DOUBLE DEFAULT -999, `RFPath` TEXT DEFAULT '-999', `Pi(dBm)` DOUBLE DEFAULT -999, `F(MHz)` DOUBLE DEFAULT -999, `S21(dB)` DOUBLE DEFAULT -999, `S11(dB)` DOUBLE DEFAULT -999, `S22(dB)` DOUBLE DEFAULT -999, `NF(dB)` DOUBLE DEFAULT -999);
-- INSERT DATA --
INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,1,-999, -999, -999, -999, -999, -999);
INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,2,0.0206,6000, 26.06, -6.11, -6.22,-999);
INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,2,0.0212,12000,26.12,-12.11,-12.22,-999);
INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,2,0.0218,18000,26.18,-18.11,-18.22,-999);
INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,3,0.0306,6000, -999, -999, -999, 1.06);
INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,3,0.0312,12000,-999, -999, -999, 1.12);
INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,3,0.0318,18000,-999, -999, -999, 1.18);
-- 7 TD rows inserted.
-- JOIN + ON Query --
SELECT r1.`F(MHz)` AS `F(MHz)`,r2.`Idd(A)`*1e3 AS `Idd(mA)`,
r1.`S21(dB)` AS `S21(dB)`,r1.`S11(dB)` AS `S11(dB)`,r1.`S22(dB)` AS `S22(dB)`,
r2.`NF(dB)` AS `NF(dB)`
FROM
(SELECT `F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)` FROM TD WHERE
`TI(id)` = 1 AND
`Die(n)` = 1 AND
`F(MHz)` = 12000 AND
`TPG(n)` = 2) AS r1
JOIN
(SELECT `Idd(A)`,`F(MHz)`,`NF(dB)` FROM TD WHERE
`TI(id)` = 1 AND
`Die(n)` = 1 AND
`F(MHz)` = 12000 AND
`TPG(n)` = 3) AS r2
ON r1.`F(MHz)` = r2.`F(MHz)`

-- JOIN + ON Query Results --
6000.0    31.2      26.06    -6.11    -6.22    1.12     
12000.0   31.2      26.12    -12.11   -12.22   1.12     
18000.0   31.2      26.18    -18.11   -18.22   1.12

(2) By Keith Medcalf (kmedcalf) on 2020-04-06 21:56:04 in reply to 1 [link] [source]

This has already been fixed on trunk. Probably the same constant propagation bug reported earlier.

QLite version 3.32.0 2020-04-06 19:27:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE TD(`n(id)` INTEGER NOT NULL PRIMARY KEY, `TI(id)` INTEGER DEFAULT -999, `DieSN` TEXT DEFAULT '-999', `Die(n)` INTEGER DEFAULT -999, `Die(wm)` DOUBLE DEFAULT -999, `DieX(um)` INTEGER DEFAULT -999, `DieY(um)` INTEGER DEFAULT -999, `DieZ(um)` INTEGER DEFAULT -999, `DieM(mm)` DOUBLE DEFAULT -999, `DieA(°)` DOUBLE DEFAULT -999, `Bin(n)` INTEGER DEFAULT -999, `TT(s)` DOUBLE DEFAULT -999, `TTp(s)` DOUBLE DEFAULT -999, `n(n)` INTEGER DEFAULT -999, `TPG(n)` INTEGER DEFAULT -999, `T(C)` DOUBLE DEFAULT -999, `Vdd(V)` DOUBLE DEFAULT -999, `Idd(A)` DOUBLE DEFAULT -999, `RFPath` TEXT DEFAULT '-999', `Pi(dBm)` DOUBLE DEFAULT -999, `F(MHz)` DOUBLE DEFAULT -999, `S21(dB)` DOUBLE DEFAULT -999, `S11(dB)` DOUBLE DEFAULT -999, `S22(dB)` DOUBLE DEFAULT -999, `NF(dB)` DOUBLE DEFAULT -999);
sqlite> -- INSERT DATA --
sqlite> INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,1,-999, -999, -999, -999, -999, -999);
sqlite> INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,2,0.0206,6000, 26.06, -6.11, -6.22,-999);
sqlite> INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,2,0.0212,12000,26.12,-12.11,-12.22,-999);
sqlite> INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,2,0.0218,18000,26.18,-18.11,-18.22,-999);
sqlite> INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,3,0.0306,6000, -999, -999, -999, 1.06);
sqlite> INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,3,0.0312,12000,-999, -999, -999, 1.12);
sqlite> INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,3,0.0318,18000,-999, -999, -999, 1.18);
sqlite> -- 7 TD rows inserted.
sqlite> -- JOIN + ON Query --
sqlite> SELECT r1.`F(MHz)` AS `F(MHz)`,r2.`Idd(A)`*1e3 AS `Idd(mA)`,
   ...> r1.`S21(dB)` AS `S21(dB)`,r1.`S11(dB)` AS `S11(dB)`,r1.`S22(dB)` AS `S22(dB)`,
   ...> r2.`NF(dB)` AS `NF(dB)`
   ...> FROM
   ...> (SELECT `F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)` FROM TD WHERE
   ...> `TI(id)` = 1 AND
   ...> `Die(n)` = 1 AND
   ...> `F(MHz)` = 12000 AND
   ...> `TPG(n)` = 2) AS r1
   ...> JOIN
   ...> (SELECT `Idd(A)`,`F(MHz)`,`NF(dB)` FROM TD WHERE
   ...> `TI(id)` = 1 AND
   ...> `Die(n)` = 1 AND
   ...> `F(MHz)` = 12000 AND
   ...> `TPG(n)` = 3) AS r2
   ...> ON r1.`F(MHz)` = r2.`F(MHz)`
   ...>
   ...> -- JOIN + ON Query Results --
   ...> ;
12000.0|31.2|26.12|-12.11|-12.22|1.12

(3) By skywalk on 2020-04-06 21:59:07 in reply to 2 [link] [source]

Whew!! Thanks for the quick reply. I cannot remember? Is the download page compiled from latest code or must I build from source?

Thanks for SQLite!

(4) By Richard Hipp (drh) on 2020-04-06 22:09:42 in reply to 3 [link] [source]

You need to build from source.

  1. Download the Pre-release Snapshot
  2. Unarchive it
  3. Unix → ./configure --enable-all && make
  4. Windows → nmake /f makefile.msc

(5.1) By skywalk on 2020-04-06 22:54:03 edited from 5.0 in reply to 4 [link] [source]

Yes thanks, this is coming back to me.
Now my sqlite3.dll has no exports. 
How to enable that in the Makefile?

# These are additional compiler options used for the core library.
#
!IFNDEF CORE_COMPILE_OPTS
!IF $(DYNAMIC_SHELL)!=0 || $(FOR_WIN10)!=0
CORE_COMPILE_OPTS = $(CORE_CCONV_OPTS) -DSQLITE_API=__declspec(dllexport)
!ELSE
CORE_COMPILE_OPTS = $(CORE_CCONV_OPTS)
!ENDIF
!ENDIF

Bypassing nmake, I get a working dll with the following cmd:
c:\sqlite-snapshot-202004061816>cl sqlite3.c -DSQLITE_THREADSAFE -DSQLITE_DQS=0 -DSQLITE_MAX_ATTACHED=125 -DSQLITE_API=__declspec(dllexport) -link -dll -out:sqlite3.dll

(6) By Keith Medcalf (kmedcalf) on 2020-04-07 02:13:12 in reply to 5.1 [link] [source]

That works.

The makefile uses dumpbin.exe (from the Windows SDK or Build Tools) and tclsh.exe (from your TCL installation) ... both of which must be in the path ... in order to build the sqlite3.def file that is used in the linkage step to create the exports:

sqlite3.def:    libsqlite3.lib
    echo EXPORTS > sqlite3.def
    dumpbin /all libsqlite3.lib \
        | $(TCLSH_CMD) $(TOP)\tool\replace.tcl include "^\s+1 _?(sqlite3(?:session|changeset|changegroup|rebaser|rbu)?_[^@]*)(?:@\d+)?$$" \1 \
        | sort >> sqlite3.def

So at a command prompt you need to be able to type the following:

sort
dumpbin
tclsh

and must not get the response that any of those are is not recognized as an internal or external command, operable program or batch file