Hi,

I have a table defined for the IRIS data set as:

CREATE TABLE IRIS
(SEPAL_LENGTH FLOAT,
SEPAL_WIDTH FLOAT,
PETAL_LENGTH FLOAT,
PETAL_WIDTH FLOAT,
DECISION_CLASS VARCHAR(17)
)ENGINE=BRIGHTHOUSE;

when I do the select * on the iris table, the results are:

|      5.1 |      3.5 |      1.4 |      0.2 | Iris-setosa   |

and now when i define a view on this table as:

CREATE VIEW iris_setosa AS
SELECT * FROM iris
WHERE decision_class = “Iris-setosa”;

and when I select * on the newly created view - iris_setosa, the values are shown as

| 5.0999999046326 |        3.5 | 1.3999999761581 | 0.20000000298023 | Iris-setosa   |

As we can see the precision in the view is changed from the one in the original table. This is causing me issues in comparing the data. Please advise.

Also I want to know what is the best way of doing the INTERSECT in Infobright. Thanks.

Hi,

Please try to define columns as DOUBLE, as the internal representation (e.g. in views) of floating point numbers is double. If the data should be exact (e.g. for comparisons), it is much better to use DECIMAL(X,Y). Note that FLOAT is an approximate numeric type and it is risky to use ‘=’ operator on it.

Regards,

Thanks Jakub. I will make use of Decimal. Please comment on INTERSECT in Infobright.

Hi,

Intersects may be implemented by joins. You will need a unique identifier (id) on both tables, and then:

This query will calculate the size of intersection of both subsets.

If it is not exactly what you need, please write more about the problem itself.

Regards,

Thanks Jakub,

Here is what I am trying to do. I am confused why the last query for comparison does not work although I now use the decimal(x,y) to define the floating numbers in the table(iris), and subsequently the views defined for each decision class.

mysql> select * from iris_setosa where sepal_length=5.7;
+———————+——————-+———————+——————-+————————+
| SEPAL_LENGTH | SEPAL_WIDTH | PETAL_LENGTH | PETAL_WIDTH | DECISION_CLASS |
+———————+——————-+———————+——————-+————————+
|      5.7 |      4.4 |      1.5 |      0.4 | Iris-setosa   |
|      5.7 |      3.8 |      1.7 |      0.3 | Iris-setosa   |
+———————+——————-+———————+——————-+————————+
2 rows in set (0.08 sec)

mysql> select * from iris_versicolor where sepal_length=5.7;
+———————+——————-+———————+——————-+————————-+
| SEPAL_LENGTH | SEPAL_WIDTH | PETAL_LENGTH | PETAL_WIDTH | DECISION_CLASS |
+———————+——————-+———————+——————-+————————-+
|      5.7 |      2.8 |      4.5 |      1.3 | Iris-versicolor |
|      5.7 |      2.6 |      3.5 |      1.0 | Iris-versicolor |
|      5.7 |      3.0 |      4.2 |      1.2 | Iris-versicolor |
|      5.7 |      2.9 |      4.2 |      1.3 | Iris-versicolor |
|      5.7 |      2.8 |      4.1 |      1.3 | Iris-versicolor |
+———————+——————-+———————+——————-+————————-+
5 rows in set (0.02 sec)

mysql> select * from iris_setosa,iris_versicolor where iris_setosa.sepal_length=
iris_versicolor.sepal_length;
Empty set (0.02 sec)

Hi,

I’m confused too.
Could you, please, attach the script defining table and views, as well as the data itself? (I suppose this one is not confidential.) We will try to reproduce and debug the problem.

Regards,

Hi Jakub,

I have pasted the sql script for the table and views and also attached the iris data set. The script also contain the select statements which are returning empty sets. Thanks.

CREATE TABLE IRIS
(SEPAL_LENGTH DECIMAL(2,1),
SEPAL_WIDTH DECIMAL(2,1),
PETAL_LENGTH DECIMAL(2,1),
PETAL_WIDTH DECIMAL(2,1),
DECISION_CLASS VARCHAR(17)
)ENGINE=BRIGHTHOUSE;

CREATE VIEW iris_setosa AS
SELECT * FROM iris
WHERE decision_class = “Iris-setosa”;

CREATE VIEW iris_virginica AS
SELECT * FROM iris
WHERE decision_class = ‘Iris-virginica’;

CREATE VIEW iris_versicolor AS
SELECT * FROM iris
WHERE decision_class = ‘Iris-versicolor’;

SELECT iris_setosa.sepal_length
FROM iris_setosa
INNER JOIN iris_versicolor
USING (sepal_length)

SELECT COUNT(*) FROM iris_setosa, iris_versicolor WHERE iris_setosa.sepal_length=iris_versicolor.sepal_length

Hi Shahid,

Thanks for test data. I have repeated your experiments but the join gives me 59 rows not an empty set. How do you load data? Can you give the exact statement?

I tried the following:

What version of ICE do you use?

Thanks,
Piotr

Hi Piotr,

I have loaded the data exactly same as yours. I upgraded the ICE to version 3.3.1 ( Please see below for starting the command line).

C:\Program Files (x86)\Infobright>“C:\Program Files (x86)\Infobright\bin\mysql.e
xe”—defaults-file=“C:\\Program Files (x86)\\Infobright\\my-ib.ini” -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.40-log build number (revision)=IB_3.3.1_r6997_7018(ice)

I am very confused. Please let me know what is wrong that I do. I am using the same SQL what I posted but getting the count as Zero.

Hi Shahid,

I managed to reproduce the problem. Indeed, there is a problem in resolving view name. The issue has been addressed and fixed some time ago and will be included in the next release (3.4.x) of ICE - 3.3.2 beta doesn’t have this fix. And as far as I know next release is planned soon, but I guess PM should give some more details.

As a workaround, I would suggest not using views but to expand their definition into main query. In this case that would be:

Hope it helps,

Piotr

Thanks Piotr. You suggestion did help. However I am waiting for version 3.4 to be released soon. In the meantime, I have another question. Are stored procedures are possible to be created in Infobright. I am asking that because I want bunch of select statements to be run and then get the result. Thanks.

Yes, Infobright supports the full MySQL stored procedure language.

Hope this helps,
Geoffrey

Hi,

I was doing a test using the data set using the Heart_1.txt( that contains all the rows with decision class = 1, total records 150) and the Heart_2.txt ( that contains all the rows with decision class = 2, total records 120), both are attached below and did a simple query of join between these two tables on one attribute - Age and the results comes back with 506 rows which is more than the number of rows in the two tables together. I have attached the results(HeartResults.txt) and the select * from the two tables.

The tables are created as following:

CREATE TABLE HEART_2
(AGE DECIMAL(3,1),
SEX DECIMAL(2,1),
PAIN DECIMAL(2,1),
BP DECIMAL(4,1),
CHSTL DECIMAL(4,1),
BS DECIMAL(2,1),
ECG DECIMAL(2,1),
HR DECIMAL(2,1),
EXER DECIMAL(4,1),
DEPRESS DECIMAL(2,1),
VESSELS DECIMAL(2,1),
SLOPE DECIMAL(2,1),
THAL DECIMAL(2,1),
DECISION_CLASS VARCHAR(17)
)ENGINE=BRIGHTHOUSE;

Please explain this behaviour.

Hello Shahid,

The result of join is correct. In general, a join is Cartesian product of rows from both tables. In your case, if there is no WHERE part, the result would contain 120x150=18000 rows. You added a condition that limited the result set to 506 rows. Nothing strange in it.

Thanks,
Piotr

I am using the Sotred Procedure in the infobright and inside the stored procedure I am using a INNER JOIN in the following prepared statement

set @s = “select count(*) from iris_setosa inner join iris_versicolor using (?)”;
PREPARE stmt1 FROM @s;

I am getting an error when I run them:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘?)’ at line 1

Please help. Thanks.

Hi,

the (?) needs a column name (join key) to be specified. With the “using” syntax, the column needs to have the same name in both tables. For example:

Geoffrey